AryaWu/sqlite
0
1# 2022-05-042# revised 2022-05-313#4# The author disclaims copyright to this source code. In place of5# a legal notice, here is a blessing:6#7# May you do good and not evil.8# May you find forgiveness for yourself and forgive others.9# May you share freely, never taking more than you give.10#11#***********************************************************************12#13# TESTRUNNER: slow14#15# This file implements tests for JOINs that use Bloom filters.16#17# The test case output is (mostly) all generated by PostgreSQL 14. This18# test module was created as follows:19#20# 1. Run a TCL script (included at the bottom of this file) that21# generates an input script for "psql" that will run man22# diverse tests on joins.23#24# 2. Run the script from step (1) through psql and collect the25# output.26#27# 3. Make a few minor global search-and-replace operations to convert28# the psql output into a form suitable for this test module.29#30# 4. Add this header, and the script content at the footer.31#32# A few extra tests that were not generated from postgresql output are33# added at the end.34#35set testdir [file dirname $argv0]36source $testdir/tester.tcl37db nullvalue -38db eval {39 CREATE TABLE t1(a INT, b INT, c INT, d INT);40 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<95)41 INSERT INTO t1(a,b,c,d) SELECT x, x+100, x+200, x+300 FROM c;42 CREATE TABLE t2(b INT, x INT);43 INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%2=0;44 CREATE INDEX t2b ON t2(b);45 CREATE TABLE t3(c INT, y INT);46 INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%3=0;47 CREATE INDEX t3c ON t3(c);48 CREATE TABLE t4(d INT, z INT);49 INSERT INTO t4(d,z) SELECT d, a FROM t1 WHERE a%5=0;50 CREATE INDEX t4d ON t4(d);51 INSERT INTO t1(a,b,c,d) VALUES52 (96,NULL,296,396),53 (97,197,NULL,397),54 (98,198,298,NULL),55 (99,NULL,NULL,NULL);56 ANALYZE;57}58do_execsql_test joinD-1 {59 SELECT t1.*, t2.*, t3.*, t4.*60 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>061 INNER JOIN t3 ON t1.c=t3.c AND t3.y>062 INNER JOIN t4 ON t1.d=t4.d AND t4.z>063 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);64} {65 30 130 230 330 130 30 230 30 330 3066 60 160 260 360 160 60 260 60 360 6067 90 190 290 390 190 90 290 90 390 9068}69do_execsql_test joinD-2 {70 SELECT t1.*, t2.*, t3.*, t4.*71 FROM t1 INNER JOIN t2 ON t1.b IS NOT DISTINCT FROM t2.b AND t2.x>072 INNER JOIN t3 ON t1.c=t3.c AND t3.y>073 INNER JOIN t4 ON t1.d=t4.d AND t4.z>074 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);75} {76 30 130 230 330 130 30 230 30 330 3077 60 160 260 360 160 60 260 60 360 6078 90 190 290 390 190 90 290 90 390 9079}80do_execsql_test joinD-3 {81 SELECT t1.*, t2.*, t3.*, t4.*82 FROM t1 INNER JOIN t2 ON t1.b=t2.b83 INNER JOIN t3 ON t1.c=t3.c AND t3.y>084 INNER JOIN t4 ON t1.d=t4.d AND t4.z>085 WHERE t2.x>086 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);87} {88 30 130 230 330 130 30 230 30 330 3089 60 160 260 360 160 60 260 60 360 6090 90 190 290 390 190 90 290 90 390 9091}92do_execsql_test joinD-4 {93 SELECT t1.*, t2.*, t3.*, t4.*94 FROM t1 INNER JOIN t2 ON t1.b=t2.b95 INNER JOIN t3 ON t1.c=t3.c AND t3.y>096 INNER JOIN t4 ON t1.d=t4.d AND t4.z>097 WHERE (t2.x>0 OR t2.x IS NULL)98 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);99} {100 30 130 230 330 130 30 230 30 330 30101 60 160 260 360 160 60 260 60 360 60102 90 190 290 390 190 90 290 90 390 90103}104do_execsql_test joinD-5 {105 SELECT t1.*, t2.*, t3.*, t4.*106 FROM t1 INNER JOIN t2 ON true107 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0108 INNER JOIN t4 ON t1.d=t4.d AND t4.z>0109 WHERE t1.b=t2.b AND t2.x>0110 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);111} {112 30 130 230 330 130 30 230 30 330 30113 60 160 260 360 160 60 260 60 360 60114 90 190 290 390 190 90 290 90 390 90115}116do_execsql_test joinD-6 {117 SELECT t1.*, t2.*, t3.*, t4.*118 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0119 INNER JOIN t3 ON t1.c=t3.c120 INNER JOIN t4 ON t1.d=t4.d AND t4.z>0121 WHERE t3.y>0122 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);123} {124 30 130 230 330 130 30 230 30 330 30125 60 160 260 360 160 60 260 60 360 60126 90 190 290 390 190 90 290 90 390 90127}128do_execsql_test joinD-7 {129 SELECT t1.*, t2.*, t3.*, t4.*130 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0131 INNER JOIN t3 ON t1.c=t3.c132 INNER JOIN t4 ON t1.d=t4.d AND t4.z>0133 WHERE t3.y>0 OR t3.y IS NULL134 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);135} {136 30 130 230 330 130 30 230 30 330 30137 60 160 260 360 160 60 260 60 360 60138 90 190 290 390 190 90 290 90 390 90139}140do_execsql_test joinD-8 {141 SELECT t1.*, t2.*, t3.*, t4.*142 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0143 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0144 INNER JOIN t4 ON t1.d=t4.d145 WHERE t4.z>0146 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);147} {148 30 130 230 330 130 30 230 30 330 30149 60 160 260 360 160 60 260 60 360 60150 90 190 290 390 190 90 290 90 390 90151}152do_execsql_test joinD-9 {153 SELECT t1.*, t2.*, t3.*, t4.*154 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0155 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0156 INNER JOIN t4 ON t1.d=t4.d157 WHERE t4.z IS NULL OR t4.z>0158 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);159} {160 30 130 230 330 130 30 230 30 330 30161 60 160 260 360 160 60 260 60 360 60162 90 190 290 390 190 90 290 90 390 90163}164do_execsql_test joinD-10 {165 SELECT t1.*, t2.*, t3.*, t4.*166 FROM t1 INNER JOIN t2 ON t1.b=t2.b167 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0168 INNER JOIN t4 ON t1.d=t4.d169 WHERE t2.x>0 AND t4.z>0170 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);171} {172 30 130 230 330 130 30 230 30 330 30173 60 160 260 360 160 60 260 60 360 60174 90 190 290 390 190 90 290 90 390 90175}176do_execsql_test joinD-11 {177 SELECT t1.*, t2.*, t3.*, t4.*178 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0179 INNER JOIN t3 ON t1.c=t3.c180 INNER JOIN t4 ON t1.d=t4.d181 WHERE t4.z>0 AND t3.y>0182 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);183} {184 30 130 230 330 130 30 230 30 330 30185 60 160 260 360 160 60 260 60 360 60186 90 190 290 390 190 90 290 90 390 90187}188do_execsql_test joinD-12 {189 SELECT t1.*, t2.*, t3.*, t4.*190 FROM t1 INNER JOIN t2 ON t1.b=t2.b191 INNER JOIN t3 ON t1.c=t3.c192 INNER JOIN t4 ON t1.d=t4.d AND t4.z>0193 WHERE t2.x>0 AND t3.y>0194 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);195} {196 30 130 230 330 130 30 230 30 330 30197 60 160 260 360 160 60 260 60 360 60198 90 190 290 390 190 90 290 90 390 90199}200do_execsql_test joinD-13 {201 SELECT t1.*, t2.*, t3.*, t4.*202 FROM t1 INNER JOIN t2 ON t1.b=t2.b203 INNER JOIN t3 ON t1.c=t3.c204 INNER JOIN t4 ON t1.d=t4.d205 WHERE t2.x>0 AND t3.y>0 AND t4.z>0206 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);207} {208 30 130 230 330 130 30 230 30 330 30209 60 160 260 360 160 60 260 60 360 60210 90 190 290 390 190 90 290 90 390 90211}212do_execsql_test joinD-14 {213 SELECT t1.*, t2.*, t3.*, t4.*214 FROM t1 INNER JOIN t2 ON t1.b IS NOT DISTINCT FROM t2.b AND t2.x>0215 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0216 INNER JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0217 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);218} {219 30 130 230 330 130 30 230 30 330 30220 60 160 260 360 160 60 260 60 360 60221 90 190 290 390 190 90 290 90 390 90222}223do_execsql_test joinD-15 {224 SELECT t1.*, t2.*, t3.*, t4.*225 FROM t1 INNER JOIN t2 ON t2.x>0226 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0227 INNER JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0228 WHERE t1.b IS NOT DISTINCT FROM t2.b229 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);230} {231 30 130 230 330 130 30 230 30 330 30232 60 160 260 360 160 60 260 60 360 60233 90 190 290 390 190 90 290 90 390 90234}235do_execsql_test joinD-16 {236 SELECT t1.*, t2.*, t3.*, t4.*237 FROM t1 INNER JOIN t2 ON t2.x>0238 INNER JOIN t3 ON t3.y>0239 INNER JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0240 WHERE t1.b IS NOT DISTINCT FROM t2.b AND t1.c IS NOT DISTINCT FROM t3.c241 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);242} {243 30 130 230 330 130 30 230 30 330 30244 60 160 260 360 160 60 260 60 360 60245 90 190 290 390 190 90 290 90 390 90246}247do_execsql_test joinD-17 {248 SELECT t1.*, t2.*, t3.*, t4.*249 FROM t1 INNER JOIN t2 ON t2.x>0250 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0251 INNER JOIN t4 ON t4.z>0252 WHERE t1.b IS NOT DISTINCT FROM t2.b AND t1.d IS NOT DISTINCT FROM t4.d253 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);254} {255 30 130 230 330 130 30 230 30 330 30256 60 160 260 360 160 60 260 60 360 60257 90 190 290 390 190 90 290 90 390 90258}259do_execsql_test joinD-18 {260 SELECT t1.*, t2.*, t3.*, t4.*261 FROM t1 INNER JOIN t2 ON t1.b IN (t2.b,-2,-3) AND t2.x>0262 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0263 INNER JOIN t4 ON t1.d=t4.d AND t4.z>0264 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);265} {266 30 130 230 330 130 30 230 30 330 30267 60 160 260 360 160 60 260 60 360 60268 90 190 290 390 190 90 290 90 390 90269}270do_execsql_test joinD-19 {271 SELECT t1.*, t2.*, t3.*, t4.*272 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0273 INNER JOIN t3 ON t1.c IN (-4,t3.c,-5) AND t3.y>0274 INNER JOIN t4 ON t1.d=t4.d AND t4.z>0275 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);276} {277 30 130 230 330 130 30 230 30 330 30278 60 160 260 360 160 60 260 60 360 60279 90 190 290 390 190 90 290 90 390 90280}281do_execsql_test joinD-20 {282 SELECT t1.*, t2.*, t3.*, t4.*283 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0284 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0285 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0286 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);287} {288 6 106 206 306 106 6 206 6 - -289 12 112 212 312 112 12 212 12 - -290 18 118 218 318 118 18 218 18 - -291 24 124 224 324 124 24 224 24 - -292 30 130 230 330 130 30 230 30 330 30293 36 136 236 336 136 36 236 36 - -294 42 142 242 342 142 42 242 42 - -295 48 148 248 348 148 48 248 48 - -296 54 154 254 354 154 54 254 54 - -297 60 160 260 360 160 60 260 60 360 60298 66 166 266 366 166 66 266 66 - -299 72 172 272 372 172 72 272 72 - -300 78 178 278 378 178 78 278 78 - -301 84 184 284 384 184 84 284 84 - -302 90 190 290 390 190 90 290 90 390 90303}304do_execsql_test joinD-21 {305 SELECT t1.*, t2.*, t3.*, t4.*306 FROM t1 INNER JOIN t2 ON t1.b IS NOT DISTINCT FROM t2.b AND t2.x>0307 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0308 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0309 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);310} {311 6 106 206 306 106 6 206 6 - -312 12 112 212 312 112 12 212 12 - -313 18 118 218 318 118 18 218 18 - -314 24 124 224 324 124 24 224 24 - -315 30 130 230 330 130 30 230 30 330 30316 36 136 236 336 136 36 236 36 - -317 42 142 242 342 142 42 242 42 - -318 48 148 248 348 148 48 248 48 - -319 54 154 254 354 154 54 254 54 - -320 60 160 260 360 160 60 260 60 360 60321 66 166 266 366 166 66 266 66 - -322 72 172 272 372 172 72 272 72 - -323 78 178 278 378 178 78 278 78 - -324 84 184 284 384 184 84 284 84 - -325 90 190 290 390 190 90 290 90 390 90326}327do_execsql_test joinD-22 {328 SELECT t1.*, t2.*, t3.*, t4.*329 FROM t1 INNER JOIN t2 ON t1.b=t2.b330 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0331 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0332 WHERE t2.x>0333 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);334} {335 6 106 206 306 106 6 206 6 - -336 12 112 212 312 112 12 212 12 - -337 18 118 218 318 118 18 218 18 - -338 24 124 224 324 124 24 224 24 - -339 30 130 230 330 130 30 230 30 330 30340 36 136 236 336 136 36 236 36 - -341 42 142 242 342 142 42 242 42 - -342 48 148 248 348 148 48 248 48 - -343 54 154 254 354 154 54 254 54 - -344 60 160 260 360 160 60 260 60 360 60345 66 166 266 366 166 66 266 66 - -346 72 172 272 372 172 72 272 72 - -347 78 178 278 378 178 78 278 78 - -348 84 184 284 384 184 84 284 84 - -349 90 190 290 390 190 90 290 90 390 90350}351do_execsql_test joinD-23 {352 SELECT t1.*, t2.*, t3.*, t4.*353 FROM t1 INNER JOIN t2 ON t1.b=t2.b354 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0355 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0356 WHERE (t2.x>0 OR t2.x IS NULL)357 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);358} {359 6 106 206 306 106 6 206 6 - -360 12 112 212 312 112 12 212 12 - -361 18 118 218 318 118 18 218 18 - -362 24 124 224 324 124 24 224 24 - -363 30 130 230 330 130 30 230 30 330 30364 36 136 236 336 136 36 236 36 - -365 42 142 242 342 142 42 242 42 - -366 48 148 248 348 148 48 248 48 - -367 54 154 254 354 154 54 254 54 - -368 60 160 260 360 160 60 260 60 360 60369 66 166 266 366 166 66 266 66 - -370 72 172 272 372 172 72 272 72 - -371 78 178 278 378 178 78 278 78 - -372 84 184 284 384 184 84 284 84 - -373 90 190 290 390 190 90 290 90 390 90374}375do_execsql_test joinD-24 {376 SELECT t1.*, t2.*, t3.*, t4.*377 FROM t1 INNER JOIN t2 ON true378 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0379 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0380 WHERE t1.b=t2.b AND t2.x>0381 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);382} {383 6 106 206 306 106 6 206 6 - -384 12 112 212 312 112 12 212 12 - -385 18 118 218 318 118 18 218 18 - -386 24 124 224 324 124 24 224 24 - -387 30 130 230 330 130 30 230 30 330 30388 36 136 236 336 136 36 236 36 - -389 42 142 242 342 142 42 242 42 - -390 48 148 248 348 148 48 248 48 - -391 54 154 254 354 154 54 254 54 - -392 60 160 260 360 160 60 260 60 360 60393 66 166 266 366 166 66 266 66 - -394 72 172 272 372 172 72 272 72 - -395 78 178 278 378 178 78 278 78 - -396 84 184 284 384 184 84 284 84 - -397 90 190 290 390 190 90 290 90 390 90398}399do_execsql_test joinD-25 {400 SELECT t1.*, t2.*, t3.*, t4.*401 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0402 INNER JOIN t3 ON t1.c=t3.c403 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0404 WHERE t3.y>0405 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);406} {407 6 106 206 306 106 6 206 6 - -408 12 112 212 312 112 12 212 12 - -409 18 118 218 318 118 18 218 18 - -410 24 124 224 324 124 24 224 24 - -411 30 130 230 330 130 30 230 30 330 30412 36 136 236 336 136 36 236 36 - -413 42 142 242 342 142 42 242 42 - -414 48 148 248 348 148 48 248 48 - -415 54 154 254 354 154 54 254 54 - -416 60 160 260 360 160 60 260 60 360 60417 66 166 266 366 166 66 266 66 - -418 72 172 272 372 172 72 272 72 - -419 78 178 278 378 178 78 278 78 - -420 84 184 284 384 184 84 284 84 - -421 90 190 290 390 190 90 290 90 390 90422}423do_execsql_test joinD-26 {424 SELECT t1.*, t2.*, t3.*, t4.*425 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0426 INNER JOIN t3 ON t1.c=t3.c427 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0428 WHERE t3.y>0 OR t3.y IS NULL429 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);430} {431 6 106 206 306 106 6 206 6 - -432 12 112 212 312 112 12 212 12 - -433 18 118 218 318 118 18 218 18 - -434 24 124 224 324 124 24 224 24 - -435 30 130 230 330 130 30 230 30 330 30436 36 136 236 336 136 36 236 36 - -437 42 142 242 342 142 42 242 42 - -438 48 148 248 348 148 48 248 48 - -439 54 154 254 354 154 54 254 54 - -440 60 160 260 360 160 60 260 60 360 60441 66 166 266 366 166 66 266 66 - -442 72 172 272 372 172 72 272 72 - -443 78 178 278 378 178 78 278 78 - -444 84 184 284 384 184 84 284 84 - -445 90 190 290 390 190 90 290 90 390 90446}447do_execsql_test joinD-27 {448 SELECT t1.*, t2.*, t3.*, t4.*449 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0450 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0451 LEFT JOIN t4 ON t1.d=t4.d452 WHERE t4.z>0453 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);454} {455 30 130 230 330 130 30 230 30 330 30456 60 160 260 360 160 60 260 60 360 60457 90 190 290 390 190 90 290 90 390 90458}459do_execsql_test joinD-28 {460 SELECT t1.*, t2.*, t3.*, t4.*461 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0462 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0463 LEFT JOIN t4 ON t1.d=t4.d464 WHERE t4.z IS NULL OR t4.z>0465 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);466} {467 6 106 206 306 106 6 206 6 - -468 12 112 212 312 112 12 212 12 - -469 18 118 218 318 118 18 218 18 - -470 24 124 224 324 124 24 224 24 - -471 30 130 230 330 130 30 230 30 330 30472 36 136 236 336 136 36 236 36 - -473 42 142 242 342 142 42 242 42 - -474 48 148 248 348 148 48 248 48 - -475 54 154 254 354 154 54 254 54 - -476 60 160 260 360 160 60 260 60 360 60477 66 166 266 366 166 66 266 66 - -478 72 172 272 372 172 72 272 72 - -479 78 178 278 378 178 78 278 78 - -480 84 184 284 384 184 84 284 84 - -481 90 190 290 390 190 90 290 90 390 90482}483do_execsql_test joinD-29 {484 SELECT t1.*, t2.*, t3.*, t4.*485 FROM t1 INNER JOIN t2 ON t1.b=t2.b486 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0487 LEFT JOIN t4 ON t1.d=t4.d488 WHERE t2.x>0 AND t4.z>0489 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);490} {491 30 130 230 330 130 30 230 30 330 30492 60 160 260 360 160 60 260 60 360 60493 90 190 290 390 190 90 290 90 390 90494}495do_execsql_test joinD-30 {496 SELECT t1.*, t2.*, t3.*, t4.*497 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0498 INNER JOIN t3 ON t1.c=t3.c499 LEFT JOIN t4 ON t1.d=t4.d500 WHERE t4.z>0 AND t3.y>0501 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);502} {503 30 130 230 330 130 30 230 30 330 30504 60 160 260 360 160 60 260 60 360 60505 90 190 290 390 190 90 290 90 390 90506}507do_execsql_test joinD-31 {508 SELECT t1.*, t2.*, t3.*, t4.*509 FROM t1 INNER JOIN t2 ON t1.b=t2.b510 INNER JOIN t3 ON t1.c=t3.c511 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0512 WHERE t2.x>0 AND t3.y>0513 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);514} {515 6 106 206 306 106 6 206 6 - -516 12 112 212 312 112 12 212 12 - -517 18 118 218 318 118 18 218 18 - -518 24 124 224 324 124 24 224 24 - -519 30 130 230 330 130 30 230 30 330 30520 36 136 236 336 136 36 236 36 - -521 42 142 242 342 142 42 242 42 - -522 48 148 248 348 148 48 248 48 - -523 54 154 254 354 154 54 254 54 - -524 60 160 260 360 160 60 260 60 360 60525 66 166 266 366 166 66 266 66 - -526 72 172 272 372 172 72 272 72 - -527 78 178 278 378 178 78 278 78 - -528 84 184 284 384 184 84 284 84 - -529 90 190 290 390 190 90 290 90 390 90530}531do_execsql_test joinD-32 {532 SELECT t1.*, t2.*, t3.*, t4.*533 FROM t1 INNER JOIN t2 ON t1.b=t2.b534 INNER JOIN t3 ON t1.c=t3.c535 LEFT JOIN t4 ON t1.d=t4.d536 WHERE t2.x>0 AND t3.y>0 AND t4.z>0537 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);538} {539 30 130 230 330 130 30 230 30 330 30540 60 160 260 360 160 60 260 60 360 60541 90 190 290 390 190 90 290 90 390 90542}543do_execsql_test joinD-33 {544 SELECT t1.*, t2.*, t3.*, t4.*545 FROM t1 INNER JOIN t2 ON t1.b IS NOT DISTINCT FROM t2.b AND t2.x>0546 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0547 LEFT JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0548 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);549} {550 6 106 206 306 106 6 206 6 - -551 12 112 212 312 112 12 212 12 - -552 18 118 218 318 118 18 218 18 - -553 24 124 224 324 124 24 224 24 - -554 30 130 230 330 130 30 230 30 330 30555 36 136 236 336 136 36 236 36 - -556 42 142 242 342 142 42 242 42 - -557 48 148 248 348 148 48 248 48 - -558 54 154 254 354 154 54 254 54 - -559 60 160 260 360 160 60 260 60 360 60560 66 166 266 366 166 66 266 66 - -561 72 172 272 372 172 72 272 72 - -562 78 178 278 378 178 78 278 78 - -563 84 184 284 384 184 84 284 84 - -564 90 190 290 390 190 90 290 90 390 90565}566do_execsql_test joinD-34 {567 SELECT t1.*, t2.*, t3.*, t4.*568 FROM t1 INNER JOIN t2 ON t2.x>0569 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0570 LEFT JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0571 WHERE t1.b IS NOT DISTINCT FROM t2.b572 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);573} {574 6 106 206 306 106 6 206 6 - -575 12 112 212 312 112 12 212 12 - -576 18 118 218 318 118 18 218 18 - -577 24 124 224 324 124 24 224 24 - -578 30 130 230 330 130 30 230 30 330 30579 36 136 236 336 136 36 236 36 - -580 42 142 242 342 142 42 242 42 - -581 48 148 248 348 148 48 248 48 - -582 54 154 254 354 154 54 254 54 - -583 60 160 260 360 160 60 260 60 360 60584 66 166 266 366 166 66 266 66 - -585 72 172 272 372 172 72 272 72 - -586 78 178 278 378 178 78 278 78 - -587 84 184 284 384 184 84 284 84 - -588 90 190 290 390 190 90 290 90 390 90589}590do_execsql_test joinD-35 {591 SELECT t1.*, t2.*, t3.*, t4.*592 FROM t1 INNER JOIN t2 ON t2.x>0593 INNER JOIN t3 ON t3.y>0594 LEFT JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0595 WHERE t1.b IS NOT DISTINCT FROM t2.b AND t1.c IS NOT DISTINCT FROM t3.c596 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);597} {598 6 106 206 306 106 6 206 6 - -599 12 112 212 312 112 12 212 12 - -600 18 118 218 318 118 18 218 18 - -601 24 124 224 324 124 24 224 24 - -602 30 130 230 330 130 30 230 30 330 30603 36 136 236 336 136 36 236 36 - -604 42 142 242 342 142 42 242 42 - -605 48 148 248 348 148 48 248 48 - -606 54 154 254 354 154 54 254 54 - -607 60 160 260 360 160 60 260 60 360 60608 66 166 266 366 166 66 266 66 - -609 72 172 272 372 172 72 272 72 - -610 78 178 278 378 178 78 278 78 - -611 84 184 284 384 184 84 284 84 - -612 90 190 290 390 190 90 290 90 390 90613}614do_execsql_test joinD-36 {615 SELECT t1.*, t2.*, t3.*, t4.*616 FROM t1 INNER JOIN t2 ON t2.x>0617 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0618 LEFT JOIN t4 ON t4.z>0619 WHERE t1.b IS NOT DISTINCT FROM t2.b AND t1.d IS NOT DISTINCT FROM t4.d620 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);621} {622 30 130 230 330 130 30 230 30 330 30623 60 160 260 360 160 60 260 60 360 60624 90 190 290 390 190 90 290 90 390 90625}626do_execsql_test joinD-37 {627 SELECT t1.*, t2.*, t3.*, t4.*628 FROM t1 INNER JOIN t2 ON t1.b IN (t2.b,-2,-3) AND t2.x>0629 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0630 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0631 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);632} {633 6 106 206 306 106 6 206 6 - -634 12 112 212 312 112 12 212 12 - -635 18 118 218 318 118 18 218 18 - -636 24 124 224 324 124 24 224 24 - -637 30 130 230 330 130 30 230 30 330 30638 36 136 236 336 136 36 236 36 - -639 42 142 242 342 142 42 242 42 - -640 48 148 248 348 148 48 248 48 - -641 54 154 254 354 154 54 254 54 - -642 60 160 260 360 160 60 260 60 360 60643 66 166 266 366 166 66 266 66 - -644 72 172 272 372 172 72 272 72 - -645 78 178 278 378 178 78 278 78 - -646 84 184 284 384 184 84 284 84 - -647 90 190 290 390 190 90 290 90 390 90648}649do_execsql_test joinD-38 {650 SELECT t1.*, t2.*, t3.*, t4.*651 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0652 INNER JOIN t3 ON t1.c IN (-4,t3.c,-5) AND t3.y>0653 LEFT JOIN t4 ON t1.d=t4.d AND t4.z>0654 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);655} {656 6 106 206 306 106 6 206 6 - -657 12 112 212 312 112 12 212 12 - -658 18 118 218 318 118 18 218 18 - -659 24 124 224 324 124 24 224 24 - -660 30 130 230 330 130 30 230 30 330 30661 36 136 236 336 136 36 236 36 - -662 42 142 242 342 142 42 242 42 - -663 48 148 248 348 148 48 248 48 - -664 54 154 254 354 154 54 254 54 - -665 60 160 260 360 160 60 260 60 360 60666 66 166 266 366 166 66 266 66 - -667 72 172 272 372 172 72 272 72 - -668 78 178 278 378 178 78 278 78 - -669 84 184 284 384 184 84 284 84 - -670 90 190 290 390 190 90 290 90 390 90671}672do_execsql_test joinD-39 {673 SELECT t1.*, t2.*, t3.*, t4.*674 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0675 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0676 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0677 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);678} {679 30 130 230 330 130 30 230 30 330 30680 60 160 260 360 160 60 260 60 360 60681 90 190 290 390 190 90 290 90 390 90682 - - - - - - - - 300 0683 - - - - - - - - 305 5684 - - - - - - - - 310 10685 - - - - - - - - 315 15686 - - - - - - - - 320 20687 - - - - - - - - 325 25688 - - - - - - - - 335 35689 - - - - - - - - 340 40690 - - - - - - - - 345 45691 - - - - - - - - 350 50692 - - - - - - - - 355 55693 - - - - - - - - 365 65694 - - - - - - - - 370 70695 - - - - - - - - 375 75696 - - - - - - - - 380 80697 - - - - - - - - 385 85698 - - - - - - - - 395 95699}700do_execsql_test joinD-40 {701 SELECT t1.*, t2.*, t3.*, t4.*702 FROM t1 INNER JOIN t2 ON t1.b IS NOT DISTINCT FROM t2.b AND t2.x>0703 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0704 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0705 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);706} {707 30 130 230 330 130 30 230 30 330 30708 60 160 260 360 160 60 260 60 360 60709 90 190 290 390 190 90 290 90 390 90710 - - - - - - - - 300 0711 - - - - - - - - 305 5712 - - - - - - - - 310 10713 - - - - - - - - 315 15714 - - - - - - - - 320 20715 - - - - - - - - 325 25716 - - - - - - - - 335 35717 - - - - - - - - 340 40718 - - - - - - - - 345 45719 - - - - - - - - 350 50720 - - - - - - - - 355 55721 - - - - - - - - 365 65722 - - - - - - - - 370 70723 - - - - - - - - 375 75724 - - - - - - - - 380 80725 - - - - - - - - 385 85726 - - - - - - - - 395 95727}728do_execsql_test joinD-41 {729 SELECT t1.*, t2.*, t3.*, t4.*730 FROM t1 INNER JOIN t2 ON t1.b=t2.b731 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0732 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0733 WHERE t2.x>0734 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);735} {736 30 130 230 330 130 30 230 30 330 30737 60 160 260 360 160 60 260 60 360 60738 90 190 290 390 190 90 290 90 390 90739}740do_execsql_test joinD-42 {741 SELECT t1.*, t2.*, t3.*, t4.*742 FROM t1 INNER JOIN t2 ON t1.b=t2.b743 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0744 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0745 WHERE (t2.x>0 OR t2.x IS NULL)746 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);747} {748 30 130 230 330 130 30 230 30 330 30749 60 160 260 360 160 60 260 60 360 60750 90 190 290 390 190 90 290 90 390 90751 - - - - - - - - 300 0752 - - - - - - - - 305 5753 - - - - - - - - 310 10754 - - - - - - - - 315 15755 - - - - - - - - 320 20756 - - - - - - - - 325 25757 - - - - - - - - 335 35758 - - - - - - - - 340 40759 - - - - - - - - 345 45760 - - - - - - - - 350 50761 - - - - - - - - 355 55762 - - - - - - - - 365 65763 - - - - - - - - 370 70764 - - - - - - - - 375 75765 - - - - - - - - 380 80766 - - - - - - - - 385 85767 - - - - - - - - 395 95768}769do_execsql_test joinD-43 {770 SELECT t1.*, t2.*, t3.*, t4.*771 FROM t1 INNER JOIN t2 ON true772 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0773 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0774 WHERE t1.b=t2.b AND t2.x>0775 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);776} {777 30 130 230 330 130 30 230 30 330 30778 60 160 260 360 160 60 260 60 360 60779 90 190 290 390 190 90 290 90 390 90780}781do_execsql_test joinD-44 {782 SELECT t1.*, t2.*, t3.*, t4.*783 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0784 INNER JOIN t3 ON t1.c=t3.c785 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0786 WHERE t3.y>0787 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);788} {789 30 130 230 330 130 30 230 30 330 30790 60 160 260 360 160 60 260 60 360 60791 90 190 290 390 190 90 290 90 390 90792}793do_execsql_test joinD-45 {794 SELECT t1.*, t2.*, t3.*, t4.*795 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0796 INNER JOIN t3 ON t1.c=t3.c797 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0798 WHERE t3.y>0 OR t3.y IS NULL799 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);800} {801 30 130 230 330 130 30 230 30 330 30802 60 160 260 360 160 60 260 60 360 60803 90 190 290 390 190 90 290 90 390 90804 - - - - - - - - 300 0805 - - - - - - - - 305 5806 - - - - - - - - 310 10807 - - - - - - - - 315 15808 - - - - - - - - 320 20809 - - - - - - - - 325 25810 - - - - - - - - 335 35811 - - - - - - - - 340 40812 - - - - - - - - 345 45813 - - - - - - - - 350 50814 - - - - - - - - 355 55815 - - - - - - - - 365 65816 - - - - - - - - 370 70817 - - - - - - - - 375 75818 - - - - - - - - 380 80819 - - - - - - - - 385 85820 - - - - - - - - 395 95821}822do_execsql_test joinD-46 {823 SELECT t1.*, t2.*, t3.*, t4.*824 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0825 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0826 RIGHT JOIN t4 ON t1.d=t4.d827 WHERE t4.z>0828 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);829} {830 30 130 230 330 130 30 230 30 330 30831 60 160 260 360 160 60 260 60 360 60832 90 190 290 390 190 90 290 90 390 90833 - - - - - - - - 305 5834 - - - - - - - - 310 10835 - - - - - - - - 315 15836 - - - - - - - - 320 20837 - - - - - - - - 325 25838 - - - - - - - - 335 35839 - - - - - - - - 340 40840 - - - - - - - - 345 45841 - - - - - - - - 350 50842 - - - - - - - - 355 55843 - - - - - - - - 365 65844 - - - - - - - - 370 70845 - - - - - - - - 375 75846 - - - - - - - - 380 80847 - - - - - - - - 385 85848 - - - - - - - - 395 95849}850do_execsql_test joinD-47 {851 SELECT t1.*, t2.*, t3.*, t4.*852 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0853 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0854 RIGHT JOIN t4 ON t1.d=t4.d855 WHERE t4.z IS NULL OR t4.z>0856 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);857} {858 30 130 230 330 130 30 230 30 330 30859 60 160 260 360 160 60 260 60 360 60860 90 190 290 390 190 90 290 90 390 90861 - - - - - - - - 305 5862 - - - - - - - - 310 10863 - - - - - - - - 315 15864 - - - - - - - - 320 20865 - - - - - - - - 325 25866 - - - - - - - - 335 35867 - - - - - - - - 340 40868 - - - - - - - - 345 45869 - - - - - - - - 350 50870 - - - - - - - - 355 55871 - - - - - - - - 365 65872 - - - - - - - - 370 70873 - - - - - - - - 375 75874 - - - - - - - - 380 80875 - - - - - - - - 385 85876 - - - - - - - - 395 95877}878do_execsql_test joinD-48 {879 SELECT t1.*, t2.*, t3.*, t4.*880 FROM t1 INNER JOIN t2 ON t1.b=t2.b881 INNER JOIN t3 ON t1.c=t3.c AND t3.y>0882 RIGHT JOIN t4 ON t1.d=t4.d883 WHERE t2.x>0 AND t4.z>0884 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);885} {886 30 130 230 330 130 30 230 30 330 30887 60 160 260 360 160 60 260 60 360 60888 90 190 290 390 190 90 290 90 390 90889}890do_execsql_test joinD-49 {891 SELECT t1.*, t2.*, t3.*, t4.*892 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0893 INNER JOIN t3 ON t1.c=t3.c894 RIGHT JOIN t4 ON t1.d=t4.d895 WHERE t4.z>0 AND t3.y>0896 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);897} {898 30 130 230 330 130 30 230 30 330 30899 60 160 260 360 160 60 260 60 360 60900 90 190 290 390 190 90 290 90 390 90901}902do_execsql_test joinD-50 {903 SELECT t1.*, t2.*, t3.*, t4.*904 FROM t1 INNER JOIN t2 ON t1.b=t2.b905 INNER JOIN t3 ON t1.c=t3.c906 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>0907 WHERE t2.x>0 AND t3.y>0908 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);909} {910 30 130 230 330 130 30 230 30 330 30911 60 160 260 360 160 60 260 60 360 60912 90 190 290 390 190 90 290 90 390 90913}914do_execsql_test joinD-51 {915 SELECT t1.*, t2.*, t3.*, t4.*916 FROM t1 INNER JOIN t2 ON t1.b=t2.b917 INNER JOIN t3 ON t1.c=t3.c918 RIGHT JOIN t4 ON t1.d=t4.d919 WHERE t2.x>0 AND t3.y>0 AND t4.z>0920 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);921} {922 30 130 230 330 130 30 230 30 330 30923 60 160 260 360 160 60 260 60 360 60924 90 190 290 390 190 90 290 90 390 90925}926do_execsql_test joinD-52 {927 SELECT t1.*, t2.*, t3.*, t4.*928 FROM t1 INNER JOIN t2 ON t1.b IS NOT DISTINCT FROM t2.b AND t2.x>0929 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0930 RIGHT JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0931 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);932} {933 30 130 230 330 130 30 230 30 330 30934 60 160 260 360 160 60 260 60 360 60935 90 190 290 390 190 90 290 90 390 90936 - - - - - - - - 300 0937 - - - - - - - - 305 5938 - - - - - - - - 310 10939 - - - - - - - - 315 15940 - - - - - - - - 320 20941 - - - - - - - - 325 25942 - - - - - - - - 335 35943 - - - - - - - - 340 40944 - - - - - - - - 345 45945 - - - - - - - - 350 50946 - - - - - - - - 355 55947 - - - - - - - - 365 65948 - - - - - - - - 370 70949 - - - - - - - - 375 75950 - - - - - - - - 380 80951 - - - - - - - - 385 85952 - - - - - - - - 395 95953}954do_execsql_test joinD-53 {955 SELECT t1.*, t2.*, t3.*, t4.*956 FROM t1 INNER JOIN t2 ON t2.x>0957 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0958 RIGHT JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0959 WHERE t1.b IS NOT DISTINCT FROM t2.b960 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);961} {962 30 130 230 330 130 30 230 30 330 30963 60 160 260 360 160 60 260 60 360 60964 90 190 290 390 190 90 290 90 390 90965 - - - - - - - - 300 0966 - - - - - - - - 305 5967 - - - - - - - - 310 10968 - - - - - - - - 320 20969 - - - - - - - - 325 25970 - - - - - - - - 335 35971 - - - - - - - - 340 40972 - - - - - - - - 350 50973 - - - - - - - - 355 55974 - - - - - - - - 365 65975 - - - - - - - - 370 70976 - - - - - - - - 380 80977 - - - - - - - - 385 85978 - - - - - - - - 395 95979}980do_execsql_test joinD-54 {981 SELECT t1.*, t2.*, t3.*, t4.*982 FROM t1 INNER JOIN t2 ON t2.x>0983 INNER JOIN t3 ON t3.y>0984 RIGHT JOIN t4 ON t1.d IS NOT DISTINCT FROM t4.d AND t4.z>0985 WHERE t1.b IS NOT DISTINCT FROM t2.b AND t1.c IS NOT DISTINCT FROM t3.c986 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);987} {988 30 130 230 330 130 30 230 30 330 30989 60 160 260 360 160 60 260 60 360 60990 90 190 290 390 190 90 290 90 390 90991 - - - - - - - - 300 0992}993do_execsql_test joinD-55 {994 SELECT t1.*, t2.*, t3.*, t4.*995 FROM t1 INNER JOIN t2 ON t2.x>0996 INNER JOIN t3 ON t1.c IS NOT DISTINCT FROM t3.c AND t3.y>0997 RIGHT JOIN t4 ON t4.z>0998 WHERE t1.b IS NOT DISTINCT FROM t2.b AND t1.d IS NOT DISTINCT FROM t4.d999 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);1000} {1001 30 130 230 330 130 30 230 30 330 301002 60 160 260 360 160 60 260 60 360 601003 90 190 290 390 190 90 290 90 390 901004}1005do_execsql_test joinD-56 {1006 SELECT t1.*, t2.*, t3.*, t4.*1007 FROM t1 INNER JOIN t2 ON t1.b IN (t2.b,-2,-3) AND t2.x>01008 INNER JOIN t3 ON t1.c=t3.c AND t3.y>01009 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>01010 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);1011} {1012 30 130 230 330 130 30 230 30 330 301013 60 160 260 360 160 60 260 60 360 601014 90 190 290 390 190 90 290 90 390 901015 - - - - - - - - 300 01016 - - - - - - - - 305 51017 - - - - - - - - 310 101018 - - - - - - - - 315 151019 - - - - - - - - 320 201020 - - - - - - - - 325 251021 - - - - - - - - 335 351022 - - - - - - - - 340 401023 - - - - - - - - 345 451024 - - - - - - - - 350 501025 - - - - - - - - 355 551026 - - - - - - - - 365 651027 - - - - - - - - 370 701028 - - - - - - - - 375 751029 - - - - - - - - 380 801030 - - - - - - - - 385 851031 - - - - - - - - 395 951032}1033do_execsql_test joinD-57 {1034 SELECT t1.*, t2.*, t3.*, t4.*1035 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>01036 INNER JOIN t3 ON t1.c IN (-4,t3.c,-5) AND t3.y>01037 RIGHT JOIN t4 ON t1.d=t4.d AND t4.z>01038 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);1039} {1040 30 130 230 330 130 30 230 30 330 301041 60 160 260 360 160 60 260 60 360 601042 90 190 290 390 190 90 290 90 390 901043 - - - - - - - - 300 01044 - - - - - - - - 305 51045 - - - - - - - - 310 101046 - - - - - - - - 315 151047 - - - - - - - - 320 201048 - - - - - - - - 325 251049 - - - - - - - - 335 351050 - - - - - - - - 340 401051 - - - - - - - - 345 451052 - - - - - - - - 350 501053 - - - - - - - - 355 551054 - - - - - - - - 365 651055 - - - - - - - - 370 701056 - - - - - - - - 375 751057 - - - - - - - - 380 801058 - - - - - - - - 385 851059 - - - - - - - - 395 951060}1061do_execsql_test joinD-58 {1062 SELECT t1.*, t2.*, t3.*, t4.*1063 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>01064 INNER JOIN t3 ON t1.c=t3.c AND t3.y>01065 FULL JOIN t4 ON t1.d=t4.d AND t4.z>01066 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);1067} {1068 6 106 206 306 106 6 206 6 - -1069 12 112 212 312 112 12 212 12 - -1070 18 118 218 318 118 18 218 18 - -1071 24 124 224 324 124 24 224 24 - -1072 30 130 230 330 130 30 230 30 330 301073 36 136 236 336 136 36 236 36 - -1074 42 142 242 342 142 42 242 42 - -1075 48 148 248 348 148 48 248 48 - -1076 54 154 254 354 154 54 254 54 - -1077 60 160 260 360 160 60 260 60 360 601078 66 166 266 366 166 66 266 66 - -1079 72 172 272 372 172 72 272 72 - -1080 78 178 278 378 178 78 278 78 - -1081 84 184 284 384 184 84 284 84 - -1082 90 190 290 390 190 90 290 90 390 901083 - - - - - - - - 300 01084 - - - - - - - - 305 51085 - - - - - - - - 310 101086 - - - - - - - - 315 151087 - - - - - - - - 320 201088 - - - - - - - - 325 251089 - - - - - - - - 335 351090 - - - - - - - - 340 401091 - - - - - - - - 345 451092 - - - - - - - - 350 501093 - - - - - - - - 355 551094 - - - - - - - - 365 651095 - - - - - - - - 370 701096 - - - - - - - - 375 751097 - - - - - - - - 380 801098 - - - - - - - - 385 851099 - - - - - - - - 395 951100}1101do_execsql_test joinD-59 {1102 SELECT t1.*, t2.*, t3.*, t4.*1103 FROM t1 INNER JOIN t2 ON t1.b IS NOT DISTINCT FROM t2.b AND t2.x>01104 INNER JOIN t3 ON t1.c=t3.c AND t3.y>01105 FULL JOIN t4 ON t1.d=t4.d AND t4.z>01106 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);1107} {1108 6 106 206 306 106 6 206 6 - -1109 12 112 212 312 112 12 212 12 - -1110 18 118 218 318 118 18 218 18 - -1111 24 124 224 324 124 24 224 24 - -1112 30 130 230 330 130 30 230 30 330 301113 36 136 236 336 136 36 236 36 - -1114 42 142 242 342 142 42 242 42 - -1115 48 148 248 348 148 48 248 48 - -1116 54 154 254 354 154 54 254 54 - -1117 60 160 260 360 160 60 260 60 360 601118 66 166 266 366 166 66 266 66 - -1119 72 172 272 372 172 72 272 72 - -1120 78 178 278 378 178 78 278 78 - -1121 84 184 284 384 184 84 284 84 - -1122 90 190 290 390 190 90 290 90 390 901123 - - - - - - - - 300 01124 - - - - - - - - 305 51125 - - - - - - - - 310 101126 - - - - - - - - 315 151127 - - - - - - - - 320 201128 - - - - - - - - 325 251129 - - - - - - - - 335 351130 - - - - - - - - 340 401131 - - - - - - - - 345 451132 - - - - - - - - 350 501133 - - - - - - - - 355 551134 - - - - - - - - 365 651135 - - - - - - - - 370 701136 - - - - - - - - 375 751137 - - - - - - - - 380 801138 - - - - - - - - 385 851139 - - - - - - - - 395 951140}1141do_execsql_test joinD-60 {1142 SELECT t1.*, t2.*, t3.*, t4.*1143 FROM t1 INNER JOIN t2 ON t1.b=t2.b1144 INNER JOIN t3 ON t1.c=t3.c AND t3.y>01145 FULL JOIN t4 ON t1.d=t4.d AND t4.z>01146 WHERE t2.x>01147 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);1148} {1149 6 106 206 306 106 6 206 6 - -1150 12 112 212 312 112 12 212 12 - -1151 18 118 218 318 118 18 218 18 - -1152 24 124 224 324 124 24 224 24 - -1153 30 130 230 330 130 30 230 30 330 301154 36 136 236 336 136 36 236 36 - -1155 42 142 242 342 142 42 242 42 - -1156 48 148 248 348 148 48 248 48 - -1157 54 154 254 354 154 54 254 54 - -1158 60 160 260 360 160 60 260 60 360 601159 66 166 266 366 166 66 266 66 - -1160 72 172 272 372 172 72 272 72 - -1161 78 178 278 378 178 78 278 78 - -1162 84 184 284 384 184 84 284 84 - -1163 90 190 290 390 190 90 290 90 390 901164}1165do_execsql_test joinD-61 {1166 SELECT t1.*, t2.*, t3.*, t4.*1167 FROM t1 INNER JOIN t2 ON t1.b=t2.b1168 INNER JOIN t3 ON t1.c=t3.c AND t3.y>01169 FULL JOIN t4 ON t1.d=t4.d AND t4.z>01170 WHERE (t2.x>0 OR t2.x IS NULL)1171 ORDER BY coalesce(t1.a,t2.b,t3.c,t4.d,0);1172} {1173 6 106 206 306 106 6 206 6 - -1174 12 112 212 312 112 12 212 12 - -1175 18 118 218 318 118 18 218 18 - -1176 24 124 224 324 124 24 224 24 - -1177 30 130 230 330 130 30 230 30 330 301178 36 136 236 336 136 36 236 36 - -1179 42 142 242 342 142 42 242 42 - -1180 48 148 248 348 148 48 248 48 - -1181 54 154 254 354 154 54 254 54 - -1182 60 160 260 360 160 60 260 60 360 601183 66 166 266 366 166 66 266 66 - -1184 72 172 272 372 172 72 272 72 - -1185 78 178 278 378 178 78 278 78 - -1186 84 184 284 384 184 84 284 84 - -1187 90 190 290 390 190 90 290 90 390 901188 - - - - - - - - 300 01189 - - - - - - - - 305 51190 - - - - - - - - 310 101191 - - - - - - - - 315 151192 - - - - - - - - 320 201193 - - - - - - - - 325 251194 - - - - - - - - 335 351195 - - - - - - - - 340 401196 - - - - - - - - 345 451197 - - - - - - - - 350 501198 - - - - - - - - 355 551199 - - - - - - - - 365 651200 - - - - - - - - 370 70