Debian Codegraph @

test/values.test

raw
1 # 2024 March 3
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 values
17
18
19 do_execsql_test 1.0 {
20 CREATE TABLE x1(a, b, c);
21 }
22
23
24 #explain_i {
25 # INSERT INTO x1(a, b, c) VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
26 #}
27 do_execsql_test 1.1.1 {
28 INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
29 }
30 do_execsql_test 1.1.2 {
31 SELECT * FROM x1;
32 } {
33 1 1 1
34 2 2 2
35 3 3 3
36 4 4 4
37 }
38
39 do_execsql_test 1.2.0 {
40 DELETE FROM x1
41 }
42 do_execsql_test 1.2.1 {
43 INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 4, 4, 4;
44 SELECT * FROM x1;
45 } {1 1 1 2 2 2 3 3 3 4 4 4}
46
47 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 4
48
49 do_execsql_test 1.2.2 {
50 DELETE FROM x1;
51 INSERT INTO x1
52 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5)
53 UNION ALL SELECT 6, 6, 6;
54 SELECT * FROM x1;
55 } {1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6}
56
57 do_execsql_test 1.2.3 {
58 DELETE FROM x1;
59 INSERT INTO x1
60 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
61 UNION ALL SELECT 6, 6, 6;
62 SELECT * FROM x1;
63 } {1 1 1 2 2 2 3 3 3 4 4 4 6 6 6}
64
65 do_execsql_test 1.2.4 {
66 DELETE FROM x1;
67 INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 6, 6, 6;
68 SELECT * FROM x1;
69 } {
70 1 1 1
71 2 2 2
72 3 3 3
73 6 6 6
74 }
75
76 set a 4
77 set b 5
78 set c 6
79 do_execsql_test 1.2.5 {
80 DELETE FROM x1;
81 INSERT INTO x1
82 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3),
83 (4, 4, $a), (5, 5, $b), (6, 6, $c)
84 }
85
86 do_execsql_test 1.2.6 {
87 SELECT * FROM x1;
88 } {
89 1 1 1
90 2 2 2
91 3 3 3
92 4 4 4
93 5 5 5
94 6 6 6
95 }
96
97 #-------------------------------------------------------------------------
98 # SQLITE_LIMIT_COMPOUND_SELECT set to 0.
99 #
100 reset_db
101
102 do_execsql_test 2.0 {
103 CREATE TABLE x1(a, b, c);
104 }
105
106 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 3
107
108 do_catchsql_test 2.1.1 {
109 INSERT INTO x1 VALUES
110 (1, 1, 1),
111 (2, 2, 2),
112 (3, 3, 3),
113 (4, 4, 4),
114 (5, 5, 5),
115 (6, 6, 6),
116 (7, 7, 7),
117 (8, 8, 8),
118 (9, 9, 9),
119 (10, 10, 10, 10)
120 } {1 {all VALUES must have the same number of terms}}
121
122 do_catchsql_test 2.1.2 {
123 INSERT INTO x1 VALUES
124 (1, 1, 1),
125 (2, 2, 2, 2),
126 (3, 3, 3),
127 (4, 4, 4),
128 (5, 5, 5),
129 (6, 6, 6),
130 (7, 7, 7),
131 (8, 8, 8),
132 (9, 9, 9),
133 (10, 10, 10)
134 } {1 {all VALUES must have the same number of terms}}
135
136 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0
137
138 do_execsql_test 2.2 {
139 INSERT INTO x1 VALUES
140 (1, 1, 1),
141 (2, 2, 2),
142 (3, 3, 3),
143 (4, 4, 4),
144 (5, 5, 5),
145 (6, 6, 6),
146 (7, 7, 7),
147 (8, 8, 8),
148 (9, 9, 9),
149 (10, 10, 10)
150 } {}
151 do_execsql_test 2.3 {
152 INSERT INTO x1 VALUES
153 (1, 1, 1),
154 (2, 2, 2),
155 (3, 3, 3),
156 (4, 4, 4),
157 (5, 5, 5),
158 (6, 6, 6),
159 (7, 7, 7),
160 (8, 8, 8),
161 (9, 9, 9),
162 (10, 10, 10)
163 UNION ALL
164 SELECT 5, 12, 12
165 ORDER BY 1
166 } {}
167
168 #-------------------------------------------------------------------------
169 reset_db
170
171 do_execsql_test 3.0 {
172 CREATE TABLE y1(x, y);
173 }
174
175 do_execsql_test 3.1.1 {
176 DELETE FROM y1;
177 INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 5);
178 }
179 do_execsql_test 3.1.2 {
180 SELECT * FROM y1;
181 } {1 2 3 4 1 5}
182 do_execsql_test 3.2.1 {
183 DELETE FROM y1;
184 INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 6)
185 , (row_number() OVER (), 7)
186 }
187 do_execsql_test 3.1.2 {
188 SELECT * FROM y1;
189 } {1 2 3 4 1 6 1 7}
190
191 #-------------------------------------------------------------------------
192 reset_db
193
194 do_execsql_test 4.0 {
195 CREATE TABLE x1(a PRIMARY KEY, b) WITHOUT ROWID;
196 }
197
198 foreach {tn iLimit} {1 0 2 3} {
199 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT $iLimit
200
201 do_execsql_test 4.1.1 {
202 DELETE FROM x1;
203 INSERT INTO x1 VALUES
204 (1, 1),
205 (2, (SELECT * FROM (VALUES('a'), ('b'), ('c'), ('d')) ))
206 }
207 do_execsql_test 4.1.2 {
208 SELECT * FROM x1
209 } {1 1 2 a}
210
211 do_execsql_test 4.2.1 {
212 DELETE FROM x1;
213 INSERT INTO x1 VALUES
214 (1, 1),
215 (2, 2),
216 (3, 3),
217 (4, 4),
218 (5, (SELECT * FROM (VALUES('a'), ('b'), ('c'), ('d')) ))
219 }
220 do_execsql_test 4.2.2 {
221 SELECT * FROM x1
222 } {1 1 2 2 3 3 4 4 5 a}
223
224 do_execsql_test 4.3.1 {
225 DELETE FROM x1;
226 INSERT INTO x1 VALUES
227 (1, (SELECT * FROM (VALUES('a'), ('b'), ('c'), ('d'), ('e')) ))
228 }
229 do_execsql_test 4.3.2 {
230 SELECT * FROM x1
231 } {1 a}
232 }
233
234 #------------------------------------------------------------------------
235 reset_db
236
237 do_execsql_test 5.0 {
238 CREATE VIEW v1 AS VALUES(1, 2, 3), (4, 5, 6), (7, 8, 9);
239 }
240 do_execsql_test 5.1 {
241 SELECT * FROM v1
242 } {1 2 3 4 5 6 7 8 9}
243
244 #-------------------------------------------------------------------------
245 reset_db
246 do_execsql_test 6.0 {
247 CREATE TABLE t1(x);
248 INSERT INTO t1 VALUES(1), (2);
249 }
250
251 do_execsql_test 6.1 {
252 SELECT ( VALUES( x ), ( x ) ) FROM t1;
253 } {1 2}
254
255 #-------------------------------------------------------------------------
256 reset_db
257 do_execsql_test 6.0 {
258 CREATE TABLE t1(x);
259 INSERT INTO t1 VALUES('x'), ('y');
260 }
261
262 do_execsql_test 6.1 {
263 SELECT * FROM t1, (VALUES(1), (2))
264 } {x 1 x 2 y 1 y 2}
265
266 do_execsql_test 6.2 {
267 VALUES(CAST(44 AS REAL)),(55);
268 } {44.0 55}
269
270 #------------------------------------------------------------------------
271 do_execsql_test 7.1 {
272 WITH x1(a, b) AS (
273 VALUES(1, 2), ('a', 'b')
274 )
275 SELECT * FROM x1 one, x1 two
276 } {
277 1 2 1 2
278 1 2 a b
279 a b 1 2
280 a b a b
281 }
282
283 #-------------------------------------------------------------------------
284 reset_db
285
286 set VVV {
287 ( VALUES('a', 'b'), ('c', 'd'), (123, NULL) )
288 }
289 set VVV2 {
290 (
291 SELECT 'a' AS column1, 'b' AS column2
292 UNION ALL SELECT 'c', 'd' UNION ALL SELECT 123, NULL
293 )
294 }
295
296 do_execsql_test 8.0 {
297 CREATE TABLE t1(x);
298 INSERT INTO t1 VALUES('d'), (NULL), (123)
299 }
300 foreach {tn q res} {
301 1 "SELECT * FROM t1 LEFT JOIN VVV" {
302 d a b d c d d 123 {}
303 {} a b {} c d {} 123 {}
304 123 a b 123 c d 123 123 {}
305 }
306
307 2 "SELECT * FROM t1 LEFT JOIN VVV ON (column1=x)" {
308 d {} {}
309 {} {} {}
310 123 123 {}
311 }
312
313 3 "SELECT * FROM t1 RIGHT JOIN VVV" {
314 d a b d c d d 123 {}
315 {} a b {} c d {} 123 {}
316 123 a b 123 c d 123 123 {}
317 }
318
319 4 "SELECT * FROM t1 RIGHT JOIN VVV ON (column1=x)" {
320 123 123 {}
321 {} a b
322 {} c d
323 }
324
325 5 "SELECT * FROM t1 FULL OUTER JOIN VVV ON (column1=x)" {
326 d {} {}
327 {} {} {}
328 123 123 {}
329 {} a b
330 {} c d
331 }
332
333 6 "SELECT count(*) FROM VVV" { 3 }
334
335 7 "SELECT (SELECT column1 FROM VVV)" { a }
336
337 8 "SELECT * FROM VVV UNION ALL SELECT * FROM VVV" {
338 a b c d 123 {}
339 a b c d 123 {}
340 }
341
342 9 "SELECT * FROM VVV INTERSECT SELECT * FROM VVV" {
343 123 {} a b c d
344 }
345
346 10 "SELECT * FROM VVV eXCEPT SELECT * FROM VVV" { }
347
348 11 "SELECT * FROM VVV eXCEPT SELECT 'a', 'b'" { 123 {} c d }
349
350 } {
351 set q1 [string map [list VVV $VVV] $q]
352 set q2 [string map [list VVV $VVV2] $q]
353 set q3 "WITH VVV AS $VVV $q"
354
355 do_execsql_test 8.1.$tn.1 $q1 $res
356 do_execsql_test 8.1.$tn.2 $q2 $res
357 do_execsql_test 8.1.$tn.3 $q3 $res
358 }
359
360 #-------------------------------------------------------------------------
361 reset_db
362
363 do_execsql_test 9.1 {
364 VALUES(456), (123), (NULL) UNION ALL SELECT 122 ORDER BY 1
365 } { {} 122 123 456 }
366
367 do_execsql_test 9.2 {
368 VALUES (1, 2), (3, 4), (
369 ( SELECT column1 FROM ( VALUES (5, 6), (7, 8) ) ),
370 ( SELECT max(column2) FROM ( VALUES (5, 1), (7, 6) ) )
371 )
372 } { 1 2 3 4 5 6 }
373
374 do_execsql_test 10.1 {
375 CREATE TABLE a2(a, b, c DEFAULT 'xyz');
376 }
377 do_execsql_test 10.2 {
378 INSERT INTO a2(a) VALUES(3),(4);
379 }
380
381 #-------------------------------------------------------------------------
382 reset_db
383 ifcapable fts3 {
384 do_execsql_test 11.0 {
385 CREATE VIRTUAL TABLE ft USING fts3(x);
386 }
387 do_execsql_test 11.1 {
388 INSERT INTO ft VALUES('one'), ('two');
389 }
390 }
391
392 #-------------------------------------------------------------------------
393 reset_db
394 do_execsql_test 12.0 {
395 CREATE TABLE t1(a, b);
396 }
397 do_execsql_test 12.1 {
398 INSERT INTO t1 SELECT 1, 2 UNION ALL VALUES(3, 4), (5, 6);
399 }
400 do_execsql_test 12.2 {
401 SELECT * FROM t1
402 } {1 2 3 4 5 6}
403
404 #-------------------------------------------------------------------------
405 reset_db
406 do_execsql_test 13.0 {
407 CREATE TABLE t1(x);
408 INSERT INTO t1 VALUES('xyz');
409
410 SELECT (
411 VALUES( (max(substr('abc', 1, 1), x)) ),
412 (123),
413 (456)
414 )
415 FROM t1;
416 } {xyz}
417
418 do_catchsql_test 13.1 {
419 VALUES(300), (zeroblob(300) OVER win);
420 } {1 {zeroblob() may not be used as a window function}}
421
422 #--------------------------------------------------------------------------
423 reset_db
424 do_execsql_test 14.1 {
425 PRAGMA encoding = utf16;
426 CREATE TABLE t1(a, b);
427 } {}
428
429 db close
430 sqlite3 db test.db
431
432 do_execsql_test 14.2 {
433 INSERT INTO t1 VALUES
434 (17, 'craft'),
435 (16, 'urtlek' IN(1,2,3));
436 }
437
438 #--------------------------------------------------------------------------
439 #
440 reset_db
441 do_eqp_test 15.1 {
442 VALUES(1),(2),(3),(4),(5);
443 } {
444 QUERY PLAN
445 `--SCAN 5-ROW VALUES CLAUSE
446 }
447 do_execsql_test 15.2 {
448 CREATE TABLE t1(a,b);
449 }
450 do_eqp_test 15.3 {
451 INSERT INTO t1 VALUES
452 (1,2),(3,4),(7,8);
453 } {
454 QUERY PLAN
455 `--SCAN 3-ROW VALUES CLAUSE
456 }
457 do_eqp_test 15.4 {
458 INSERT INTO t1 VALUES
459 (1,2),(3,4),(7,8),
460 (5,row_number()OVER());
461 } {
462 QUERY PLAN
463 `--COMPOUND QUERY
464 |--LEFT-MOST SUBQUERY
465 | `--SCAN 3-ROW VALUES CLAUSE
466 `--UNION ALL
467 |--CO-ROUTINE (subquery-xxxxxx)
468 | `--SCAN CONSTANT ROW
469 `--SCAN (subquery-xxxxxx)
470 }
471 do_eqp_test 15.5 {
472 SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6)), (VALUES('a'),('b'),('c'));
473 } {
474 QUERY PLAN
475 |--SCAN 6-ROW VALUES CLAUSE
476 `--SCAN 3-ROW VALUES CLAUSE
477 }
478 do_execsql_test 15.6 {
479 CREATE TABLE t2(x,y);
480 }
481 do_eqp_test 15.7 {
482 SELECT * FROM t2 UNION ALL VALUES(1,2),(3,4),(5,6),(7,8);
483 } {
484 QUERY PLAN
485 `--COMPOUND QUERY
486 |--LEFT-MOST SUBQUERY
487 | `--SCAN t2
488 `--UNION ALL
489 `--SCAN 4-ROW VALUES CLAUSE
490 }
491
492 #--------------------------------------------------------------------------
493 # The VALUES-as-coroutine optimization can be applied to later rows of
494 # a VALUES clause even if earlier rows do not qualify.
495 #
496 reset_db
497 do_execsql_test 16.1 {
498 CREATE TABLE t1(a,b);
499 }
500 do_execsql_test 16.2 {
501 BEGIN;
502 INSERT INTO t1 VALUES(1,2),(3,4),(5,6),
503 (7,row_number()OVER()),
504 (9,10), (11,12), (13,14), (15,16);
505 SELECT * FROM t1 ORDER BY a, b;
506 ROLLBACK;
507 } {1 2 3 4 5 6 7 1 9 10 11 12 13 14 15 16}
508 do_eqp_test 16.3 {
509 INSERT INTO t1 VALUES(1,2),(3,4),(5,6),
510 (7,row_number()OVER()),
511 (9,10), (11,12), (13,14), (15,16);
512 } {
513 QUERY PLAN
514 `--COMPOUND QUERY
515 |--LEFT-MOST SUBQUERY
516 | `--SCAN 3-ROW VALUES CLAUSE
517 |--UNION ALL
518 | |--CO-ROUTINE (subquery-xxxxxx)
519 | | `--SCAN CONSTANT ROW
520 | `--SCAN (subquery-xxxxxx)
521 `--UNION ALL
522 `--SCAN 4-ROW VALUES CLAUSE
523 }
524 do_execsql_test 16.4 {
525 BEGIN;
526 INSERT INTO t1 VALUES
527 (1,row_number()OVER()),
528 (2,3), (4,5), (6,7);
529 SELECT * FROM t1 ORDER BY a, b;
530 ROLLBACK;
531 } {1 1 2 3 4 5 6 7}
532 do_eqp_test 16.5 {
533 INSERT INTO t1 VALUES
534 (1,row_number()OVER()),
535 (2,3), (4,5), (6,7);
536 } {
537 QUERY PLAN
538 `--COMPOUND QUERY
539 |--LEFT-MOST SUBQUERY
540 | |--CO-ROUTINE (subquery-xxxxxx)
541 | | `--SCAN CONSTANT ROW
542 | `--SCAN (subquery-xxxxxx)
543 `--UNION ALL
544 `--SCAN 3-ROW VALUES CLAUSE
545 }
546 do_execsql_test 16.6 {
547 BEGIN;
548 INSERT INTO t1 VALUES
549 (1,2),(3,4),
550 (5,row_number()OVER()),
551 (7,8),(9,10),(11,12),
552 (13,row_number()OVER()),
553 (15,16),(17,18),(19,20),(21,22);
554 SELECT * FROM t1 ORDER BY a, b;
555 ROLLBACK;
556 } { 1 2 3 4 5 1 7 8 9 10 11 12 13 1 15 16 17 18 19 20 21 22}
557 do_eqp_test 16.7 {
558 INSERT INTO t1 VALUES
559 (1,2),(3,4),
560 (5,row_number()OVER()),
561 (7,8),(9,10),(11,12),
562 (13,row_number()OVER()),
563 (15,16),(17,18),(19,20),(21,22);
564 } {
565 QUERY PLAN
566 `--COMPOUND QUERY
567 |--LEFT-MOST SUBQUERY
568 | `--SCAN 2-ROW VALUES CLAUSE
569 |--UNION ALL
570 | |--CO-ROUTINE (subquery-xxxxxx)
571 | | `--SCAN CONSTANT ROW
572 | `--SCAN (subquery-xxxxxx)
573 |--UNION ALL
574 | `--SCAN 3-ROW VALUES CLAUSE
575 |--UNION ALL
576 | |--CO-ROUTINE (subquery-xxxxxx)
577 | | `--SCAN CONSTANT ROW
578 | `--SCAN (subquery-xxxxxx)
579 `--UNION ALL
580 `--SCAN 4-ROW VALUES CLAUSE
581 }
582
583 #--------------------------------------------------------------------------
584 # 2024-03-23 dbsqlfuzz crash-c2c5e7e08b7e489d270a26d895077a03f678c33b
585 #
586 do_execsql_test 17.1 {
587 DROP TABLE IF EXISTS t1;
588 CREATE TABLE t1 AS SELECT * FROM (VALUES(1,2), (3,4 IN (1,2,3)));
589 }
590
591 do_execsql_test 17.2 {
592 SELECT * FROM t1
593 } {1 2 3 0}
594
595 # 2024-03-25 dbsqlfuzz crash-74cf7c9904360322a6c917e4934b127543d1cd51
596 #
597 do_catchsql_test 18.1 {
598 DROP TABLE t1;
599 CREATE TABLE t1(x INTEGER PRIMARY KEY);
600 INSERT INTO t1 VALUES(RAISE(IGNORE)),(0);
601 } {1 {RAISE() may only be used within a trigger-program}}
602 do_catchsql_test 18.2 {
603 DROP TABLE t1;
604 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
605 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
606 INSERT INTO t1(y) VALUES(RAISE(IGNORE)),(0);
607 END;
608 INSERT INTO t1 VALUES(1,2,3);
609 SELECT * FROM t1;
610 } {0 {1 2 3}}
611 do_catchsql_test 18.3.1 {
612 DROP TABLE t1;
613 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
614 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
615 INSERT INTO t1(y) VALUES(RAISE(ABORT,'error 18.3')),(0);
616 END;
617 INSERT INTO t1 VALUES(1,2,3);
618 } {1 {error 18.3}}
619 do_execsql_test 18.3.2 {
620 SELECT * FROM t1;
621 } {}
622 do_catchsql_test 18.4.1 {
623 DROP TABLE t1;
624 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
625 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
626 INSERT INTO t1(y) VALUES(1),(RAISE(ABORT,'error 18.4')),(0);
627 END;
628 INSERT INTO t1 VALUES(1,2,3);
629 } {1 {error 18.4}}
630 do_execsql_test 18.4.2 {
631 SELECT * FROM t1;
632 } {}
633 do_catchsql_test 18.5.1 {
634 DROP TABLE t1;
635 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
636 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
637 INSERT INTO t1(y) VALUES(1),
638 (CASE WHEN new.z>7 THEN RAISE(ABORT,'error 18.5') ELSE 2 END);
639 END;
640 INSERT INTO t1 VALUES(1,2,3);
641 SELECT * FROM t1;
642 } {0 {1 2 3 2 1 {} 3 2 {}}}
643 do_catchsql_test 18.5.2 {
644 DELETE FROM t1;
645 INSERT INTO t1 VALUES(1,2,13);
646 } {1 {error 18.5}}
647 do_catchsql_test 18.5.3 {
648 SELECT * FROM t1;
649 } {0 {}}
650
651 # 2024-04-18 dbsqlfuzz crash-bde3bf80aedf25afa56e2997a0545a314765d3f8
652 # Verify that the VALUES expressions used as an argument to an outer
653 # join work correctly.
654 #
655 reset_db
656 db null NULL
657 do_execsql_test 19.1 {
658 CREATE TABLE t1(a INT, b INT);
659 INSERT INTO t1 VALUES(11,22);
660 SELECT * FROM t1 LEFT JOIN (VALUES(33,44),(55,66)) AS t2 ON a=b;
661 } {11 22 NULL NULL}
662 do_execsql_test 19.2 {
663 SELECT * FROM (VALUES(33,44),(55,66)) AS t2 RIGHT JOIN t1 ON a=b;
664 } {NULL NULL 11 22}
665 do_execsql_test 19.3 {
666 SELECT *, '|' FROM t1 FULL JOIN (VALUES(33,44),(55,66)) AS t2 ON a=b
667 ORDER BY +column1
668 } {11 22 NULL NULL | NULL NULL 33 44 | NULL NULL 55 66 |}
669 do_execsql_test 19.4 {
670 SELECT *, '|' FROM (VALUES(33,44),(55,66)) AS t2 FULL JOIN t1 ON a=b
671 ORDER BY +column1
672 } {NULL NULL 11 22 | 33 44 NULL NULL | 55 66 NULL NULL |}
673
674 # 2024-04-21 dbsqlfuzz 6fd1ff3a64bef4a6c092e8d757548e95698b0df5
675 # A continuation of the 2024-04-18 problem above. We have to create
676 # Pseudo-cursor that is always NULL on the viaCoroutine loop in case
677 # there are OP_Columns generated against it by the sub-WHERE clause.
678 #
679 db null N
680 do_execsql_test 19.5 {
681 DROP TABLE IF EXISTS t1;
682 DROP TABLE IF EXISTS t2;
683 DROP TABLE IF EXISTS t3;
684 CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2);
685 CREATE TABLE t2(column1,column2); INSERT INTO t2 VALUES(11,22),(33,44);
686 CREATE TABLE t3(d,e); INSERT INTO t3 VALUES(3,4);
687 }
688 do_execsql_test 19.6 {
689 -- output verify using PG 14.2
690 SELECT *
691 FROM t1 CROSS JOIN t2 FULL JOIN t3 ON a=d
692 ORDER BY +d, +column1;
693 } {1 2 11 22 N N
694 1 2 33 44 N N
695 N N N N 3 4}
696 do_execsql_test 19.7 {
697 SELECT *
698 FROM t1 CROSS JOIN (VALUES(11,22),(33,44)) FULL JOIN t3 ON a=d
699 ORDER BY +d, +column1;
700 } {1 2 11 22 N N
701 1 2 33 44 N N
702 N N N N 3 4}
703 do_execsql_test 19.8 {
704 -- output verified using PG 14.2
705 SELECT *
706 FROM t1 CROSS JOIN t2 FULL JOIN t3 ON a=d
707 WHERE column1 IS NULL;
708 } {N N N N 3 4}
709 do_execsql_test 19.9 {
710 SELECT *
711 FROM t1 CROSS JOIN (VALUES(11,22),(33,44)) FULL JOIN t3 ON a=d
712 WHERE column1 IS NULL;
713 } {N N N N 3 4}
714
715 finish_test