Fixes default log output to console for macOS
[sqlcipher.git] / test / join5.test
blob703c256f8674048f56b267d40ff452bbf5fc54f8
1 # 2005 September 19
3 # The author disclaims copyright to this source code.  In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
13 # This file implements tests for left outer joins containing ON
14 # clauses that restrict the scope of the left term of the join.
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19 set testprefix join5
22 do_test join5-1.1 {
23   execsql {
24     BEGIN;
25     CREATE TABLE t1(a integer primary key, b integer, c integer);
26     CREATE TABLE t2(x integer primary key, y);
27     CREATE TABLE t3(p integer primary key, q);
28     INSERT INTO t3 VALUES(11,'t3-11');
29     INSERT INTO t3 VALUES(12,'t3-12');
30     INSERT INTO t2 VALUES(11,'t2-11');
31     INSERT INTO t2 VALUES(12,'t2-12');
32     INSERT INTO t1 VALUES(1, 5, 0);
33     INSERT INTO t1 VALUES(2, 11, 2);
34     INSERT INTO t1 VALUES(3, 12, 1);
35     COMMIT;
36   }
37 } {}
38 do_test join5-1.2 {
39   execsql {
40     select * from t1 left join t2 on t1.b=t2.x and t1.c=1
41   }
42 } {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12}
43 do_test join5-1.3 {
44   execsql {
45     select * from t1 left join t2 on t1.b=t2.x where t1.c=1
46   }
47 } {3 12 1 12 t2-12}
48 do_test join5-1.4 {
49   execsql {
50     select * from t1 left join t2 on t1.b=t2.x and t1.c=1
51                      left join t3 on t1.b=t3.p and t1.c=2
52   }
53 } {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}}
54 do_test join5-1.5 {
55   execsql {
56     select * from t1 left join t2 on t1.b=t2.x and t1.c=1
57                      left join t3 on t1.b=t3.p where t1.c=2
58   }
59 } {2 11 2 {} {} 11 t3-11}
61 # Ticket #2403
63 do_test join5-2.1 {
64   execsql {
65     CREATE TABLE ab(a,b);
66     INSERT INTO "ab" VALUES(1,2);
67     INSERT INTO "ab" VALUES(3,NULL);
69     CREATE TABLE xy(x,y);
70     INSERT INTO "xy" VALUES(2,3);
71     INSERT INTO "xy" VALUES(NULL,1);
72   }
73   execsql {SELECT * FROM xy LEFT JOIN ab ON 0}
74 } {2 3 {} {} {} 1 {} {}}
75 do_test join5-2.2 {
76   execsql {SELECT * FROM xy LEFT JOIN ab ON 1}
77 } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}
78 do_test join5-2.3 {
79   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL}
80 } {2 3 {} {} {} 1 {} {}}
81 do_test join5-2.4 {
82   execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0}
83 } {}
84 do_test join5-2.5 {
85   execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0}
86 } {}
87 do_test join5-2.6 {
88   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0}
89 } {}
90 do_test join5-2.7 {
91   execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1}
92 } {2 3 {} {} {} 1 {} {}}
93 do_test join5-2.8 {
94   execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1}
95 } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}
96 do_test join5-2.9 {
97   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1}
98 } {2 3 {} {} {} 1 {} {}}
99 do_test join5-2.10 {
100   execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL}
101 } {}
102 do_test join5-2.11 {
103   execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL}
104 } {}
105 do_test join5-2.12 {
106   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL}
107 } {}
109 # Ticket https://www.sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601
110 # Incorrect output on a LEFT JOIN.
112 do_execsql_test join5-3.1 {
113   DROP TABLE IF EXISTS t1;
114   DROP TABLE IF EXISTS t2;
115   DROP TABLE IF EXISTS t3;
116   CREATE TABLE x1(a);
117   INSERT INTO x1 VALUES(1);
118   CREATE TABLE x2(b NOT NULL);
119   CREATE TABLE x3(c, d);
120   INSERT INTO x3 VALUES('a', NULL);
121   INSERT INTO x3 VALUES('b', NULL);
122   INSERT INTO x3 VALUES('c', NULL);
123   SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b;
124 } {1 {} {} {}}
125 do_execsql_test join5-3.2 {
126   DROP TABLE IF EXISTS t1;
127   DROP TABLE IF EXISTS t2;
128   DROP TABLE IF EXISTS t3;
129   DROP TABLE IF EXISTS t4;
130   DROP TABLE IF EXISTS t5;
131   CREATE TABLE t1(x text NOT NULL, y text);
132   CREATE TABLE t2(u text NOT NULL, x text NOT NULL);
133   CREATE TABLE t3(w text NOT NULL, v text);
134   CREATE TABLE t4(w text NOT NULL, z text NOT NULL);
135   CREATE TABLE t5(z text NOT NULL, m text);
136   INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL);
137   INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL);
138   INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL);
139   INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL);
140   INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c',
141                         'f6d7661f-4efe-4c90-87b5-858e61cd178b');
142   SELECT *
143     FROM t3
144          INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL
145          LEFT JOIN t4  ON t4.w = t3.w
146          LEFT JOIN t5  ON t5.z = t4.z
147          LEFT JOIN t2  ON t2.u = t5.m
148          LEFT JOIN t1 xyz ON xyz.y = t2.x;
149 } {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}}
150 do_execsql_test join5-3.3 {
151   DROP TABLE IF EXISTS x1;
152   DROP TABLE IF EXISTS x2;
153   DROP TABLE IF EXISTS x3;
154   CREATE TABLE x1(a);
155   INSERT INTO x1 VALUES(1);
156   CREATE TABLE x2(b NOT NULL);
157   CREATE TABLE x3(c, d);
158   INSERT INTO x3 VALUES('a', NULL);
159   INSERT INTO x3 VALUES('b', NULL);
160   INSERT INTO x3 VALUES('c', NULL);
161   SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
162 } {}
164 # Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on
165 # 2015-08-20.  LEFT JOIN and the push-down optimization.
167 do_execsql_test join5-4.1 {
168   SELECT *
169   FROM (
170       SELECT 'apple' fruit
171       UNION ALL SELECT 'banana'
172   ) a
173   JOIN (
174       SELECT 'apple' fruit
175       UNION ALL SELECT 'banana'
176   ) b ON a.fruit=b.fruit
177   LEFT JOIN (
178       SELECT 1 isyellow
179   ) c ON b.fruit='banana';
180 } {apple apple {} banana banana 1}
181 do_execsql_test join5-4.2 {
182   SELECT *
183     FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
184          LEFT JOIN (SELECT 1) ON fruit='banana';
185 } {apple {} banana 1}
187 #-------------------------------------------------------------------------
188 do_execsql_test 5.0 {
189   CREATE TABLE y1(x, y, z);
190   INSERT INTO y1 VALUES(0, 0, 1);
191   CREATE TABLE y2(a);
194 do_execsql_test 5.1 {
195   SELECT count(z) FROM y1 LEFT JOIN y2 ON x GROUP BY y;
196 } 1
198 do_execsql_test 5.2 {
199   SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x GROUP BY y;
200 } 1
202 do_execsql_test 5.3 {
203   CREATE VIEW v1 AS SELECT x, y, z FROM y1;
204   SELECT count(z) FROM v1 LEFT JOIN y2 ON x GROUP BY y;
205 } 1
207 do_execsql_test 5.4 {
208   SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
209 } 1
211 do_execsql_test 5.5 {
212   SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
213 } {0 0 1 {}}
215 #-------------------------------------------------------------------------
217 reset_db
218 do_execsql_test 6.1 {
219   CREATE TABLE t1(x); 
220   INSERT INTO t1 VALUES(1);
222   CREATE TABLE t2(y INTEGER PRIMARY KEY,a,b);
223   INSERT INTO t2 VALUES(1,2,3);
224   CREATE INDEX t2a ON t2(a); 
225   CREATE INDEX t2b ON t2(b); 
228 do_execsql_test 6.2 {
229   SELECT * FROM t1 LEFT JOIN t2 ON a=2 OR b=3 WHERE y IS NULL;
230 } {}
232 do_execsql_test 6.3.1 {
233   CREATE TABLE t3(x);
234   INSERT INTO t3 VALUES(1);
235   CREATE TABLE t4(y, z);
236   SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);
237 } {!!!}
239 do_execsql_test 6.3.2 {
240   CREATE INDEX t4i ON t4(y, ifnull(z, '!!!'));
241   SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);
242 } {!!!}
244 # 2019-02-08 https://sqlite.org/src/info/4e8e4857d32d401f
245 reset_db
246 do_execsql_test 6.100 {
247   CREATE TABLE t1(aa, bb);
248   CREATE INDEX t1x1 on t1(abs(aa), abs(bb));
249   INSERT INTO t1 VALUES(-2,-3),(+2,-3),(-2,+3),(+2,+3);
250   SELECT * FROM (t1) 
251    WHERE ((abs(aa)=1 AND 1=2) OR abs(aa)=2)
252      AND abs(bb)=3
253   ORDER BY +1, +2;
254 } {-2 -3 -2 3 2 -3 2 3}
256 #-------------------------------------------------------------------------
258 reset_db
259 do_execsql_test 7.0 {
260   CREATE TABLE t1(x);
261   INSERT INTO t1 VALUES(1);
264 do_execsql_test 7.1 {
265   CREATE TABLE t2(x, y, z);
266   CREATE INDEX t2xy ON t2(x, y);
267   WITH s(i) AS (
268     SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000
269   )
270   INSERT INTO t2 SELECT i/10, i, NULL FROM s;
271   ANALYZE;
274 do_eqp_test 7.2 {
275   SELECT * FROM t1 LEFT JOIN t2 ON (
276     t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
277   );
278 } {
279   QUERY PLAN
280   |--SCAN t1
281   `--MULTI-INDEX OR
282      |--INDEX 1
283      |  `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
284      `--INDEX 2
285         `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
288 do_execsql_test 7.3 {
289   CREATE TABLE t3(x);
290   INSERT INTO t3(x) VALUES(1);
291   CREATE INDEX t3x ON t3(x);
293   CREATE TABLE t4(x, y, z);
294   CREATE INDEX t4xy ON t4(x, y);
295   CREATE INDEX t4xz ON t4(x, z);
297   WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000)
298   INSERT INTO t4 SELECT i/10, i, i FROM s;
300   ANALYZE;
301   UPDATE sqlite_stat1 SET stat='1000000 10 1' WHERE idx='t3x';
302   ANALYZE sqlite_schema;
305 # If both sides of the OR reference the right-hand side of the LEFT JOIN
306 # then simplify the LEFT JOIN.
308 do_eqp_test 7.4 {
309   SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?);
310 } {
311   QUERY PLAN
312   |--SCAN t4
313   `--SEARCH t3 USING COVERING INDEX t3x (x=?)
315 # If only one side of the OR references the right-hand side of the LEFT JOIN
316 # then do not do the simplification
318 do_eqp_test 7.4b {
319   SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t3.x = ?);
320 } {
321   QUERY PLAN
322   |--SCAN t3
323   `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN
325 do_eqp_test 7.4c {
326   SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t3.x = ? OR t4.z = ?);
327 } {
328   QUERY PLAN
329   |--SCAN t3
330   `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN
332 do_eqp_test 7.4d {
333   SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?);
334 } {
335   QUERY PLAN
336   |--SCAN t3
337   |--BLOOM FILTER ON t4 (x=?)
338   `--SEARCH t4 USING INDEX t4xz (x=?)
341 reset_db
342 do_execsql_test 8.0 {
343   CREATE TABLE t0 (c0, c1, PRIMARY KEY (c0, c1));
344   CREATE TABLE t1 (c0);
346   INSERT INTO t1 VALUES (2);
348   INSERT INTO t0 VALUES(0, 10);
349   INSERT INTO t0 VALUES(1, 10);
350   INSERT INTO t0 VALUES(2, 10);
351   INSERT INTO t0 VALUES(3, 10);
354 do_execsql_test 8.1 {
355   SELECT * FROM t0, t1 
356   WHERE (t0.c1 >= 1 OR t0.c1 < 1) AND t0.c0 IN (1, t1.c0) ORDER BY 1;
357 } {
358   1 10 2
359   2 10 2
363 # 2022-01-31 dbsqlfuzz 787d9bd73164c6f0c85469e2e48b2aff19af6938
365 reset_db
366 do_execsql_test 9.1 {
367   CREATE TABLE t1(a ,b FLOAT);
368   INSERT INTO t1 VALUES(1,1);
369   CREATE INDEX t1x1 ON t1(a,b,a,a,a,a,a,a,a,a,a,b);
370   ANALYZE sqlite_schema;
371   INSERT INTO sqlite_stat1 VALUES('t1','t1x1','648 324 81 81 81 81 81 81 81081 81 81 81');
372   ANALYZE sqlite_schema;
374 do_catchsql_test 9.2 {
375   SELECT a FROM 
376       (SELECT a FROM t1 NATURAL LEFT JOIN t1) NATURAL LEFT JOIN t1 
377   WHERE (rowid,1)<=(5,0);
378 } {0 1}
380 # 2022-03-02 https://sqlite.org/forum/info/50a1bbe08ce4c29c
381 # Bloom-filter pulldown is incompatible with skip-scan.
383 reset_db
384 do_execsql_test 10.1 {
385   CREATE TABLE t1(x INT);
386   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
387     INSERT INTO t1(x) SELECT 0 FROM c;
388   CREATE INDEX t1x1 ON t1(x BETWEEN 0 AND 10, x);
389   ANALYZE;
390   DELETE FROM t1;
391   INSERT INTO t1 VALUES(0),(0);
392   CREATE VIEW v1 AS SELECT * FROM t1 NATURAL JOIN t1 WHERE (x BETWEEN 0 AND 10) OR true;
393   CREATE VIEW v2 AS SELECT * FROM v1 NATURAL JOIN v1;
394   CREATE VIEW v3 AS SELECT * FROM v2, v1 USING (x) GROUP BY x;
395   SELECT x FROM v3; 
396 } {0}
398 # 2022-03-24 https://sqlite.org/forum/forumpost/031e262a89b6a9d2
399 # Bloom-filter on a LEFT JOIN with NULL-based WHERE constraints.
401 reset_db
402 do_execsql_test 11.1 {
403   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
404   CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
405   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8)
406   INSERT INTO t1(a,b) SELECT x, 10*x FROM c;
407   INSERT INTO t2(c,d) SELECT b*2, 100*a FROM t1;
408   ANALYZE;
409   DELETE FROM sqlite_stat1;
410   INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES
411     ('t1',NULL,150105),('t2',NULL,98747);
412   ANALYZE sqlite_schema;
413 } {}
414 do_execsql_test 11.2 {
415   SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d IS NULL;
416 } {4}
417 do_execsql_test 11.3 {
418   SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d=100;
419 } {1}
420 do_execsql_test 11.4 {
421   SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d>=300;
422 } {2}
424 # 2022-05-03 https://sqlite.org/forum/forumpost/2482b32700384a0f
425 # Bloom-filter pull-down does not handle NOT NULL constraints correctly.
427 reset_db
428 do_execsql_test 12.1 {
429   CREATE TABLE t1(a INT, b INT, c INT);
430   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
431     INSERT INTO t1(a,b,c) SELECT x, x*1000, x*1000000 FROM c;
432   CREATE TABLE t2(b INT, x INT);
433   INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%3==0;
434   CREATE INDEX t2b ON t2(b);
435   CREATE TABLE t3(c INT, y INT);
436   INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%4==0;
437   CREATE INDEX t3c ON t3(c);
438   INSERT INTO t1(a,b,c) VALUES(200, 200000, NULL);
439   ANALYZE;
440 } {}
441 do_execsql_test 12.2 {
442   SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE x>0 AND y>0
443   ORDER BY +a;
444 } {
445   12  12000  12000000  12  12
446   24  24000  24000000  24  24
447   36  36000  36000000  36  36
448   48  48000  48000000  48  48
449   60  60000  60000000  60  60
450   72  72000  72000000  72  72
451   84  84000  84000000  84  84
452   96  96000  96000000  96  96
458 finish_test