AryaWu/sqlite
0
1# 2022-05-132#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11#12# This file implements tests for JOINs that use Bloom filters.13#14# The test case output is (mostly) all generated by PostgreSQL 14. This15# test module was created as follows:16#17# 1. Run a TCL script (included at the bottom of this file) that18# generates an input script for "psql" that will run man19# diverse tests on joins.20#21# 2. Run the script from step (1) through psql and collect the22# output.23#24# 3. Make a few minor global search-and-replace operations to convert25# the psql output into a form suitable for this test module.26#27# 4. Add this header, and the script content at the footer.28#29set testdir [file dirname $argv0]30source $testdir/tester.tcl31db nullvalue -32db eval {33 CREATE TABLE t1(a INT);34 INSERT INTO t1 VALUES(1),(NULL);35 CREATE TABLE t2(b INT);36 INSERT INTO t2 VALUES(2),(NULL);37}38do_execsql_test joinE-1 {39 SELECT a, b40 FROM t1 INNER JOIN t2 ON true41 ORDER BY coalesce(a,b,3);42} {43 1 244 1 -45 - 246 - -47}48do_execsql_test joinE-2 {49 SELECT a, b50 FROM t1 INNER JOIN t2 ON true WHERE a IS NULL51 ORDER BY coalesce(a,b,3);52} {53 - 254 - -55}56do_execsql_test joinE-3 {57 SELECT a, b58 FROM t1 INNER JOIN t2 ON a IS NULL59 ORDER BY coalesce(a,b,3);60} {61 - 262 - -63}64do_execsql_test joinE-4 {65 SELECT a, b66 FROM t1 INNER JOIN t2 ON true WHERE b IS NULL67 ORDER BY coalesce(a,b,3);68} {69 1 -70 - -71}72do_execsql_test joinE-5 {73 SELECT a, b74 FROM t1 INNER JOIN t2 ON b IS NULL75 ORDER BY coalesce(a,b,3);76} {77 1 -78 - -79}80do_execsql_test joinE-6 {81 SELECT a, b82 FROM t1 LEFT JOIN t2 ON true83 ORDER BY coalesce(a,b,3);84} {85 1 286 1 -87 - 288 - -89}90do_execsql_test joinE-7 {91 SELECT a, b92 FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL93 ORDER BY coalesce(a,b,3);94} {95 - 296 - -97}98do_execsql_test joinE-8 {99 SELECT a, b100 FROM t1 LEFT JOIN t2 ON a IS NULL101 ORDER BY coalesce(a,b,3);102} {103 1 -104 - 2105 - -106}107do_execsql_test joinE-9 {108 SELECT a, b109 FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL110 ORDER BY coalesce(a,b,3);111} {112 1 -113 - -114}115do_execsql_test joinE-10 {116 SELECT a, b117 FROM t1 LEFT JOIN t2 ON b IS NULL118 ORDER BY coalesce(a,b,3);119} {120 1 -121 - -122}123do_execsql_test joinE-11 {124 SELECT a, b125 FROM t1 RIGHT JOIN t2 ON true126 ORDER BY coalesce(a,b,3);127} {128 1 2129 1 -130 - 2131 - -132}133do_execsql_test joinE-12 {134 SELECT a, b135 FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL136 ORDER BY coalesce(a,b,3);137} {138 - 2139 - -140}141do_execsql_test joinE-13 {142 SELECT a, b143 FROM t1 RIGHT JOIN t2 ON a IS NULL144 ORDER BY coalesce(a,b,3);145} {146 - 2147 - -148}149do_execsql_test joinE-14 {150 SELECT a, b151 FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL152 ORDER BY coalesce(a,b,3);153} {154 1 -155 - -156}157do_execsql_test joinE-15 {158 SELECT a, b159 FROM t1 RIGHT JOIN t2 ON b IS NULL160 ORDER BY coalesce(a,b,3);161} {162 1 -163 - 2164 - -165}166do_execsql_test joinE-16 {167 SELECT a, b168 FROM t1 FULL JOIN t2 ON true169 ORDER BY coalesce(a,b,3);170} {171 1 2172 1 -173 - 2174 - -175}176do_execsql_test joinE-17 {177 SELECT a, b178 FROM t1 FULL JOIN t2 ON true WHERE a IS NULL179 ORDER BY coalesce(a,b,3);180} {181 - 2182 - -183}184 185# PG-14 is unable to perform this join. It says: FULL JOIN is only186# supported with merge-joinable or hash-joinable join conditions187# 188# do_execsql_test joinE-18 {189# SELECT a, b190# FROM t1 FULL JOIN t2 ON a IS NULL191# ORDER BY coalesce(a,b,3);192# } {193# }194 195do_execsql_test joinE-19 {196 SELECT a, b197 FROM t1 FULL JOIN t2 ON true WHERE b IS NULL198 ORDER BY coalesce(a,b,3);199} {200 1 -201 - -202}203 204# PG-14 is unable to perform this join. It says: FULL JOIN is only205# supported with merge-joinable or hash-joinable join conditions206# 207# do_execsql_test joinE-20 {208# SELECT a, b209# FROM t1 FULL JOIN t2 ON b IS NULL210# ORDER BY coalesce(a,b,3);211# } {212# }213 214db eval {215 DELETE FROM t1;216 INSERT INTO t1 VALUES(1);217 DELETE FROM t2;218 INSERT INTO t2 VALUES(NULL);219}220 221do_execsql_test joinE-21 {222 SELECT a, b223 FROM t1 INNER JOIN t2 ON true224 ORDER BY coalesce(a,b,3);225} {226 1 -227}228do_execsql_test joinE-22 {229 SELECT a, b230 FROM t1 INNER JOIN t2 ON true WHERE a IS NULL231 ORDER BY coalesce(a,b,3);232} {233}234do_execsql_test joinE-23 {235 SELECT a, b236 FROM t1 INNER JOIN t2 ON a IS NULL237 ORDER BY coalesce(a,b,3);238} {239}240do_execsql_test joinE-24 {241 SELECT a, b242 FROM t1 INNER JOIN t2 ON true WHERE b IS NULL243 ORDER BY coalesce(a,b,3);244} {245 1 -246}247do_execsql_test joinE-25 {248 SELECT a, b249 FROM t1 INNER JOIN t2 ON b IS NULL250 ORDER BY coalesce(a,b,3);251} {252 1 -253}254do_execsql_test joinE-26 {255 SELECT a, b256 FROM t1 LEFT JOIN t2 ON true257 ORDER BY coalesce(a,b,3);258} {259 1 -260}261do_execsql_test joinE-27 {262 SELECT a, b263 FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL264 ORDER BY coalesce(a,b,3);265} {266}267do_execsql_test joinE-28 {268 SELECT a, b269 FROM t1 LEFT JOIN t2 ON a IS NULL270 ORDER BY coalesce(a,b,3);271} {272 1 -273}274do_execsql_test joinE-29 {275 SELECT a, b276 FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL277 ORDER BY coalesce(a,b,3);278} {279 1 -280}281do_execsql_test joinE-30 {282 SELECT a, b283 FROM t1 LEFT JOIN t2 ON b IS NULL284 ORDER BY coalesce(a,b,3);285} {286 1 -287}288do_execsql_test joinE-31 {289 SELECT a, b290 FROM t1 RIGHT JOIN t2 ON true291 ORDER BY coalesce(a,b,3);292} {293 1 -294}295 296do_execsql_test joinE-32 {297 SELECT a, b298 FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL299 ORDER BY coalesce(a,b,3);300} {301}302 303do_execsql_test joinE-33 {304 SELECT a, b305 FROM t1 RIGHT JOIN t2 ON a IS NULL306 ORDER BY coalesce(a,b,3);307} {308 - -309}310do_execsql_test joinE-34 {311 SELECT a, b312 FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL313 ORDER BY coalesce(a,b,3);314} {315 1 -316}317do_execsql_test joinE-35 {318 SELECT a, b319 FROM t1 RIGHT JOIN t2 ON b IS NULL320 ORDER BY coalesce(a,b,3);321} {322 1 -323}324do_execsql_test joinE-36 {325 SELECT a, b326 FROM t1 FULL JOIN t2 ON true327 ORDER BY coalesce(a,b,3);328} {329 1 -330}331do_execsql_test joinE-37 {332 SELECT a, b333 FROM t1 FULL JOIN t2 ON true WHERE a IS NULL334 ORDER BY coalesce(a,b,3);335} {336}337 338# PG-14 is unable339#340# do_execsql_test joinE-38 {341# SELECT a, b342# FROM t1 FULL JOIN t2 ON a IS NULL343# ORDER BY coalesce(a,b,3);344# } {345# }346 347do_execsql_test joinE-39 {348 SELECT a, b349 FROM t1 FULL JOIN t2 ON true WHERE b IS NULL350 ORDER BY coalesce(a,b,3);351} {352 1 -353}354 355# PG-14 is unable356# do_execsql_test joinE-40 {357# SELECT a, b358# FROM t1 FULL JOIN t2 ON b IS NULL359# ORDER BY coalesce(a,b,3);360# } {361# }362 363finish_test364 365##############################################################################366# This is the PG-14 test script generator367#368# puts "369# \\pset border off370# \\pset tuples_only on371# \\pset null -372# 373# DROP TABLE IF EXISTS t1;374# DROP TABLE IF EXISTS t2;375# CREATE TABLE t1(a INT);376# INSERT INTO t1 VALUES(1),(NULL);377# CREATE TABLE t2(b INT);378# INSERT INTO t2 VALUES(2),(NULL);379# "380# 381# proc echo {prefix txt} {382# regsub -all {\n} $txt \n$prefix txt383# puts "$prefix$txt"384# }385# 386# set n 0387# set k 0388# foreach j1 {INNER LEFT RIGHT FULL} {389# foreach on1 {390# true391# {true WHERE a IS NULL}392# {a IS NULL}393# {true WHERE b IS NULL}394# {b IS NULL}395# } {396# 397# incr n398# incr k399# set q1 ""400# append q1 "SELECT a, b\n"401# append q1 " FROM t1 $j1 JOIN t2 ON $on1\n"402# append q1 " ORDER BY coalesce(a,b,3);"403# 404# echo "\\qecho " "do_execsql_test joinE-$n \{"405# echo "\\qecho X " $q1406# echo "\\qecho " "\} \{"407# puts $q1408# echo "\\qecho " "\}"409# 410# }411# }412# 413# puts "414# DELETE FROM t1;415# INSERT INTO t1 VALUES(1);416# DELETE FROM t2;417# INSERT INTO t2 VALUES(NULL);418# "419# 420# foreach j1 {INNER LEFT RIGHT FULL} {421# foreach on1 {422# true423# {true WHERE a IS NULL}424# {a IS NULL}425# {true WHERE b IS NULL}426# {b IS NULL}427# } {428# 429# incr n430# incr k431# set q1 ""432# append q1 "SELECT a, b\n"433# append q1 " FROM t1 $j1 JOIN t2 ON $on1\n"434# append q1 " ORDER BY coalesce(a,b,3);"435# 436# echo "\\qecho " "do_execsql_test joinE-$n \{"437# echo "\\qecho X " $q1438# echo "\\qecho " "\} \{"439# puts $q1440# echo "\\qecho " "\}"441# 442# }443# }444