AryaWu/sqlite
0
1# 2002 May 242#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 joins, including outer joins.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix join219 20do_test join2-1.1 {21 execsql {22 CREATE TABLE t1(a,b);23 INSERT INTO t1 VALUES(1,11);24 INSERT INTO t1 VALUES(2,22);25 INSERT INTO t1 VALUES(3,33);26 SELECT * FROM t1;27 } 28} {1 11 2 22 3 33}29do_test join2-1.2 {30 execsql {31 CREATE TABLE t2(b,c);32 INSERT INTO t2 VALUES(11,111);33 INSERT INTO t2 VALUES(33,333);34 INSERT INTO t2 VALUES(44,444);35 SELECT * FROM t2;36 } 37} {11 111 33 333 44 444};38do_test join2-1.3 {39 execsql {40 CREATE TABLE t3(c,d);41 INSERT INTO t3 VALUES(111,1111);42 INSERT INTO t3 VALUES(444,4444);43 INSERT INTO t3 VALUES(555,5555);44 SELECT * FROM t3;45 } 46} {111 1111 444 4444 555 5555}47 48do_test join2-1.4 {49 execsql {50 SELECT * FROM51 t1 NATURAL JOIN t2 NATURAL JOIN t352 }53} {1 11 111 1111}54do_test join2-1.5 {55 execsql {56 SELECT * FROM57 t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t358 }59} {1 11 111 1111 3 33 333 {}}60do_test join2-1.6 {61 execsql {62 SELECT * FROM63 t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t364 }65} {1 11 111 1111}66do_test join2-1.6-rj {67 execsql {68 SELECT * FROM69 t2 NATURAL RIGHT OUTER JOIN t1 NATURAL JOIN t370 }71} {11 111 1 1111}72ifcapable subquery {73 do_test join2-1.7 {74 execsql {75 SELECT * FROM76 t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)77 }78 } {1 11 111 1111 2 22 {} {} 3 33 {} {}}79 do_test join2-1.7-rj {80 execsql {81 SELECT a, b, c, d FROM82 t2 NATURAL JOIN t3 NATURAL RIGHT JOIN t183 }84 } {1 11 111 1111 2 22 {} {} 3 33 {} {}}85}86 87#-------------------------------------------------------------------------88# Check that ticket [25e335f802ddc] has been resolved. It should be an89# error for the ON clause of a LEFT JOIN to refer to a table to its right.90#91do_execsql_test 2.0 {92 CREATE TABLE aa(a);93 CREATE TABLE bb(b);94 CREATE TABLE cc(c);95 INSERT INTO aa VALUES('one');96 INSERT INTO bb VALUES('one');97 INSERT INTO cc VALUES('one');98}99 100do_catchsql_test 2.1 {101 SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));102} {1 {ON clause references tables to its right}}103do_catchsql_test 2.1b {104 SELECT * FROM aa RIGHT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));105} {1 {ON clause references tables to its right}}106do_catchsql_test 2.2 {107 SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);108} {0 {one one one}}109 110#-------------------------------------------------------------------------111# Test that a problem causing where.c to overlook opportunities to112# omit unnecessary tables from a LEFT JOIN when UNIQUE, NOT NULL column 113# that makes this possible happens to be the leftmost in its table.114#115reset_db116do_execsql_test 3.0 {117 CREATE TABLE t1(k1 INTEGER PRIMARY KEY, k2, k3);118 CREATE TABLE t2(k2 INTEGER PRIMARY KEY, v2);119 120 -- Prior to this problem being fixed, table t3_2 would be omitted from121 -- the join queries below, but if t3_1 were used in its place it would122 -- not.123 CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID;124 CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID;125}126 127do_eqp_test 3.1 {128 SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3);129} {130 QUERY PLAN131 |--SCAN t1132 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN133}134 135do_eqp_test 3.2 {136 SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3);137} {138 QUERY PLAN139 |--SCAN t1140 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN141}142 143#-------------------------------------------------------------------------144# Test that tables other than the rightmost can be omitted from a145# LEFT JOIN query.146#147do_execsql_test 4.0 {148 CREATE TABLE c1(k INTEGER PRIMARY KEY, v1);149 CREATE TABLE c2(k INTEGER PRIMARY KEY, v2);150 CREATE TABLE c3(k INTEGER PRIMARY KEY, v3);151 152 INSERT INTO c1 VALUES(1, 2);153 INSERT INTO c2 VALUES(2, 3);154 INSERT INTO c3 VALUES(3, 'v3');155 156 INSERT INTO c1 VALUES(111, 1112);157 INSERT INTO c2 VALUES(112, 1113);158 INSERT INTO c3 VALUES(113, 'v1113');159}160do_execsql_test 4.1.1 {161 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);162} {2 v3 1112 {}}163do_execsql_test 4.1.2 {164 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);165} {2 v3 1112 {}}166 167do_execsql_test 4.1.3 {168 SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);169} {2 v3 1112 {}}170 171do_execsql_test 4.1.4 {172 SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);173} {2 v3 2 v3 1112 {} 1112 {}}174 175do_eqp_test 4.1.5 {176 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);177} {178 QUERY PLAN179 |--SCAN c1180 |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN181 `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN182}183do_eqp_test 4.1.6 {184 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);185} {186 QUERY PLAN187 |--SCAN c1188 `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN189}190 191do_execsql_test 4.2.0 {192 DROP TABLE c1;193 DROP TABLE c2;194 DROP TABLE c3;195 CREATE TABLE c1(k UNIQUE, v1);196 CREATE TABLE c2(k UNIQUE, v2);197 CREATE TABLE c3(k UNIQUE, v3);198 199 INSERT INTO c1 VALUES(1, 2);200 INSERT INTO c2 VALUES(2, 3);201 INSERT INTO c3 VALUES(3, 'v3');202 203 INSERT INTO c1 VALUES(111, 1112);204 INSERT INTO c2 VALUES(112, 1113);205 INSERT INTO c3 VALUES(113, 'v1113');206}207do_execsql_test 4.2.1 {208 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);209} {2 v3 1112 {}}210do_execsql_test 4.2.2 {211 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);212} {2 v3 1112 {}}213 214do_execsql_test 4.2.3 {215 SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);216} {2 v3 1112 {}}217 218do_execsql_test 4.2.4 {219 SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);220} {2 v3 2 v3 1112 {} 1112 {}}221 222do_eqp_test 4.2.5 {223 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);224} {225 QUERY PLAN226 |--SCAN c1227 |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?) LEFT-JOIN228 `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN229}230do_eqp_test 4.2.6 {231 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);232} {233 QUERY PLAN234 |--SCAN c1235 `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN236}237 238# 2017-11-23 (Thanksgiving day)239# OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.240#241do_execsql_test 4.3.0 {242 DROP TABLE IF EXISTS t1;243 DROP TABLE IF EXISTS t2;244 CREATE TABLE t1(x PRIMARY KEY) WITHOUT ROWID;245 CREATE TABLE t2(x);246 SELECT a.x247 FROM t1 AS a248 LEFT JOIN t1 AS b ON (a.x=b.x)249 LEFT JOIN t2 AS c ON (a.x=c.x);250} {}251do_execsql_test 4.3.1 {252 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)253 INSERT INTO t1(x) SELECT x FROM c;254 INSERT INTO t2(x) SELECT x+9 FROM t1;255 SELECT a.x, c.x256 FROM t1 AS a257 LEFT JOIN t1 AS b ON (a.x=b.x)258 LEFT JOIN t2 AS c ON (a.x=c.x);259} {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10}260 261do_execsql_test 5.0 {262 CREATE TABLE s1 (a INTEGER PRIMARY KEY);263 CREATE TABLE s2 (a INTEGER PRIMARY KEY);264 CREATE TABLE s3 (a INTEGER);265 CREATE UNIQUE INDEX ndx on s3(a);266}267do_eqp_test 5.1 {268 SELECT s1.a FROM s1 left join s2 using (a);269} {SCAN s1}270 271do_eqp_test 5.2 {272 SELECT s1.a FROM s1 left join s3 using (a);273} {SCAN s1}274 275do_execsql_test 6.0 {276 CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c);277 CREATE TABLE u2(a INTEGER PRIMARY KEY, b, c);278 CREATE INDEX u1ab ON u1(b, c);279}280do_eqp_test 6.1 {281 SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c );282} {SCAN u2}283 284db close285sqlite3 db :memory:286do_execsql_test 7.0 {287 CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2),(3,4),(5,6);288 CREATE TABLE t2(c,d); INSERT INTO t2 VALUES(2,4),(3,6);289 CREATE TABLE t3(x); INSERT INTO t3 VALUES(9);290 CREATE VIEW test AS291 SELECT *, 'x'292 FROM t1 LEFT JOIN (SELECT * FROM t2, t3) ON (c=b AND x=9)293 WHERE c IS NULL;294 SELECT * FROM test;295} {3 4 {} {} {} x 5 6 {} {} {} x}296 297#-------------------------------------------------------------------------298# Ticket [dfd66334].299#300reset_db301do_execsql_test 8.0 {302 CREATE TABLE t0(c0);303 CREATE TABLE t1(c0);304}305 306do_execsql_test 8.1 {307 SELECT * FROM t0 LEFT JOIN t1 308 WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0);309}310 311#-------------------------------------------------------------------------312# Ticket [45f4bf4eb] reported by Manuel Rigger (2020-04-25)313#314# Follow up error reported by Eric Speckman on the SQLite forum315# https://sqlite.org/forum/info/c49496d24d35bd7c (2020-08-19)316#317reset_db318do_execsql_test 9.0 {319 CREATE TABLE t0(c0 INT);320 CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;321 INSERT INTO t0(c0) VALUES (0);322}323 324do_execsql_test 9.1 {325 SELECT typeof(c0), c0 FROM v0 WHERE c0>='0'326} {integer 0}327 328do_execsql_test 9.2 {329 SELECT * FROM t0, v0 WHERE v0.c0 >= '0';330} {0 0}331 332do_execsql_test 9.3 {333 SELECT * FROM t0 LEFT JOIN v0 WHERE v0.c0 >= '0';334} {0 0}335 336do_execsql_test 9.4 {337 SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0';338} {0 0}339 340do_execsql_test 9.5 {341 SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0' WHERE TRUE 342 UNION SELECT 0,0 WHERE 0; 343} {0 0}344 345do_execsql_test 9.10 {346 CREATE TABLE t1 (aaa);347 INSERT INTO t1 VALUES(23456);348 CREATE TABLE t2(bbb);349 CREATE VIEW v2(ccc) AS SELECT bbb IS 1234 FROM t2;350 SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;351} {{} 1}352optimization_control db query-flattener 0353do_execsql_test 9.11 {354 SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;355} {{} 1}356 357# 2023-03-01 https://sqlite.org/forum/forumpost/26387ea7ef358# When flattening a VIEW which is the RHS of a LEFT JOIN, always put359# an TK_IF_NULL_ROW operator on all accesses, even TK_COLUMN nodes, since360# the TK_COLUMN might reference an outer subquery.361#362reset_db363db null NULL364do_execsql_test 10.1 {365 CREATE TABLE t1 (x INTEGER);366 INSERT INTO t1 VALUES(1); -- Some true value367 CREATE TABLE t2 (z TEXT);368 INSERT INTO t2 VALUES('some value');369 CREATE TABLE t3(w TEXT);370 INSERT INTO t3 VALUES('some other value');371}372do_execsql_test 10.2 {373 SELECT (374 SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)375 ) FROM t1;376} NULL377do_execsql_test 10.3 {378 SELECT (379 SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v)380 ) FROM t1;381} NULL382optimization_control db all 0383do_execsql_test 10.4 {384 SELECT (385 SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)386 ) FROM t1;387} NULL388 389# 2023-03-02 https://sqlite.org/forum/forumpost/402f05296d390#391# The TK_IF_NULL_ROW expression node must ensure that it does not overwrite392# the result register of an OP_Once subroutine.393#394optimization_control db all 1395do_execsql_test 11.1 {396 DROP TABLE t1;397 DROP TABLE t2;398 DROP TABLE t3;399 CREATE TABLE t1(x TEXT, y INTEGER);400 INSERT INTO t1(x,y) VALUES(NULL,-2),(NULL,1),('0',2);401 CREATE TABLE t2(z INTEGER);402 INSERT INTO t2(z) VALUES(2),(-2);403 CREATE VIEW t3 AS SELECT z, (SELECT count(*) FROM t1) AS w FROM t2;404 SELECT * FROM t1 LEFT JOIN t3 ON y=z;405} {NULL -2 -2 3 NULL 1 NULL NULL 0 2 2 3}406 407# 2023-03-11 https://sqlite.org/forum/forumpost/b405033490fa56d9408# The fix that test 11.1 above checks also caused a performance regression.409# This test case verifies that the performance regression has been resolved.410#411do_execsql_test 12.1 {412 DROP TABLE t1;413 DROP TABLE t2;414 DROP VIEW t3;415 CREATE TABLE t1(a INTEGER PRIMARY KEY);416 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100)417 INSERT INTO t1(a) SELECT n FROM c;418 CREATE VIEW t2(b) AS SELECT a FROM t1;419}420do_vmstep_test 12.2 {421 SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;422} 2000 {99 99 100 100}423do_eqp_test 12.3 {424 SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;425} {426 QUERY PLAN427 |--SCAN t1428 `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN429}430 431# 2024-09-05 https://sqlite.org/forum/forumpost/8a1e467e905b8d27432# When performing the Omit-Noop-Join optimization, if FROM clause terms433# to the right of the omitted join have the reverse-order bit set in the434# WhereInfo.revMask bitmask, those bits need to be shifted to account435# for the omitted join.436#437reset_db438do_execsql_test 13.0 {439 CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1 INT);440 CREATE TABLE t2(c2 INT, d2 INTEGER PRIMARY KEY);441 CREATE TABLE t3(e3 INTEGER PRIMARY KEY);442 INSERT INTO t1 VALUES(33,0);443 INSERT INTO t2 VALUES(33,1),(33,2);444}445do_execsql_test 13.1 {446 SELECT t1.a1, t2.d2447 FROM (t1 LEFT JOIN t3 ON t3.e3=t1.b1) JOIN t2 ON t2.c2=t1.a1448 WHERE t1.a1=33449 ORDER BY t2.d2 DESC;450} {33 2 33 1}451 452finish_test453