AryaWu/sqlite
0
1# 2022-05-312#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 JOINs13#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#29# A few extra tests that were not generated from postgresql output are30# added at the end.31#32set testdir [file dirname $argv0]33source $testdir/tester.tcl34db nullvalue -35db eval {36 CREATE TABLE t1(x INT);37 CREATE TABLE t2(y INT);38 CREATE TABLE t3(z INT);39 CREATE TABLE t4(w INT);40 INSERT INTO t1 VALUES(10);41 INSERT INTO t3 VALUES(20),(30);42 INSERT INTO t4 VALUES(50);43}44do_execsql_test joinF-1 {45 SELECT *46 FROM t1 INNER JOIN t2 ON true47 INNER JOIN t3 ON t2.y IS NOT NULL48 INNER JOIN t4 ON true49 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);50} {51}52do_execsql_test joinF-2 {53 SELECT *54 FROM t1 INNER JOIN t2 ON true55 INNER JOIN t3 ON t2.y IS NOT NULL56 INNER JOIN t4 ON true57 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)58 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);59} {60}61do_execsql_test joinF-3 {62 SELECT *63 FROM t1 INNER JOIN t2 ON true64 INNER JOIN t3 ON t2.y IS NOT NULL65 LEFT JOIN t4 ON true66 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);67} {68}69do_execsql_test joinF-4 {70 SELECT *71 FROM t1 INNER JOIN t2 ON true72 INNER JOIN t3 ON t2.y IS NOT NULL73 LEFT JOIN t4 ON true74 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)75 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);76} {77}78do_execsql_test joinF-5 {79 SELECT *80 FROM t1 INNER JOIN t2 ON true81 INNER JOIN t3 ON t2.y IS NOT NULL82 RIGHT JOIN t4 ON true83 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);84} {85 - - - 5086}87do_execsql_test joinF-6 {88 SELECT *89 FROM t1 INNER JOIN t2 ON true90 INNER JOIN t3 ON t2.y IS NOT NULL91 RIGHT JOIN t4 ON true92 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)93 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);94} {95}96do_execsql_test joinF-7 {97 SELECT *98 FROM t1 INNER JOIN t2 ON true99 LEFT JOIN t3 ON t2.y IS NOT NULL100 INNER JOIN t4 ON true101 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);102} {103}104do_execsql_test joinF-8 {105 SELECT *106 FROM t1 INNER JOIN t2 ON true107 LEFT JOIN t3 ON t2.y IS NOT NULL108 INNER JOIN t4 ON true109 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)110 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);111} {112}113do_execsql_test joinF-9 {114 SELECT *115 FROM t1 INNER JOIN t2 ON true116 LEFT JOIN t3 ON t2.y IS NOT NULL117 LEFT JOIN t4 ON true118 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);119} {120}121do_execsql_test joinF-10 {122 SELECT *123 FROM t1 INNER JOIN t2 ON true124 LEFT JOIN t3 ON t2.y IS NOT NULL125 LEFT JOIN t4 ON true126 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)127 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);128} {129}130do_execsql_test joinF-11 {131 SELECT *132 FROM t1 INNER JOIN t2 ON true133 LEFT JOIN t3 ON t2.y IS NOT NULL134 RIGHT JOIN t4 ON true135 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);136} {137 - - - 50138}139do_execsql_test joinF-12 {140 SELECT *141 FROM t1 INNER JOIN t2 ON true142 LEFT JOIN t3 ON t2.y IS NOT NULL143 RIGHT JOIN t4 ON true144 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)145 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);146} {147}148do_execsql_test joinF-13 {149 SELECT *150 FROM t1 INNER JOIN t2 ON true151 RIGHT JOIN t3 ON t2.y IS NOT NULL152 INNER JOIN t4 ON true153 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);154} {155 - - 20 50156 - - 30 50157}158do_execsql_test joinF-14 {159 SELECT *160 FROM t1 INNER JOIN t2 ON true161 RIGHT JOIN t3 ON t2.y IS NOT NULL162 INNER JOIN t4 ON true163 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)164 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);165} {166 - - 20 50167 - - 30 50168}169do_execsql_test joinF-15 {170 SELECT *171 FROM t1 INNER JOIN t2 ON true172 RIGHT JOIN t3 ON t2.y IS NOT NULL173 LEFT JOIN t4 ON true174 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);175} {176 - - 20 50177 - - 30 50178}179do_execsql_test joinF-16 {180 SELECT *181 FROM t1 INNER JOIN t2 ON true182 RIGHT JOIN t3 ON t2.y IS NOT NULL183 LEFT JOIN t4 ON true184 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)185 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);186} {187 - - 20 50188 - - 30 50189}190do_execsql_test joinF-17 {191 SELECT *192 FROM t1 INNER JOIN t2 ON true193 RIGHT JOIN t3 ON t2.y IS NOT NULL194 RIGHT JOIN t4 ON true195 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);196} {197 - - 20 50198 - - 30 50199}200do_execsql_test joinF-18 {201 SELECT *202 FROM t1 INNER JOIN t2 ON true203 RIGHT JOIN t3 ON t2.y IS NOT NULL204 RIGHT JOIN t4 ON true205 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)206 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);207} {208 - - 20 50209 - - 30 50210}211do_execsql_test joinF-19 {212 SELECT *213 FROM t1 LEFT JOIN t2 ON true214 INNER JOIN t3 ON t2.y IS NOT NULL215 INNER JOIN t4 ON true216 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);217} {218}219do_execsql_test joinF-20 {220 SELECT *221 FROM t1 LEFT JOIN t2 ON true222 INNER JOIN t3 ON t2.y IS NOT NULL223 INNER JOIN t4 ON true224 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)225 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);226} {227}228do_execsql_test joinF-21 {229 SELECT *230 FROM t1 LEFT JOIN t2 ON true231 INNER JOIN t3 ON t2.y IS NOT NULL232 LEFT JOIN t4 ON true233 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);234} {235}236do_execsql_test joinF-22 {237 SELECT *238 FROM t1 LEFT JOIN t2 ON true239 INNER JOIN t3 ON t2.y IS NOT NULL240 LEFT JOIN t4 ON true241 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)242 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);243} {244}245do_execsql_test joinF-23 {246 SELECT *247 FROM t1 LEFT JOIN t2 ON true248 INNER JOIN t3 ON t2.y IS NOT NULL249 RIGHT JOIN t4 ON true250 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);251} {252 - - - 50253}254do_execsql_test joinF-24 {255 SELECT *256 FROM t1 LEFT JOIN t2 ON true257 INNER JOIN t3 ON t2.y IS NOT NULL258 RIGHT JOIN t4 ON true259 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)260 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);261} {262}263do_execsql_test joinF-25 {264 SELECT *265 FROM t1 LEFT JOIN t2 ON true266 LEFT JOIN t3 ON t2.y IS NOT NULL267 INNER JOIN t4 ON true268 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);269} {270 10 - - 50271}272do_execsql_test joinF-26 {273 SELECT *274 FROM t1 LEFT JOIN t2 ON true275 LEFT JOIN t3 ON t2.y IS NOT NULL276 INNER JOIN t4 ON true277 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)278 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);279} {280}281do_execsql_test joinF-27 {282 SELECT *283 FROM t1 LEFT JOIN t2 ON true284 LEFT JOIN t3 ON t2.y IS NOT NULL285 LEFT JOIN t4 ON true286 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);287} {288 10 - - 50289}290do_execsql_test joinF-28 {291 SELECT *292 FROM t1 LEFT JOIN t2 ON true293 LEFT JOIN t3 ON t2.y IS NOT NULL294 LEFT JOIN t4 ON true295 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)296 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);297} {298}299do_execsql_test joinF-29 {300 SELECT *301 FROM t1 LEFT JOIN t2 ON true302 LEFT JOIN t3 ON t2.y IS NOT NULL303 RIGHT JOIN t4 ON true304 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);305} {306 10 - - 50307}308do_execsql_test joinF-30 {309 SELECT *310 FROM t1 LEFT JOIN t2 ON true311 LEFT JOIN t3 ON t2.y IS NOT NULL312 RIGHT JOIN t4 ON true313 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)314 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);315} {316}317do_execsql_test joinF-31 {318 SELECT *319 FROM t1 LEFT JOIN t2 ON true320 RIGHT JOIN t3 ON t2.y IS NOT NULL321 INNER JOIN t4 ON true322 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);323} {324 - - 20 50325 - - 30 50326}327do_execsql_test joinF-32 {328 SELECT *329 FROM t1 LEFT JOIN t2 ON true330 RIGHT JOIN t3 ON t2.y IS NOT NULL331 INNER JOIN t4 ON true332 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)333 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);334} {335 - - 20 50336 - - 30 50337}338do_execsql_test joinF-33 {339 SELECT *340 FROM t1 LEFT JOIN t2 ON true341 RIGHT JOIN t3 ON t2.y IS NOT NULL342 LEFT JOIN t4 ON true343 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);344} {345 - - 20 50346 - - 30 50347}348do_execsql_test joinF-34 {349 SELECT *350 FROM t1 LEFT JOIN t2 ON true351 RIGHT JOIN t3 ON t2.y IS NOT NULL352 LEFT JOIN t4 ON true353 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)354 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);355} {356 - - 20 50357 - - 30 50358}359do_execsql_test joinF-35 {360 SELECT *361 FROM t1 LEFT JOIN t2 ON true362 RIGHT JOIN t3 ON t2.y IS NOT NULL363 RIGHT JOIN t4 ON true364 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);365} {366 - - 20 50367 - - 30 50368}369do_execsql_test joinF-36 {370 SELECT *371 FROM t1 LEFT JOIN t2 ON true372 RIGHT JOIN t3 ON t2.y IS NOT NULL373 RIGHT JOIN t4 ON true374 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)375 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);376} {377 - - 20 50378 - - 30 50379}380do_execsql_test joinF-37 {381 SELECT *382 FROM t1 RIGHT JOIN t2 ON true383 INNER JOIN t3 ON t2.y IS NOT NULL384 INNER JOIN t4 ON true385 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);386} {387}388do_execsql_test joinF-38 {389 SELECT *390 FROM t1 RIGHT JOIN t2 ON true391 INNER JOIN t3 ON t2.y IS NOT NULL392 INNER JOIN t4 ON true393 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)394 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);395} {396}397do_execsql_test joinF-39 {398 SELECT *399 FROM t1 RIGHT JOIN t2 ON true400 INNER JOIN t3 ON t2.y IS NOT NULL401 LEFT JOIN t4 ON true402 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);403} {404}405do_execsql_test joinF-40 {406 SELECT *407 FROM t1 RIGHT JOIN t2 ON true408 INNER JOIN t3 ON t2.y IS NOT NULL409 LEFT JOIN t4 ON true410 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)411 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);412} {413}414do_execsql_test joinF-41 {415 SELECT *416 FROM t1 RIGHT JOIN t2 ON true417 INNER JOIN t3 ON t2.y IS NOT NULL418 RIGHT JOIN t4 ON true419 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);420} {421 - - - 50422}423do_execsql_test joinF-42 {424 SELECT *425 FROM t1 RIGHT JOIN t2 ON true426 INNER JOIN t3 ON t2.y IS NOT NULL427 RIGHT JOIN t4 ON true428 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)429 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);430} {431}432do_execsql_test joinF-43 {433 SELECT *434 FROM t1 RIGHT JOIN t2 ON true435 LEFT JOIN t3 ON t2.y IS NOT NULL436 INNER JOIN t4 ON true437 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);438} {439}440do_execsql_test joinF-44 {441 SELECT *442 FROM t1 RIGHT JOIN t2 ON true443 LEFT JOIN t3 ON t2.y IS NOT NULL444 INNER JOIN t4 ON true445 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)446 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);447} {448}449do_execsql_test joinF-45 {450 SELECT *451 FROM t1 RIGHT JOIN t2 ON true452 LEFT JOIN t3 ON t2.y IS NOT NULL453 LEFT JOIN t4 ON true454 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);455} {456}457do_execsql_test joinF-46 {458 SELECT *459 FROM t1 RIGHT JOIN t2 ON true460 LEFT JOIN t3 ON t2.y IS NOT NULL461 LEFT JOIN t4 ON true462 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)463 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);464} {465}466do_execsql_test joinF-47 {467 SELECT *468 FROM t1 RIGHT JOIN t2 ON true469 LEFT JOIN t3 ON t2.y IS NOT NULL470 RIGHT JOIN t4 ON true471 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);472} {473 - - - 50474}475do_execsql_test joinF-48 {476 SELECT *477 FROM t1 RIGHT JOIN t2 ON true478 LEFT JOIN t3 ON t2.y IS NOT NULL479 RIGHT JOIN t4 ON true480 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)481 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);482} {483}484do_execsql_test joinF-49 {485 SELECT *486 FROM t1 RIGHT JOIN t2 ON true487 RIGHT JOIN t3 ON t2.y IS NOT NULL488 INNER JOIN t4 ON true489 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);490} {491 - - 20 50492 - - 30 50493}494do_execsql_test joinF-50 {495 SELECT *496 FROM t1 RIGHT JOIN t2 ON true497 RIGHT JOIN t3 ON t2.y IS NOT NULL498 INNER JOIN t4 ON true499 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)500 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);501} {502 - - 20 50503 - - 30 50504}505do_execsql_test joinF-51 {506 SELECT *507 FROM t1 RIGHT JOIN t2 ON true508 RIGHT JOIN t3 ON t2.y IS NOT NULL509 LEFT JOIN t4 ON true510 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);511} {512 - - 20 50513 - - 30 50514}515do_execsql_test joinF-52 {516 SELECT *517 FROM t1 RIGHT JOIN t2 ON true518 RIGHT JOIN t3 ON t2.y IS NOT NULL519 LEFT JOIN t4 ON true520 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)521 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);522} {523 - - 20 50524 - - 30 50525}526do_execsql_test joinF-53 {527 SELECT *528 FROM t1 RIGHT JOIN t2 ON true529 RIGHT JOIN t3 ON t2.y IS NOT NULL530 RIGHT JOIN t4 ON true531 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);532} {533 - - 20 50534 - - 30 50535}536do_execsql_test joinF-54 {537 SELECT *538 FROM t1 RIGHT JOIN t2 ON true539 RIGHT JOIN t3 ON t2.y IS NOT NULL540 RIGHT JOIN t4 ON true541 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)542 ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);543} {544 - - 20 50545 - - 30 50546}547finish_test548 549############################################################################550# This is the TCL script used to generate the psql script that generated551# the data above.552#553# puts "554# \\pset border off555# \\pset tuples_only on556# \\pset null -557# 558# DROP TABLE IF EXISTS t1;559# DROP TABLE IF EXISTS t2;560# DROP TABLE IF EXISTS t3;561# DROP TABLE IF EXISTS t4;562# CREATE TABLE t1(x INT);563# CREATE TABLE t2(y INT);564# CREATE TABLE t3(z INT);565# CREATE TABLE t4(w INT);566# INSERT INTO t1 VALUES(10);567# INSERT INTO t3 VALUES(20),(30);568# INSERT INTO t4 VALUES(50);569# "570# 571# proc echo {prefix txt} {572# regsub -all {\n} $txt \n$prefix txt573# puts "$prefix$txt"574# }575# 576# set n 0577# foreach j1 {INNER LEFT RIGHT} {578# foreach j2 {INNER LEFT RIGHT} {579# foreach j3 {INNER LEFT RIGHT} {580# 581# incr n582# set q1 ""583# append q1 "SELECT *\n"584# append q1 " FROM t1 $j1 JOIN t2 ON true\n"585# append q1 " $j2 JOIN t3 ON t2.y IS NOT NULL\n"586# append q1 " $j3 JOIN t4 ON true\n"587# append q1 " ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);"588# 589# echo "\\qecho " "do_execsql_test joinF-$n \{"590# echo "\\qecho X " $q1591# echo "\\qecho " "\} \{"592# puts $q1593# echo "\\qecho " "\}"594# 595# incr n596# set q1 ""597# append q1 "SELECT *\n"598# append q1 " FROM t1 $j1 JOIN t2 ON true\n"599# append q1 " $j2 JOIN t3 ON t2.y IS NOT NULL\n"600# append q1 " $j3 JOIN t4 ON true\n"601# append q1 " WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)\n"602# append q1 " ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);"603# 604# echo "\\qecho " "do_execsql_test joinF-$n \{"605# echo "\\qecho X " $q1606# echo "\\qecho " "\} \{"607# puts $q1608# echo "\\qecho " "\}"609# 610# }611# }612# }613#614