AryaWu/sqlite
0
1# 2005 September 192#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# This file implements regression tests for SQLite library.12#13# This file implements tests for left outer joins containing ON14# clauses that restrict the scope of the left term of the join.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix join520 21 22do_test join5-1.1 {23 execsql {24 BEGIN;25 CREATE TABLE t1(a integer primary key, b integer, c integer);26 CREATE TABLE t2(x integer primary key, y);27 CREATE TABLE t3(p integer primary key, q);28 INSERT INTO t3 VALUES(11,'t3-11');29 INSERT INTO t3 VALUES(12,'t3-12');30 INSERT INTO t2 VALUES(11,'t2-11');31 INSERT INTO t2 VALUES(12,'t2-12');32 INSERT INTO t1 VALUES(1, 5, 0);33 INSERT INTO t1 VALUES(2, 11, 2);34 INSERT INTO t1 VALUES(3, 12, 1);35 COMMIT;36 }37} {}38do_test join5-1.2 {39 execsql {40 select * from t1 left join t2 on t1.b=t2.x and t1.c=141 }42} {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12}43do_test join5-1.3 {44 execsql {45 select * from t1 left join t2 on t1.b=t2.x where t1.c=146 }47} {3 12 1 12 t2-12}48do_test join5-1.4 {49 execsql {50 select * from t1 left join t2 on t1.b=t2.x and t1.c=151 left join t3 on t1.b=t3.p and t1.c=252 }53} {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}}54do_test join5-1.5 {55 execsql {56 select * from t1 left join t2 on t1.b=t2.x and t1.c=157 left join t3 on t1.b=t3.p where t1.c=258 }59} {2 11 2 {} {} 11 t3-11}60 61# Ticket #240362#63do_test join5-2.1 {64 execsql {65 CREATE TABLE ab(a,b);66 INSERT INTO "ab" VALUES(1,2);67 INSERT INTO "ab" VALUES(3,NULL);68 69 CREATE TABLE xy(x,y);70 INSERT INTO "xy" VALUES(2,3);71 INSERT INTO "xy" VALUES(NULL,1);72 }73 execsql {SELECT * FROM xy LEFT JOIN ab ON 0}74} {2 3 {} {} {} 1 {} {}}75do_test join5-2.2 {76 execsql {SELECT * FROM xy LEFT JOIN ab ON 1}77} {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}78do_test join5-2.3 {79 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL}80} {2 3 {} {} {} 1 {} {}}81do_test join5-2.4 {82 execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0}83} {}84do_test join5-2.5 {85 execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0}86} {}87do_test join5-2.6 {88 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0}89} {}90do_test join5-2.7 {91 execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1}92} {2 3 {} {} {} 1 {} {}}93do_test join5-2.8 {94 execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1}95} {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}96do_test join5-2.9 {97 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1}98} {2 3 {} {} {} 1 {} {}}99do_test join5-2.10 {100 execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL}101} {}102do_test join5-2.11 {103 execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL}104} {}105do_test join5-2.12 {106 execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL}107} {}108 109# Ticket https://sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601110# Incorrect output on a LEFT JOIN.111#112do_execsql_test join5-3.1 {113 DROP TABLE IF EXISTS t1;114 DROP TABLE IF EXISTS t2;115 DROP TABLE IF EXISTS t3;116 CREATE TABLE x1(a);117 INSERT INTO x1 VALUES(1);118 CREATE TABLE x2(b NOT NULL);119 CREATE TABLE x3(c, d);120 INSERT INTO x3 VALUES('a', NULL);121 INSERT INTO x3 VALUES('b', NULL);122 INSERT INTO x3 VALUES('c', NULL);123 SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b;124} {1 {} {} {}}125do_execsql_test join5-3.2 {126 DROP TABLE IF EXISTS t1;127 DROP TABLE IF EXISTS t2;128 DROP TABLE IF EXISTS t3;129 DROP TABLE IF EXISTS t4;130 DROP TABLE IF EXISTS t5;131 CREATE TABLE t1(x text NOT NULL, y text);132 CREATE TABLE t2(u text NOT NULL, x text NOT NULL);133 CREATE TABLE t3(w text NOT NULL, v text);134 CREATE TABLE t4(w text NOT NULL, z text NOT NULL);135 CREATE TABLE t5(z text NOT NULL, m text);136 INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL);137 INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL);138 INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL);139 INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL);140 INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c',141 'f6d7661f-4efe-4c90-87b5-858e61cd178b');142 SELECT *143 FROM t3144 INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL145 LEFT JOIN t4 ON t4.w = t3.w146 LEFT JOIN t5 ON t5.z = t4.z147 LEFT JOIN t2 ON t2.u = t5.m148 LEFT JOIN t1 xyz ON xyz.y = t2.x;149} {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}}150do_execsql_test join5-3.3 {151 DROP TABLE IF EXISTS x1;152 DROP TABLE IF EXISTS x2;153 DROP TABLE IF EXISTS x3;154 CREATE TABLE x1(a);155 INSERT INTO x1 VALUES(1);156 CREATE TABLE x2(b NOT NULL);157 CREATE TABLE x3(c, d);158 INSERT INTO x3 VALUES('a', NULL);159 INSERT INTO x3 VALUES('b', NULL);160 INSERT INTO x3 VALUES('c', NULL);161 SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;162} {}163 164# Ticket https://sqlite.org/src/tktview/c2a19d81652f40568c770c43 on165# 2015-08-20. LEFT JOIN and the push-down optimization.166#167do_execsql_test join5-4.1 {168 SELECT *169 FROM (170 SELECT 'apple' fruit171 UNION ALL SELECT 'banana'172 ) a173 JOIN (174 SELECT 'apple' fruit175 UNION ALL SELECT 'banana'176 ) b ON a.fruit=b.fruit177 LEFT JOIN (178 SELECT 1 isyellow179 ) c ON b.fruit='banana';180} {apple apple {} banana banana 1}181do_execsql_test join5-4.2 {182 SELECT *183 FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')184 LEFT JOIN (SELECT 1) ON fruit='banana';185} {apple {} banana 1}186 187#-------------------------------------------------------------------------188do_execsql_test 5.0 {189 CREATE TABLE y1(x, y, z);190 INSERT INTO y1 VALUES(0, 0, 1);191 CREATE TABLE y2(a);192}193 194do_execsql_test 5.1 {195 SELECT count(z) FROM y1 LEFT JOIN y2 ON x GROUP BY y;196} 1197 198do_execsql_test 5.2 {199 SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x GROUP BY y;200} 1201 202do_execsql_test 5.3 {203 CREATE VIEW v1 AS SELECT x, y, z FROM y1;204 SELECT count(z) FROM v1 LEFT JOIN y2 ON x GROUP BY y;205} 1206 207do_execsql_test 5.4 {208 SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x209} 1210 211do_execsql_test 5.5 {212 SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x213} {0 0 1 {}}214 215#-------------------------------------------------------------------------216#217reset_db218do_execsql_test 6.1 {219 CREATE TABLE t1(x); 220 INSERT INTO t1 VALUES(1);221 222 CREATE TABLE t2(y INTEGER PRIMARY KEY,a,b);223 INSERT INTO t2 VALUES(1,2,3);224 CREATE INDEX t2a ON t2(a); 225 CREATE INDEX t2b ON t2(b); 226}227 228do_execsql_test 6.2 {229 SELECT * FROM t1 LEFT JOIN t2 ON a=2 OR b=3 WHERE y IS NULL;230} {}231 232do_execsql_test 6.3.1 {233 CREATE TABLE t3(x);234 INSERT INTO t3 VALUES(1);235 CREATE TABLE t4(y, z);236 SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);237} {!!!}238 239do_execsql_test 6.3.2 {240 CREATE INDEX t4i ON t4(y, ifnull(z, '!!!'));241 SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);242} {!!!}243 244# 2019-02-08 https://sqlite.org/src/info/4e8e4857d32d401f245reset_db246do_execsql_test 6.100 {247 CREATE TABLE t1(aa, bb);248 CREATE INDEX t1x1 on t1(abs(aa), abs(bb));249 INSERT INTO t1 VALUES(-2,-3),(+2,-3),(-2,+3),(+2,+3);250 SELECT * FROM (t1) 251 WHERE ((abs(aa)=1 AND 1=2) OR abs(aa)=2)252 AND abs(bb)=3253 ORDER BY +1, +2;254} {-2 -3 -2 3 2 -3 2 3}255 256#-------------------------------------------------------------------------257#258reset_db259do_execsql_test 7.0 {260 CREATE TABLE t1(x);261 INSERT INTO t1 VALUES(1);262}263 264do_execsql_test 7.1 {265 CREATE TABLE t2(x, y, z);266 CREATE INDEX t2xy ON t2(x, y);267 WITH s(i) AS (268 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000269 )270 INSERT INTO t2 SELECT i/10, i, NULL FROM s;271 ANALYZE;272}273 274do_eqp_test 7.2 {275 SELECT * FROM t1 LEFT JOIN t2 ON (276 t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))277 );278} {279 QUERY PLAN280 |--SCAN t1281 `--MULTI-INDEX OR282 |--INDEX 1283 | `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN284 `--INDEX 2285 `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN286}287 288do_execsql_test 7.3 {289 CREATE TABLE t3(x);290 INSERT INTO t3(x) VALUES(1);291 CREATE INDEX t3x ON t3(x);292 293 CREATE TABLE t4(x, y, z);294 CREATE INDEX t4xy ON t4(x, y);295 CREATE INDEX t4xz ON t4(x, z);296 297 WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000)298 INSERT INTO t4 SELECT i/10, i, i FROM s;299 300 ANALYZE;301 UPDATE sqlite_stat1 SET stat='1000000 10 1' WHERE idx='t3x';302 ANALYZE sqlite_schema;303}304 305# If both sides of the OR reference the right-hand side of the LEFT JOIN306# then simplify the LEFT JOIN.307#308do_eqp_test 7.4 {309 SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?);310} {311 QUERY PLAN312 |--SCAN t4313 `--SEARCH t3 USING COVERING INDEX t3x (x=?)314} 315# If only one side of the OR references the right-hand side of the LEFT JOIN316# then do not do the simplification317#318do_eqp_test 7.4b {319 SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t3.x = ?);320} {321 QUERY PLAN322 |--SCAN t3323 `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN324} 325do_eqp_test 7.4c {326 SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t3.x = ? OR t4.z = ?);327} {328 QUERY PLAN329 |--SCAN t3330 `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN331} 332do_eqp_test 7.4d {333 SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?);334} {335 QUERY PLAN336 |--SCAN t3337 |--BLOOM FILTER ON t4 (x=?)338 `--SEARCH t4 USING INDEX t4xz (x=?)339} 340 341reset_db342do_execsql_test 8.0 {343 CREATE TABLE t0 (c0, c1, PRIMARY KEY (c0, c1));344 CREATE TABLE t1 (c0);345 346 INSERT INTO t1 VALUES (2);347 348 INSERT INTO t0 VALUES(0, 10);349 INSERT INTO t0 VALUES(1, 10);350 INSERT INTO t0 VALUES(2, 10);351 INSERT INTO t0 VALUES(3, 10);352}353 354do_execsql_test 8.1 {355 SELECT * FROM t0, t1 356 WHERE (t0.c1 >= 1 OR t0.c1 < 1) AND t0.c0 IN (1, t1.c0) ORDER BY 1;357} {358 1 10 2359 2 10 2360}361 362 363# 2022-01-31 dbsqlfuzz 787d9bd73164c6f0c85469e2e48b2aff19af6938364#365reset_db366do_execsql_test 9.1 {367 CREATE TABLE t1(a ,b FLOAT);368 INSERT INTO t1 VALUES(1,1);369 CREATE INDEX t1x1 ON t1(a,b,a,a,a,a,a,a,a,a,a,b);370 ANALYZE sqlite_schema;371 INSERT INTO sqlite_stat1 VALUES('t1','t1x1','648 324 81 81 81 81 81 81 81081 81 81 81');372 ANALYZE sqlite_schema;373}374do_catchsql_test 9.2 {375 SELECT a FROM 376 (SELECT a FROM t1 NATURAL LEFT JOIN t1) NATURAL LEFT JOIN t1 377 WHERE (rowid,1)<=(5,0);378} {0 1}379 380# 2022-03-02 https://sqlite.org/forum/info/50a1bbe08ce4c29c381# Bloom-filter pulldown is incompatible with skip-scan.382#383reset_db384do_execsql_test 10.1 {385 CREATE TABLE t1(x INT);386 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)387 INSERT INTO t1(x) SELECT 0 FROM c;388 CREATE INDEX t1x1 ON t1(x BETWEEN 0 AND 10, x);389 ANALYZE;390 DELETE FROM t1;391 INSERT INTO t1 VALUES(0),(0);392 CREATE VIEW v1 AS SELECT * FROM t1 NATURAL JOIN t1 WHERE (x BETWEEN 0 AND 10) OR true;393 CREATE VIEW v2 AS SELECT * FROM v1 NATURAL JOIN v1;394 CREATE VIEW v3 AS SELECT * FROM v2, v1 USING (x) GROUP BY x;395 SELECT x FROM v3; 396} {0}397 398# 2022-03-24 https://sqlite.org/forum/forumpost/031e262a89b6a9d2399# Bloom-filter on a LEFT JOIN with NULL-based WHERE constraints.400#401reset_db402do_execsql_test 11.1 {403 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);404 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);405 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8)406 INSERT INTO t1(a,b) SELECT x, 10*x FROM c;407 INSERT INTO t2(c,d) SELECT b*2, 100*a FROM t1;408 ANALYZE;409 DELETE FROM sqlite_stat1;410 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES411 ('t1',NULL,150105),('t2',NULL,98747);412 ANALYZE sqlite_schema;413} {}414do_execsql_test 11.2 {415 SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d IS NULL;416} {4}417do_execsql_test 11.3 {418 SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d=100;419} {1}420do_execsql_test 11.4 {421 SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d>=300;422} {2}423 424# 2022-05-03 https://sqlite.org/forum/forumpost/2482b32700384a0f425# Bloom-filter pull-down does not handle NOT NULL constraints correctly.426#427reset_db428do_execsql_test 12.1 {429 CREATE TABLE t1(a INT, b INT, c INT);430 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)431 INSERT INTO t1(a,b,c) SELECT x, x*1000, x*1000000 FROM c;432 CREATE TABLE t2(b INT, x INT);433 INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%3==0;434 CREATE INDEX t2b ON t2(b);435 CREATE TABLE t3(c INT, y INT);436 INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%4==0;437 CREATE INDEX t3c ON t3(c);438 INSERT INTO t1(a,b,c) VALUES(200, 200000, NULL);439 ANALYZE;440} {}441do_execsql_test 12.2 {442 SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE x>0 AND y>0443 ORDER BY +a;444} {445 12 12000 12000000 12 12446 24 24000 24000000 24 24447 36 36000 36000000 36 36448 48 48000 48000000 48 48449 60 60000 60000000 60 60450 72 72000 72000000 72 72451 84 84000 84000000 84 84452 96 96000 96000000 96 96453}454 455 456 457 458finish_test459