Debian Codegraph @

test/join.test

raw
1 # 2002-05-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
19 do_test join-1.1 {
20 execsql {
21 CREATE TABLE t1(a,b,c);
22 INSERT INTO t1 VALUES(1,2,3);
23 INSERT INTO t1 VALUES(2,3,4);
24 INSERT INTO t1 VALUES(3,4,5);
25 SELECT * FROM t1;
26 }
27 } {1 2 3 2 3 4 3 4 5}
28 do_test join-1.2 {
29 execsql {
30 CREATE TABLE t2(b,c,d);
31 INSERT INTO t2 VALUES(1,2,3);
32 INSERT INTO t2 VALUES(2,3,4);
33 INSERT INTO t2 VALUES(3,4,5);
34 SELECT * FROM t2;
35 }
36 } {1 2 3 2 3 4 3 4 5}
37
38 # A FROM clause of the form: "<table>, <table> ON <expr>" is not
39 # allowed by the SQLite syntax diagram, nor by any other SQL database
40 # engine that we are aware of. Nevertheless, historic versions of
41 # SQLite have allowed it. We need to continue to support it moving
42 # forward to prevent breakage of legacy applications. Though, we will
43 # not advertise it as being supported.
44 #
45 do_execsql_test join-1.2.1 {
46 SELECT t1.rowid, t2.rowid, '|' FROM t1, t2 ON t1.a=t2.b;
47 } {1 1 | 2 2 | 3 3 |}
48
49 do_test join-1.3 {
50 execsql2 {
51 SELECT * FROM t1 NATURAL JOIN t2;
52 }
53 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
54 do_test join-1.3.1 {
55 execsql2 {
56 SELECT * FROM t2 NATURAL JOIN t1;
57 }
58 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
59 do_test join-1.3.2 {
60 execsql2 {
61 SELECT * FROM t2 AS x NATURAL JOIN t1;
62 }
63 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
64 do_test join-1.3.3 {
65 execsql2 {
66 SELECT * FROM t2 NATURAL JOIN t1 AS y;
67 }
68 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
69 do_test join-1.3.4 {
70 execsql {
71 SELECT b FROM t1 NATURAL JOIN t2;
72 }
73 } {2 3}
74
75 # ticket #3522
76 do_test join-1.3.5 {
77 execsql2 {
78 SELECT t2.* FROM t2 NATURAL JOIN t1
79 }
80 } {b 2 c 3 d 4 b 3 c 4 d 5}
81 do_test join-1.3.6 {
82 execsql2 {
83 SELECT xyzzy.* FROM t2 AS xyzzy NATURAL JOIN t1
84 }
85 } {b 2 c 3 d 4 b 3 c 4 d 5}
86 do_test join-1.3.7 {
87 execsql2 {
88 SELECT t1.* FROM t2 NATURAL JOIN t1
89 }
90 } {a 1 b 2 c 3 a 2 b 3 c 4}
91 do_test join-1.3.8 {
92 execsql2 {
93 SELECT xyzzy.* FROM t2 NATURAL JOIN t1 AS xyzzy
94 }
95 } {a 1 b 2 c 3 a 2 b 3 c 4}
96 do_test join-1.3.9 {
97 execsql2 {
98 SELECT aaa.*, bbb.* FROM t2 AS aaa NATURAL JOIN t1 AS bbb
99 }
100 } {b 2 c 3 d 4 a 1 b 2 c 3 b 3 c 4 d 5 a 2 b 3 c 4}
101 do_test join-1.3.10 {
102 execsql2 {
103 SELECT t1.*, t2.* FROM t2 NATURAL JOIN t1
104 }
105 } {a 1 b 2 c 3 b 2 c 3 d 4 a 2 b 3 c 4 b 3 c 4 d 5}
106
107
108 do_test join-1.4.1 {
109 execsql2 {
110 SELECT * FROM t1 INNER JOIN t2 USING(b,c);
111 }
112 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
113 do_test join-1.4.2 {
114 execsql2 {
115 SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
116 }
117 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
118 do_test join-1.4.3 {
119 execsql2 {
120 SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
121 }
122 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
123 do_test join-1.4.4 {
124 execsql2 {
125 SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
126 }
127 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
128 do_test join-1.4.5 {
129 execsql {
130 SELECT b FROM t1 JOIN t2 USING(b);
131 }
132 } {2 3}
133
134 # Ticket #3522
135 do_test join-1.4.6 {
136 execsql2 {
137 SELECT t1.* FROM t1 JOIN t2 USING(b);
138 }
139 } {a 1 b 2 c 3 a 2 b 3 c 4}
140 do_test join-1.4.7 {
141 execsql2 {
142 SELECT t2.* FROM t1 JOIN t2 USING(b);
143 }
144 } {b 2 c 3 d 4 b 3 c 4 d 5}
145
146 do_test join-1.5 {
147 execsql2 {
148 SELECT * FROM t1 INNER JOIN t2 USING(b);
149 }
150 } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
151 do_test join-1.6 {
152 execsql2 {
153 SELECT * FROM t1 INNER JOIN t2 USING(c);
154 }
155 } {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
156 do_test join-1.7 {
157 execsql2 {
158 SELECT * FROM t1 INNER JOIN t2 USING(c,b);
159 }
160 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
161
162 do_test join-1.8 {
163 execsql {
164 SELECT * FROM t1 NATURAL CROSS JOIN t2;
165 }
166 } {1 2 3 4 2 3 4 5}
167 do_test join-1.9 {
168 execsql {
169 SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
170 }
171 } {1 2 3 4 2 3 4 5}
172 do_test join-1.10 {
173 execsql {
174 SELECT * FROM t1 NATURAL INNER JOIN t2;
175 }
176 } {1 2 3 4 2 3 4 5}
177 do_test join-1.11 {
178 execsql {
179 SELECT * FROM t1 INNER JOIN t2 USING(b,c);
180 }
181 } {1 2 3 4 2 3 4 5}
182 do_test join-1.12 {
183 execsql {
184 SELECT * FROM t1 natural inner join t2;
185 }
186 } {1 2 3 4 2 3 4 5}
187
188 ifcapable subquery {
189 do_test join-1.13 {
190 execsql2 {
191 SELECT * FROM t1 NATURAL JOIN
192 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
193 }
194 } {a 1 b 2 c 3 d 4 e 5}
195 do_test join-1.14 {
196 execsql2 {
197 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
198 NATURAL JOIN t1
199 }
200 } {c 3 d 4 e 5 a 1 b 2}
201 }
202
203 do_test join-1.15 {
204 execsql {
205 CREATE TABLE t3(c,d,e);
206 INSERT INTO t3 VALUES(2,3,4);
207 INSERT INTO t3 VALUES(3,4,5);
208 INSERT INTO t3 VALUES(4,5,6);
209 SELECT * FROM t3;
210 }
211 } {2 3 4 3 4 5 4 5 6}
212 do_test join-1.16 {
213 execsql {
214 SELECT * FROM t1 natural join t2 natural join t3;
215 }
216 } {1 2 3 4 5 2 3 4 5 6}
217 do_test join-1.17 {
218 execsql2 {
219 SELECT * FROM t1 natural join t2 natural join t3;
220 }
221 } {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
222 do_test join-1.18 {
223 execsql {
224 CREATE TABLE t4(d,e,f);
225 INSERT INTO t4 VALUES(2,3,4);
226 INSERT INTO t4 VALUES(3,4,5);
227 INSERT INTO t4 VALUES(4,5,6);
228 SELECT * FROM t4;
229 }
230 } {2 3 4 3 4 5 4 5 6}
231 do_test join-1.19.1 {
232 execsql {
233 SELECT * FROM t1 natural join t2 natural join t4;
234 }
235 } {1 2 3 4 5 6}
236 do_test join-1.19.2 {
237 execsql2 {
238 SELECT * FROM t1 natural join t2 natural join t4;
239 }
240 } {a 1 b 2 c 3 d 4 e 5 f 6}
241 do_test join-1.20 {
242 execsql {
243 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
244 }
245 } {1 2 3 4 5}
246
247 do_test join-2.1 {
248 execsql {
249 SELECT * FROM t1 NATURAL LEFT JOIN t2;
250 }
251 } {1 2 3 4 2 3 4 5 3 4 5 {}}
252
253 # EVIDENCE-OF: R-52129-05406 you can say things like "OUTER LEFT NATURAL
254 # JOIN" which means the same as "NATURAL LEFT OUTER JOIN".
255 do_test join-2.1b {
256 execsql {
257 SELECT * FROM t1 OUTER LEFT NATURAL JOIN t2;
258 }
259 } {1 2 3 4 2 3 4 5 3 4 5 {}}
260 do_test join-2.1c {
261 execsql {
262 SELECT * FROM t1 NATURAL LEFT OUTER JOIN t2;
263 }
264 } {1 2 3 4 2 3 4 5 3 4 5 {}}
265
266 # ticket #3522
267 do_test join-2.1.1 {
268 execsql2 {
269 SELECT * FROM t1 NATURAL LEFT JOIN t2;
270 }
271 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5 a 3 b 4 c 5 d {}}
272 do_test join-2.1.2 {
273 execsql2 {
274 SELECT t1.* FROM t1 NATURAL LEFT JOIN t2;
275 }
276 } {a 1 b 2 c 3 a 2 b 3 c 4 a 3 b 4 c 5}
277 do_test join-2.1.3 {
278 execsql2 {
279 SELECT t2.* FROM t1 NATURAL LEFT JOIN t2;
280 }
281 } {b 2 c 3 d 4 b 3 c 4 d 5 b {} c {} d {}}
282
283 do_test join-2.2 {
284 execsql {
285 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
286 }
287 } {1 2 3 {} 2 3 4 1 3 4 5 2}
288
289 #do_test join-2.3 {
290 # catchsql {
291 # SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
292 # }
293 #} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
294
295 do_test join-2.4 {
296 execsql {
297 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
298 }
299 } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
300 do_test join-2.5 {
301 execsql {
302 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
303 }
304 } {2 3 4 {} {} {} 3 4 5 1 2 3}
305 do_test join-2.6 {
306 execsql {
307 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
308 }
309 } {1 2 3 {} {} {} 2 3 4 {} {} {}}
310
311 do_test join-3.1 {
312 catchsql {
313 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
314 }
315 } {1 {a NATURAL join may not have an ON or USING clause}}
316 do_test join-3.2 {
317 catchsql {
318 SELECT * FROM t1 NATURAL JOIN t2 USING(b);
319 }
320 } {1 {a NATURAL join may not have an ON or USING clause}}
321 do_test join-3.3 {
322 catchsql {
323 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
324 }
325 } {1 {near "USING": syntax error}}
326 do_test join-3.4.1 {
327 catchsql {
328 SELECT * FROM t1 JOIN t2 USING(a);
329 }
330 } {1 {cannot join using column a - column not present in both tables}}
331 do_test join-3.4.2 {
332 catchsql {
333 SELECT * FROM t1 JOIN t2 USING(d);
334 }
335 } {1 {cannot join using column d - column not present in both tables}}
336 do_test join-3.5 {
337 catchsql { SELECT * FROM t1 USING(a) }
338 } {1 {a JOIN clause is required before USING}}
339 do_test join-3.6 {
340 catchsql {
341 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
342 }
343 } {1 {no such column: t3.a}}
344
345 # EVIDENCE-OF: R-47973-48020 you cannot say "INNER OUTER JOIN", because
346 # that would be contradictory.
347 do_test join-3.7 {
348 catchsql {
349 SELECT * FROM t1 INNER OUTER JOIN t2;
350 }
351 } {1 {unknown join type: INNER OUTER}}
352 do_test join-3.8 {
353 catchsql {
354 SELECT * FROM t1 INNER OUTER CROSS JOIN t2;
355 }
356 } {1 {unknown join type: INNER OUTER CROSS}}
357 do_test join-3.9 {
358 catchsql {
359 SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;
360 }
361 } {1 {unknown join type: OUTER NATURAL INNER}}
362 do_test join-3.10 {
363 catchsql {
364 SELECT * FROM t1 LEFT BOGUS JOIN t2;
365 }
366 } {1 {unknown join type: LEFT BOGUS}}
367 do_test join-3.11 {
368 catchsql {
369 SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;
370 }
371 } {1 {unknown join type: INNER BOGUS CROSS}}
372 do_test join-3.12 {
373 catchsql {
374 SELECT * FROM t1 NATURAL AWK SED JOIN t2;
375 }
376 } {1 {unknown join type: NATURAL AWK SED}}
377
378 do_test join-4.1 {
379 execsql {
380 BEGIN;
381 CREATE TABLE t5(a INTEGER PRIMARY KEY);
382 CREATE TABLE t6(a INTEGER);
383 INSERT INTO t6 VALUES(NULL);
384 INSERT INTO t6 VALUES(NULL);
385 INSERT INTO t6 SELECT * FROM t6;
386 INSERT INTO t6 SELECT * FROM t6;
387 INSERT INTO t6 SELECT * FROM t6;
388 INSERT INTO t6 SELECT * FROM t6;
389 INSERT INTO t6 SELECT * FROM t6;
390 INSERT INTO t6 SELECT * FROM t6;
391 COMMIT;
392 }
393 execsql {
394 SELECT * FROM t6 NATURAL JOIN t5;
395 }
396 } {}
397 do_test join-4.2 {
398 execsql {
399 SELECT * FROM t6, t5 WHERE t6.a<t5.a;
400 }
401 } {}
402 do_test join-4.3 {
403 execsql {
404 SELECT * FROM t6, t5 WHERE t6.a>t5.a;
405 }
406 } {}
407 do_test join-4.4 {
408 execsql {
409 UPDATE t6 SET a='xyz';
410 SELECT * FROM t6 NATURAL JOIN t5;
411 }
412 } {}
413 do_test join-4.6 {
414 execsql {
415 SELECT * FROM t6, t5 WHERE t6.a<t5.a;
416 }
417 } {}
418 do_test join-4.7 {
419 execsql {
420 SELECT * FROM t6, t5 WHERE t6.a>t5.a;
421 }
422 } {}
423 do_test join-4.8 {
424 execsql {
425 UPDATE t6 SET a=1;
426 SELECT * FROM t6 NATURAL JOIN t5;
427 }
428 } {}
429 do_test join-4.9 {
430 execsql {
431 SELECT * FROM t6, t5 WHERE t6.a<t5.a;
432 }
433 } {}
434 do_test join-4.10 {
435 execsql {
436 SELECT * FROM t6, t5 WHERE t6.a>t5.a;
437 }
438 } {}
439
440 do_test join-5.1 {
441 execsql {
442 BEGIN;
443 create table centros (id integer primary key, centro);
444 INSERT INTO centros VALUES(1,'xxx');
445 create table usuarios (id integer primary key, nombre, apellidos,
446 idcentro integer);
447 INSERT INTO usuarios VALUES(1,'a','aa',1);
448 INSERT INTO usuarios VALUES(2,'b','bb',1);
449 INSERT INTO usuarios VALUES(3,'c','cc',NULL);
450 create index idcentro on usuarios (idcentro);
451 END;
452 select usuarios.id, usuarios.nombre, centros.centro from
453 usuarios left outer join centros on usuarios.idcentro = centros.id;
454 }
455 } {1 a xxx 2 b xxx 3 c {}}
456
457 # A test for ticket #247.
458 #
459 do_test join-7.1 {
460 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
461 execsql {
462 CREATE TABLE t7 (x, y);
463 INSERT INTO t7 VALUES ("pa1", 1);
464 INSERT INTO t7 VALUES ("pa2", NULL);
465 INSERT INTO t7 VALUES ("pa3", NULL);
466 INSERT INTO t7 VALUES ("pa4", 2);
467 INSERT INTO t7 VALUES ("pa30", 131);
468 INSERT INTO t7 VALUES ("pa31", 130);
469 INSERT INTO t7 VALUES ("pa28", NULL);
470
471 CREATE TABLE t8 (a integer primary key, b);
472 INSERT INTO t8 VALUES (1, "pa1");
473 INSERT INTO t8 VALUES (2, "pa4");
474 INSERT INTO t8 VALUES (3, NULL);
475 INSERT INTO t8 VALUES (4, NULL);
476 INSERT INTO t8 VALUES (130, "pa31");
477 INSERT INTO t8 VALUES (131, "pa30");
478
479 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
480 }
481 } {1 999 999 2 131 130 999}
482
483 # Make sure a left join where the right table is really a view that
484 # is itself a join works right. Ticket #306.
485 #
486 ifcapable view {
487 do_test join-8.1 {
488 execsql {
489 BEGIN;
490 CREATE TABLE t9(a INTEGER PRIMARY KEY, b);
491 INSERT INTO t9 VALUES(1,11);
492 INSERT INTO t9 VALUES(2,22);
493 CREATE TABLE t10(x INTEGER PRIMARY KEY, y);
494 INSERT INTO t10 VALUES(1,2);
495 INSERT INTO t10 VALUES(3,3);
496 CREATE TABLE t11(p INTEGER PRIMARY KEY, q);
497 INSERT INTO t11 VALUES(2,111);
498 INSERT INTO t11 VALUES(3,333);
499 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
500 COMMIT;
501 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
502 }
503 } {1 11 1 111 2 22 {} {}}
504 ifcapable subquery {
505 do_test join-8.2 {
506 execsql {
507 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
508 ON( a=x);
509 }
510 } {1 11 1 111 2 22 {} {}}
511 }
512 do_test join-8.3 {
513 execsql {
514 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
515 }
516 } {1 111 1 11 3 333 {} {}}
517 ifcapable subquery {
518 # Constant expressions in a subquery that is the right element of a
519 # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not
520 # match. Ticket #3300
521 do_test join-8.4 {
522 execsql {
523 SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a
524 }
525 } {1 11 {} {} {} 2 22 44 2 111}
526 }
527 } ;# ifcapable view
528
529 # Ticket #350 describes a scenario where LEFT OUTER JOIN does not
530 # function correctly if the right table in the join is really
531 # subquery.
532 #
533 # To test the problem, we generate the same LEFT OUTER JOIN in two
534 # separate selects but with on using a subquery and the other calling
535 # the table directly. Then connect the two SELECTs using an EXCEPT.
536 # Both queries should generate the same results so the answer should
537 # be an empty set.
538 #
539 ifcapable compound {
540 do_test join-9.1 {
541 execsql {
542 BEGIN;
543 CREATE TABLE t12(a,b);
544 INSERT INTO t12 VALUES(1,11);
545 INSERT INTO t12 VALUES(2,22);
546 CREATE TABLE t13(b,c);
547 INSERT INTO t13 VALUES(22,222);
548 COMMIT;
549 }
550 } {}
551
552 ifcapable subquery {
553 do_test join-9.1.1 {
554 execsql {
555 SELECT * FROM t12 NATURAL LEFT JOIN t13
556 EXCEPT
557 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
558 }
559 } {}
560 }
561 ifcapable view {
562 do_test join-9.2 {
563 execsql {
564 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
565 SELECT * FROM t12 NATURAL LEFT JOIN t13
566 EXCEPT
567 SELECT * FROM t12 NATURAL LEFT JOIN v13;
568 }
569 } {}
570 } ;# ifcapable view
571 } ;# ifcapable compound
572
573 ifcapable subquery {
574 # Ticket #1697: Left Join WHERE clause terms that contain an
575 # aggregate subquery.
576 #
577 do_test join-10.1 {
578 execsql {
579 CREATE TABLE t21(a,b,c);
580 CREATE TABLE t22(p,q);
581 CREATE INDEX i22 ON t22(q);
582 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
583 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
584 }
585 } {}
586
587 # Test a LEFT JOIN when the right-hand side of hte join is an empty
588 # sub-query. Seems fine.
589 #
590 do_test join-10.2 {
591 execsql {
592 CREATE TABLE t23(a, b, c);
593 CREATE TABLE t24(a, b, c);
594 INSERT INTO t23 VALUES(1, 2, 3);
595 }
596 execsql {
597 SELECT * FROM t23 LEFT JOIN t24;
598 }
599 } {1 2 3 {} {} {}}
600 do_test join-10.3 {
601 execsql {
602 SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24);
603 }
604 } {1 2 3 {} {} {}}
605
606 } ;# ifcapable subquery
607
608 #-------------------------------------------------------------------------
609 # The following tests are to ensure that bug b73fb0bd64 is fixed.
610 #
611 do_test join-11.1 {
612 drop_all_tables
613 execsql {
614 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
615 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
616 INSERT INTO t1 VALUES(1,'abc');
617 INSERT INTO t1 VALUES(2,'def');
618 INSERT INTO t2 VALUES(1,'abc');
619 INSERT INTO t2 VALUES(2,'def');
620 SELECT * FROM t1 NATURAL JOIN t2;
621 }
622 } {1 abc 2 def}
623
624 do_test join-11.2 {
625 execsql { SELECT a FROM t1 JOIN t1 USING (a)}
626 } {1 2}
627 do_test join-11.3 {
628 execsql { SELECT a FROM t1 JOIN t1 AS t2 USING (a)}
629 } {1 2}
630 do_test join-11.3 {
631 execsql { SELECT * FROM t1 NATURAL JOIN t1 AS t2}
632 } {1 abc 2 def}
633 do_test join-11.4 {
634 execsql { SELECT * FROM t1 NATURAL JOIN t1 }
635 } {1 abc 2 def}
636
637 do_test join-11.5 {
638 drop_all_tables
639 execsql {
640 CREATE TABLE t1(a COLLATE nocase, b);
641 CREATE TABLE t2(a, b);
642 INSERT INTO t1 VALUES('ONE', 1);
643 INSERT INTO t1 VALUES('two', 2);
644 INSERT INTO t2 VALUES('one', 1);
645 INSERT INTO t2 VALUES('two', 2);
646 }
647 } {}
648 do_test join-11.6 {
649 execsql { SELECT * FROM t1 NATURAL JOIN t2 }
650 } {ONE 1 two 2}
651 do_test join-11.7 {
652 execsql { SELECT * FROM t2 NATURAL JOIN t1 }
653 } {two 2}
654
655 do_test join-11.8 {
656 drop_all_tables
657 execsql {
658 CREATE TABLE t1(a, b TEXT);
659 CREATE TABLE t2(b INTEGER, a);
660 INSERT INTO t1 VALUES('one', '1.0');
661 INSERT INTO t1 VALUES('two', '2');
662 INSERT INTO t2 VALUES(1, 'one');
663 INSERT INTO t2 VALUES(2, 'two');
664 }
665 } {}
666 do_test join-11.9 {
667 execsql { SELECT * FROM t1 NATURAL JOIN t2 }
668 } {one 1.0 two 2}
669 do_test join-11.10 {
670 execsql { SELECT * FROM t2 NATURAL JOIN t1 }
671 } {1 one 2 two}
672
673 #-------------------------------------------------------------------------
674 # Test that at most 64 tables are allowed in a join.
675 #
676 do_execsql_test join-12.1 {
677 CREATE TABLE t14(x);
678 INSERT INTO t14 VALUES('abcdefghij');
679 }
680
681 proc jointest {tn nTbl res} {
682 set sql "SELECT 1 FROM [string repeat t14, [expr $nTbl-1]] t14;"
683 uplevel [list do_catchsql_test $tn $sql $res]
684 }
685
686 jointest join-12.2 30 {0 1}
687 jointest join-12.3 63 {0 1}
688 jointest join-12.4 64 {0 1}
689 jointest join-12.5 65 {1 {at most 64 tables in a join}}
690 jointest join-12.6 66 {1 {at most 64 tables in a join}}
691 jointest join-12.7 127 {1 {at most 64 tables in a join}}
692 jointest join-12.8 128 {1 {at most 64 tables in a join}}
693
694 # As of 2019-01-17, the number of elements in a SrcList is limited
695 # to 200. The following tests still run, but the answer is now
696 # an SQLITE_NOMEM error.
697 #
698 # jointest join-12.9 1000 {1 {at most 64 tables in a join}}
699 #
700 # If SQLite is built with SQLITE_MEMDEBUG, then the huge number of realloc()
701 # calls made by the following test cases are too time consuming to run.
702 # Without SQLITE_MEMDEBUG, realloc() is fast enough that these are not
703 # a problem.
704 #
705 # ifcapable pragma&&compileoption_diags {
706 # if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} {
707 # jointest join-12.10 65534 {1 {at most 64 tables in a join}}
708 # jointest join-12.11 65535 {1 {too many references to "t14": max 65535}}
709 # jointest join-12.12 65536 {1 {too many references to "t14": max 65535}}
710 # jointest join-12.13 65537 {1 {too many references to "t14": max 65535}}
711 # }
712 # }
713
714
715 #-------------------------------------------------------------------------
716 # Test a problem with reordering tables following a LEFT JOIN.
717 #
718 do_execsql_test join-13.0 {
719 CREATE TABLE aa(a);
720 CREATE TABLE bb(b);
721 CREATE TABLE cc(c);
722
723 INSERT INTO aa VALUES(45);
724 INSERT INTO cc VALUES(45);
725 INSERT INTO cc VALUES(45);
726 }
727
728 do_execsql_test join-13.1 {
729 SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
730 } {45 {} 45 45 {} 45}
731
732 # In the following, the order of [cc] and [bb] must not be exchanged, even
733 # though this would be helpful if the query used an inner join.
734 do_execsql_test join-13.2 {
735 CREATE INDEX ccc ON cc(c);
736 SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
737 } {45 {} 45 45 {} 45}
738
739 # Verify that that iTable attributes the TK_IF_NULL_ROW operators in the
740 # expression tree are correctly updated by the query flattener. This was
741 # a bug discovered on 2017-05-22 by Mark Brand.
742 #
743 do_execsql_test join-14.1 {
744 SELECT *
745 FROM (SELECT 1 a) AS x
746 LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT 1)));
747 } {1 1 1}
748 do_execsql_test join-14.2 {
749 SELECT *
750 FROM (SELECT 1 a) AS x
751 LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT * FROM (SELECT 1)))) AS y
752 JOIN (SELECT * FROM (SELECT 9)) AS z;
753 } {1 1 1 9}
754 do_execsql_test join-14.3 {
755 SELECT *
756 FROM (SELECT 111)
757 LEFT JOIN (SELECT cc+222, * FROM (SELECT * FROM (SELECT 333 cc)));
758 } {111 555 333}
759
760 do_execsql_test join-14.4 {
761 DROP TABLE IF EXISTS t1;
762 CREATE TABLE t1(c PRIMARY KEY, a TEXT(10000), b TEXT(10000));
763 SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
764 } {111 {}}
765 do_execsql_test join-14.4b {
766 SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1);
767 } {111 {}}
768 do_execsql_test join-14.5 {
769 SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 222)
770 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
771 } {111 {} 222 {}}
772 do_execsql_test join-14.5b {
773 SELECT count(*)
774 FROM (SELECT 111 AS x UNION ALL SELECT 222)
775 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y;
776 } {2}
777 do_execsql_test join-14.5c {
778 SELECT count(*)
779 FROM (SELECT c+333 AS y FROM t1)
780 RIGHT JOIN (SELECT 111 AS x UNION ALL SELECT 222) ON x=y;
781 } {2}
782 do_execsql_test join-14.6 {
783 SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111)
784 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
785 } {111 {}}
786 do_execsql_test join-14.7 {
787 SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111 UNION ALL SELECT 222)
788 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
789 } {111 {} 222 {}}
790 do_execsql_test join-14.8 {
791 INSERT INTO t1(c) VALUES(-111);
792 SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111 UNION ALL SELECT 222)
793 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
794 } {111 {} 222 222}
795 do_execsql_test join-14.9 {
796 DROP TABLE IF EXISTS t1;
797 CREATE TABLE t1(c PRIMARY KEY) WITHOUT ROWID;
798 SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
799 } {111 {}}
800
801 # Verify the fix to ticket
802 # https://sqlite.org/src/tktview/7fde638e94287d2c948cd9389
803 #
804 db close
805 sqlite3 db :memory:
806 do_execsql_test join-14.10 {
807 CREATE TABLE t1(a);
808 INSERT INTO t1 VALUES(1),(2),(3);
809 CREATE VIEW v2 AS SELECT a, 1 AS b FROM t1;
810 CREATE TABLE t3(x);
811 INSERT INTO t3 VALUES(2),(4);
812 SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b=1;
813 } {2 2 1 |}
814 do_execsql_test join-14.11 {
815 SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b+1=x;
816 } {2 2 1 |}
817 do_execsql_test join-14.12 {
818 SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x ORDER BY b;
819 } {4 {} {} | 2 2 1 |}
820
821 # Verify the fix for ticket
822 # https://sqlite.org/src/info/892fc34f173e99d8
823 #
824 db close
825 sqlite3 db :memory:
826 do_execsql_test join-14.20 {
827 CREATE TABLE t1(id INTEGER PRIMARY KEY);
828 CREATE TABLE t2(id INTEGER PRIMARY KEY, c2 INTEGER);
829 CREATE TABLE t3(id INTEGER PRIMARY KEY, c3 INTEGER);
830 INSERT INTO t1(id) VALUES(456);
831 INSERT INTO t3(id) VALUES(1),(2);
832 SELECT t1.id, x2.id, x3.id
833 FROM t1
834 LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
835 LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
836 } {456 {} {}}
837
838 # 2018-03-24.
839 # E.Pasma discovered that the LEFT JOIN strength reduction optimization
840 # was misbehaving. The problem turned out to be that the
841 # sqlite3ExprImpliesNotNull() routine was saying that CASE expressions
842 # like
843 #
844 # CASE WHEN true THEN true ELSE x=0 END
845 #
846 # could never be true if x is NULL. The following test cases verify
847 # that this error has been resolved.
848 #
849 db close
850 sqlite3 db :memory:
851 do_execsql_test join-15.100 {
852 CREATE TABLE t1(a INT, b INT);
853 INSERT INTO t1 VALUES(1,2),(3,4);
854 CREATE TABLE t2(x INT, y INT);
855 SELECT *, 'x'
856 FROM t1 LEFT JOIN t2
857 WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;
858 } {1 2 {} {} x 3 4 {} {} x}
859 do_execsql_test join-15.105 {
860 SELECT *, 'x'
861 FROM t1 LEFT JOIN t2
862 WHERE a IN (1,3,x,y);
863 } {1 2 {} {} x 3 4 {} {} x}
864 do_execsql_test join-15.106a {
865 SELECT *, 'x'
866 FROM t1 LEFT JOIN t2
867 WHERE NOT ( 'x'='y' AND t2.y=1 );
868 } {1 2 {} {} x 3 4 {} {} x}
869 do_execsql_test join-15.106b {
870 SELECT *, 'x'
871 FROM t1 LEFT JOIN t2
872 WHERE ~ ( 'x'='y' AND t2.y=1 );
873 } {1 2 {} {} x 3 4 {} {} x}
874 do_execsql_test join-15.107 {
875 SELECT *, 'x'
876 FROM t1 LEFT JOIN t2
877 WHERE t2.y IS NOT 'abc'
878 } {1 2 {} {} x 3 4 {} {} x}
879 do_execsql_test join-15.110 {
880 DROP TABLE t1;
881 DROP TABLE t2;
882 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
883 INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
884 CREATE INDEX t1b ON t1(b);
885 CREATE TABLE t2(x INTEGER PRIMARY KEY);
886 INSERT INTO t2(x) VALUES(0),(1);
887 SELECT a1, a2, a3, a4, a5
888 FROM (SELECT a AS a1 FROM t1 WHERE b=0)
889 JOIN (SELECT x AS x1 FROM t2)
890 LEFT JOIN (SELECT a AS a2, b AS b2 FROM t1)
891 ON x1 IS TRUE AND b2=a1
892 JOIN (SELECT x AS x2 FROM t2)
893 ON x2<=CASE WHEN x1 THEN CASE WHEN a2 THEN 1 ELSE -1 END ELSE 0 END
894 LEFT JOIN (SELECT a AS a3, b AS b3 FROM t1)
895 ON x2 IS TRUE AND b3=a2
896 JOIN (SELECT x AS x3 FROM t2)
897 ON x3<=CASE WHEN x2 THEN CASE WHEN a3 THEN 1 ELSE -1 END ELSE 0 END
898 LEFT JOIN (SELECT a AS a4, b AS b4 FROM t1)
899 ON x3 IS TRUE AND b4=a3
900 JOIN (SELECT x AS x4 FROM t2)
901 ON x4<=CASE WHEN x3 THEN CASE WHEN a4 THEN 1 ELSE -1 END ELSE 0 END
902 LEFT JOIN (SELECT a AS a5, b AS b5 FROM t1)
903 ON x4 IS TRUE AND b5=a4
904 ORDER BY a1, a2, a3, a4, a5;
905 } {1 {} {} {} {} 1 11 {} {} {} 1 12 {} {} {} 1 12 121 {} {} 1 13 {} {} {}}
906
907 # 2019-02-05 Ticket https://sqlite.org/src/tktview/5948e09b8c415bc45da5c
908 # Error in join due to the LEFT JOIN strength reduction optimization.
909 #
910 do_execsql_test join-16.100 {
911 DROP TABLE IF EXISTS t1;
912 DROP TABLE IF EXISTS t2;
913 CREATE TABLE t1(a INT);
914 INSERT INTO t1(a) VALUES(1);
915 CREATE TABLE t2(b INT);
916 SELECT a, b
917 FROM t1 LEFT JOIN t2 ON 0
918 WHERE (b IS NOT NULL)=0;
919 } {1 {}}
920
921 # 2019-08-17 ticket https://sqlite.org/src/tktview/6710d2f7a13a299728ab
922 # Ensure that constants that derive from the right-hand table of a LEFT JOIN
923 # are never factored out, since they are not really constant.
924 #
925 do_execsql_test join-17.100 {
926 DROP TABLE IF EXISTS t1;
927 CREATE TABLE t1(x);
928 INSERT INTO t1(x) VALUES(0),(1);
929 SELECT * FROM t1 LEFT JOIN (SELECT abs(1) AS y FROM t1) ON x WHERE NOT(y='a');
930 } {1 1 1 1}
931 do_execsql_test join-17.110 {
932 SELECT * FROM t1 LEFT JOIN (SELECT abs(1)+2 AS y FROM t1) ON x
933 WHERE NOT(y='a');
934 } {1 3 1 3}
935
936 #-------------------------------------------------------------------------
937 reset_db
938 do_execsql_test join-18.1 {
939 CREATE TABLE t0(a);
940 CREATE TABLE t1(b);
941 CREATE VIEW v0 AS SELECT a FROM t1 LEFT JOIN t0;
942 INSERT INTO t1 VALUES (1);
943 } {}
944
945 do_execsql_test join-18.2 {
946 SELECT * FROM v0 WHERE NOT(v0.a IS FALSE);
947 } {{}}
948
949 do_execsql_test join-18.3 {
950 SELECT * FROM t1 LEFT JOIN t0 WHERE NOT(a IS FALSE);
951 } {1 {}}
952
953 do_execsql_test join-18.4 {
954 SELECT NOT(v0.a IS FALSE) FROM v0
955 } {1}
956
957 #-------------------------------------------------------------------------
958 reset_db
959 do_execsql_test join-19.0 {
960 CREATE TABLE t1(a);
961 CREATE TABLE t2(b);
962 INSERT INTO t1(a) VALUES(0);
963 CREATE VIEW v0(c) AS SELECT t2.b FROM t1 LEFT JOIN t2;
964 }
965
966 do_execsql_test join-19.1 {
967 SELECT * FROM v0 WHERE v0.c NOTNULL NOTNULL;
968 } {{}}
969
970 do_execsql_test join-19.2 {
971 SELECT * FROM t1 LEFT JOIN t2
972 } {0 {}}
973
974 do_execsql_test join-19.3 {
975 SELECT * FROM t1 LEFT JOIN t2 WHERE (b IS NOT NULL) IS NOT NULL;
976 } {0 {}}
977
978 do_execsql_test join-19.4 {
979 SELECT (b IS NOT NULL) IS NOT NULL FROM t1 LEFT JOIN t2
980 } {1}
981
982 do_execsql_test join-19.5 {
983 SELECT * FROM t1 LEFT JOIN t2 WHERE
984 (b IS NOT NULL AND b IS NOT NULL) IS NOT NULL;
985 } {0 {}}
986
987 # 2019-11-02 ticket 623eff57e76d45f6
988 # The optimization of exclusing the WHERE expression of a partial index
989 # from the WHERE clause of the query if the index is used does not work
990 # of the table of the index is the right-hand table of a LEFT JOIN.
991 #
992 db close
993 sqlite3 db :memory:
994 do_execsql_test join-20.1 {
995 CREATE TABLE t1(c1);
996 CREATE TABLE t0(c0);
997 INSERT INTO t0(c0) VALUES (0);
998 SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
999 } {}
1000 do_execsql_test join-20.2 {
1001 CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
1002 SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
1003 } {}
1004
1005 # 2025-05-29 forum post 7dee41d32506c4ae
1006 # The complaint in the forum post appears to be the same as for the
1007 # ticket on 2019-11-02, only for RIGHT JOIN instead of LEFT JOIN. Note
1008 # that RIGHT JOIN did not yet exist in SQLite when the ticket was
1009 # written and fixed.
1010 #
1011 do_execsql_test join-20.3 {
1012 DROP TABLE t1;
1013 CREATE TABLE t1(x INT); INSERT INTO t1(x) VALUES(1);
1014 CREATE TABLE t2(y BOOLEAN); INSERT INTO t2(y) VALUES(false);
1015 CREATE TABLE t3(z INT); INSERT INTO t3(z) VALUES(3);
1016 CREATE INDEX t2y ON t2(y) WHERE y;
1017 SELECT quote(z) FROM t1 RIGHT JOIN t2 ON y LEFT JOIN t3 ON y;
1018 } {NULL}
1019
1020 # 2019-11-30 ticket 7f39060a24b47353
1021 # Do not allow a WHERE clause term to qualify a partial index on the
1022 # right table of a LEFT JOIN.
1023 #
1024 do_execsql_test join-21.10 {
1025 DROP TABLE t0;
1026 DROP TABLE t1;
1027 CREATE TABLE t0(aa);
1028 CREATE TABLE t1(bb);
1029 INSERT INTO t0(aa) VALUES (1);
1030 INSERT INTO t1(bb) VALUES (1);
1031 SELECT 11, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
1032 SELECT 12, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
1033 SELECT 13, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
1034 SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
1035 CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
1036 SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
1037 SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
1038 SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
1039 SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
1040 } {13 1 {} 14 1 {} 23 1 {} 24 1 {}}
1041
1042 # 2019-12-18 problem with a LEFT JOIN where the RHS is a view.
1043 # Detected by Yongheng and Rui.
1044 # Follows from the optimization attempt of check-in 41c27bc0ff1d3135
1045 # on 2017-04-18
1046 #
1047 reset_db
1048 do_execsql_test join-22.10 {
1049 CREATE TABLE t0(a, b);
1050 CREATE INDEX t0a ON t0(a);
1051 INSERT INTO t0 VALUES(10,10),(10,11),(10,12);
1052 SELECT DISTINCT c FROM t0 LEFT JOIN (SELECT a+1 AS c FROM t0) ORDER BY c ;
1053 } {11}
1054
1055 # 2019-12-22 ticket 7929c1efb2d67e98
1056 # Verification of testtag-20230227a
1057 #
1058 # 2023-02-27 https://sqlite.org/forum/forumpost/422e635f3beafbf6
1059 # Verification of testtag-20230227a, testtag-20230227b, and testtag-20230227c
1060 #
1061 reset_db
1062 ifcapable vtab {
1063 do_execsql_test join-23.10 {
1064 CREATE TABLE t0(c0);
1065 INSERT INTO t0(c0) VALUES(123);
1066 CREATE VIEW v0(c0) AS SELECT 0 GROUP BY 1;
1067 SELECT t0.c0, v0.c0, vt0.name
1068 FROM v0, t0 LEFT JOIN pragma_table_info('t0') AS vt0
1069 ON vt0.name LIKE 'c0'
1070 WHERE v0.c0 == 0;
1071 } {123 0 c0}
1072 do_execsql_test join-23.20 {
1073 CREATE TABLE a(value TEXT);
1074 INSERT INTO a(value) SELECT value FROM json_each('["a", "b", null]');
1075 CREATE TABLE b(value TEXT);
1076 INSERT INTO b(value) SELECT value FROM json_each('["a", "c", null]');
1077 SELECT a.value, b.value FROM a RIGHT JOIN b ON a.value = b.value;
1078 } {a a {} c {} {}}
1079 do_execsql_test join-23.21 {
1080 SELECT a.value, b.value FROM b LEFT JOIN a ON a.value = b.value;
1081 } {a a {} c {} {}}
1082 do_execsql_test join-23.22 {
1083 SELECT a.value, b.value
1084 FROM json_each('["a", "c", null]') AS b
1085 LEFT JOIN
1086 json_each('["a", "b", null]') AS a ON a.value = b.value;
1087 } {a a {} c {} {}}
1088 do_execsql_test join-23.23 {
1089 SELECT a.value, b.value
1090 FROM json_each('["a", "b", null]') AS a
1091 RIGHT JOIN
1092 json_each('["a", "c", null]') AS b ON a.value = b.value;
1093 } {a a {} c {} {}}
1094 do_execsql_test join-23.24 {
1095 SELECT a.value, b.value
1096 FROM json_each('["a", "b", null]') AS a
1097 RIGHT JOIN
1098 b ON a.value = b.value;
1099 } {a a {} c {} {}}
1100 do_execsql_test join-23.25 {
1101 SELECT a.value, b.value
1102 FROM a
1103 RIGHT JOIN
1104 json_each('["a", "c", null]') AS b ON a.value = b.value;
1105 } {a a {} c {} {}}
1106 }
1107
1108 #-------------------------------------------------------------------------
1109 reset_db
1110 do_execsql_test join-24.1 {
1111 CREATE TABLE t1(a PRIMARY KEY, x);
1112 CREATE TABLE t2(b INT);
1113 CREATE INDEX t1aa ON t1(a, a);
1114
1115 INSERT INTO t1 VALUES('abc', 'def');
1116 INSERT INTO t2 VALUES(1);
1117 }
1118
1119 do_execsql_test join-24.2 {
1120 SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='def';
1121 } {1 abc def}
1122 do_execsql_test join-24.3 {
1123 SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='abc';
1124 } {}
1125
1126 do_execsql_test join-24.2 {
1127 SELECT * FROM t2 LEFT JOIN t1 ON a=0 WHERE (x='x' OR x IS NULL);
1128 } {1 {} {}}
1129
1130 # 2020-09-30 ticket 66e4b0e271c47145
1131 # The query flattener inserts an "expr AND expr" expression as a substitution
1132 # for the column of a view where that view column is part of an ON expression
1133 # of a LEFT JOIN.
1134 #
1135 reset_db
1136 do_execsql_test join-25.1 {
1137 CREATE TABLE t0(c0 INT);
1138 CREATE VIEW v0 AS SELECT (NULL AND 5) as c0 FROM t0;
1139 INSERT INTO t0(c0) VALUES (NULL);
1140 SELECT count(*) FROM v0 LEFT JOIN t0 ON v0.c0;
1141 } {1}
1142
1143 # 2022-04-21 Parser issue detected by dbsqlfuzz
1144 #
1145 reset_db
1146 do_catchsql_test join-26.1 {
1147 CREATE TABLE t4(a,b);
1148 CREATE TABLE t5(a,c);
1149 CREATE TABLE t6(a,d);
1150 SELECT * FROM t5 JOIN ((t4 JOIN (t5 JOIN t6)) t7);
1151 } {/1 {.*}/}
1152
1153 # 2022-06-09 Invalid subquery flattening caused by
1154 # check-in 3f45007d544e5f78 and detected by dbsqlfuzz
1155 #
1156 reset_db
1157 do_execsql_test join-27.1 {
1158 CREATE TABLE t1(a INT,b INT,c INT); INSERT INTO t1 VALUES(NULL,NULL,NULL);
1159 CREATE TABLE t2(d INT,e INT); INSERT INTO t2 VALUES(NULL,NULL);
1160 CREATE INDEX x2 ON t1(c,b);
1161 CREATE TABLE t3(x INT); INSERT INTO t3 VALUES(NULL);
1162 }
1163 do_execsql_test join-27.2 {
1164 WITH t99(b) AS MATERIALIZED (
1165 SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1166 )
1167 SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
1168 } {}
1169 do_execsql_test join-27.3 {
1170 WITH t99(b) AS NOT MATERIALIZED (
1171 SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1172 )
1173 SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
1174 } {}
1175 do_execsql_test join-27.4 {
1176 WITH t99(b) AS (SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3))
1177 SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
1178 } {}
1179 do_execsql_test join-27.5 {
1180 SELECT 5
1181 FROM t2 JOIN (
1182 SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1183 ) AS t99 ON b IN (1,2,3);
1184 } {}
1185
1186 db null NULL
1187 do_execsql_test join-27.6 {
1188 INSERT INTO t1 VALUES(3,4,NULL);
1189 INSERT INTO t2 VALUES(1,2);
1190 WITH t99(b) AS (
1191 SELECT coalesce(b,3) FROM t2 AS x LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1192 )
1193 SELECT d, e, b FROM t2 JOIN t99 ON b IN (1,2,3) ORDER BY +d;
1194 } {NULL NULL 3 NULL NULL 3 1 2 3 1 2 3}
1195 do_execsql_test join-27.7 {
1196 SELECT d, e, b2
1197 FROM t2
1198 JOIN (SELECT coalesce(b,3) AS b2 FROM t2 AS x LEFT JOIN t1
1199 ON c IN (SELECT x FROM t3)) AS t99
1200 ON b2 IN (1,2,3) ORDER BY +d;
1201 } {NULL NULL 3 NULL NULL 3 1 2 3 1 2 3}
1202
1203 do_execsql_test join-27.8 {
1204 DELETE FROM t1;
1205 DELETE FROM t2 WHERE d IS NOT NULL;
1206 DELETE FROM t3;
1207 SELECT * FROM t2 JOIN (SELECT b FROM t2 LEFT JOIN t1
1208 ON c IN (SELECT x FROM t3)) AS t99 ON b IN (1,2,3);
1209 } {}
1210
1211 do_execsql_test join-27.9 {
1212 DELETE FROM t1;
1213 DELETE FROM t2;
1214 DELETE FROM t3;
1215 INSERT INTO t1 VALUES(4,3,5);
1216 INSERT INTO t2 VALUES(1,2);
1217 INSERT INTO t3 VALUES(5);
1218 SELECT * FROM t2 JOIN (SELECT b FROM t2 LEFT JOIN t1
1219 ON c IN (SELECT x FROM t3)) AS t99 ON b IS NULL;
1220 } {}
1221 do_execsql_test join-27.10 {
1222 WITH t99(b) AS (
1223 SELECT b FROM t2 AS x LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1224 )
1225 SELECT d, e, b FROM t2 JOIN t99 ON b IS NULL;
1226 } {}
1227
1228
1229 # 2022-09-19 https://sqlite.org/forum/forumpost/96b9e5709cf47cda
1230 # Performance regression relative to version 3.38.0 that resulted from
1231 # a new query flattener restriction that was added to fixes the join-27.*
1232 # tests above. The restriction needed to be removed and the join-27.*
1233 # problem fixed another way.
1234 #
1235 reset_db
1236 do_execsql_test join-28.1 {
1237 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT);
1238 CREATE TABLE t2(d INTEGER PRIMARY KEY, e INT);
1239 CREATE VIEW t3(a,b,c,d,e) AS SELECT * FROM t1 LEFT JOIN t2 ON d=c;
1240 CREATE TABLE t4(x INT, y INT);
1241 INSERT INTO t1 VALUES(1,2,3);
1242 INSERT INTO t2 VALUES(1,5);
1243 INSERT INTO t4 VALUES(1,4);
1244 SELECT a, b, y FROM t4 JOIN t3 ON a=x;
1245 } {1 2 4}
1246 do_eqp_test join-28.2 {
1247 SELECT a, b, y FROM t4 JOIN t3 ON a=x;
1248 } {
1249 QUERY PLAN
1250 |--SCAN t4
1251 `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
1252 }
1253 # ^^^^^^^ Without the fix (if the query flattening optimization does not
1254 # run) the query plan above would look like this:
1255 #
1256 # QUERY PLAN
1257 # |--MATERIALIZE t3
1258 # | |--SCAN t1
1259 # | `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
1260 # |--SCAN t4
1261 # `--SEARCH t3 USING AUTOMATIC COVERING INDEX (a=?)
1262
1263
1264 # 2023-05-01 https://sqlite.org/forum/forumpost/96cd4a7e9e
1265 #
1266 reset_db
1267 db null NULL
1268 do_execsql_test join-29.1 {
1269 CREATE TABLE t0(a INT); INSERT INTO t0(a) VALUES (1);
1270 CREATE TABLE t1(b INT); INSERT INTO t1(b) VALUES (2);
1271 CREATE VIEW v2(c) AS SELECT 3 FROM t1;
1272 SELECT * FROM t1 JOIN v2 ON 0 FULL OUTER JOIN t0 ON true;
1273 } {NULL NULL 1}
1274 do_execsql_test join-29.2 {
1275 SELECT * FROM t1 JOIN v2 ON 1=0 FULL OUTER JOIN t0 ON true;
1276 } {NULL NULL 1}
1277 do_execsql_test join-29.3 {
1278 SELECT * FROM t1 JOIN v2 ON false FULL OUTER JOIN t0 ON true;
1279 } {NULL NULL 1}
1280
1281 # 2023-05-11 https://sqlite.org/forum/forumpost/49f2c7f690
1282 # Verify that omit-noop-join optimization does not apply if the table
1283 # to be omitted has an inner-join constraint and there is a RIGHT JOIN
1284 # anywhere in the query.
1285 #
1286 reset_db
1287 db null NULL
1288 do_execsql_test join-30.1 {
1289 CREATE TABLE t0(z INT); INSERT INTO t0 VALUES(1),(2);
1290 CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(1);
1291 CREATE TABLE t2(b INT); INSERT INTO t2 VALUES(2);
1292 CREATE TABLE t3(c INT, d INT); INSERT INTO t3 VALUES(3,4);
1293 CREATE TABLE t4(e INT); INSERT INTO t4 VALUES(5);
1294 CREATE VIEW v5(x,y) AS SELECT c, d FROM t3 LEFT JOIN t4 ON false;
1295 }
1296 do_execsql_test join-30.2 {
1297 SELECT DISTINCT a, b
1298 FROM t1 RIGHT JOIN t2 ON a=b LEFT JOIN v5 ON false
1299 WHERE x <= y;
1300 } {}
1301 do_execsql_test join-30.3 {
1302 SELECT DISTINCT a, b
1303 FROM t0 JOIN t1 ON z=a RIGHT JOIN t2 ON a=b LEFT JOIN v5 ON false
1304 WHERE x <= y;
1305 } {}
1306
1307 # 2025-05-30 https://sqlite.org/forum/forumpost/4fc70203b61c7e12
1308 #
1309 # When converting a USING(x) or NATURAL into the constraint expression
1310 # t1.x==t2.x, mark the t1.x term as EP_CanBeNull if it is the left table
1311 # of a RIGHT JOIN.
1312 #
1313 reset_db
1314 db null NULL
1315 do_execsql_test join-31.1 {
1316 CREATE TABLE t1(c0 INT , c1 INT); INSERT INTO t1(c0, c1) VALUES(NULL,11);
1317 CREATE TABLE t2(c0 INT NOT NULL);
1318 CREATE TABLE t2n(c0 INT);
1319 CREATE TABLE t3(x INT); INSERT INTO t3(x) VALUES(3);
1320 CREATE TABLE t4(y INT); INSERT INTO t4(y) VALUES(4);
1321 CREATE TABLE t5(c0 INT, x INT); INSERT INTO t5 VALUES(NULL, 5);
1322 }
1323 do_execsql_test join-31.2 {
1324 SELECT * FROM t2 RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0);
1325 } {NULL 3 NULL}
1326 do_execsql_test join-31.3 {
1327 SELECT * FROM t2 RIGHT JOIN t3 ON true NATURAL LEFT JOIN t1;
1328 } {NULL 3 NULL}
1329 do_execsql_test join-31.4 {
1330 SELECT * FROM t2n RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0);
1331 } {NULL 3 NULL}
1332 do_execsql_test join-31.5 {
1333 SELECT * FROM t5 LEFT JOIN t1 USING(c0);
1334 } {NULL 5 NULL}
1335 do_execsql_test join-31.6 {
1336 SELECT * FROM t3 LEFT JOIN t2 ON true LEFT JOIN t1 USING(c0);
1337 } {3 NULL NULL}
1338 do_execsql_test join-31.7 {
1339 SELECT * FROM t3 LEFT JOIN t2 ON true NATURAL LEFT JOIN t1;
1340 } {3 NULL NULL}
1341 do_execsql_test join-31.8 {
1342 SELECT * FROM t3 LEFT JOIN t2 ON true JOIN t4 ON true NATURAL LEFT JOIN t1;
1343 } {3 NULL 4 NULL}
1344
1345 # 2025-06-16 https://sqlite.org/forum/forumpost/68f29a2005
1346 #
1347 # The transitive-constraint optimization was not working for RIGHT JOIN.
1348 #
1349 reset_db
1350 db null NULL
1351 do_execsql_test join-32.1 {
1352 CREATE TABLE t0(w INT);
1353 CREATE TABLE t1(x INT);
1354 CREATE TABLE t2(y INT UNIQUE);
1355 CREATE VIEW v0(z) AS SELECT CAST(x AS INT) FROM t1 LEFT JOIN t2 ON true;
1356 INSERT INTO t1(x) VALUES(123);
1357 INSERT INTO t2(y) VALUES(NULL);
1358 }
1359 do_execsql_test join-32.2 {
1360 SELECT *
1361 FROM t0 JOIN v0 ON w=z
1362 RIGHT JOIN t1 ON true
1363 INNER JOIN t2 ON y IS z;
1364 } {NULL NULL 123 NULL}
1365 do_execsql_test join-32.3 {
1366 SELECT *
1367 FROM t0 JOIN v0 ON w=z
1368 RIGHT JOIN t1 ON true
1369 INNER JOIN t2 ON +y IS z;
1370 } {NULL NULL 123 NULL}
1371
1372 # 2025-12-24 https://sqlite.org/forum/forumpost/11a53f2bad
1373 #
1374 # Chained omit-noop-join optimization
1375 #
1376 reset_db
1377 db null NULL
1378 do_execsql_test join-33.1 {
1379 CREATE TABLE t1(a1 INTEGER PRIMARY KEY, b1);
1380 CREATE TABLE t2(a2 INTEGER PRIMARY KEY, b2);
1381 CREATE TABLE t3(a3 INTEGER PRIMARY KEY, b3);
1382 CREATE TABLE t4(a4 INTEGER PRIMARY KEY, b4);
1383 INSERT INTO t1 VALUES(1,11),(2,12),(3,13), (5,15);
1384 INSERT INTO t2 VALUES(1,21), (3,23),(4,24),(5,25);
1385 INSERT INTO t3 VALUES (2,32),(3,33), (5,35);
1386 INSERT INTO t4 VALUES(1,41),(2,42), (4,44),(5,45);
1387 CREATE VIEW vchain AS
1388 SELECT a1, b1, b2, b3, b4
1389 FROM t1 LEFT JOIN t2 ON a1=a2
1390 LEFT JOIN t3 ON a2=a3
1391 LEFT JOIN t4 ON a3=a4;
1392 }
1393 do_execsql_test join-33.2 {
1394 SELECT a1 FROM vchain ORDER BY a1;
1395 } {1 2 3 5}
1396 do_eqp_test join-33.2-eqp {
1397 SELECT a1 FROM vchain ORDER BY a1;
1398 } {
1399 QUERY PLAN
1400 `--SCAN t1
1401 }
1402 do_execsql_test join-33.3 {
1403 SELECT a1, b2 FROM vchain ORDER BY a1;
1404 } {1 21 2 NULL 3 23 5 25}
1405 do_eqp_test join-33.3-eqp {
1406 SELECT a1, b2 FROM vchain ORDER BY a1;
1407 } {
1408 QUERY PLAN
1409 |--SCAN t1
1410 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
1411 }
1412
1413 finish_test