Debian Codegraph @

test/joinH.test

raw
1 # 2022 May 17
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix joinH
17
18 do_execsql_test 1.0 {
19 CREATE TABLE t1(a INT);
20 CREATE TABLE t2(b INT);
21 INSERT INTO t2(b) VALUES(NULL);
22 }
23
24 db nullvalue NULL
25
26 do_execsql_test 1.1 {
27 SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL);
28 } {NULL}
29 do_execsql_test 1.2 {
30 SELECT a FROM t1 FULL JOIN t2 ON true;
31 } {NULL}
32 do_execsql_test 1.3 {
33 SELECT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL);
34 } {NULL}
35 do_execsql_test 1.4 {
36 SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true;
37 } {NULL}
38
39 #-----------------------------------------------------------
40
41 reset_db
42 do_execsql_test 2.0 {
43 CREATE TABLE r3(x);
44 CREATE TABLE r4(y INTEGER PRIMARY KEY);
45 INSERT INTO r4 VALUES(55);
46 }
47
48 do_execsql_test 2.1 {
49 SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x);
50 } {value!}
51
52 do_execsql_test 2.2 {
53 SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x) WHERE +y=55;
54 } {value!}
55
56 #-----------------------------------------------------------
57 reset_db
58 do_execsql_test 3.1 {
59 CREATE TABLE t0 (c0);
60 CREATE TABLE t1 (c0);
61 CREATE TABLE t2 (c0 , c1 , c2 , UNIQUE (c0), UNIQUE (c2 DESC));
62 INSERT INTO t2 VALUES ('x', 'y', 'z');
63 ANALYZE;
64 CREATE VIEW v0(c0) AS SELECT FALSE;
65 }
66
67 do_catchsql_test 3.2 {
68 SELECT * FROM t0 LEFT OUTER JOIN t1 ON v0.c0 INNER JOIN v0 INNER JOIN t2 ON (t2.c2 NOT NULL);
69 } {1 {ON clause references tables to its right}}
70
71 #-------------------------------------------------------------
72
73 reset_db
74 do_execsql_test 4.1 {
75 CREATE TABLE t1(a,b,c,d,e,f,g,h,PRIMARY KEY(a,b,c)) WITHOUT ROWID;
76 CREATE TABLE t2(i, j);
77 INSERT INTO t2 VALUES(10, 20);
78 }
79
80 do_execsql_test 4.2 {
81 SELECT (d IS NULL) FROM t1 RIGHT JOIN t2 ON (j=33);
82 } {1}
83
84 do_execsql_test 4.3 {
85 CREATE INDEX i1 ON t1( (d IS NULL), d );
86 }
87
88 do_execsql_test 4.4 {
89 SELECT (d IS NULL) FROM t1 RIGHT JOIN t2 ON (j=33);
90 } {1}
91
92 #-------------------------------------------------------------------------
93 #
94 reset_db
95 do_execsql_test 5.0 {
96 CREATE TABLE t0(w);
97 CREATE TABLE t1(x);
98 CREATE TABLE t2(y);
99 CREATE TABLE t3(z);
100 INSERT INTO t3 VALUES('t3val');
101 }
102
103 do_execsql_test 5.1 {
104 SELECT * FROM t1 INNER JOIN t2 ON (0) RIGHT OUTER JOIN t3;
105 } {{} {} t3val}
106
107 do_execsql_test 5.2 {
108 SELECT * FROM t1 INNER JOIN t2 ON (0) FULL OUTER JOIN t3;
109 } {{} {} t3val}
110
111 do_execsql_test 5.3 {
112 SELECT * FROM t3 LEFT JOIN t2 ON (0);
113 } {t3val {}}
114
115 do_execsql_test 5.4 {
116 SELECT * FROM t0 RIGHT JOIN t1 INNER JOIN t2 ON (0) RIGHT JOIN t3
117 } {{} {} {} t3val}
118
119 do_execsql_test 5.5 {
120 SELECT * FROM t0 RIGHT JOIN t1 INNER JOIN t2 ON (0)
121 } {}
122
123
124 reset_db
125 db null NULL
126 do_execsql_test 6.0 {
127 CREATE TABLE t1(a INT);
128 CREATE TABLE t2(b INT);
129 INSERT INTO t1 VALUES(3);
130 SELECT CASE WHEN t2.b THEN 0 ELSE 1 END FROM t1 LEFT JOIN t2 ON true;
131 } {1}
132 do_execsql_test 6.1 {
133 SELECT * FROM t1 LEFT JOIN t2 ON true WHERE CASE WHEN t2.b THEN 0 ELSE 1 END;
134 } {3 NULL}
135
136 #-------------------------------------------------------------------------
137 reset_db
138 do_execsql_test 7.0 {
139 CREATE TABLE t1(a, b);
140 CREATE TABLE t2(c);
141 CREATE TABLE t3(d);
142
143 INSERT INTO t1 VALUES ('a', 'a');
144 INSERT INTO t2 VALUES ('ddd');
145 INSERT INTO t3 VALUES(1234);
146 }
147
148 do_execsql_test 7.1 {
149 SELECT t2.rowid FROM t1 JOIN (t2 JOIN t3);
150 } {1}
151
152 do_execsql_test 7.1 {
153 UPDATE t1 SET b = t2.rowid FROM t2, t3;
154 }
155
156 do_execsql_test 7.2 {
157 SELECT * FROM t1
158 } {a 1}
159
160 #-------------------------------------------------------------------------
161 reset_db
162 do_execsql_test 8.0 {
163 CREATE TABLE x1(a INTEGER PRIMARY KEY, b);
164 CREATE TABLE x2(c, d);
165 CREATE TABLE x3(rowid, _rowid_);
166
167 CREATE TABLE x4(rowid, _rowid_, oid);
168
169 INSERT INTO x1 VALUES(1000, 'thousand');
170 INSERT INTO x2 VALUES('c', 'd');
171 INSERT INTO x3(oid, rowid, _rowid_) VALUES(43, 'hello', 'world');
172 INSERT INTO x4(oid, rowid, _rowid_) VALUES('forty three', 'hello', 'world');
173 }
174
175 do_execsql_test 8.1 {
176 SELECT x3.oid FROM x1 JOIN (x2 JOIN x3 ON c='c')
177 } 43
178
179 breakpoint
180 do_execsql_test 8.2 {
181 SELECT x3.rowid FROM x1 JOIN (x2 JOIN x3 ON c='c')
182 } {hello}
183
184 do_execsql_test 8.3 {
185 SELECT x4.oid FROM x1 JOIN (x2 JOIN x4 ON c='c')
186 } {{forty three}}
187
188
189 #---------------------------------------------------------------------
190 #
191 reset_db
192 do_execsql_test 9.0 {
193 CREATE TABLE x1(a);
194 CREATE TABLE x2(b);
195 CREATE TABLE x3(c);
196
197 CREATE TABLE wo1(a PRIMARY KEY, b) WITHOUT ROWID;
198 CREATE TABLE wo2(a PRIMARY KEY, rowid) WITHOUT ROWID;
199 CREATE TABLE wo3(a PRIMARY KEY, b) WITHOUT ROWID;
200 }
201
202 do_catchsql_test 9.1 {
203 SELECT rowid FROM wo1, x1, x2;
204 } {1 {ambiguous column name: rowid}}
205 do_catchsql_test 9.2 {
206 SELECT rowid FROM wo1, (x1, x2);
207 } {1 {ambiguous column name: rowid}}
208 do_catchsql_test 9.3 {
209 SELECT rowid FROM wo1 JOIN (x1 JOIN x2);
210 } {1 {ambiguous column name: rowid}}
211 do_catchsql_test 9.4 {
212 SELECT a FROM wo1, x1, x2;
213 } {1 {ambiguous column name: a}}
214
215
216 # It is not possible to use "rowid" in a USING clause.
217 #
218 do_catchsql_test 9.5 {
219 SELECT * FROM x1 JOIN x2 USING (rowid);
220 } {1 {cannot join using column rowid - column not present in both tables}}
221 do_catchsql_test 9.6 {
222 SELECT * FROM wo2 JOIN x2 USING (rowid);
223 } {1 {cannot join using column rowid - column not present in both tables}}
224
225 # "rowid" columns are not matched by NATURAL JOIN. If they were, then
226 # the SELECT below would return zero rows.
227 do_execsql_test 9.7 {
228 INSERT INTO x1(rowid, a) VALUES(101, 'A');
229 INSERT INTO x2(rowid, b) VALUES(55, 'B');
230 SELECT * FROM x1 NATURAL JOIN x2;
231 } {A B}
232
233 do_execsql_test 9.8 {
234 INSERT INTO wo1(a, b) VALUES('mya', 'myb');
235 INSERT INTO wo2(a, rowid) VALUES('mypk', 'myrowid');
236 INSERT INTO wo3(a, b) VALUES('MYA', 'MYB');
237 INSERT INTO x3(rowid, c) VALUES(99, 'x3B');
238 }
239
240 do_catchsql_test 9.8 {
241 SELECT rowid FROM x1 JOIN (x2 JOIN wo2);
242 } {0 myrowid}
243 do_catchsql_test 9.9 {
244 SELECT _rowid_ FROM wo1 JOIN (wo3 JOIN x3)
245 } {0 99}
246 do_catchsql_test 9.10 {
247 SELECT oid FROM wo1 JOIN (wo3 JOIN x3)
248 } {0 99}
249 do_catchsql_test 9.11 {
250 SELECT oid FROM wo2 JOIN (wo3 JOIN x3)
251 } {0 99}
252
253 reset_db
254 do_execsql_test 10.0 {
255 CREATE TABLE rt0 (c0 INTEGER, c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER);
256 CREATE TABLE rt3 (c3 INTEGER);
257
258 INSERT INTO rt0(c3, c1) VALUES (x'', '1');
259 INSERT INTO rt0(c3, c1) VALUES ('-1', -1e500);
260 INSERT INTO rt0(c3, c1) VALUES (1, x'');
261
262 CREATE VIEW v6(c0, c1, c2) AS SELECT 0, 0, 0;
263 }
264
265 do_execsql_test 10.1 {
266 SELECT COUNT(*) FROM rt0 LEFT JOIN rt3 JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1); -- 2
267 } {0}
268
269 do_execsql_test 10.2 {
270 SELECT COUNT(*) FROM rt0 LEFT JOIN rt3 RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1); -- 2
271 } {0}
272
273 #-------------------------------------------------------------------------
274
275 do_execsql_test 11.1 {
276 CREATE TABLE t1(a, b);
277 CREATE TABLE t2(c, d);
278 CREATE TABLE t3(e, f);
279
280 INSERT INTO t1 VALUES(1, 1);
281 INSERT INTO t2 VALUES(2, 2);
282 INSERT INTO t3 VALUES(3, 3);
283 }
284
285 do_execsql_test 11.2 {
286 SELECT * FROM t1 LEFT JOIN t2 RIGHT JOIN t3 ON (t2.c=10)
287 } {{} {} {} {} 3 3}
288
289 do_execsql_test 11.3 {
290 SELECT * FROM t1 LEFT JOIN t2 RIGHT JOIN t3 ON (t2.c=10) WHERE t1.a=1
291 } {}
292
293 #-------------------------------------------------------------------------
294 reset_db
295
296 do_execsql_test 12.1 {
297 CREATE TABLE t1(a1 INT, b1 TEXT);
298 INSERT INTO t1 VALUES(88,'');
299 CREATE TABLE t2(c2 INT, d2 TEXT);
300 INSERT INTO t2 VALUES(88,'');
301 CREATE TABLE t3(e3 TEXT PRIMARY KEY);
302 INSERT INTO t3 VALUES('');
303 }
304
305 do_execsql_test 12.2 {
306 SELECT * FROM t1 LEFT JOIN t2 ON true RIGHT JOIN t3 ON d2=e3 WHERE c2 BETWEEN NULL AND a1;
307 }
308 do_execsql_test 12.3 {
309 SELECT * FROM t1 LEFT JOIN t2 ON true RIGHT JOIN t3 ON d2=e3 WHERE c2 BETWEEN NULL AND a1;
310 }
311
312 #-------------------------------------------------------------------------
313 # 2024-04-05 dbsqlfuzz b9e65e2f110df998f1306571fae7af6c01e4d92b
314 reset_db
315 do_execsql_test 13.1 {
316 CREATE TABLE t1(a INT AS (b), b INT);
317 INSERT INTO t1(b) VALUES(123);
318 CREATE TABLE t2(a INT, c INT);
319 SELECT a FROM t2 NATURAL RIGHT JOIN t1;
320 } {123}
321 do_execsql_test 13.2 {
322 CREATE INDEX t1a ON t1(a);
323 SELECT a FROM t2 NATURAL RIGHT JOIN t1;
324 } {123}
325 # Further tests of the same logic (indexes on expressions
326 # used by RIGHT JOIN) from check-in ffe23af73fcb324d and
327 # forum post https://sqlite.org/forum/forumpost/9b491e1debf0b67a.
328 db null NULL
329 do_execsql_test 13.3 {
330 CREATE TABLE t3(a INT, b INT);
331 CREATE UNIQUE INDEX t3x ON t3(a, a+b);
332 INSERT INTO t3(a,b) VALUES(1,2),(4,8),(16,32),(4,80),(1,-300);
333 CREATE TABLE t4(x INT, y INT);
334 INSERT INTO t4(x,y) SELECT a, b FROM t3;
335 INSERT INTO t4(x,y) VALUES(99,99);
336 SELECT a1.a, sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t4 ON a=x
337 GROUP BY a1.a ORDER BY 1;
338 } {NULL NULL 1 -592 4 192 16 48}
339 do_execsql_test 13.4 {
340 SELECT sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t3 ON true
341 GROUP BY a1.a ORDER BY 1;
342 } {-1480 240 480}
343
344 #-------------------------------------------------------------------------
345 # 2025-05-30
346 # https://sqlite.org/forum/forumpost/5028c785b6
347 #
348 reset_db
349
350 do_execsql_test 14.0 {
351 CREATE TABLE t1(c0 INT);
352 CREATE TABLE t2(c0 BLOB);
353 CREATE TABLE t3(c0 BLOB);
354 CREATE TABLE t4(c4 BLOB);
355 INSERT INTO t1(c0) VALUES(0);
356 INSERT INTO t3(c0) VALUES('0');
357 }
358
359 do_execsql_test 14.1.1 {
360 SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3;
361 } {0}
362
363 do_execsql_test 14.1.2 {
364 SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3 FULL JOIN t4 ON true;
365 } {0 {}}
366
367 do_execsql_test 14.1.3 {
368 SELECT * FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) FULL JOIN t4 ON true;
369 } {0 {}}
370
371 do_execsql_test 14.1.4 {
372 SELECT *
373 FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) AS qq FULL JOIN t4 ON true;
374 } {0 {}}
375
376 do_execsql_test 14.2.1 {
377 SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1;
378 } {0}
379
380 do_execsql_test 14.2.2 {
381 SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1 FULL JOIN t4 ON true;
382 } {0 {}}
383
384 do_execsql_test 14.2.3 {
385 SELECT * FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) FULL JOIN t4 ON true;
386 } {0 {}}
387
388 do_execsql_test 14.2.4 {
389 SELECT *
390 FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) AS qq FULL JOIN t4 ON true;
391 } {0 {}}
392
393 # 2025-06-01
394 #
395 reset_db
396 do_execsql_test 15.1 {
397 CREATE TABLE t0(c0);
398 CREATE TABLE t1(c0);
399 CREATE TABLE t2(c0);
400 INSERT INTO t0 VALUES ('1.0');
401 INSERT INTO t2(c0) VALUES (9);
402 SELECT t0.c0,t2.c0 FROM (SELECT CAST(t0.c0 as REAL) AS c0 FROM t0) as subquery NATURAL LEFT JOIN t1 NATURAL JOIN t0 RIGHT JOIN t2 ON 1;
403 } {1.0 9}
404 do_execsql_test 15.2 {
405 CREATE TABLE x1(x COLLATE nocase);
406 CREATE TABLE x2(x);
407 CREATE TABLE x3(x);
408 CREATE TABLE t4(y);
409 INSERT INTO x1 VALUES('ABC');
410 INSERT INTO x3 VALUES('abc');
411 SELECT lower(x), quote(y) FROM x1 LEFT JOIN x2 USING (x) JOIN x3 USING (x) FULL JOIN t4;
412 } {abc NULL}
413
414 #-------------------------------------------------------------------------
415 reset_db
416 do_execsql_test 16.0 {
417 CREATE TABLE t0_a (c0 INT);
418 CREATE TABLE t0_b (c0 INT);
419
420 CREATE TABLE t2 (c0 INT);
421 INSERT INTO t2 VALUES (1);
422 }
423
424 do_execsql_test 16.1 {
425 SELECT * FROM t2 LEFT JOIN t0_b
426 } {1 {}}
427
428 do_execsql_test 16.2.1 {
429 SELECT * FROM (t0_a RIGHT JOIN ( t2 LEFT JOIN t0_b))
430 } {{} 1 {}}
431 do_execsql_test 16.2.2 {
432 SELECT * FROM (t0_a RIGHT JOIN (SELECT * FROM t2 LEFT JOIN t0_b))
433 } {{} 1 {}}
434
435
436 do_catchsql_test 16.3.1 {
437 SELECT * FROM (t0_a RIGHT JOIN ( t2 LEFT JOIN t0_b) USING (c0));
438 } {1 {ambiguous column name: c0}}
439
440 do_execsql_test 16.3.2 {
441 SELECT * FROM (t0_a RIGHT JOIN (SELECT * FROM t2 LEFT JOIN t0_b) USING (c0));
442 } {1 {}}
443
444 do_execsql_test 16.4.0 {
445 CREATE TABLE x0(a TEXT);
446 CREATE TABLE x1(a TEXT);
447 CREATE TABLE x2(a TEXT);
448
449 INSERT INTO x1 VALUES('blue');
450 INSERT INTO x2 VALUES('red');
451 }
452
453 do_catchsql_test 16.4.1 {
454 SELECT * FROM x0 RIGHT JOIN ( x1, x2) USING (a)
455 } {1 {ambiguous column name: a}}
456
457 do_execsql_test 16.4.2 {
458 SELECT * FROM x0 RIGHT JOIN (SELECT * FROM x1, x2) USING (a)
459 } {blue red}
460
461 reset_db
462 do_execsql_test 16.5.0 {
463 CREATE TABLE t0 (c0 INT);
464 CREATE TABLE t1 (c0 INT);
465 CREATE TABLE t2 (c0 INT);
466
467 INSERT INTO t1(c0) VALUES (NULL);
468 INSERT INTO t2 VALUES (1);
469
470 CREATE VIEW v0(c0) AS SELECT 1 AS col_0 FROM t0;
471 }
472
473 do_catchsql_test 16.5.2 {
474 SELECT * FROM (t0 NATURAL RIGHT JOIN (t0 FULL JOIN (v0 NATURAL FULL JOIN t2) ON TRUE)) NATURAL FULL JOIN t1;
475 } {1 {ambiguous column name: c0}}
476
477 finish_test