Debian Codegraph @

test/join8.test

raw
1 # 2022-04-12
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 #
12 # This file implements tests for RIGHT and FULL OUTER JOINs.
13
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16
17 ifcapable !vtab {
18 finish_test
19 return
20 }
21
22 db null NULL
23 # EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the
24 # same as "FULL JOIN".
25 do_execsql_test join8-10 {
26 CREATE TABLE t1(a,b,c);
27 CREATE TABLE t2(x,y);
28 CREATE INDEX t2x ON t2(x);
29 SELECT avg(DISTINCT b) FROM (SELECT * FROM t2 LEFT RIGHT JOIN t1 ON c);
30 } {NULL}
31
32 # Pending optimization opportunity:
33 # Row-value initialization subroutines must be called from with the
34 # RIGHT JOIN body subroutine before the first use of any register containing
35 # the results of that subroutine. This seems dodgy. Test case:
36 #
37 reset_db
38 do_execsql_test join8-1000 {
39 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT,b,c,d,e,f,g,h,j,k,l,m,n,o,p,q,r,s);
40 CREATE INDEX t1x1 ON t1(g+h,j,k);
41 CREATE INDEX t1x2 ON t1(b);
42 INSERT INTO t1 DEFAULT VALUES;
43 } {}
44 do_catchsql_test join8-1010 {
45 SELECT a
46 FROM (
47 SELECT a
48 FROM (
49 SELECT a
50 FROM (
51 SELECT a FROM t1 NATURAL LEFT JOIN t1
52 WHERE (b, 2 ) IS ( SELECT 2 IN(2,2),2)
53 )
54 NATURAL LEFT FULL JOIN t1
55 WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0)
56 ORDER BY a ASC
57 )
58 NATURAL LEFT JOIN t1
59 WHERE (b, 2 ) IS ( SELECT 3 IN(3,3),3)
60 )
61 NATURAL LEFT FULL JOIN t1
62 WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0)
63 ORDER BY a ASC;
64 } {0 1}
65
66 # Pending issue #2: (now resolved)
67 # Jump to addrHalt inside the RIGHT JOIN body subroutine bypasses the
68 # OP_Return, resulting in a subroutine loop. Test case:
69 #
70 reset_db
71 do_execsql_test join8-2000 {
72 CREATE TABLE t1(a int, b int, c int);
73 INSERT INTO t1 VALUES(1,2,3),(4,5,6);
74 CREATE TABLE t2(d int, e int);
75 INSERT INTO t2 VALUES(3,333),(4,444);
76 CREATE TABLE t3(f int, g int);
77 PRAGMA automatic_index=off;
78 } {}
79 do_catchsql_test join8-2010 {
80 SELECT * FROM t1 RIGHT JOIN t2 ON c=d JOIN t3 ON f=e;
81 } {0 {}}
82
83 # Demonstrate that nested FULL JOINs and USING clauses work
84 #
85 reset_db
86 load_static_extension db series
87 do_execsql_test join8-3000 {
88 CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT);
89 CREATE TABLE t2(id INTEGER PRIMARY KEY, b INT);
90 CREATE TABLE t3(id INTEGER PRIMARY KEY, c INT);
91 CREATE TABLE t4(id INTEGER PRIMARY KEY, d INT);
92 CREATE TABLE t5(id INTEGER PRIMARY KEY, e INT);
93 CREATE TABLE t6(id INTEGER PRIMARY KEY, f INT);
94 CREATE TABLE t7(id INTEGER PRIMARY KEY, g INT);
95 CREATE TABLE t8(id INTEGER PRIMARY KEY, h INT);
96 INSERT INTO t1 SELECT value, 1 FROM generate_series(1,256) WHERE value & 1;
97 INSERT INTO t2 SELECT value, 1 FROM generate_series(1,256) WHERE value & 2;
98 INSERT INTO t3 SELECT value, 1 FROM generate_series(1,256) WHERE value & 4;
99 INSERT INTO t4 SELECT value, 1 FROM generate_series(1,256) WHERE value & 8;
100 INSERT INTO t5 SELECT value, 1 FROM generate_series(1,256) WHERE value & 16;
101 INSERT INTO t6 SELECT value, 1 FROM generate_series(1,256) WHERE value & 32;
102 INSERT INTO t7 SELECT value, 1 FROM generate_series(1,256) WHERE value & 64;
103 INSERT INTO t8 SELECT value, 1 FROM generate_series(1,256) WHERE value & 128;
104 CREATE TABLE t9 AS
105 SELECT id, h, g, f, e, d, c, b, a
106 FROM t1
107 NATURAL FULL JOIN t2
108 NATURAL FULL JOIN t3
109 NATURAL FULL JOIN t4
110 NATURAL FULL JOIN t5
111 NATURAL FULL JOIN t6
112 NATURAL FULL JOIN t7
113 NATURAL FULL JOIN t8;
114 } {}
115 do_execsql_test join8-3010 {
116 SELECT count(*) FROM t9;
117 } {255}
118 do_execsql_test join8-3020 {
119 SELECT id, count(*) FROM t9 GROUP BY id HAVING count(*)!=1;
120 } {}
121 do_execsql_test join8-3030 {
122 UPDATE t9 SET a=0 WHERE a IS NULL;
123 UPDATE t9 SET b=0 WHERE b IS NULL;
124 UPDATE t9 SET c=0 WHERE c IS NULL;
125 UPDATE t9 SET d=0 WHERE d IS NULL;
126 UPDATE t9 SET e=0 WHERE e IS NULL;
127 UPDATE t9 SET f=0 WHERE f IS NULL;
128 UPDATE t9 SET g=0 WHERE g IS NULL;
129 UPDATE t9 SET h=0 WHERE h IS NULL;
130 SELECT count(*) FROM t9 WHERE id=128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
131 } {255}
132 do_execsql_test join8-3040 {
133 SELECT * FROM t9 WHERE id<>128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
134 } {}
135
136 # 2022-04-21 dbsqlfuzz find
137 #
138 reset_db
139 do_execsql_test join8-4000 {
140 CREATE TABLE t1(x INTEGER PRIMARY KEY, a, b);
141 INSERT INTO t1 VALUES(1,5555,4);
142 CREATE INDEX i1a ON t1(a);
143 CREATE INDEX i1b ON t1(b);
144 SELECT a FROM t1 NATURAL RIGHT JOIN t1 WHERE a=5555 OR (1,b)==(SELECT 2 IN (2,2),4);
145 } {5555}
146
147 # 2022-04-23 dbsqlfuzz c7ee5500e3abddec3557016de777713b80c790d3
148 # Escape from the right-join body subroutine via the ORDER BY LIMIT optimization.
149 #
150 reset_db
151 db null -
152 do_catchsql_test join8-5000 {
153 CREATE TABLE t1(x);
154 INSERT INTO t1(x) VALUES(NULL),(NULL);
155 CREATE TABLE t2(c, d);
156 INSERT INTO t2(c,d) SELECT x, x FROM t1;
157 CREATE INDEX t2dc ON t2(d, c);
158 SELECT (SELECT c FROM sqlite_temp_schema FULL JOIN t2 ON d IN (1,2,3) ORDER BY d) AS x FROM t1;
159 } {0 {- -}}
160
161 # 2022-04-29 dbsqlfuzz 19f1102a70cf966ab249de56d944fc20dbebcfcf
162 # Verification of testtag-20230227b and testtag-20230227c
163 #
164 reset_db
165 do_execsql_test join8-6000 {
166 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d REAL);
167 INSERT INTO t1 VALUES(1,'A','aa',2.5);
168 SELECT * FROM t1 AS t2 NATURAL RIGHT JOIN t1 AS t3
169 WHERE (a,b) IN (SELECT rowid, b FROM t1);
170 } {1 A aa 2.5}
171 do_execsql_test join8-6010 {
172 DROP TABLE IF EXISTS t1;
173 CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, c TEXT, d INT) WITHOUT ROWID;
174 INSERT INTO t1 VALUES(15,'xray','baker',42);
175 SELECT value, t1.* FROM json_each('7') NATURAL RIGHT JOIN t1
176 WHERE (a,b) IN (SELECT a, b FROM t1);
177 } {7 15 xray baker 42}
178 do_execsql_test join8-6020 {
179 DROP TABLE IF EXISTS t1;
180 CREATE TABLE t1(a INTEGER PRIMARY KEY,b);
181 INSERT INTO t1 VALUES(0,NULL),(1,2);
182 SELECT value, t1.* FROM json_each('17') NATURAL RIGHT JOIN t1
183 WHERE (a,b) IN (SELECT rowid, b FROM t1);
184 } {17 1 2}
185 do_execsql_test join8-6021 {
186 SELECT value, t1.* FROM json_each('null') NATURAL RIGHT JOIN t1
187 WHERE (a,b) IN (SELECT rowid, b FROM t1);
188 } {{} 1 2}
189 do_execsql_test join8-6022 {
190 CREATE TABLE a(key TEXT);
191 INSERT INTO a(key) VALUES('a'),('b');
192 SELECT quote(a.key), b.value
193 FROM a RIGHT JOIN json_each('["a","c"]') AS b ON a.key=b.value;
194 } {'a' a NULL c}
195
196 # Bloom filter usage by RIGHT and FULL JOIN
197 #
198 reset_db
199 do_execsql_test join8-7000 {
200 CREATE TABLE t1(a INT, b INT, c INT, d INT);
201 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<10)
202 INSERT INTO t1(a,b,c,d) SELECT x, x+100, x+200, x+300 FROM c;
203 CREATE TABLE t2(b INT, x INT);
204 INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%2=0;
205 CREATE INDEX t2b ON t2(b);
206 CREATE TABLE t3(c INT, y INT);
207 INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%3=0;
208 CREATE INDEX t3c ON t3(c);
209 CREATE TABLE t4(d INT, z INT);
210 INSERT INTO t4(d,z) SELECT d, a FROM t1 WHERE a%5=0;
211 CREATE INDEX t4d ON t4(d);
212 INSERT INTO t1(a,b,c,d) VALUES
213 (96,NULL,296,396),
214 (97,197,NULL,397),
215 (98,198,298,NULL),
216 (99,NULL,NULL,NULL);
217 ANALYZE sqlite_schema;
218 INSERT INTO sqlite_stat1 VALUES('t4','t4d','20 1');
219 INSERT INTO sqlite_stat1 VALUES('t3','t3c','32 1');
220 INSERT INTO sqlite_stat1 VALUES('t2','t2b','48 1');
221 INSERT INTO sqlite_stat1 VALUES('t1',NULL,'100');
222 ANALYZE sqlite_schema;
223 } {}
224 db null -
225 do_execsql_test join8-7010 {
226 WITH t0 AS MATERIALIZED (
227 SELECT t1.*, t2.*, t3.*
228 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
229 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
230 )
231 SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
232 ORDER BY coalesce(t0.a, t0.y+200, t4.d);
233 } {
234 6 106 206 306 106 6 206 6 - -
235 - - - - - - 200 0 - -
236 - - - - - - 203 3 - -
237 - - - - - - 209 9 - -
238 - - - - - - - - 300 0
239 - - - - - - - - 305 5
240 - - - - - - - - 310 10
241 }
242
243 # EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the
244 # same as "FULL JOIN".
245 do_execsql_test join8-7011 {
246 WITH t0 AS MATERIALIZED (
247 SELECT t1.*, t2.*, t3.*
248 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
249 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
250 )
251 SELECT * FROM t0 LEFT RIGHT JOIN t4 ON t0.a=t4.d AND t4.z>0
252 ORDER BY coalesce(t0.a, t0.y+200, t4.d);
253 } {
254 6 106 206 306 106 6 206 6 - -
255 - - - - - - 200 0 - -
256 - - - - - - 203 3 - -
257 - - - - - - 209 9 - -
258 - - - - - - - - 300 0
259 - - - - - - - - 305 5
260 - - - - - - - - 310 10
261 }
262
263 do_execsql_test join8-7020 {
264 EXPLAIN QUERY PLAN
265 WITH t0 AS MATERIALIZED (
266 SELECT t1.*, t2.*, t3.*
267 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
268 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
269 )
270 SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
271 ORDER BY coalesce(t0.a, t0.y+200, t4.d);
272 } {/.*BLOOM FILTER ON t2.*BLOOM FILTER ON t3.*/}
273
274 # 2022-05-12 Difference with PG found (by Dan) while exploring
275 # https://sqlite.org/forum/forumpost/677a0ab93fcd9ccd
276 #
277 reset_db
278 do_execsql_test join8-8000 {
279 CREATE TABLE t1(a INT, b INT);
280 CREATE TABLE t2(c INT, d INT);
281 CREATE TABLE t3(e INT, f INT);
282 INSERT INTO t1 VALUES(1, 2);
283 INSERT INTO t2 VALUES(3, 4);
284 INSERT INTO t3 VALUES(5, 6);
285 } {}
286 do_execsql_test join8-8010 {
287 SELECT *
288 FROM t3 LEFT JOIN t2 ON true
289 JOIN t1 ON (t3.e IS t2.c);
290 } {}
291 do_execsql_test join8-8020 {
292 SELECT *
293 FROM t3 LEFT JOIN t2 ON true
294 JOIN t1 ON (t3.e IS NOT DISTINCT FROM t2.c);
295 } {}
296
297 # 2022-05-13 The idea of reusing subquery cursors does not
298 # work, if the cursors are used both for scanning and lookups.
299 #
300 reset_db
301 db null -
302 do_execsql_test join8-9000 {
303 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d REAL);
304 INSERT INTO t1 VALUES(1,'E','bb',NULL),(2,NULL,NULL,NULL);
305 SELECT * FROM t1 NATURAL RIGHT JOIN t1 AS t2 WHERE (a,b) IN (SELECT a+0, b FROM t1);
306 } {1 E bb -}
307
308 # 2022-05-14 https://sqlite.org/forum/forumpost/c06b10ad7e
309 #
310 reset_db
311 db null -
312 do_execsql_test join8-10000 {
313 CREATE TABLE t1(c0 INT UNIQUE);
314 CREATE TABLE t2(c0);
315 CREATE TABLE t2i(c0 INT);
316 CREATE TABLE t3(c0 INT);
317 INSERT INTO t1 VALUES(1);
318 INSERT INTO t2 VALUES(2);
319 INSERT INTO t2i VALUES(2);
320 INSERT INTO t3 VALUES(3);
321 } {}
322 do_execsql_test join8-10010 {
323 SELECT DISTINCT t1.c0, t3.c0
324 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
325 } {- 3}
326 do_execsql_test join8-10020 {
327 SELECT t1.c0, t3.c0
328 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
329 } {- 3}
330 do_execsql_test join8-10030 {
331 SELECT DISTINCT t1.c0, t3.c0
332 FROM t2 NATURAL CROSS JOIN t1 RIGHT JOIN t3 ON t1.c0;
333 } {- 3}
334 do_execsql_test join8-10040 {
335 SELECT t1.c0, t3.c0
336 FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;
337 } {- 3}
338 do_execsql_test join8-10050 {
339 SELECT DISTINCT t1.c0, t3.c0
340 FROM t2i NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
341 } {- 3}
342 do_execsql_test join8-10060 {
343 SELECT DISTINCT +t1.c0, t3.c0
344 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
345 } {- 3}
346 do_execsql_test join8-10070 {
347 SELECT DISTINCT +t1.c0, t3.c0
348 FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;
349 } {- 3}
350 do_execsql_test join8-10080 {
351 SELECT DISTINCT t1.c0, t3.c0
352 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0<>0;
353 } {- 3}
354
355 # 2022-05-14
356 # index-on-expr scan on a RIGHT JOIN
357 # dbsqlfuzz 39ee60004ff027a9e2846cf76e02cd5ac0953739
358 #
359 reset_db
360 db null -
361 do_execsql_test join8-11000 {
362 CREATE TABLE t1(a);
363 CREATE TABLE t2(b);
364 INSERT INTO t2 VALUES(0),(1),(2);
365 SELECT * FROM t1 RIGHT JOIN t2 ON (a=b) WHERE 99+(b+1)!=99;
366 } {- 0 - 1 - 2}
367 do_execsql_test join8-11010 {
368 CREATE INDEX t2b ON t2(b+1) WHERE b IS NOT NULL;
369 SELECT * FROM t1 RIGHT JOIN t2 ON (a=b) WHERE 99+(b+1)!=99;
370 } {- 0 - 1 - 2}
371 do_execsql_test join8-11020 {
372 DROP TABLE t1;
373 DROP TABLE t2;
374 CREATE TABLE t1(a);
375 CREATE TABLE t2(b, c, d);
376 INSERT INTO t2 VALUES(1, 3, 'not-4');
377 SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
378 } {1 not-4}
379 do_execsql_test join8-11030 {
380 CREATE INDEX i2 ON t2((b+0), d);
381 SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
382 } {1 not-4}
383 do_execsql_test join8-11040 {
384 DROP INDEX i2;
385 CREATE INDEX i2 ON t2((b+0), d) WHERE d IS NOT NULL;
386 SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
387 } {1 not-4}
388
389 # 2022-05-23
390 # NATURAL JOIN name resolution is more forgiving with LEFT JOIN
391 # https://sqlite.org/forum/forumpost/e90a8e6e6f
392 #
393 reset_db
394 db null -
395 do_execsql_test join8-12000 {
396 CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(0),(1);
397 CREATE TABLE t2(a INT); INSERT INTO t2 VALUES(0),(2);
398 CREATE TABLE t3(a INT); INSERT INTO t3 VALUES(0),(3);
399 } {}
400 do_catchsql_test join8-12010 {
401 SELECT * FROM t1 RIGHT JOIN t2 ON t2.a<>0 NATURAL RIGHT JOIN t3;
402 } {1 {ambiguous reference to a in USING()}}
403 do_catchsql_test join8-12020 {
404 SELECT * FROM t1 RIGHT JOIN t2 ON t2.a<>0 NATURAL LEFT JOIN t3;
405 } {1 {ambiguous reference to a in USING()}}
406 do_catchsql_test join8-12030 {
407 SELECT * FROM t1 LEFT JOIN t2 ON t2.a<>0 NATURAL RIGHT JOIN t3;
408 } {1 {ambiguous reference to a in USING()}}
409
410 # The following query should probably also return the same error as the
411 # previous three cases. However, historical versions of SQLite have always
412 # let it pass. We will not "fix" this, since to do so might break legacy
413 # applications.
414 #
415 do_catchsql_test join8-12040 {
416 SELECT * FROM t1 LEFT JOIN t2 ON t2.a<>0 NATURAL LEFT JOIN t3;
417 } {0 {0 2 1 2}}
418
419 # 2022-05-24
420 # https://sqlite.org/forum/forumpost/687b0bf563a1d4f1
421 #
422 reset_db
423 do_execsql_test join8-13000 {
424 CREATE TABLE t0(t TEXT, u TEXT); INSERT INTO t0 VALUES('t', 'u');
425 CREATE TABLE t1(v TEXT, w TEXT); INSERT INTO t1 VALUES('v', 'w');
426 CREATE TABLE t2(x TEXT, y TEXT); INSERT INTO t2 VALUES('x', 'y');
427 SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false;
428 SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false
429 WHERE t2.y ISNULL;
430 } {}
431
432 # 2022-05-25
433 # https://sqlite.org/forum/forumpost/5cfe08eed6
434 #
435 reset_db
436 do_execsql_test join8-14000 {
437 CREATE TABLE t0(a TEXT, b TEXT, c TEXT);
438 CREATE TABLE t1(a TEXT);
439 INSERT INTO t1 VALUES('1');
440 CREATE VIEW v0 AS SELECT 'xyz' AS d;
441 SELECT * FROM v0 RIGHT JOIN t1 ON t1.a<>'' INNER JOIN t0 ON t0.c<>'';
442 SELECT * FROM v0 RIGHT JOIN t1 ON t1.a<>'' INNER JOIN t0 ON t0.c<>'' WHERE b ISNULL;
443 } {}
444 do_execsql_test join8-14010 {
445 CREATE TABLE y0(a INT);
446 CREATE TABLE y1(b INT); INSERT INTO y1 VALUES(1), (2);
447 CREATE TABLE y2(c INT); INSERT INTO y2 VALUES(3), (4);
448 } {}
449 db null -
450 do_execsql_test join8-14020 {
451 SELECT * FROM y0 RIGHT JOIN y1 ON true INNER JOIN y2 ON true WHERE y2.c!=99 AND y2.c!=98;
452 } {
453 - 1 3
454 - 1 4
455 - 2 3
456 - 2 4
457 }
458
459 # 2022-05-30
460 # https://sqlite.org/forum/forumpost/3902c7b833
461 #
462 reset_db
463 do_execsql_test join8-15000 {
464 CREATE TABLE t1(x INT);
465 CREATE TABLE t2(y INT);
466 CREATE TABLE t3(z INT);
467 INSERT INTO t1 VALUES(10);
468 INSERT INTO t3 VALUES(20),(30);
469 }
470 do_execsql_test join8-15010 {
471 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL;
472 } {}
473 do_execsql_test join8-15020 {
474 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL
475 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600);
476 } {}
477 do_execsql_test join8-15100 {
478 PRAGMA automatic_index = 0;
479 CREATE TABLE t4(x TEXT);
480 CREATE TABLE t5(y TEXT);
481 CREATE TABLE t6(z TEXT);
482 INSERT INTO t4 VALUES('a'), ('b');
483 INSERT INTO t5 VALUES('b'), ('c');
484 INSERT INTO t6 VALUES('a'), ('d');
485 } {}
486 db null -
487 do_execsql_test join8-15110 {
488 SELECT * FROM t4 LEFT JOIN t5 ON x=y LEFT JOIN t6 ON (x=z) ORDER BY +x;
489 } {a - a b b -}
490 do_execsql_test join8-15120 {
491 SELECT * FROM t4 LEFT JOIN t5 ON x=y LEFT JOIN t6 ON (x=z)
492 WHERE t5.y!='x' AND t4.x!='x';
493 } {b b -}
494
495 # 2022-05-31
496 # https://sqlite.org/forum/forumpost/c2554d560b
497 reset_db
498 do_execsql_test join8-16000 {
499 CREATE TABLE t1(a TEXT);
500 CREATE TABLE t2(b TEXT);
501 CREATE TABLE t3(c TEXT);
502 INSERT INTO t2(b) VALUES ('x');
503 INSERT INTO t3(c) VALUES ('y'), ('z');
504 } {}
505 db null -
506 do_execsql_test join8-16010 {
507 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'';
508 } {- x -}
509 do_execsql_test join8-16020 {
510 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NULL;
511 } {- x -}
512 do_execsql_test join8-16020 {
513 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NULL;
514 } {}
515 do_execsql_test join8-16030 {
516 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'';
517 } {}
518 do_execsql_test join8-16040 {
519 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c<>'';
520 } {}
521 do_execsql_test join8-16050 {
522 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NOT NULL;
523 } {}
524 do_execsql_test join8-16060 {
525 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c<>'';
526 } {}
527 do_execsql_test join8-16070 {
528 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NOT NULL;
529 } {}
530
531 # 2022-06-01
532 # https://sqlite.org/forum/forumpost/087de2d9ec
533 #
534 reset_db
535 do_execsql_test join8-17000 {
536 CREATE TABLE t1(id INTEGER PRIMARY KEY, x INT, y INT);
537 CREATE TABLE t2(z INT);
538 INSERT INTO t1(id,x,y) VALUES(1, 0, 0);
539 } {}
540 db null NULL
541 do_execsql_test join8-17010 {
542 SELECT * FROM t2 RIGHT JOIN t1 ON true;
543 } {NULL 1 0 0}
544 do_execsql_test join8-17020 {
545 SELECT 99=id AND 0=y AS "truth" FROM t2 RIGHT JOIN t1 ON true;
546 } {0}
547 do_execsql_test join8-17030 {
548 SELECT (99, 0)==(id, y) AS "truth" FROM t2 RIGHT JOIN t1;
549 } {0}
550 do_execsql_test join8-17040 {
551 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=id AND 0=y;
552 } {}
553 do_execsql_test join8-17041 {
554 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=+id AND 0=y;
555 } {}
556 do_execsql_test join8-17050 {
557 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(id,y);
558 } {}
559 do_execsql_test join8-17051 {
560 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(+id,y);
561 } {}
562 do_execsql_test join8-17060 {
563 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=id AND 0=y;
564 } {NULL 1 0 0}
565 do_execsql_test join8-17061 {
566 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=+id AND 0=y;
567 } {NULL 1 0 0}
568 do_execsql_test join8-17070 {
569 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(id,y);
570 } {NULL 1 0 0}
571 do_execsql_test join8-17071 {
572 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(+id,y);
573 } {NULL 1 0 0}
574 do_execsql_test join8-17080 {
575 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INT);
576 CREATE TABLE t4(x INT, y INT);
577 INSERT INTO t3(a,b) VALUES(1, 3);
578 } {}
579 do_execsql_test join8-17090 {
580 SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4)=(SELECT 3, 4);
581 } {1}
582 do_execsql_test join8-17091 {
583 SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4) IS (SELECT 3, 4);
584 } {1}
585
586 # 2022-06-06
587 # https://sqlite.org/forum/forumpost/206d99a16dd9212f
588 # tag-20191211-001
589 #
590 reset_db
591 do_execsql_test join8-18000 {
592 CREATE TABLE t1(a BOOLEAN); INSERT INTO t1 VALUES (false);
593 CREATE TABLE t2(x INT); INSERT INTO t2 VALUES (0);
594 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;
595 } {}
596 do_execsql_test join8-18010 {
597 CREATE INDEX t1a ON t1(a);
598 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;
599 } {}
600
601 do_execsql_test join8-18020 {
602 CREATE TABLE t3(z);
603 INSERT INTO t3 VALUES('t3value');
604 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true INNER JOIN t3 ON (x NOTNULL)=a;
605 } {}
606
607 ifcapable rtree {
608 do_execsql_test join8-18030 {
609 CREATE VIRTUAL TABLE rtree1 USING rtree(a, x1, x2);
610 INSERT INTO rtree1 VALUES(0, 0, 0);
611 }
612 do_execsql_test join8-18040 {
613 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2
614 RIGHT JOIN rtree1 ON true INNER JOIN t3 ON (x NOTNULL)=+a;
615 } {}
616 do_execsql_test join8-18050 {
617 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2
618 RIGHT JOIN rtree1 ON true INNER JOIN t3 ON (x NOTNULL)=a;
619 } {}
620 }
621
622
623 reset_db
624 do_execsql_test join8-19000 {
625 CREATE TABLE t1(a INT);
626 CREATE TABLE t2(b INT, c INT);
627 CREATE TABLE t3(d INT);
628
629 INSERT INTO t1 VALUES(10);
630 INSERT INTO t2 VALUES(50,51);
631 INSERT INTO t3 VALUES(299);
632
633 CREATE INDEX t2b ON t2( (b IS NOT NULL) );
634 }
635
636 do_execsql_test join8-19010 {
637 SELECT * FROM t1 LEFT JOIN t2 ON true INNER JOIN t3 ON (b IS NOT NULL)=0;
638 }
639
640 # 2022-06-07
641 # https://sqlite.org/forum/forumpost/323f86cc30
642 reset_db
643 do_execsql_test join8-20000 {
644 CREATE TABLE t1(x TEXT);
645 INSERT INTO t1(x) VALUES('aaa');
646 CREATE VIEW v0(y) AS SELECT x FROM t1;
647 CREATE TABLE t2(z TEXT);
648 } {}
649 db null -
650 do_execsql_test join8-20010 {
651 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';
652 } {- - aaa}
653 do_execsql_test join8-20020 {
654 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z;
655 } {- - aaa}
656 do_execsql_test join8-20030 {
657 SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';
658 } {99}
659 do_execsql_test join8-20040 {
660 SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z;
661 } {99}
662 do_execsql_test join8-20050 {
663 SELECT count(*)
664 FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'') AS "t3";
665 } {1}
666 do_execsql_test join8-20060 {
667 SELECT count(*)
668 FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'' ORDER BY z) AS "t3";
669 } {1}
670
671 # 2022-06-10
672 # https://sqlite.org/forum/forumpost/8e4c352937e82929
673 #
674 # Do not allow constant propagation between ON and WHERE clause terms.
675 # (Updated 2022-06-20) See also https://sqlite.org/forum/forumpost/57bdf2217d
676 #
677 reset_db
678 do_execsql_test join8-21000 {
679 CREATE TABLE t1(a INT,b BOOLEAN);
680 CREATE TABLE t2(c INT); INSERT INTO t2 VALUES(NULL);
681 CREATE TABLE t3(d INT);
682 }
683 do_execsql_test join8-21010 {
684 SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;
685 } {0}
686 do_execsql_test join8-22020 {
687 SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);
688 } {}
689 do_execsql_test join8-22030 {
690 DROP TABLE t1;
691 DROP TABLE t2;
692 DROP TABLE t3;
693 CREATE TABLE t1(a INT);
694 CREATE TABLE t2(b INT);
695 CREATE TABLE t3(c INTEGER PRIMARY KEY, d INT);
696 CREATE INDEX t3d ON t3(d);
697 INSERT INTO t3 VALUES(0, 0);
698 }
699 do_catchsql_test join8-22031 {
700 SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE +d = 0;
701 } {1 {ON clause references tables to its right}}
702 do_catchsql_test join8-22040 {
703 SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE d = 0;
704 } {1 {ON clause references tables to its right}}
705
706
707 # 2022-06-10
708 # https://sqlite.org/forum/forumpost/51e6959f61
709 #
710 # Restrictions on the usage of WHERE clause constraints by joins that are
711 # involved with a RIGHT JOIN must also be applied to automatic indexes.
712 #
713 reset_db
714 do_execsql_test join8-22000 {
715 CREATE TABLE t1(a INT);
716 CREATE TABLE t2(b INT);
717 CREATE TABLE t3(c TEXT); INSERT INTO t3 VALUES('x');
718 CREATE TABLE t4(d TEXT); INSERT INTO t4 VALUES('y');
719 SELECT 99
720 FROM t1
721 LEFT JOIN t2 ON true
722 RIGHT JOIN t3 ON true
723 RIGHT JOIN t4 ON true
724 WHERE a=b;
725 } {}
726
727 # 2022-06-13
728 # https://sqlite.org/forum/forumpost/b40696f501
729 #
730 # This optimization that converts "x ISNULL" into "FALSE" when column "x" has a
731 # NOT NULL constraint is too aggresive if the query contains RIGHT JOIN.
732 #
733 reset_db
734 db null -
735 do_execsql_test join8-23000 {
736 CREATE TABLE t1(a TEXT);
737 INSERT INTO t1 VALUES('c');
738 CREATE TABLE t2(b TEXT, c TEXT NOT NULL);
739 INSERT INTO t2 VALUES('a', 'b');
740 CREATE TABLE t3(d TEXT);
741 INSERT INTO t3 VALUES('x');
742 CREATE TABLE t4(e TEXT);
743 INSERT INTO t4 VALUES('y');
744 }
745 do_execsql_test join8-23010 {
746 SELECT *
747 FROM t1
748 LEFT JOIN t2 ON TRUE
749 JOIN t3 ON c=''
750 RIGHT JOIN t4 ON b='';
751 } {- - - - y}
752 do_execsql_test join8-23020 {
753 SELECT *
754 FROM t1
755 LEFT JOIN t2 ON TRUE
756 JOIN t3 ON c=''
757 RIGHT JOIN t4 ON b=''
758 WHERE d ISNULL
759 } {- - - - y}
760
761 # 2022-06-14
762 # dbsqlfuzz 2f3101834d14325a976f601b9267a0fd323d6bbd
763 #
764 # When the OP_NullRow opcode creates a new cursor, it must
765 # set the cursor to no-reuse so that an OP_OpenEphemeral in
766 # a subroutine does not try to reuse it.
767 #
768 reset_db
769 db null -
770 do_execsql_test join8-24000 {
771 CREATE TABLE t4(b INT, c INT);
772 CREATE TABLE t5(a INT, f INT);
773 INSERT INTO t5 VALUES(1,2);
774 WITH t7(x, y) AS (SELECT 100, 200 FROM t5)
775 SELECT * FROM t4 JOIN t7 ON true RIGHT JOIN (SELECT y AS z FROM t7) AS t6 ON (x=z);
776 } {- - - - 200}
777
778 # 2022-06-20
779 # forum/forumpost/6650cd40b5634f35
780 #
781 reset_db
782 do_execsql_test join8-25000 {
783 CREATE TABLE t1(a1 INT);
784 CREATE TABLE t2(b2 INT);
785 CREATE TABLE t3(c3 INT, d3 INT UNIQUE);
786 CREATE TABLE t4(e4 INT, f4 TEXT);
787 INSERT INTO t3(c3, d3) VALUES (2, 1);
788 INSERT INTO t4(f4) VALUES ('x');
789 CREATE INDEX i0 ON t3(c3) WHERE d3 ISNULL;
790 ANALYZE main;
791 }
792 db null -
793 do_execsql_test join8-25010 {
794 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
795 } {- - - - - x}
796 do_execsql_test join8-25020 {
797 SELECT 1 FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
798 } {1}
799
800 # 2022-07-13
801 # forum/forumpost/174afeae57
802 #
803 reset_db
804 db null -
805 do_execsql_test join8-26000 {
806 CREATE TABLE t1(a INT);
807 CREATE TABLE t2(b INT, c INT);
808 CREATE VIEW t3(d) AS SELECT NULL FROM t2 FULL OUTER JOIN t1 ON c=a UNION ALL SELECT b FROM t2;
809 INSERT INTO t1(a) VALUES (NULL);
810 INSERT INTO t2(b, c) VALUES (99, NULL);
811 SELECT DISTINCT b, c, d FROM t2, t3 WHERE b<>0
812 UNION SELECT DISTINCT b, c, d FROM t2, t3 WHERE b ISNULL;
813 } {99 - - 99 - 99}
814
815 finish_test