| # 2005 September 19 |
| # |
| # 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 left outer joins containing ON |
| # clauses that restrict the scope of the left term of the join. |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| set testprefix join5 |
| |
| |
| do_test join5-1.1 { |
| execsql { |
| BEGIN; |
| CREATE TABLE t1(a integer primary key, b integer, c integer); |
| CREATE TABLE t2(x integer primary key, y); |
| CREATE TABLE t3(p integer primary key, q); |
| INSERT INTO t3 VALUES(11,'t3-11'); |
| INSERT INTO t3 VALUES(12,'t3-12'); |
| INSERT INTO t2 VALUES(11,'t2-11'); |
| INSERT INTO t2 VALUES(12,'t2-12'); |
| INSERT INTO t1 VALUES(1, 5, 0); |
| INSERT INTO t1 VALUES(2, 11, 2); |
| INSERT INTO t1 VALUES(3, 12, 1); |
| COMMIT; |
| } |
| } {} |
| do_test join5-1.2 { |
| execsql { |
| select * from t1 left join t2 on t1.b=t2.x and t1.c=1 |
| } |
| } {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12} |
| do_test join5-1.3 { |
| execsql { |
| select * from t1 left join t2 on t1.b=t2.x where t1.c=1 |
| } |
| } {3 12 1 12 t2-12} |
| do_test join5-1.4 { |
| execsql { |
| select * from t1 left join t2 on t1.b=t2.x and t1.c=1 |
| left join t3 on t1.b=t3.p and t1.c=2 |
| } |
| } {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}} |
| do_test join5-1.5 { |
| execsql { |
| select * from t1 left join t2 on t1.b=t2.x and t1.c=1 |
| left join t3 on t1.b=t3.p where t1.c=2 |
| } |
| } {2 11 2 {} {} 11 t3-11} |
| |
| # Ticket #2403 |
| # |
| do_test join5-2.1 { |
| execsql { |
| CREATE TABLE ab(a,b); |
| INSERT INTO "ab" VALUES(1,2); |
| INSERT INTO "ab" VALUES(3,NULL); |
| |
| CREATE TABLE xy(x,y); |
| INSERT INTO "xy" VALUES(2,3); |
| INSERT INTO "xy" VALUES(NULL,1); |
| } |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 0} |
| } {2 3 {} {} {} 1 {} {}} |
| do_test join5-2.2 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 1} |
| } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}} |
| do_test join5-2.3 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON NULL} |
| } {2 3 {} {} {} 1 {} {}} |
| do_test join5-2.4 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0} |
| } {} |
| do_test join5-2.5 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0} |
| } {} |
| do_test join5-2.6 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0} |
| } {} |
| do_test join5-2.7 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1} |
| } {2 3 {} {} {} 1 {} {}} |
| do_test join5-2.8 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1} |
| } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}} |
| do_test join5-2.9 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1} |
| } {2 3 {} {} {} 1 {} {}} |
| do_test join5-2.10 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL} |
| } {} |
| do_test join5-2.11 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL} |
| } {} |
| do_test join5-2.12 { |
| execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL} |
| } {} |
| |
| # Ticket https://www.sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601 |
| # Incorrect output on a LEFT JOIN. |
| # |
| do_execsql_test join5-3.1 { |
| DROP TABLE IF EXISTS t1; |
| DROP TABLE IF EXISTS t2; |
| DROP TABLE IF EXISTS t3; |
| CREATE TABLE x1(a); |
| INSERT INTO x1 VALUES(1); |
| CREATE TABLE x2(b NOT NULL); |
| CREATE TABLE x3(c, d); |
| INSERT INTO x3 VALUES('a', NULL); |
| INSERT INTO x3 VALUES('b', NULL); |
| INSERT INTO x3 VALUES('c', NULL); |
| SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b; |
| } {1 {} {} {}} |
| do_execsql_test join5-3.2 { |
| DROP TABLE IF EXISTS t1; |
| DROP TABLE IF EXISTS t2; |
| DROP TABLE IF EXISTS t3; |
| DROP TABLE IF EXISTS t4; |
| DROP TABLE IF EXISTS t5; |
| CREATE TABLE t1(x text NOT NULL, y text); |
| CREATE TABLE t2(u text NOT NULL, x text NOT NULL); |
| CREATE TABLE t3(w text NOT NULL, v text); |
| CREATE TABLE t4(w text NOT NULL, z text NOT NULL); |
| CREATE TABLE t5(z text NOT NULL, m text); |
| INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL); |
| INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL); |
| INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL); |
| INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL); |
| INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c', |
| 'f6d7661f-4efe-4c90-87b5-858e61cd178b'); |
| SELECT * |
| FROM t3 |
| INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL |
| LEFT JOIN t4 ON t4.w = t3.w |
| LEFT JOIN t5 ON t5.z = t4.z |
| LEFT JOIN t2 ON t2.u = t5.m |
| LEFT JOIN t1 xyz ON xyz.y = t2.x; |
| } {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}} |
| do_execsql_test join5-3.3 { |
| DROP TABLE IF EXISTS x1; |
| DROP TABLE IF EXISTS x2; |
| DROP TABLE IF EXISTS x3; |
| CREATE TABLE x1(a); |
| INSERT INTO x1 VALUES(1); |
| CREATE TABLE x2(b NOT NULL); |
| CREATE TABLE x3(c, d); |
| INSERT INTO x3 VALUES('a', NULL); |
| INSERT INTO x3 VALUES('b', NULL); |
| INSERT INTO x3 VALUES('c', NULL); |
| SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b; |
| } {} |
| |
| # Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on |
| # 2015-08-20. LEFT JOIN and the push-down optimization. |
| # |
| do_execsql_test join6-4.1 { |
| SELECT * |
| FROM ( |
| SELECT 'apple' fruit |
| UNION ALL SELECT 'banana' |
| ) a |
| JOIN ( |
| SELECT 'apple' fruit |
| UNION ALL SELECT 'banana' |
| ) b ON a.fruit=b.fruit |
| LEFT JOIN ( |
| SELECT 1 isyellow |
| ) c ON b.fruit='banana'; |
| } {apple apple {} banana banana 1} |
| do_execsql_test join6-4.2 { |
| SELECT * |
| FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana') |
| LEFT JOIN (SELECT 1) ON fruit='banana'; |
| } {apple {} banana 1} |
| |
| #------------------------------------------------------------------------- |
| do_execsql_test 5.0 { |
| CREATE TABLE y1(x, y, z); |
| INSERT INTO y1 VALUES(0, 0, 1); |
| CREATE TABLE y2(a); |
| } |
| |
| do_execsql_test 5.1 { |
| SELECT count(z) FROM y1 LEFT JOIN y2 ON x GROUP BY y; |
| } 1 |
| |
| do_execsql_test 5.2 { |
| SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x GROUP BY y; |
| } 1 |
| |
| do_execsql_test 5.3 { |
| CREATE VIEW v1 AS SELECT x, y, z FROM y1; |
| SELECT count(z) FROM v1 LEFT JOIN y2 ON x GROUP BY y; |
| } 1 |
| |
| do_execsql_test 5.4 { |
| SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x |
| } 1 |
| |
| do_execsql_test 5.5 { |
| SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x |
| } {0 0 1 {}} |
| |
| finish_test |