Debian Codegraph @

test/existsexpr.test

raw
1 # 2024 May 25
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 #
12
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 source $testdir/lock_common.tcl
16 set testprefix existsexpr
17
18 do_execsql_test 1.0 {
19 CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
20 INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
21 CREATE INDEX x1b ON x1(b);
22
23 CREATE TABLE x2(x, y);
24 INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
25 }
26
27 do_execsql_test 1.1 {
28 SELECT 1 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=5)
29 } {1}
30
31 do_execsql_test 1.2 {
32 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
33 } {1 2 3 4 5 6}
34
35 # With "a=x", the UNIQUE index means the EXIST can be transformed to a join.
36 # So no "SUBQUERY". With "b=x", the index is not UNIQUE and so there is a
37 # "SUBQUERY".
38 do_execsql_test 1.3.1 {
39 EXPLAIN QUERY PLAN
40 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
41 } {~/SUBQUERY/}
42 do_execsql_test 1.3.2 {
43 EXPLAIN QUERY PLAN
44 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE b=x)
45 } {~/SUBQUERY/}
46
47 do_execsql_test 1.4.1 {
48 EXPLAIN QUERY PLAN
49 SELECT * FROM x2 WHERE x=1 AND EXISTS (SELECT 1 FROM x1 WHERE a=x)
50 } {~/SUBQUERY/}
51 do_execsql_test 1.4.2 {
52 EXPLAIN QUERY PLAN
53 SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y=2
54 } {~/SUBQUERY/}
55
56 do_execsql_test 1.5 {
57 SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
58 } {3}
59
60 #-------------------------------------------------------------------------
61 do_execsql_test 2.0 {
62 CREATE TABLE t1(a, b);
63 WITH s(i) AS (
64 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
65 ) INSERT INTO t1 SELECT i, i FROM s;
66
67 CREATE TABLE t2(c, d);
68 WITH s(i) AS (
69 SELECT 10 UNION ALL SELECT i+10 FROM s WHERE i<1000
70 ) INSERT INTO t2 SELECT i, i FROM s;
71 }
72
73 do_execsql_test 2.1 {
74 SELECT count(*) FROM t1;
75 SELECT count(*) FROM t2;
76 } {1000 100}
77
78 do_execsql_test 2.2 {
79 SELECT count(*) FROM t1, t2 WHERE a=c;
80 } {100}
81
82 do_execsql_test 2.3 {
83 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
84 } {100}
85 do_eqp_test 2.4 {
86 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
87 } {SCAN t1}
88
89 do_execsql_test 2.4.0 {
90 CREATE UNIQUE INDEX t2c ON t2(c);
91 CREATE UNIQUE INDEX t1a ON t1(a);
92 }
93
94 do_eqp_test 2.4.1 {
95 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
96 } {SCAN t1*t2 EXISTS}
97 do_execsql_test 2.4.2 {
98 ANALYZE;
99 }
100 do_eqp_test 2.4.3 {
101 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
102 } {SCAN t1*t2 EXISTS}
103 do_execsql_test 2.4.4 {
104 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
105 } {100}
106
107 do_execsql_test 2.5.1 {
108 EXPLAIN QUERY PLAN
109 SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.rowid=a);
110 } {~/SUBQUERY/}
111
112 #-------------------------------------------------------------------------
113 proc do_subquery_test {tn bSub sql res} {
114 set r1(0) ~/SUBQUERY/
115 set r1(1) /SUBQUERY/
116 do_execsql_test $tn.1 "explain query plan $sql" $r1($bSub)
117 do_execsql_test $tn.2 $sql $res
118 }
119
120 do_execsql_test 3.0 {
121 CREATE TABLE y1(a, b, c);
122 CREATE TABLE y2(x, y, z);
123 CREATE UNIQUE INDEX y2zy ON y2(z, y);
124
125 INSERT INTO y1 VALUES(1, 1, 1);
126 INSERT INTO y1 VALUES(2, 2, 2);
127 INSERT INTO y1 VALUES(3, 3, 3);
128 INSERT INTO y1 VALUES(4, 4, 4);
129
130 INSERT INTO y2 VALUES(1, 1, 1);
131 INSERT INTO y2 VALUES(3, 3, 3);
132 }
133
134 do_subquery_test 3.1 0 {
135 SELECT * FROM y1 WHERE EXISTS (
136 SELECT 1 FROM y2 WHERE z=a AND y=b AND x=z
137 )
138 } {
139 1 1 1 3 3 3
140 }
141
142 do_subquery_test 3.2 0 {
143 SELECT * FROM y1 WHERE EXISTS (
144 SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND x=z
145 )
146 } {
147 1 1 1 3 3 3
148 }
149
150 do_subquery_test 3.3 0 {
151 SELECT * FROM y1 WHERE EXISTS (
152 SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND c!=3
153 )
154 } {
155 1 1 1
156 }
157
158 do_subquery_test 3.4 0 {
159 SELECT * FROM y1 WHERE EXISTS (
160 SELECT 1 FROM y2 WHERE z=max(a,b) AND b=3
161 )
162 } {
163 3 3 3
164 }
165
166 do_subquery_test 3.5 0 {
167 SELECT * FROM y1 WHERE EXISTS (
168 SELECT 1 FROM y2 WHERE z=a-1 AND y=a-1
169 )
170 } {
171 2 2 2
172 4 4 4
173 }
174
175 do_subquery_test 3.6 0 {
176 SELECT * FROM y1 WHERE EXISTS (
177 SELECT 1 FROM y2 WHERE z=a-1 AND y+1=a
178 )
179 } {
180 2 2 2
181 4 4 4
182 }
183
184 do_subquery_test 3.7 1 {
185 SELECT * FROM y1 WHERE EXISTS (
186 SELECT count(*) FROM y2 WHERE z=a-1 AND y=a-1
187 )
188 } {
189 1 1 1
190 2 2 2
191 3 3 3
192 4 4 4
193 }
194
195 do_subquery_test 3.8 0 {
196 SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 )
197 } {
198 1 1 1
199 2 2 2
200 3 3 3
201 4 4 4
202 }
203
204 do_subquery_test 3.9 1 {
205 SELECT * FROM y1 WHERE EXISTS (
206 SELECT 1 FROM y2 one, y2 two WHERE one.z=a-1 AND one.y=a-1
207 )
208 } {
209 2 2 2
210 4 4 4
211 }
212
213 #-------------------------------------------------------------------------
214 reset_db
215 do_execsql_test 4.0 {
216 CREATE TABLE tx1(a TEXT COLLATE nocase, b TEXT);
217 CREATE UNIQUE INDEX tx1ab ON tx1(a, b);
218
219 INSERT INTO tx1 VALUES('a', 'a');
220 INSERT INTO tx1 VALUES('B', 'b');
221 INSERT INTO tx1 VALUES('c', 'c');
222 INSERT INTO tx1 VALUES('D', 'd');
223 INSERT INTO tx1 VALUES('e', 'e');
224
225 CREATE TABLE tx2(x, y);
226 INSERT INTO tx2 VALUES('A', 'a');
227 INSERT INTO tx2 VALUES('b', 'b');
228 INSERT INTO tx2 VALUES('C', 'c');
229 INSERT INTO tx2 VALUES('D', 'd');
230 }
231
232 do_subquery_test 4.1 0 {
233 SELECT * FROM tx2 WHERE EXISTS (
234 SELECT 1 FROM tx1 WHERE a=x AND b=y
235 )
236 } {
237 A a
238 b b
239 C c
240 D d
241 }
242
243 do_subquery_test 4.1.1 0 {
244 SELECT * FROM tx2 WHERE EXISTS (
245 SELECT 1 FROM tx1 WHERE (a COLLATE nocase)=x AND b=y
246 )
247 } {
248 A a b b C c D d
249 }
250 do_subquery_test 4.1.2 0 {
251 SELECT * FROM tx2 WHERE EXISTS (
252 SELECT 1 FROM tx1 WHERE a=x AND (b COLLATE binary)=y
253 )
254 } {
255 A a b b C c D d
256 }
257 do_subquery_test 4.1.1 0 {
258 SELECT * FROM tx2 WHERE EXISTS (
259 SELECT 1 FROM tx1 WHERE x=(a COLLATE nocase) AND b=y
260 )
261 } {
262 A a b b C c D d
263 }
264 do_subquery_test 4.1.2 0 {
265 SELECT * FROM tx2 WHERE EXISTS (
266 SELECT 1 FROM tx1 WHERE a=x AND y=(b COLLATE binary)
267 )
268 } {
269 A a b b C c D d
270 }
271
272 do_subquery_test 4.2 0 {
273 SELECT * FROM tx2 WHERE EXISTS (
274 SELECT 1 FROM tx1 WHERE a=x AND b=y COLLATE nocase
275 )
276 } {
277 A a
278 b b
279 C c
280 D d
281 }
282
283 do_execsql_test 4.3 {
284 DROP INDEX tx1ab;
285 CREATE UNIQUE INDEX tx1ab ON tx1(a COLLATE binary, b);
286 }
287
288 do_subquery_test 4.4 0 {
289 SELECT * FROM tx2 WHERE EXISTS (
290 SELECT 1 FROM tx1 WHERE a=x AND b=y
291 )
292 } {
293 A a
294 b b
295 C c
296 D d
297 }
298
299 do_subquery_test 4.4 0 {
300 SELECT * FROM tx2 WHERE EXISTS (
301 SELECT 1 FROM tx1 WHERE a=x COLLATE binary AND b=y
302 )
303 } {
304 D d
305 }
306
307 do_subquery_test 4.4 1 {
308 SELECT EXISTS ( SELECT x FROM tx1 ) FROM tx2
309 } {
310 1 1 1 1
311 }
312
313 do_subquery_test 4.4 1 {
314 SELECT (SELECT EXISTS ( SELECT x FROM tx1 ) WHERE 1) FROM tx2
315 } {
316 1 1 1 1
317 }
318
319 #-------------------------------------------------------------------------
320 proc cols {s f} {
321 set lCols [list]
322 for {set i $s} {$i<=$f} {incr i} {
323 lappend lCols [format "c%02d" $i]
324 }
325 join $lCols ", "
326 }
327 proc vals {n val} {
328 set lVal [list]
329 for {set i 0} {$i<$n} {incr i} {
330 lappend lVal $val
331 }
332 join $lVal ", "
333 }
334 proc exprs {s f} {
335 set lExpr [list]
336 for {set i $s} {$i<=$f} {incr i} {
337 lappend lExpr [format "c%02d = o" $i]
338 }
339 join $lExpr " AND "
340 }
341
342
343 do_execsql_test 5.0 "
344 CREATE TABLE a1( [cols 0 99] );
345 "
346 do_execsql_test 5.1 "
347 -- 63 column index
348 CREATE UNIQUE INDEX a1idx1 ON a1( [cols 0 62] );
349 "
350 do_execsql_test 5.2 "
351 -- 64 column index
352 CREATE UNIQUE INDEX a1idx2 ON a1( [cols 10 73] );
353 "
354 do_execsql_test 5.2 "
355 -- 65 column index
356 CREATE UNIQUE INDEX a1idx3 ON a1( [cols 20 84] );
357 "
358
359 do_test 5.3 {
360 foreach v {1 2 3 4 5 6} {
361 execsql "INSERT INTO a1 VALUES( [vals 100 $v] )"
362 }
363 } {}
364
365 do_execsql_test 5.4 {
366 CREATE TABLE a2(o);
367 INSERT INTO a2 VALUES(2), (5);
368 }
369
370 do_subquery_test 5.5 0 "
371 SELECT o FROM a2 WHERE EXISTS (
372 SELECT 1 FROM a1 WHERE [exprs 0 62]
373 )
374 " {
375 2 5
376 }
377
378 do_subquery_test 5.6 0 "
379 SELECT o FROM a2 WHERE EXISTS (
380 SELECT 1 FROM a1 WHERE [exprs 10 73]
381 )
382 " {
383 2 5
384 }
385
386 do_subquery_test 5.7 0 "
387 SELECT o FROM a2 WHERE EXISTS (
388 SELECT 1 FROM a1 WHERE [exprs 20 84]
389 )
390 " {
391 2 5
392 }
393
394 #-------------------------------------------------------------------------
395 reset_db
396 do_execsql_test 6.0 {
397 CREATE TABLE t1(a, b UNIQUE, c UNIQUE);
398 CREATE TABLE t2(a INfEGER PRIMARY KEY, b);
399 CREATE UNIQUE INDEX t2b ON t2(b);
400 }
401
402 do_catchsql_test 6.1 {
403 SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c COLLATE f = a)
404 } {1 {no such collation sequence: f}}
405
406 #-------------------------------------------------------------------------
407 reset_db
408 do_execsql_test 7.0 {
409 CREATE TABLE t1(x);
410 CREATE TABLE t2(y UNIQUE);
411
412 INSERT INTO t1 VALUES(1), (2);
413 INSERT INTO t2 VALUES(1), (3);
414
415 SELECT * FROM t1 one LEFT JOIN t1 two ON (one.x=two.x AND EXISTS (
416 SELECT 1 FROM t2 WHERE y=one.x
417 ));
418 } {
419 1 1
420 2 {}
421 }
422
423 # https://sqlite.org/forum/forumpost/2025-07-23T10:59:14z
424 reset_db
425 do_execsql_test 8.0 {
426 CREATE TABLE t0 (c0 INT); INSERT INTO t0(c0) VALUES (1);
427 CREATE TABLE t1(c0 INT); INSERT INTO t1(c0) VALUES (2);
428 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 LIMIT 0);
429 } {}
430
431 #-------------------------------------------------------------------------
432 reset_db
433
434 do_execsql_test 9.0 {
435 CREATE TABLE t1(xx);
436 INSERT INTO t1 VALUES('big string value');
437 } {}
438
439 do_execsql_test 9.1 {
440 PRAGMA automatic_index = off;
441 CREATE TABLE t2(ii);
442 INSERT INTO t2 VALUES(100);
443 INSERT INTO t2 VALUES(200);
444 }
445
446 do_execsql_test 9.2 {
447 CREATE TABLE t3(yy);
448 INSERT INTO t3 VALUES(200);
449 }
450
451 do_execsql_test 9.3 {
452 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii )
453 } {1}
454
455 do_execsql_test 9.4 {
456 SELECT EXISTS (
457 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii )
458 )
459 } {1}
460
461 do_execsql_test 9.5 {
462 SELECT 1234 WHERE EXISTS (
463 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii )
464 )
465 } {1234}
466
467 set Q {
468 SELECT * FROM t1 WHERE
469 EXISTS (
470 SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t3 WHERE yy==t2.ii )
471 )
472 }
473
474 do_execsql_test 9.5 $Q {{big string value}}
475 catch { optimization_control db exists-to-join 0 }
476 db cache flush
477 do_execsql_test 9.6 $Q {{big string value}}
478
479 #-------------------------------------------------------------------------
480 # Forum post 2026-04-21T10:57:09Z
481 do_execsql_test 9.7.1 {
482 DROP TABLE t3;
483 CREATE TABLE t3(x INT, y INT);
484 CREATE TABLE t4(z INT);
485 INSERT INTO t3 VALUES (1,0),(2,2),(3,3);
486 INSERT INTO t4 VALUES (4),(5);
487 }
488 set Q {
489 SELECT x FROM t3
490 WHERE EXISTS(SELECT 1 FROM t4 WHERE z>y)
491 LIMIT 1 OFFSET 1
492 }
493 catch { optimization_control db all 1 }
494 db cache flush
495 do_execsql_test 9.7.2 $Q {2}
496 catch { optimization_control db exists-to-join 0 }
497 db cache flush
498 do_execsql_test 9.7.3 $Q {2}
499 catch { optimization_control db all 1 }
500 db cache flush
501
502 #-------------------------------------------------------------------------
503 reset_db
504
505 do_execsql_test 10.0 {
506 CREATE TABLE t1(a);
507 CREATE TABLE x1(x);
508 }
509
510 do_execsql_test 10.1 {
511 SELECT EXISTS( SELECT 1 FROM t1 ) aaa FROM x1 WHERE aaa AND aaa
512 }
513
514 do_execsql_test 10.2 {
515 SELECT
516 EXISTS( SELECT 1 FROM t1 ) aaa
517 WHERE (
518 SELECT 1 FROM x1 WHERE aaa AND aaa
519 );
520 }
521
522 # https://sqlite.org/forum/forumpost/2026-01-03T14:05:48z
523 do_execsql_test 11.0 {
524 CREATE TABLE parent (id TEXT PRIMARY KEY);
525 CREATE TABLE child_a (id TEXT);
526 CREATE TABLE child_b (id TEXT);
527 INSERT INTO parent VALUES ('p1');
528 INSERT INTO child_a VALUES ('p1');
529 }
530 do_execsql_test 11.1 {
531 SELECT count(*), parent.id FROM parent
532 WHERE EXISTS (
533 SELECT 1 FROM child_a WHERE child_a.id = parent.id
534 UNION
535 SELECT 1 FROM child_b WHERE child_b.id = parent.id
536 )
537 GROUP BY id;
538 } {1 p1}
539
540 #-------------------------------------------------------------------------
541 reset_db
542 do_execsql_test 12.0 {
543 CREATE TABLE t2(id INT, data INT);
544 CREATE TABLE t3(amount INT);
545
546 INSERT INTO t2 VALUES (1,0),(2,0);
547 INSERT INTO t3 VALUES (1),(1);
548 }
549
550 do_execsql_test 12.1 {
551 SELECT * FROM t2
552 WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.amount > t2.data)
553 } {
554 1 0
555 2 0
556 }
557
558 do_execsql_test 12.2 {
559 SELECT * FROM t2
560 WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.amount > t2.data)
561 LIMIT 2 OFFSET 3
562 } {}
563
564 #-------------------------------------------------------------------------
565 reset_db
566
567 do_execsql_test 13.0 {
568 CREATE TABLE t1(b,c);
569 CREATE TABLE t2(x,y);
570 CREATE TABLE t3(p,q);
571
572 INSERT INTO t1 VALUES(7,10);
573 INSERT INTO t2 VALUES(1,1), (4,7);
574 INSERT INTO t3 VALUES(4,7);
575
576 PRAGMA automatic_index = 0;
577 }
578
579 do_execsql_test 13.1 {
580 SELECT * FROM t1 WHERE EXISTS (
581 SELECT 1 FROM t2
582 WHERE EXISTS (
583 SELECT 1 FROM t3 WHERE t3.p=t2.x AND t3.q=t1.b
584 )
585 )
586 } {7 10}
587
588 #-------------------------------------------------------------------------
589 # Forum post https://sqlite.org/forum/forumpost/c8b205720c
590 #
591 reset_db
592 do_execsql_test 14.1.0 {
593 CREATE TABLE t(n INTEGER, parent INTEGER);
594 CREATE INDEX idx ON t(parent);
595 INSERT INTO t VALUES (1, NULL);
596 INSERT INTO t VALUES (10, NULL);
597 }
598
599 do_execsql_test 14.1.1 {
600 SELECT count(*) FROM t b
601 WHERE (b.parent IS NULL OR b.parent = 99)
602 AND 5 <= +b.n
603 } 1
604
605 do_execsql_test 14.1.2 {
606 SELECT 'match' WHERE EXISTS (
607 SELECT 1 FROM t b
608 WHERE (b.parent IS NULL OR b.parent = 99)
609 AND 5 <= +b.n
610 )
611 } {match}
612
613 do_execsql_test 14.2.1 {
614 CREATE TABLE t1(a, b);
615 INSERT INTO t1 VALUES(20, 20);
616
617 CREATE TABLE t2(c, d);
618 CREATE INDEX t2c ON t2(c);
619 CREATE INDEX t2d ON t2(d);
620
621 INSERT INTO t2 VALUES(20, -1);
622 INSERT INTO t2 VALUES(20, 0);
623 }
624
625 do_execsql_test 14.2.2 {
626 SELECT * FROM t1 WHERE EXISTS (
627 SELECT 1 FROM t2 WHERE (c=a OR d=a) AND (d+1)>0
628 )
629 } {20 20}
630
631
632 finish_test