1# 2002 May 24 2# 3# The author disclaims copyright to this source code. In place of 4# 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.tcl 18set testprefix join2 19 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 * FROM 51 t1 NATURAL JOIN t2 NATURAL JOIN t3 52 } 53} {1 11 111 1111} 54do_test join2-1.5 { 55 execsql { 56 SELECT * FROM 57 t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t3 58 } 59} {1 11 111 1111 3 33 333 {}} 60do_test join2-1.6 { 61 execsql { 62 SELECT * FROM 63 t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3 64 } 65} {1 11 111 1111} 66ifcapable subquery { 67 do_test join2-1.7 { 68 execsql { 69 SELECT * FROM 70 t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3) 71 } 72 } {1 11 111 1111 2 22 {} {} 3 33 {} {}} 73} 74 75#------------------------------------------------------------------------- 76# Check that ticket [25e335f802ddc] has been resolved. It should be an 77# error for the ON clause of a LEFT JOIN to refer to a table to its right. 78# 79do_execsql_test 2.0 { 80 CREATE TABLE aa(a); 81 CREATE TABLE bb(b); 82 CREATE TABLE cc(c); 83 INSERT INTO aa VALUES('one'); 84 INSERT INTO bb VALUES('one'); 85 INSERT INTO cc VALUES('one'); 86} 87 88do_catchsql_test 2.1 { 89 SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=c); 90} {1 {ON clause references tables to its right}} 91do_catchsql_test 2.2 { 92 SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c); 93} {0 {one one one}} 94 95#------------------------------------------------------------------------- 96# Test that a problem causing where.c to overlook opportunities to 97# omit unnecessary tables from a LEFT JOIN when UNIQUE, NOT NULL column 98# that makes this possible happens to be the leftmost in its table. 99# 100reset_db 101do_execsql_test 3.0 { 102 CREATE TABLE t1(k1 INTEGER PRIMARY KEY, k2, k3); 103 CREATE TABLE t2(k2 INTEGER PRIMARY KEY, v2); 104 105 -- Prior to this problem being fixed, table t3_2 would be omitted from 106 -- the join queries below, but if t3_1 were used in its place it would 107 -- not. 108 CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID; 109 CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID; 110} 111 112do_eqp_test 3.1 { 113 SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3); 114} { 115 0 0 0 {SCAN TABLE t1} 116 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} 117} 118 119do_eqp_test 3.2 { 120 SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3); 121} { 122 0 0 0 {SCAN TABLE t1} 123 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} 124} 125 126#------------------------------------------------------------------------- 127# Test that tables other than the rightmost can be omitted from a 128# LEFT JOIN query. 129# 130do_execsql_test 4.0 { 131 CREATE TABLE c1(k INTEGER PRIMARY KEY, v1); 132 CREATE TABLE c2(k INTEGER PRIMARY KEY, v2); 133 CREATE TABLE c3(k INTEGER PRIMARY KEY, v3); 134 135 INSERT INTO c1 VALUES(1, 2); 136 INSERT INTO c2 VALUES(2, 3); 137 INSERT INTO c3 VALUES(3, 'v3'); 138 139 INSERT INTO c1 VALUES(111, 1112); 140 INSERT INTO c2 VALUES(112, 1113); 141 INSERT INTO c3 VALUES(113, 'v1113'); 142} 143do_execsql_test 4.1.1 { 144 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); 145} {2 v3 1112 {}} 146do_execsql_test 4.1.2 { 147 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); 148} {2 v3 1112 {}} 149 150do_execsql_test 4.1.3 { 151 SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); 152} {2 v3 1112 {}} 153 154do_execsql_test 4.1.4 { 155 SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); 156} {2 v3 2 v3 1112 {} 1112 {}} 157 158do_eqp_test 4.2.1 { 159 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); 160} { 161 0 0 0 {SCAN TABLE c1} 162 0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)} 163 0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)} 164} 165do_eqp_test 4.2.2 { 166 SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); 167} { 168 0 0 0 {SCAN TABLE c1} 169 0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)} 170} 171 172# 2017-11-23 (Thanksgiving day) 173# OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code. 174# 175do_execsql_test 4.3.0 { 176 DROP TABLE IF EXISTS t1; 177 DROP TABLE IF EXISTS t2; 178 CREATE TABLE t1(x PRIMARY KEY) WITHOUT ROWID; 179 CREATE TABLE t2(x); 180 SELECT a.x 181 FROM t1 AS a 182 LEFT JOIN t1 AS b ON (a.x=b.x) 183 LEFT JOIN t2 AS c ON (a.x=c.x); 184} {} 185do_execsql_test 4.3.1 { 186 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10) 187 INSERT INTO t1(x) SELECT x FROM c; 188 INSERT INTO t2(x) SELECT x+9 FROM t1; 189 SELECT a.x, c.x 190 FROM t1 AS a 191 LEFT JOIN t1 AS b ON (a.x=b.x) 192 LEFT JOIN t2 AS c ON (a.x=c.x); 193} {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10} 194 195finish_test 196