Debian Codegraph @

test/join9.test

raw
1 # 2022-04-16
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 RIGHT and FULL OUTER JOINs.
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 foreach {id schema} {
19 1 {
20 CREATE TABLE t3(id INTEGER PRIMARY KEY, w TEXT);
21 CREATE TABLE t4(id INTEGER PRIMARY KEY, x TEXT);
22 CREATE TABLE t5(id INTEGER PRIMARY KEY, y TEXT);
23 CREATE TABLE t6(id INTEGER PRIMARY KEY, z INT);
24 CREATE VIEW dual(dummy) AS VALUES('x');
25 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
26 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
27 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
28 (5,'blue');
29 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
30 }
31 2 {
32 CREATE TABLE t3(id INT PRIMARY KEY, w TEXT) WITHOUT ROWID;
33 CREATE TABLE t4(id INT PRIMARY KEY, x TEXT) WITHOUT ROWID;
34 CREATE TABLE t5(id INT PRIMARY KEY, y TEXT) WITHOUT ROWID;
35 CREATE TABLE t6(id INT PRIMARY KEY, z INT) WITHOUT ROWID;
36 CREATE TABLE dual(dummy TEXT);
37 INSERT INTO dual(dummy) VALUES('x');
38 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
39 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
40 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
41 (5,'blue');
42 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
43 }
44 3 {
45 CREATE TABLE t3x(id INTEGER PRIMARY KEY, w TEXT);
46 CREATE TABLE t4x(id INTEGER PRIMARY KEY, x TEXT);
47 CREATE TABLE t5x(id INTEGER PRIMARY KEY, y TEXT);
48 CREATE TABLE t6x(id INTEGER PRIMARY KEY, z INT);
49 CREATE VIEW dual(dummy) AS VALUES('x');
50 INSERT INTO t3x(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
51 INSERT INTO t4x(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
52 INSERT INTO t5x(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
53 (5,'blue');
54 INSERT INTO t6x(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
55 CREATE VIEW t3 AS SELECT * FROM t3x LIMIT 1000;
56 CREATE VIEW t4 AS SELECT * FROM t4x LIMIT 1000;
57 CREATE VIEW t5 AS SELECT * FROM t5x LIMIT 1000;
58 CREATE VIEW t6 AS SELECT * FROM t6x LIMIT 1000;
59 }
60 4 {
61 CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT);
62 CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT);
63 CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT);
64 CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT);
65 CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT);
66 CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT);
67 CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT);
68 CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT);
69 CREATE VIEW dual(dummy) AS VALUES('x');
70 INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three');
71 INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven');
72 INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob');
73 INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave');
74 INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow');
75 INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue');
76 INSERT INTO t6a(id,z) VALUES(3,333),(4,444);
77 INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999);
78 CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b;
79 CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b;
80 CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b;
81 CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b;
82 }
83 5 {
84 CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT) WITHOUT ROWID;
85 CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT);
86 CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID;
87 CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID;
88 CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT);
89 CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT) WITHOUT ROWID;
90 CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT);
91 CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT);
92 CREATE VIEW dual(dummy) AS VALUES('x');
93 INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three');
94 INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven');
95 INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob');
96 INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave');
97 INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow');
98 INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue');
99 INSERT INTO t6a(id,z) VALUES(3,333),(4,444);
100 INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999);
101 CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b;
102 CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b LIMIT 50;
103 CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b LIMIT 100;
104 CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b;
105 }
106 } {
107 reset_db
108 db nullvalue -
109 do_execsql_test join9-$id.setup $schema {}
110
111 # Verifid by PG-14 for case 1
112 do_execsql_test join9-$id.100 {
113 SELECT *, t4.id, t5.id, t6.id
114 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6
115 ORDER BY 1;
116 } {
117 2 alice orange - 2 2 -
118 4 bob green 444 4 4 4
119 6 cindy - - 6 - -
120 8 dave - - 8 - -
121 }
122
123 do_execsql_test join9-$id.101 {
124 SELECT *, t4.id, t5.id, t6.id
125 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6
126 ORDER BY id;
127 } {
128 2 alice orange - 2 2 -
129 4 bob green 444 4 4 4
130 6 cindy - - 6 - -
131 8 dave - - 8 - -
132 }
133 do_execsql_test join9-$id.102 {
134 SELECT *, t4.id, t5.id, t6.id
135 FROM t4 LEFT JOIN t5 USING(id) LEFT JOIN t6 USING(id)
136 ORDER BY id;
137 } {
138 2 alice orange - 2 2 -
139 4 bob green 444 4 4 4
140 6 cindy - - 6 - -
141 8 dave - - 8 - -
142 }
143
144 # Verifid by PG-14 using case 1
145 do_execsql_test join9-$id.200 {
146 SELECT id, x, y, z, t4.id, t5.id, t6.id
147 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6
148 ORDER BY 1;
149 } {
150 2 alice orange - 2 2 -
151 4 bob green 444 4 4 4
152 6 cindy - - 6 - -
153 8 dave - - 8 - -
154 }
155
156 do_execsql_test join9-$id.201 {
157 SELECT id, x, y, z, t4.id, t5.id, t6.id
158 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6
159 ORDER BY id;
160 } {
161 2 alice orange - 2 2 -
162 4 bob green 444 4 4 4
163 6 cindy - - 6 - -
164 8 dave - - 8 - -
165 }
166
167 # Verified by PG-14 using case 1
168 do_execsql_test join9-$id.300 {
169 SELECT *, t4.id, t5.id, t6.id
170 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6
171 ORDER BY 1;
172 } {
173 0 - - 1000 - - 0
174 3 - yellow 333 - 3 3
175 4 bob green 444 4 4 4
176 5 - blue 555 - 5 5
177 9 - - 999 - - 9
178 }
179
180 do_execsql_test join9-$id.301 {
181 SELECT *, t4.id, t5.id, t6.id
182 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6
183 ORDER BY id;
184 } {
185 0 - - 1000 - - 0
186 3 - yellow 333 - 3 3
187 4 bob green 444 4 4 4
188 5 - blue 555 - 5 5
189 9 - - 999 - - 9
190 }
191
192 # Verified by PG-14 for case 1
193 do_execsql_test join9-$id.400 {
194 SELECT *, t4.id, t5.id, t6.id
195 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6
196 ORDER BY 1;
197 } {
198 0 - - 1000 - - 0
199 1 - red - - 1 -
200 2 alice orange - 2 2 -
201 3 - yellow 333 - 3 3
202 4 bob green 444 4 4 4
203 5 - blue 555 - 5 5
204 6 cindy - - 6 - -
205 8 dave - - 8 - -
206 9 - - 999 - - 9
207 }
208
209 do_execsql_test join9-$id.401 {
210 SELECT *, t4.id, t5.id, t6.id
211 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6
212 ORDER BY id;
213 } {
214 0 - - 1000 - - 0
215 1 - red - - 1 -
216 2 alice orange - 2 2 -
217 3 - yellow 333 - 3 3
218 4 bob green 444 4 4 4
219 5 - blue 555 - 5 5
220 6 cindy - - 6 - -
221 8 dave - - 8 - -
222 9 - - 999 - - 9
223 }
224 do_execsql_test join9-$id.402 {
225 SELECT id, x, y, z, t4.id, t5.id, t6.id
226 FROM t4 NATURAL FULL JOIN t6 NATURAL FULL JOIN t5
227 ORDER BY id;
228 } {
229 0 - - 1000 - - 0
230 1 - red - - 1 -
231 2 alice orange - 2 2 -
232 3 - yellow 333 - 3 3
233 4 bob green 444 4 4 4
234 5 - blue 555 - 5 5
235 6 cindy - - 6 - -
236 8 dave - - 8 - -
237 9 - - 999 - - 9
238 }
239 do_execsql_test join9-$id.403 {
240 SELECT id, x, y, z, t4.id, t5.id, t6.id
241 FROM t5 NATURAL FULL JOIN t4 NATURAL FULL JOIN t6
242 ORDER BY id;
243 } {
244 0 - - 1000 - - 0
245 1 - red - - 1 -
246 2 alice orange - 2 2 -
247 3 - yellow 333 - 3 3
248 4 bob green 444 4 4 4
249 5 - blue 555 - 5 5
250 6 cindy - - 6 - -
251 8 dave - - 8 - -
252 9 - - 999 - - 9
253 }
254 do_execsql_test join9-$id.404 {
255 SELECT id, x, y, z, t4.id, t5.id, t6.id
256 FROM t5 NATURAL FULL JOIN t6 NATURAL FULL JOIN t4
257 ORDER BY id;
258 } {
259 0 - - 1000 - - 0
260 1 - red - - 1 -
261 2 alice orange - 2 2 -
262 3 - yellow 333 - 3 3
263 4 bob green 444 4 4 4
264 5 - blue 555 - 5 5
265 6 cindy - - 6 - -
266 8 dave - - 8 - -
267 9 - - 999 - - 9
268 }
269 do_execsql_test join9-$id.405 {
270 SELECT id, x, y, z, t4.id, t5.id, t6.id
271 FROM t6 NATURAL FULL JOIN t4 NATURAL FULL JOIN t5
272 ORDER BY id;
273 } {
274 0 - - 1000 - - 0
275 1 - red - - 1 -
276 2 alice orange - 2 2 -
277 3 - yellow 333 - 3 3
278 4 bob green 444 4 4 4
279 5 - blue 555 - 5 5
280 6 cindy - - 6 - -
281 8 dave - - 8 - -
282 9 - - 999 - - 9
283 }
284 do_execsql_test join9-$id.406 {
285 SELECT id, x, y, z, t4.id, t5.id, t6.id
286 FROM t6 NATURAL FULL JOIN t5 NATURAL FULL JOIN t4
287 ORDER BY id;
288 } {
289 0 - - 1000 - - 0
290 1 - red - - 1 -
291 2 alice orange - 2 2 -
292 3 - yellow 333 - 3 3
293 4 bob green 444 4 4 4
294 5 - blue 555 - 5 5
295 6 cindy - - 6 - -
296 8 dave - - 8 - -
297 9 - - 999 - - 9
298 }
299
300 # Verified by PG-14 using case 1
301 do_execsql_test join9-$id.500 {
302 SELECT id, w, x, y, z
303 FROM t3 FULL JOIN t4 USING(id)
304 NATURAL FULL JOIN t5
305 FULL JOIN t6 USING(id)
306 ORDER BY 1;
307 } {
308 0 - - - 1000
309 1 - - red -
310 2 two alice orange -
311 3 three - yellow 333
312 4 - bob green 444
313 5 - - blue 555
314 6 six cindy - -
315 7 seven - - -
316 8 - dave - -
317 9 - - - 999
318 }
319
320 # Verified by PG-14 using case 1
321 do_execsql_test join9-$id.600 {
322 SELECT id, w, x, y, z
323 FROM t3 JOIN dual AS d1 ON true
324 FULL JOIN t4 USING(id)
325 JOIN dual AS d2 ON true
326 NATURAL FULL JOIN t5
327 JOIN dual AS d3 ON true
328 FULL JOIN t6 USING(id)
329 CROSS JOIN dual AS d4
330 ORDER BY 1;
331 } {
332 0 - - - 1000
333 1 - - red -
334 2 two alice orange -
335 3 three - yellow 333
336 4 - bob green 444
337 5 - - blue 555
338 6 six cindy - -
339 7 seven - - -
340 8 - dave - -
341 9 - - - 999
342 }
343
344 # Verified by PG-14 using case 1
345 do_execsql_test join9-$id.700 {
346 SELECT id, w, x, y, z
347 FROM t3 JOIN dual AS d1 ON true
348 FULL JOIN t4 USING(id)
349 JOIN dual AS d2 ON true
350 NATURAL FULL JOIN t5
351 JOIN dual AS d3 ON true
352 FULL JOIN t6 USING(id)
353 CROSS JOIN dual AS d4
354 WHERE x<>'bob' OR x IS NULL
355 ORDER BY 1;
356 } {
357 0 - - - 1000
358 1 - - red -
359 2 two alice orange -
360 3 three - yellow 333
361 5 - - blue 555
362 6 six cindy - -
363 7 seven - - -
364 8 - dave - -
365 9 - - - 999
366 }
367
368 # Verified by PG-14 using case 1
369 do_execsql_test join9-$id.800 {
370 WITH t7(id,a) AS MATERIALIZED (SELECT * FROM t4 WHERE false)
371 SELECT *
372 FROM t7
373 JOIN t7 AS t7b USING(id)
374 FULL JOIN t3 USING(id);
375 } {
376 2 - - two
377 3 - - three
378 6 - - six
379 7 - - seven
380 }
381
382 # Verified by PG-14
383 do_execsql_test join9-$id.900 {
384 SELECT *
385 FROM (t3 NATURAL FULL JOIN t4)
386 NATURAL FULL JOIN
387 (t5 NATURAL FULL JOIN t6)
388 ORDER BY 1;
389 } {
390 0 - - - 1000
391 1 - - red -
392 2 two alice orange -
393 3 three - yellow 333
394 4 - bob green 444
395 5 - - blue 555
396 6 six cindy - -
397 7 seven - - -
398 8 - dave - -
399 9 - - - 999
400 }
401 do_execsql_test join9-$id.910 {
402 SELECT *
403 FROM t3 NATURAL FULL JOIN
404 (t4 NATURAL FULL JOIN
405 (t5 NATURAL FULL JOIN t6))
406 ORDER BY 1;
407 } {
408 0 - - - 1000
409 1 - - red -
410 2 two alice orange -
411 3 three - yellow 333
412 4 - bob green 444
413 5 - - blue 555
414 6 six cindy - -
415 7 seven - - -
416 8 - dave - -
417 9 - - - 999
418 }
419 do_execsql_test join9-$id.920 {
420 SELECT *
421 FROM t3 FULL JOIN (
422 t4 FULL JOIN (
423 t5 FULL JOIN t6 USING (id)
424 ) USING(id)
425 ) USING(id)
426 ORDER BY 1;
427 } {
428 0 - - - 1000
429 1 - - red -
430 2 two alice orange -
431 3 three - yellow 333
432 4 - bob green 444
433 5 - - blue 555
434 6 six cindy - -
435 7 seven - - -
436 8 - dave - -
437 9 - - - 999
438 }
439 do_execsql_test join9-$id.920 {
440 SELECT *
441 FROM t3 FULL JOIN (
442 t4 FULL JOIN (
443 t5 FULL JOIN t6 USING (id)
444 ) USING(id)
445 ) USING(id)
446 ORDER BY 1;
447 } {
448 0 - - - 1000
449 1 - - red -
450 2 two alice orange -
451 3 three - yellow 333
452 4 - bob green 444
453 5 - - blue 555
454 6 six cindy - -
455 7 seven - - -
456 8 - dave - -
457 9 - - - 999
458 }
459
460 # Verified by PG-14
461 do_execsql_test join9-$id.930 {
462 SELECT *
463 FROM t3 FULL JOIN (
464 t4 FULL JOIN (
465 t5 FULL JOIN t6 USING(id)
466 ) USING(id)
467 ) AS j1 ON j1.id=t3.id
468 ORDER BY coalesce(t3.id,j1.id);
469 } {
470 - - 0 - - 1000
471 - - 1 - red -
472 2 two 2 alice orange -
473 3 three 3 - yellow 333
474 - - 4 bob green 444
475 - - 5 - blue 555
476 6 six 6 cindy - -
477 7 seven - - - -
478 - - 8 dave - -
479 - - 9 - - 999
480 }
481
482 # Verified by PG-14
483 do_execsql_test join9-$id.940 {
484 SELECT *
485 FROM t3 FULL JOIN (
486 t4 RIGHT JOIN (
487 t5 FULL JOIN t6 USING(id)
488 ) USING(id)
489 ) AS j1 ON j1.id=t3.id
490 ORDER BY coalesce(t3.id,j1.id);
491 } {
492 - - 0 - - 1000
493 - - 1 - red -
494 2 two 2 alice orange -
495 3 three 3 - yellow 333
496 - - 4 bob green 444
497 - - 5 - blue 555
498 6 six - - - -
499 7 seven - - - -
500 - - 9 - - 999
501 }
502
503 # Verified by PG-14
504 do_execsql_test join9-$id.950 {
505 SELECT *
506 FROM t3 FULL JOIN (
507 t4 LEFT JOIN (
508 t5 FULL JOIN t6 USING(id)
509 ) USING(id)
510 ) AS j1 ON j1.id=t3.id
511 ORDER BY coalesce(t3.id,j1.id);
512 } {
513 2 two 2 alice orange -
514 3 three - - - -
515 - - 4 bob green 444
516 6 six 6 cindy - -
517 7 seven - - - -
518 - - 8 dave - -
519 }
520
521 # Restriction (27) in the query flattener
522 # Verified by PG-14
523 do_execsql_test join9-$id.1000 {
524 WITH t56(id,y,z) AS (SELECT * FROM t5 FULL JOIN t6 USING(id) LIMIT 50)
525 SELECT id,x,y,z FROM t4 JOIN t56 USING(id)
526 ORDER BY 1;
527 } {
528 2 alice orange -
529 4 bob green 444
530 }
531
532 # Verified by PG-14
533 do_execsql_test join9-$id.1010 {
534 SELECT id,x,y,z
535 FROM t4 INNER JOIN (t5 FULL JOIN t6 USING(id)) USING(id)
536 ORDER BY 1;
537 } {
538 2 alice orange -
539 4 bob green 444
540 }
541
542 # Verified by PG-14
543 do_execsql_test join9-$id.1020 {
544 SELECT id,x,y,z
545 FROM t4 FULL JOIN t5 USING(id) INNER JOIN t6 USING(id)
546 ORDER BY 1;
547 } {
548 3 - yellow 333
549 4 bob green 444
550 5 - blue 555
551 }
552
553 # Verified by PG-14
554 do_execsql_test join9-$id.1030 {
555 WITH t45(id,x,y) AS (SELECT * FROM t4 FULL JOIN t5 USING(id) LIMIT 50)
556 SELECT id,x,y,z FROM t45 JOIN t6 USING(id)
557 ORDER BY 1;
558 } {
559 3 - yellow 333
560 4 bob green 444
561 5 - blue 555
562 }
563
564 }
565 finish_test