diff options
Diffstat (limited to '')
-rw-r--r-- | test/join2.test | 431 |
1 files changed, 431 insertions, 0 deletions
diff --git a/test/join2.test b/test/join2.test new file mode 100644 index 0000000..15e76f9 --- /dev/null +++ b/test/join2.test @@ -0,0 +1,431 @@ +# 2002 May 24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# +# This file implements tests for joins, including outer joins. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix join2 + +do_test join2-1.1 { + execsql { + CREATE TABLE t1(a,b); + INSERT INTO t1 VALUES(1,11); + INSERT INTO t1 VALUES(2,22); + INSERT INTO t1 VALUES(3,33); + SELECT * FROM t1; + } +} {1 11 2 22 3 33} +do_test join2-1.2 { + execsql { + CREATE TABLE t2(b,c); + INSERT INTO t2 VALUES(11,111); + INSERT INTO t2 VALUES(33,333); + INSERT INTO t2 VALUES(44,444); + SELECT * FROM t2; + } +} {11 111 33 333 44 444}; +do_test join2-1.3 { + execsql { + CREATE TABLE t3(c,d); + INSERT INTO t3 VALUES(111,1111); + INSERT INTO t3 VALUES(444,4444); + INSERT INTO t3 VALUES(555,5555); + SELECT * FROM t3; + } +} {111 1111 444 4444 555 5555} + +do_test join2-1.4 { + execsql { + SELECT * FROM + t1 NATURAL JOIN t2 NATURAL JOIN t3 + } +} {1 11 111 1111} +do_test join2-1.5 { + execsql { + SELECT * FROM + t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t3 + } +} {1 11 111 1111 3 33 333 {}} +do_test join2-1.6 { + execsql { + SELECT * FROM + t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3 + } +} {1 11 111 1111} +do_test join2-1.6-rj { + execsql { + SELECT * FROM + t2 NATURAL RIGHT OUTER JOIN t1 NATURAL JOIN t3 + } +} {11 111 1 1111} +ifcapable subquery { + do_test join2-1.7 { + execsql { + SELECT * FROM + t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3) + } + } {1 11 111 1111 2 22 {} {} 3 33 {} {}} + do_test join2-1.7-rj { + execsql { + SELECT a, b, c, d FROM + t2 NATURAL JOIN t3 NATURAL RIGHT JOIN t1 + } + } {1 11 111 1111 2 22 {} {} 3 33 {} {}} +} + +#------------------------------------------------------------------------- +# Check that ticket [25e335f802ddc] has been resolved. It should be an +# error for the ON clause of a LEFT JOIN to refer to a table to its right. +# +do_execsql_test 2.0 { + CREATE TABLE aa(a); + CREATE TABLE bb(b); + CREATE TABLE cc(c); + INSERT INTO aa VALUES('one'); + INSERT INTO bb VALUES('one'); + INSERT INTO cc VALUES('one'); +} + +do_catchsql_test 2.1 { + SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1)); +} {1 {ON clause references tables to its right}} +do_catchsql_test 2.1b { + SELECT * FROM aa RIGHT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1)); +} {1 {ON clause references tables to its right}} +do_catchsql_test 2.2 { + SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c); +} {0 {one one one}} + +#------------------------------------------------------------------------- +# Test that a problem causing where.c to overlook opportunities to +# omit unnecessary tables from a LEFT JOIN when UNIQUE, NOT NULL column +# that makes this possible happens to be the leftmost in its table. +# +reset_db +do_execsql_test 3.0 { + CREATE TABLE t1(k1 INTEGER PRIMARY KEY, k2, k3); + CREATE TABLE t2(k2 INTEGER PRIMARY KEY, v2); + + -- Prior to this problem being fixed, table t3_2 would be omitted from + -- the join queries below, but if t3_1 were used in its place it would + -- not. + CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID; + CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID; +} + +do_eqp_test 3.1 { + SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3); +} { + QUERY PLAN + |--SCAN t1 + `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN +} + +do_eqp_test 3.2 { + SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3); +} { + QUERY PLAN + |--SCAN t1 + `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN +} + +#------------------------------------------------------------------------- +# Test that tables other than the rightmost can be omitted from a +# LEFT JOIN query. +# +do_execsql_test 4.0 { + CREATE TABLE c1(k INTEGER PRIMARY KEY, v1); + CREATE TABLE c2(k INTEGER PRIMARY KEY, v2); + CREATE TABLE c3(k INTEGER PRIMARY KEY, v3); + + INSERT INTO c1 VALUES(1, 2); + INSERT INTO c2 VALUES(2, 3); + INSERT INTO c3 VALUES(3, 'v3'); + + INSERT INTO c1 VALUES(111, 1112); + INSERT INTO c2 VALUES(112, 1113); + INSERT INTO c3 VALUES(113, 'v1113'); +} +do_execsql_test 4.1.1 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); +} {2 v3 1112 {}} +do_execsql_test 4.1.2 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 1112 {}} + +do_execsql_test 4.1.3 { + SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 1112 {}} + +do_execsql_test 4.1.4 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 2 v3 1112 {} 1112 {}} + +do_eqp_test 4.1.5 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); +} { + QUERY PLAN + |--SCAN c1 + |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN + `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN +} +do_eqp_test 4.1.6 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); +} { + QUERY PLAN + |--SCAN c1 + `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN +} + +do_execsql_test 4.2.0 { + DROP TABLE c1; + DROP TABLE c2; + DROP TABLE c3; + CREATE TABLE c1(k UNIQUE, v1); + CREATE TABLE c2(k UNIQUE, v2); + CREATE TABLE c3(k UNIQUE, v3); + + INSERT INTO c1 VALUES(1, 2); + INSERT INTO c2 VALUES(2, 3); + INSERT INTO c3 VALUES(3, 'v3'); + + INSERT INTO c1 VALUES(111, 1112); + INSERT INTO c2 VALUES(112, 1113); + INSERT INTO c3 VALUES(113, 'v1113'); +} +do_execsql_test 4.2.1 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); +} {2 v3 1112 {}} +do_execsql_test 4.2.2 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 1112 {}} + +do_execsql_test 4.2.3 { + SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 1112 {}} + +do_execsql_test 4.2.4 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); +} {2 v3 2 v3 1112 {} 1112 {}} + +do_eqp_test 4.2.5 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); +} { + QUERY PLAN + |--SCAN c1 + |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?) LEFT-JOIN + `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN +} +do_eqp_test 4.2.6 { + SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); +} { + QUERY PLAN + |--SCAN c1 + `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN +} + +# 2017-11-23 (Thanksgiving day) +# OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code. +# +do_execsql_test 4.3.0 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(x PRIMARY KEY) WITHOUT ROWID; + CREATE TABLE t2(x); + SELECT a.x + FROM t1 AS a + LEFT JOIN t1 AS b ON (a.x=b.x) + LEFT JOIN t2 AS c ON (a.x=c.x); +} {} +do_execsql_test 4.3.1 { + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10) + INSERT INTO t1(x) SELECT x FROM c; + INSERT INTO t2(x) SELECT x+9 FROM t1; + SELECT a.x, c.x + FROM t1 AS a + LEFT JOIN t1 AS b ON (a.x=b.x) + LEFT JOIN t2 AS c ON (a.x=c.x); +} {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10} + +do_execsql_test 5.0 { + CREATE TABLE s1 (a INTEGER PRIMARY KEY); + CREATE TABLE s2 (a INTEGER PRIMARY KEY); + CREATE TABLE s3 (a INTEGER); + CREATE UNIQUE INDEX ndx on s3(a); +} +do_eqp_test 5.1 { + SELECT s1.a FROM s1 left join s2 using (a); +} {SCAN s1} + +do_eqp_test 5.2 { + SELECT s1.a FROM s1 left join s3 using (a); +} {SCAN s1} + +do_execsql_test 6.0 { + CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c); + CREATE TABLE u2(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX u1ab ON u1(b, c); +} +do_eqp_test 6.1 { + SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c ); +} {SCAN u2} + +db close +sqlite3 db :memory: +do_execsql_test 7.0 { + CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2),(3,4),(5,6); + CREATE TABLE t2(c,d); INSERT INTO t2 VALUES(2,4),(3,6); + CREATE TABLE t3(x); INSERT INTO t3 VALUES(9); + CREATE VIEW test AS + SELECT *, 'x' + FROM t1 LEFT JOIN (SELECT * FROM t2, t3) ON (c=b AND x=9) + WHERE c IS NULL; + SELECT * FROM test; +} {3 4 {} {} {} x 5 6 {} {} {} x} + +#------------------------------------------------------------------------- +# Ticket [dfd66334]. +# +reset_db +do_execsql_test 8.0 { + CREATE TABLE t0(c0); + CREATE TABLE t1(c0); +} + +do_execsql_test 8.1 { + SELECT * FROM t0 LEFT JOIN t1 + WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0); +} + +#------------------------------------------------------------------------- +# Ticket [45f4bf4eb] reported by Manuel Rigger (2020-04-25) +# +# Follow up error reported by Eric Speckman on the SQLite forum +# https://sqlite.org/forum/info/c49496d24d35bd7c (2020-08-19) +# +reset_db +do_execsql_test 9.0 { + CREATE TABLE t0(c0 INT); + CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0; + INSERT INTO t0(c0) VALUES (0); +} + +do_execsql_test 9.1 { + SELECT typeof(c0), c0 FROM v0 WHERE c0>='0' +} {integer 0} + +do_execsql_test 9.2 { + SELECT * FROM t0, v0 WHERE v0.c0 >= '0'; +} {0 0} + +do_execsql_test 9.3 { + SELECT * FROM t0 LEFT JOIN v0 WHERE v0.c0 >= '0'; +} {0 0} + +do_execsql_test 9.4 { + SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0'; +} {0 0} + +do_execsql_test 9.5 { + SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0' WHERE TRUE + UNION SELECT 0,0 WHERE 0; +} {0 0} + +do_execsql_test 9.10 { + CREATE TABLE t1 (aaa); + INSERT INTO t1 VALUES(23456); + CREATE TABLE t2(bbb); + CREATE VIEW v2(ccc) AS SELECT bbb IS 1234 FROM t2; + SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2; +} {{} 1} +optimization_control db query-flattener 0 +do_execsql_test 9.11 { + SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2; +} {{} 1} + +# 2023-03-01 https://sqlite.org/forum/forumpost/26387ea7ef +# When flattening a VIEW which is the RHS of a LEFT JOIN, always put +# an TK_IF_NULL_ROW operator on all accesses, even TK_COLUMN nodes, since +# the TK_COLUMN might reference an outer subquery. +# +reset_db +db null NULL +do_execsql_test 10.1 { + CREATE TABLE t1 (x INTEGER); + INSERT INTO t1 VALUES(1); -- Some true value + CREATE TABLE t2 (z TEXT); + INSERT INTO t2 VALUES('some value'); + CREATE TABLE t3(w TEXT); + INSERT INTO t3 VALUES('some other value'); +} +do_execsql_test 10.2 { + SELECT ( + SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE) + ) FROM t1; +} NULL +do_execsql_test 10.3 { + SELECT ( + SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v) + ) FROM t1; +} NULL +optimization_control db all 0 +do_execsql_test 10.4 { + SELECT ( + SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE) + ) FROM t1; +} NULL + +# 2023-03-02 https://sqlite.org/forum/forumpost/402f05296d +# +# The TK_IF_NULL_ROW expression node must ensure that it does not overwrite +# the result register of an OP_Once subroutine. +# +optimization_control db all 1 +do_execsql_test 11.1 { + DROP TABLE t1; + DROP TABLE t2; + DROP TABLE t3; + CREATE TABLE t1(x TEXT, y INTEGER); + INSERT INTO t1(x,y) VALUES(NULL,-2),(NULL,1),('0',2); + CREATE TABLE t2(z INTEGER); + INSERT INTO t2(z) VALUES(2),(-2); + CREATE VIEW t3 AS SELECT z, (SELECT count(*) FROM t1) AS w FROM t2; + SELECT * FROM t1 LEFT JOIN t3 ON y=z; +} {NULL -2 -2 3 NULL 1 NULL NULL 0 2 2 3} + +# 2023-03-11 https://sqlite.org/forum/forumpost/b405033490fa56d9 +# The fix that test 11.1 above checks also caused a performance regression. +# This test case verifies that the performance regression has been resolved. +# +do_execsql_test 12.1 { + DROP TABLE t1; + DROP TABLE t2; + DROP VIEW t3; + CREATE TABLE t1(a INTEGER PRIMARY KEY); + WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100) + INSERT INTO t1(a) SELECT n FROM c; + CREATE VIEW t2(b) AS SELECT a FROM t1; +} +do_vmstep_test 12.2 { + SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98; +} 2000 {99 99 100 100} +do_eqp_test 12.3 { + SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98; +} { + QUERY PLAN + |--SCAN t1 + `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN +} + +finish_test |