gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/join2.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set testprefix join2 19 20 do_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} 29 do_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}; 38 do_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 48 do_test join2-1.4 { 49 execsql { 50 SELECT * FROM 51 t1 NATURAL JOIN t2 NATURAL JOIN t3 52 } 53 } {1 11 111 1111} 54 do_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 {}} 60 do_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} 66 do_test join2-1.6-rj { 67 execsql { 68 SELECT * FROM 69 t2 NATURAL RIGHT OUTER JOIN t1 NATURAL JOIN t3 70 } 71 } {11 111 1 1111} 72 ifcapable subquery { 73 do_test join2-1.7 { 74 execsql { 75 SELECT * FROM 76 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 FROM 82 t2 NATURAL JOIN t3 NATURAL RIGHT JOIN t1 83 } 84 } {1 11 111 1111 2 22 {} {} 3 33 {} {}} 85 } 86 87 #------------------------------------------------------------------------- 88 # Check that ticket [25e335f802ddc] has been resolved. It should be an 89 # error for the ON clause of a LEFT JOIN to refer to a table to its right. 90 # 91 do_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 100 do_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}} 103 do_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}} 106 do_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 to 112 # 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 # 115 reset_db 116 do_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 from 121 -- the join queries below, but if t3_1 were used in its place it would 122 -- 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 127 do_eqp_test 3.1 { 128 SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3); 129 } { 130 QUERY PLAN 131 |--SCAN t1 132 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN 133 } 134 135 do_eqp_test 3.2 { 136 SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3); 137 } { 138 QUERY PLAN 139 |--SCAN t1 140 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN 141 } 142 143 #------------------------------------------------------------------------- 144 # Test that tables other than the rightmost can be omitted from a 145 # LEFT JOIN query. 146 # 147 do_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 } 160 do_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 {}} 163 do_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 167 do_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 171 do_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 175 do_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 PLAN 179 |--SCAN c1 180 |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN 181 `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN 182 } 183 do_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 PLAN 187 |--SCAN c1 188 `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN 189 } 190 191 do_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 } 207 do_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 {}} 210 do_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 214 do_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 218 do_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 222 do_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 PLAN 226 |--SCAN c1 227 |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?) LEFT-JOIN 228 `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN 229 } 230 do_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 PLAN 234 |--SCAN c1 235 `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN 236 } 237 238 # 2017-11-23 (Thanksgiving day) 239 # OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code. 240 # 241 do_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.x 247 FROM t1 AS a 248 LEFT JOIN t1 AS b ON (a.x=b.x) 249 LEFT JOIN t2 AS c ON (a.x=c.x); 250 } {} 251 do_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.x 256 FROM t1 AS a 257 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 261 do_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 } 267 do_eqp_test 5.1 { 268 SELECT s1.a FROM s1 left join s2 using (a); 269 } {SCAN s1} 270 271 do_eqp_test 5.2 { 272 SELECT s1.a FROM s1 left join s3 using (a); 273 } {SCAN s1} 274 275 do_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 } 280 do_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 284 db close 285 sqlite3 db :memory: 286 do_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 AS 291 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 # 300 reset_db 301 do_execsql_test 8.0 { 302 CREATE TABLE t0(c0); 303 CREATE TABLE t1(c0); 304 } 305 306 do_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 forum 315 # https://sqlite.org/forum/info/c49496d24d35bd7c (2020-08-19) 316 # 317 reset_db 318 do_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 324 do_execsql_test 9.1 { 325 SELECT typeof(c0), c0 FROM v0 WHERE c0>='0' 326 } {integer 0} 327 328 do_execsql_test 9.2 { 329 SELECT * FROM t0, v0 WHERE v0.c0 >= '0'; 330 } {0 0} 331 332 do_execsql_test 9.3 { 333 SELECT * FROM t0 LEFT JOIN v0 WHERE v0.c0 >= '0'; 334 } {0 0} 335 336 do_execsql_test 9.4 { 337 SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0'; 338 } {0 0} 339 340 do_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 345 do_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} 352 optimization_control db query-flattener 0 353 do_execsql_test 9.11 { 354 SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2; 355 } {{} 1} 356 357 358 finish_test