Debian Codegraph @

test/rowvalue.test

raw
1 # 2016 June 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. The
12 # focus of this file is testing the SELECT statement.
13 #
14
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set ::testprefix rowvalue
19
20 do_execsql_test 0.0 {
21 CREATE TABLE one(o);
22 INSERT INTO one VALUES(1);
23 }
24
25 foreach {tn v1 v2 eq ne is isnot} {
26 1 "1, 2, 3" "1, 2, 3" 1 0 1 0
27 2 "1, 0, 3" "1, 2, 3" 0 1 0 1
28 3 "1, 2, NULL" "1, 2, 3" {} {} 0 1
29 4 "1, 2, NULL" "1, 2, NULL" {} {} 1 0
30 5 "NULL, NULL, NULL" "NULL, NULL, NULL" {} {} 1 0
31
32 6 "1, NULL, 1" "1, 1, 1" {} {} 0 1
33 7 "1, NULL, 1" "1, 1, 2" 0 1 0 1
34 } {
35 do_execsql_test 1.$tn.eq "SELECT ($v1) == ($v2)" [list $eq]
36 do_execsql_test 1.$tn.ne "SELECT ($v1) != ($v2)" [list $ne]
37
38 do_execsql_test 1.$tn.is "SELECT ($v1) IS ($v2)" [list $is]
39 do_execsql_test 1.$tn.isnot "SELECT ($v1) IS NOT ($v2)" [list $isnot]
40
41 do_execsql_test 1.$tn.2.eq "SELECT (SELECT $v1) == (SELECT $v2)" [list $eq]
42 do_execsql_test 1.$tn.2.ne "SELECT (SELECT $v1) != (SELECT $v2)" [list $ne]
43 }
44
45 foreach {tn v1 v2 lt gt le ge} {
46 1 "(1, 1, 3)" "(1, 2, 3)" 1 0 1 0
47 2 "(1, 2, 3)" "(1, 2, 3)" 0 0 1 1
48 3 "(1, 3, 3)" "(1, 2, 3)" 0 1 0 1
49
50 4 "(1, NULL, 3)" "(1, 2, 3)" {} {} {} {}
51 5 "(1, 3, 3)" "(1, NULL, 3)" {} {} {} {}
52 6 "(1, NULL, 3)" "(1, NULL, 3)" {} {} {} {}
53 } {
54 foreach {tn2 expr res} [list \
55 2.$tn.lt "$v1 < $v2" $lt \
56 2.$tn.gt "$v1 > $v2" $gt \
57 2.$tn.le "$v1 <= $v2" $le \
58 2.$tn.ge "$v1 >= $v2" $ge \
59 ] {
60 do_execsql_test $tn2 "SELECT $expr" [list $res]
61
62 set map(0) [list]
63 set map() [list]
64 set map(1) [list 1]
65 do_execsql_test $tn2.where1 "SELECT * FROM one WHERE $expr" $map($res)
66
67 set map(0) [list 1]
68 set map() [list]
69 set map(1) [list]
70 do_execsql_test $tn2.where2 "SELECT * FROM one WHERE NOT $expr" $map($res)
71 }
72 }
73
74 do_execsql_test 3.0 {
75 CREATE TABLE t1(x, y);
76 INSERT INTO t1 VALUES(1, 1);
77 INSERT INTO t1 VALUES(1, 2);
78 INSERT INTO t1 VALUES(2, 3);
79 INSERT INTO t1 VALUES(2, 4);
80 INSERT INTO t1 VALUES(3, 5);
81 INSERT INTO t1 VALUES(3, 6);
82 }
83
84 foreach {tn r order} {
85 1 "(1, 1)" "ORDER BY y"
86 2 "(1, 1)" "ORDER BY x, y"
87 3 "(1, 2)" "ORDER BY x, y DESC"
88 4 "(3, 6)" "ORDER BY x DESC, y DESC"
89 5 "((3, 5))" "ORDER BY x DESC, y"
90 6 "(SELECT 3, 5)" "ORDER BY x DESC, y"
91 } {
92 do_execsql_test 3.$tn.1 "SELECT $r == (SELECT x,y FROM t1 $order)" 1
93 do_execsql_test 3.$tn.2 "SELECT $r == (SELECT * FROM t1 $order)" 1
94
95 do_execsql_test 3.$tn.3 "
96 SELECT (SELECT * FROM t1 $order) == (SELECT * FROM t1 $order)
97 " 1
98 do_execsql_test 3.$tn.4 "
99 SELECT (SELECT 0, 0) == (SELECT * FROM t1 $order)
100 " 0
101 }
102
103 foreach {tn expr res} {
104 1 {(2, 2) BETWEEN (2, 2) AND (3, 3)} 1
105 2 {(2, 2) BETWEEN (2, NULL) AND (3, 3)} {}
106 3 {(2, 2) BETWEEN (3, NULL) AND (3, 3)} 0
107 } {
108 do_execsql_test 4.$tn "SELECT $expr" [list $res]
109 }
110
111 foreach {tn expr res} {
112 1 {(2, 4) IN (SELECT * FROM t1)} 1
113 2 {(3, 4) IN (SELECT * FROM t1)} 0
114
115 3 {(NULL, 4) IN (SELECT * FROM t1)} {}
116 4 {(NULL, 0) IN (SELECT * FROM t1)} 0
117
118 5 {(NULL, 4) NOT IN (SELECT * FROM t1)} {}
119 6 {(NULL, 0) NOT IN (SELECT * FROM t1)} 1
120 } {
121 do_execsql_test 5.$tn "SELECT $expr" [list $res]
122 }
123
124 do_execsql_test 6.0 {
125 CREATE TABLE hh(a, b, c);
126 INSERT INTO hh VALUES('abc', 1, 'i');
127 INSERT INTO hh VALUES('ABC', 1, 'ii');
128 INSERT INTO hh VALUES('def', 2, 'iii');
129 INSERT INTO hh VALUES('DEF', 2, 'iv');
130 INSERT INTO hh VALUES('GHI', 3, 'v');
131 INSERT INTO hh VALUES('ghi', 3, 'vi');
132
133 CREATE INDEX hh_ab ON hh(a, b);
134 }
135
136 do_execsql_test 6.1 {
137 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1);
138 } {i}
139 do_execsql_test 6.2 {
140 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc' COLLATE nocase, 1);
141 } {i}
142 do_execsql_test 6.3 {
143 SELECT c FROM hh WHERE a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1);
144 } {i}
145 do_execsql_test 6.4 {
146 SELECT c FROM hh WHERE +a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1);
147 } {i}
148 do_execsql_test 6.5 {
149 SELECT c FROM hh WHERE a = (SELECT 'abc') COLLATE nocase AND b = (SELECT 1);
150 } {i ii}
151 do_catchsql_test 6.6 {
152 SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1) COLLATE nocase;
153 } {1 {row value misused}}
154 do_catchsql_test 6.7 {
155 SELECT c FROM hh WHERE (a, b) = 1;
156 } {1 {row value misused}}
157 do_execsql_test 6.8 {
158 SELECT c FROM hh WHERE (a COLLATE nocase, b) = (SELECT 'def', 2);
159 } {iii iv}
160 do_execsql_test 6.9 {
161 SELECT c FROM hh WHERE (a COLLATE nocase, b) IS NOT (SELECT 'def', 2);
162 } {i ii v vi}
163 do_execsql_test 6.10 {
164 SELECT c FROM hh WHERE (b, a) = (SELECT 2, 'def');
165 } {iii}
166
167 do_execsql_test 7.0 {
168 CREATE TABLE xy(i INTEGER PRIMARY KEY, j, k);
169 INSERT INTO xy VALUES(1, 1, 1);
170 INSERT INTO xy VALUES(2, 2, 2);
171 INSERT INTO xy VALUES(3, 3, 3);
172 INSERT INTO xy VALUES(4, 4, 4);
173 }
174
175
176 foreach {tn sql res eqp} {
177 1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2}
178 "SEARCH xy USING INTEGER PRIMARY KEY (rowid=?)"
179
180 2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2}
181 "SCAN xy"
182
183 3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2}
184 "SEARCH xy USING INTEGER PRIMARY KEY (rowid<?)"
185
186 4 "SELECT * FROM xy WHERE (i, j) > (2, 1)" {2 2 2 3 3 3 4 4 4}
187 "SEARCH xy USING INTEGER PRIMARY KEY (rowid>?)"
188
189 5 "SELECT * FROM xy WHERE (i, j) > ('2', 1)" {2 2 2 3 3 3 4 4 4}
190 "SEARCH xy USING INTEGER PRIMARY KEY (rowid>?)"
191
192 } {
193 do_eqp_test 7.$tn.1 $sql $eqp
194 do_execsql_test 7.$tn.2 $sql $res
195 }
196
197 do_execsql_test 8.0 {
198 CREATE TABLE j1(a);
199 }
200 do_execsql_test 8.1 {
201 SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?)
202 }
203
204 do_execsql_test 9.0 {
205 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
206 INSERT INTO t2 VALUES(1, 1, 1);
207 INSERT INTO t2 VALUES(2, 2, 2);
208 INSERT INTO t2 VALUES(3, 3, 3);
209 INSERT INTO t2 VALUES(4, 4, 4);
210 INSERT INTO t2 VALUES(5, 5, 5);
211 }
212
213 foreach {tn q res} {
214 1 "(a, b) > (2, 1)" {2 3 4 5}
215 2 "(a, b) > (2, 2)" {3 4 5}
216 3 "(a, b) < (4, 5)" {1 2 3 4}
217 4 "(a, b) < (4, 3)" {1 2 3}
218 } {
219 do_execsql_test 9.$tn "SELECT c FROM t2 WHERE $q" $res
220 }
221
222 do_execsql_test 10.0 {
223 CREATE TABLE dual(dummy); INSERT INTO dual(dummy) VALUES('X');
224 CREATE TABLE t3(a TEXT,b TEXT,c TEXT,d TEXT,e TEXT,f TEXT);
225 CREATE INDEX t3x ON t3(b,c,d,e,f);
226
227 SELECT a FROM t3
228 WHERE (c,d) IN (SELECT 'c','d' FROM dual)
229 AND (a,b,e) IN (SELECT 'a','b','d' FROM dual);
230 }
231
232 do_catchsql_test 11.1 {
233 CREATE TABLE t11(a);
234 SELECT * FROM t11 WHERE (a,a)<=1;
235 } {1 {row value misused}}
236 do_catchsql_test 11.2 {
237 SELECT * FROM t11 WHERE (a,a)<1;
238 } {1 {row value misused}}
239 do_catchsql_test 11.3 {
240 SELECT * FROM t11 WHERE (a,a)>=1;
241 } {1 {row value misused}}
242 do_catchsql_test 11.4 {
243 SELECT * FROM t11 WHERE (a,a)>1;
244 } {1 {row value misused}}
245 do_catchsql_test 11.5 {
246 SELECT * FROM t11 WHERE (a,a)==1;
247 } {1 {row value misused}}
248 do_catchsql_test 11.6 {
249 SELECT * FROM t11 WHERE (a,a)<>1;
250 } {1 {row value misused}}
251 do_catchsql_test 11.7 {
252 SELECT * FROM t11 WHERE (a,a) IS 1;
253 } {1 {row value misused}}
254 do_catchsql_test 11.8 {
255 SELECT * FROM t11 WHERE (a,a) IS NOT 1;
256 } {1 {row value misused}}
257
258 # 2016-10-27: https://sqlite.org/src/tktview/fef4bb4bd9185ec8f
259 # Incorrect result from a LEFT JOIN with a row-value constraint
260 #
261 do_execsql_test 12.1 {
262 DROP TABLE IF EXISTS t1;
263 CREATE TABLE t1(a INT,b INT); INSERT INTO t1 VALUES(1,2);
264 DROP TABLE IF EXISTS t2;
265 CREATE TABLE t2(x INT,y INT); INSERT INTO t2 VALUES(3,4);
266 SELECT *,'x' FROM t1 LEFT JOIN t2 ON (a,b)=(x,y);
267 } {1 2 {} {} x}
268 db null -
269 do_execsql_test 12.2 {
270 SELECT t1.*, t2.* FROM t2 RIGHT JOIN t1 ON (a,b)=(x,y);
271 } {1 2 - -}
272 do_execsql_test 12.3 {
273 SELECT t1.*, t2.* FROM t1 FULL JOIN t2 ON (a,b)=(x,y)
274 ORDER BY coalesce(a,x);
275 } {
276 1 2 - -
277 - - 3 4
278 }
279 db null {}
280
281
282 foreach {tn sql} {
283 0 "SELECT (1,2) AS x WHERE x=3"
284 1 "SELECT (1,2) BETWEEN 1 AND 2"
285 2 "SELECT 1 BETWEEN (1,2) AND 2"
286 3 "SELECT 2 BETWEEN 1 AND (1,2)"
287 4 "SELECT (1,2) FROM (SELECT 1) ORDER BY 1"
288 5 "SELECT (1,2) FROM (SELECT 1) GROUP BY 1"
289 } {
290 do_catchsql_test 13.$tn $sql {1 {row value misused}}
291 }
292
293 do_execsql_test 14.0 {
294 CREATE TABLE t12(x);
295 INSERT INTO t12 VALUES(2), (4);
296 }
297 do_execsql_test 14.1 "SELECT 1 WHERE (2,2) BETWEEN (1,1) AND (3,3)" 1
298 do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
299 do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
300 do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1
301 do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1
302 do_execsql_test 14.6 {
303 SELECT 1 FROM t12 WHERE (1,x) BETWEEN (1,1) AND (3,3)
304 } {1 1}
305
306 #-------------------------------------------------------------------------
307 # Test that errors are not concealed by the SELECT flattening or
308 # WHERE-clause push-down optimizations.
309 do_execsql_test 14.1 {
310 CREATE TABLE x1(a PRIMARY KEY, b);
311 CREATE TABLE x2(a INTEGER PRIMARY KEY, b);
312 }
313
314 foreach {tn n sql} {
315 1 0 "SELECT * FROM (SELECT (1, 1) AS c FROM x1) WHERE c=1"
316 2 2 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9) AS y) WHERE y<1"
317 3 3 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9,10) AS y) WHERE y<1"
318 4 0 "SELECT * FROM (SELECT (a, b) AS c FROM x1), x2 WHERE c=a"
319 5 0 "SELECT * FROM (SELECT a AS c, (1, 2, 3) FROM x1), x2 WHERE c=a"
320 6 0 "SELECT * FROM (SELECT 1 AS c, (1, 2, 3) FROM x1) WHERE c=1"
321 } {
322 if {$n==0} {
323 set err "row value misused"
324 } else {
325 set err "sub-select returns $n columns - expected 1"
326 }
327 do_catchsql_test 14.2.$tn $sql [list 1 $err]
328 }
329
330 #--------------------------------------------------------------------------
331 # Test for vector size mismatches concealed by unexpanded subqueries.
332 #
333 do_catchsql_test 15.1 {
334 DETACH (SELECT * FROM (SELECT 1,2))<3;
335 } {1 {row value misused}}
336 do_catchsql_test 15.2 {
337 UPDATE x1 SET a=(SELECT * FROM (SELECT b,2))<3;
338 } {1 {row value misused}}
339 do_catchsql_test 15.3 {
340 UPDATE x1 SET a=NULL WHERE a<(SELECT * FROM (SELECT b,2));
341 } {1 {sub-select returns 2 columns - expected 1}}
342 do_catchsql_test 15.4 {
343 DELETE FROM x1 WHERE a<(SELECT * FROM (SELECT b,2));
344 } {1 {sub-select returns 2 columns - expected 1}}
345 do_catchsql_test 15.5 {
346 INSERT INTO x1(a,b) VALUES(1,(SELECT * FROM (SELECT 1,2))<3);
347 } {1 {row value misused}}
348
349 #-------------------------------------------------------------------------
350 # Row-values used in UPDATE statements within TRIGGERs
351 #
352 # Ticket https://sqlite.org/src/info/8c9458e703666e1a
353 #
354 do_execsql_test 16.1 {
355 CREATE TABLE t16a(a,b,c);
356 INSERT INTO t16a VALUES(1,2,3);
357 CREATE TABLE t16b(x);
358 INSERT INTO t16b(x) VALUES(1);
359 CREATE TRIGGER t16r AFTER UPDATE ON t16b BEGIN
360 UPDATE t16a SET (a,b,c)=(SELECT new.x,new.x+1,new.x+2);
361 END;
362 UPDATE t16b SET x=7;
363 SELECT * FROM t16a;
364 } {7 8 9}
365 do_execsql_test 16.2 {
366 UPDATE t16b SET x=97;
367 SELECT * FROM t16a;
368 } {97 98 99}
369
370 do_execsql_test 16.3 {
371 CREATE TABLE t16c(a, b, c, d, e);
372 INSERT INTO t16c VALUES(1, 'a', 'b', 'c', 'd');
373 CREATE TRIGGER t16c1 AFTER INSERT ON t16c BEGIN
374 UPDATE t16c SET (c, d) = (SELECT 'A', 'B'), (e, b) = (SELECT 'C', 'D')
375 WHERE a = new.a-1;
376 END;
377
378 SELECT * FROM t16c;
379 } {1 a b c d}
380
381 do_execsql_test 16.4 {
382 INSERT INTO t16c VALUES(2, 'w', 'x', 'y', 'z');
383 SELECT * FROM t16c;
384 } {
385 1 D A B C
386 2 w x y z
387 }
388
389 do_execsql_test 16.5 {
390 DROP TRIGGER t16c1;
391 PRAGMA recursive_triggers = 1;
392 INSERT INTO t16c VALUES(3, 'i', 'ii', 'iii', 'iv');
393 CREATE TRIGGER t16c1 AFTER UPDATE ON t16c WHEN new.a>1 BEGIN
394 UPDATE t16c SET (e, d) = (
395 SELECT b, c FROM t16c WHERE a = new.a-1
396 ), (c, b) = (
397 SELECT d, e FROM t16c WHERE a = new.a-1
398 ) WHERE a = new.a-1;
399 END;
400
401 UPDATE t16c SET a=a WHERE a=3;
402 SELECT * FROM t16c;
403 } {
404 1 C B A D
405 2 z y x w
406 3 i ii iii iv
407 }
408
409 do_execsql_test 17.0 {
410 CREATE TABLE b1(a, b);
411 CREATE TABLE b2(x);
412 }
413
414 do_execsql_test 17.1 {
415 SELECT * FROM b2 CROSS JOIN b1
416 WHERE b2.x=b1.a AND (b1.a, 2)
417 IN (VALUES(1, 2));
418 } {}
419
420 do_execsql_test 18.0 {
421 CREATE TABLE b3 ( a, b, PRIMARY KEY (a, b) );
422 CREATE TABLE b4 ( a );
423 CREATE TABLE b5 ( a, b );
424 INSERT INTO b3 VALUES (1, 1), (1, 2);
425 INSERT INTO b4 VALUES (1);
426 INSERT INTO b5 VALUES (1, 1), (1, 2);
427 }
428
429 do_execsql_test 18.1 {
430 SELECT * FROM b3 WHERE (SELECT b3.a, b3.b) IN ( SELECT a, b FROM b5 )
431 } {1 1 1 2}
432 do_execsql_test 18.2 {
433 SELECT * FROM b3 WHERE (VALUES(b3.a, b3.b)) IN ( SELECT a, b FROM b5 );
434 } {1 1 1 2}
435 do_execsql_test 18.3 {
436 SELECT * FROM b3 WHERE (b3.a, b3.b) IN ( SELECT a, b FROM b5 );
437 } {1 1 1 2}
438 do_execsql_test 18.4 {
439 SELECT * FROM b3 JOIN b4 ON b4.a = b3.a
440 WHERE (SELECT b3.a, b3.b) IN ( SELECT a, b FROM b5 );
441 } {1 1 1 1 2 1}
442 do_execsql_test 18.5 {
443 SELECT * FROM b3 JOIN b4 ON b4.a = b3.a
444 WHERE (VALUES(b3.a, b3.b)) IN ( SELECT a, b FROM b5 );
445 } {1 1 1 1 2 1}
446 do_execsql_test 18.6 {
447 SELECT * FROM b3 JOIN b4 ON b4.a = b3.a
448 WHERE (b3.a, b3.b) IN ( SELECT a, b FROM b5 );
449 } {1 1 1 1 2 1}
450
451
452 # 2018-02-13 Ticket https://sqlite.org/src/tktview/f484b65f3d6230593c3
453 # Incorrect result from a row-value comparison in the WHERE clause.
454 #
455 do_execsql_test 19.1 {
456 DROP TABLE IF EXISTS t1;
457 CREATE TABLE t1(a INTEGER PRIMARY KEY,b);
458 INSERT INTO t1(a,b) VALUES(1,11),(2,22),(3,33),(4,44);
459 SELECT * FROM t1 WHERE (a,b)>(0,0) ORDER BY a;
460 } {1 11 2 22 3 33 4 44}
461 do_execsql_test 19.2 {
462 SELECT * FROM t1 WHERE (a,b)>=(0,0) ORDER BY a;
463 } {1 11 2 22 3 33 4 44}
464 do_execsql_test 19.3 {
465 SELECT * FROM t1 WHERE (a,b)<(5,0) ORDER BY a DESC;
466 } {4 44 3 33 2 22 1 11}
467 do_execsql_test 19.4 {
468 SELECT * FROM t1 WHERE (a,b)<=(5,0) ORDER BY a DESC;
469 } {4 44 3 33 2 22 1 11}
470 do_execsql_test 19.5 {
471 SELECT * FROM t1 WHERE (a,b)>(3,0) ORDER BY a;
472 } {3 33 4 44}
473 do_execsql_test 19.6 {
474 SELECT * FROM t1 WHERE (a,b)>=(3,0) ORDER BY a;
475 } {3 33 4 44}
476 do_execsql_test 19.7 {
477 SELECT * FROM t1 WHERE (a,b)<(3,0) ORDER BY a DESC;
478 } {2 22 1 11}
479 do_execsql_test 19.8 {
480 SELECT * FROM t1 WHERE (a,b)<=(3,0) ORDER BY a DESC;
481 } {2 22 1 11}
482 do_execsql_test 19.9 {
483 SELECT * FROM t1 WHERE (a,b)>(3,32) ORDER BY a;
484 } {3 33 4 44}
485 do_execsql_test 19.10 {
486 SELECT * FROM t1 WHERE (a,b)>(3,33) ORDER BY a;
487 } {4 44}
488 do_execsql_test 19.11 {
489 SELECT * FROM t1 WHERE (a,b)>=(3,33) ORDER BY a;
490 } {3 33 4 44}
491 do_execsql_test 19.12 {
492 SELECT * FROM t1 WHERE (a,b)>=(3,34) ORDER BY a;
493 } {4 44}
494 do_execsql_test 19.13 {
495 SELECT * FROM t1 WHERE (a,b)<(3,34) ORDER BY a DESC;
496 } {3 33 2 22 1 11}
497 do_execsql_test 19.14 {
498 SELECT * FROM t1 WHERE (a,b)<(3,33) ORDER BY a DESC;
499 } {2 22 1 11}
500 do_execsql_test 19.15 {
501 SELECT * FROM t1 WHERE (a,b)<=(3,33) ORDER BY a DESC;
502 } {3 33 2 22 1 11}
503 do_execsql_test 19.16 {
504 SELECT * FROM t1 WHERE (a,b)<=(3,32) ORDER BY a DESC;
505 } {2 22 1 11}
506 do_execsql_test 19.21 {
507 SELECT * FROM t1 WHERE (0,0)<(a,b) ORDER BY a;
508 } {1 11 2 22 3 33 4 44}
509 do_execsql_test 19.22 {
510 SELECT * FROM t1 WHERE (0,0)<=(a,b) ORDER BY a;
511 } {1 11 2 22 3 33 4 44}
512 do_execsql_test 19.23 {
513 SELECT * FROM t1 WHERE (5,0)>(a,b) ORDER BY a DESC;
514 } {4 44 3 33 2 22 1 11}
515 do_execsql_test 19.24 {
516 SELECT * FROM t1 WHERE (5,0)>=(a,b) ORDER BY a DESC;
517 } {4 44 3 33 2 22 1 11}
518 do_execsql_test 19.25 {
519 SELECT * FROM t1 WHERE (3,0)<(a,b) ORDER BY a;
520 } {3 33 4 44}
521 do_execsql_test 19.26 {
522 SELECT * FROM t1 WHERE (3,0)<=(a,b) ORDER BY a;
523 } {3 33 4 44}
524 do_execsql_test 19.27 {
525 SELECT * FROM t1 WHERE (3,0)>(a,b) ORDER BY a DESC;
526 } {2 22 1 11}
527 do_execsql_test 19.28 {
528 SELECT * FROM t1 WHERE (3,0)>=(a,b) ORDER BY a DESC;
529 } {2 22 1 11}
530 do_execsql_test 19.29 {
531 SELECT * FROM t1 WHERE (3,32)<(a,b) ORDER BY a;
532 } {3 33 4 44}
533 do_execsql_test 19.30 {
534 SELECT * FROM t1 WHERE (3,33)<(a,b) ORDER BY a;
535 } {4 44}
536 do_execsql_test 19.31 {
537 SELECT * FROM t1 WHERE (3,33)<=(a,b) ORDER BY a;
538 } {3 33 4 44}
539 do_execsql_test 19.32 {
540 SELECT * FROM t1 WHERE (3,34)<=(a,b) ORDER BY a;
541 } {4 44}
542 do_execsql_test 19.33 {
543 SELECT * FROM t1 WHERE (3,34)>(a,b) ORDER BY a DESC;
544 } {3 33 2 22 1 11}
545 do_execsql_test 19.34 {
546 SELECT * FROM t1 WHERE (3,33)>(a,b) ORDER BY a DESC;
547 } {2 22 1 11}
548 do_execsql_test 19.35 {
549 SELECT * FROM t1 WHERE (3,33)>=(a,b) ORDER BY a DESC;
550 } {3 33 2 22 1 11}
551 do_execsql_test 19.36 {
552 SELECT * FROM t1 WHERE (3,32)>=(a,b) ORDER BY a DESC;
553 } {2 22 1 11}
554
555 # 2018-02-18: Memory leak nested row-value. Detected by OSSFuzz.
556 #
557 do_catchsql_test 20.1 {
558 SELECT 1 WHERE (2,(2,0)) IS (2,(2,0));
559 } {0 1}
560
561 # 2018-11-03: Ticket https://sqlite.org/src/info/1a84668dcfdebaf1
562 # Assertion fault when doing row-value operations on a primary key
563 # containing duplicate columns.
564 #
565 do_execsql_test 21.0 {
566 DROP TABLE IF EXISTS t1;
567 CREATE TABLE t1(a,b,PRIMARY KEY(b,b));
568 INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
569 SELECT * FROM t1 WHERE (a,b) IN (VALUES(1,2));
570 } {1 2}
571
572 # 2019-08-09: Multi-column subquery on the RHS of an IN operator.
573 #
574 do_execsql_test 22.100 {
575 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1) IN (SELECT 3,4);
576 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1) IN (SELECT 5,6);
577 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1) IN (SELECT 3,4);
578 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1) IN (SELECT 5,6);
579 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1 DESC) IN (SELECT 3,4);
580 SELECT (SELECT 3,4 UNION SELECT 5,6 ORDER BY 1 DESC) IN (SELECT 5,6);
581 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1 DESC) IN (SELECT 3,4);
582 SELECT (SELECT 5,6 UNION SELECT 3,4 ORDER BY 1 DESC) IN (SELECT 5,6);
583 } {1 0 1 0 0 1 0 1}
584
585 # 2019-10-21 Ticket b47e3627ecaadbde
586 #
587 do_execsql_test 23.100 {
588 DROP TABLE IF EXISTS t0;
589 CREATE TABLE t0(aa COLLATE NOCASE, bb);
590 INSERT INTO t0 VALUES('a', 'A');
591 SELECT (+bb,1) >= (aa, 1), (aa,1)<=(+bb,1) FROM t0;
592 SELECT 2 FROM t0 WHERE (+bb,1) >= (aa,1);
593 SELECT 3 FROM t0 WHERE (aa,1) <= (+bb,1);
594 } {0 1 3}
595 do_execsql_test 23.110 {
596 SELECT (SELECT +bb,1) >= (aa, 1), (aa,1)<=(SELECT +bb,1) FROM t0;
597 SELECT 2 FROM t0 WHERE (SELECT +bb,1) >= (aa,1);
598 SELECT 3 FROM t0 WHERE (aa,1) <= (SELECT +bb,1);
599 } {0 1 3}
600
601 # 2019-10-22 Ticket 6ef984af8972c2eb
602 do_execsql_test 24.100 {
603 DROP TABLE t0;
604 CREATE TABLE t0(c0 TEXT PRIMARY KEY);
605 INSERT INTO t0(c0) VALUES ('');
606 SELECT (t0.c0, TRUE) > (CAST(0 AS REAL), FALSE) FROM t0;
607 SELECT 2 FROM t0 WHERE (t0.c0, TRUE) > (CAST('' AS REAL), FALSE);
608 } {1 2}
609
610 # 2019-10-23 Ticket 135c9da7513e5a97
611 do_execsql_test 25.10 {
612 DROP TABLE t0;
613 CREATE TABLE t0(c0 UNIQUE);
614 INSERT INTO t0(c0) VALUES('a');
615 SELECT (t0.c0, 0) < ('B' COLLATE NOCASE, 0) FROM t0;
616 SELECT 2 FROM t0 WHERE (t0.c0, 0) < ('B' COLLATE NOCASE, 0);
617 } {1 2}
618 do_execsql_test 25.20 {
619 SELECT ('B' COLLATE NOCASE, 0)> (t0.c0, 0) FROM t0;
620 SELECT 2 FROM t0 WHERE ('B' COLLATE NOCASE, 0)> (t0.c0, 0);
621 } {1 2}
622 do_execsql_test 25.30 {
623 SELECT ('B', 0)> (t0.c0 COLLATE nocase, 0) FROM t0;
624 SELECT 2 FROM t0 WHERE ('B', 0)> (t0.c0 COLLATE nocase, 0);
625 } {1 2}
626 do_execsql_test 25.40 {
627 SELECT (t0.c0 COLLATE nocase, 0) < ('B', 0) FROM t0;
628 SELECT 2 FROM t0 WHERE (t0.c0 COLLATE nocase, 0) < ('B', 0);
629 } {1 2}
630
631 # 2019-11-04 Ticket 02aa2bd02f97d0f2
632 # The TK_VECTOR operator messes up sqlite3ExprImpliesNonNull() which
633 # causes incorrect LEFT JOIN strength reduction. TK_VECTOR should be
634 # treated the same as TK_OR.
635 #
636 db close
637 sqlite3 db :memory:
638 do_execsql_test 26.10 {
639 CREATE TABLE t0(c0);
640 CREATE TABLE t1(c1);
641 INSERT INTO t1(c1) VALUES (0);
642 SELECT (c0, x'') != (NULL, 0) FROM t1 LEFT JOIN t0;
643 } {1}
644 do_execsql_test 26.20 {
645 SELECT 2 FROM t1 LEFT JOIN t0 ON (c0, x'') != (NULL, 0);
646 } {2}
647 do_execsql_test 26.21 {
648 SELECT 21 FROM t0 RIGHT JOIN t1 ON (c0, x'') != (NULL, 0);
649 } {21}
650 do_execsql_test 26.30 {
651 SELECT 3 FROM t1 LEFT JOIN t0 WHERE (c0, x'') != (NULL, 0);
652 } {3}
653 do_execsql_test 26.31 {
654 SELECT 31 FROM t0 RIGHT JOIN t1 WHERE (c0, x'') != (NULL, 0);
655 } {31}
656
657 # 2019-12-30 ticket 892575cdba4e1e36
658 #
659 reset_db
660 do_catchsql_test 27.10 {
661 CREATE TABLE t0(c0 CHECK(((0, 0) > (0, c0))));
662 INSERT INTO t0(c0) VALUES(0) ON CONFLICT(c0) DO UPDATE SET c0 = 3;
663 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
664
665 # 2021-02-03
666 # https://bugs.chromium.org/p/chromium/issues/detail?id=1173511
667 # Faulty assert() statement.
668 #
669 reset_db
670 do_catchsql_test 28.10 {
671 CREATE TABLE t0(c0 PRIMARY KEY, c1);
672 CREATE TRIGGER trigger0 BEFORE DELETE ON t0 BEGIN
673 SELECT (SELECT c0,c1 FROM t0) FROM t0;
674 END ;
675 DELETE FROM t0;
676 } {1 {sub-select returns 2 columns - expected 1}}
677
678 # 2021-03-19
679 # dbsqlfuzz find of a NEVER().
680 do_catchsql_test 29.1 {
681 SELECT (SELECT 1 WHERE ((SELECT 1 WHERE (2,(2,0)) IS (2,(20))),(2,0)) IS (2,(20))) WHERE (2,(2,0)) IS (2 IN(SELECT 1 WHERE (2,(2,2,0)) IS (2,(20))),(20));
682 } {1 {row value misused}}
683
684 #-------------------------------------------------------------------------
685 reset_db
686 do_execsql_test 30.0 {
687 CREATE TABLE t1(x, y, z);
688 CREATE TABLE t2(a, b);
689
690 INSERT INTO t1 VALUES(1000, 2000, 3000);
691 INSERT INTO t2 VALUES(NULL, NULL);
692 }
693
694 do_execsql_test 30.1 {
695 UPDATE t2 SET (a,b)=(
696 SELECT max( t1.x ) OVER( PARTITION BY sum( (SELECT t1.y) ) ), 2
697 )
698 FROM t1;
699 } {}
700
701 do_execsql_test 30.2 {
702 SELECT * FROM t2
703 } {1000 2}
704
705 reset_db
706 do_execsql_test 30.3 {
707 CREATE TABLE t1(x INT PRIMARY KEY, y, z);
708 CREATE TABLE t2(a,b,c,d,e,PRIMARY KEY(a,b))WITHOUT ROWID;
709
710 UPDATE t2 SET (d,d,a)=(SELECT EXISTS(SELECT 1 IN(SELECT max( 1 IN(SELECT x ORDER BY 1)) OVER(PARTITION BY sum((SELECT y FROM t1 UNION SELECT x ORDER BY 1)))INTERSECT SELECT EXISTS(SELECT 1 FROM t1 UNION SELECT x ORDER BY 1) ORDER BY 1) ORDERa)|9 AS blob, 2, 3) FROM t1 WHERE x<a;
711 }
712
713 # 2022-01-21 https://sqlite.org/forum/forumpost/ab95010d410a0a55
714 reset_db
715 do_execsql_test 31.1 {
716 CREATE TABLE a(a1 PRIMARY KEY,a2);
717 INSERT INTO a VALUES(1,5);
718 CREATE TABLE b(b1 UNIQUE,b2);
719 SELECT * FROM a LEFT JOIN b ON b2=NULL AND b2=5 WHERE (b1,substr(b.b1,1,1))==(SELECT 1024,'b');
720 } {}
721 do_execsql_test 31.1b {
722 SELECT * FROM b RIGHT JOIN a ON b2=NULL AND b2=5 WHERE (b1,substr(b.b1,1,1))==(SELECT 1024,'b');
723 } {}
724 do_execsql_test 31.2 {
725 CREATE TABLE t1(a);
726 INSERT INTO t1 VALUES(0);
727 CREATE TABLE t2(b,c,d);
728 INSERT INTO t2 VALUES(NULL,123,456);
729 SELECT * FROM t1 LEFT JOIN t2 ON b=NULL WHERE (c,d)==(SELECT 123, 456+a);
730 } {}
731 do_execsql_test 31.2b {
732 SELECT * FROM t2 RIGHT JOIN t1 ON b=NULL WHERE (c,d)==(SELECT 123, 456+a);
733 } {}
734
735 # 2022-02-03 dbsqlfuzz 80a9fade844b4fb43564efc972bcb2c68270f5d1
736 reset_db
737 do_execsql_test 32.1 {
738 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT);
739 CREATE TABLE t2(d INTEGER PRIMARY KEY);
740 INSERT INTO t1(a,b,c) VALUES(500,654,456);
741 INSERT INTO t1(a,b,c) VALUES(501,655,456);
742 INSERT INTO t1(a,b,c) VALUES(502,654,122);
743 INSERT INTO t1(a,b,c) VALUES(503,654,221);
744 INSERT INTO t1(a,b,c) VALUES(601,654,122);
745 INSERT INTO t2(d) VALUES(456);
746 INSERT INTO t2(d) VALUES(122);
747 SELECT a FROM (
748 SELECT t1.a FROM t2, t1
749 WHERE (987, t1.b) = ( SELECT 987, 654 ) AND t2.d=t1.c
750 ) AS t3
751 WHERE a=1234 OR a<=567;
752 } {500 502}
753
754 # 2022-07-15
755 # https://sqlite.org/forum/forumpost/3607259d3c
756 #
757 reset_db
758 do_execsql_test 33.1 {
759 CREATE TABLE t1(a INT, b INT PRIMARY KEY) WITHOUT ROWID;
760 INSERT INTO t1(a, b) VALUES (0, 1),(15,-7),(3,100);
761 ANALYZE;
762 } {}
763 do_execsql_test 33.2 {
764 SELECT * FROM t1 WHERE (b,a) BETWEEN (0,5) AND (99,-2);
765 } {0 1}
766 do_execsql_test 33.3 {
767 SELECT * FROM t1 WHERE (b,a) BETWEEN (-8,5) AND (0,-2);
768 } {15 -7}
769 do_execsql_test 33.3 {
770 SELECT * FROM t1 WHERE (b,a) BETWEEN (3,5) AND (100,4);
771 } {3 100}
772 do_execsql_test 33.3 {
773 SELECT * FROM t1 WHERE (b,a) BETWEEN (3,5) AND (100,2);
774 } {}
775 do_execsql_test 33.3 {
776 SELECT * FROM t1 WHERE (a,b) BETWEEN (-2,99) AND (1,0);
777 } {0 1}
778 do_execsql_test 33.3 {
779 SELECT * FROM t1 WHERE (a,b) BETWEEN (14,99) AND (16,0);
780 } {15 -7}
781 do_execsql_test 33.3 {
782 SELECT * FROM t1 WHERE (a,b) BETWEEN (2,99) AND (4,0);
783 } {3 100}
784
785 # 2025-04-15 https://sqlite.org/forum/forumpost/b9647a113b465950
786 # Incorrect result when the schema includes a table with a UNIQUE
787 # constraint and one of the columns in the UNIQUE constraint is the
788 # INTEGER PRIMARY KEY, and the columns that UNIQUE constraint are
789 # used in a rowvalue-IN operator constraint.
790 #
791 # 2025-07-07 Discovered that the original fix was incomplete and
792 # new tests added. See tag-20250707-01 in the code.
793 #
794 reset_db
795 do_execsql_test 34.1 {
796 CREATE TABLE items (
797 Id INTEGER /* rowid alias */,
798 Item INTEGER /* any type */,
799 Test TEXT /* TEXT or BLOB */,
800 Filler, /* any type */
801 PRIMARY KEY(Id),
802 UNIQUE(Item, Id)
803 );
804 INSERT INTO items (Id, Item)
805 VALUES (1, 2), (2, 2), (3, 3), (4, 5);
806 UPDATE items SET test='ok'
807 WHERE (Id, Item) IN (SELECT Id, Item FROM items);
808 SELECT Id, Item, test FROM items ORDER BY id;
809 } {1 2 ok 2 2 ok 3 3 ok 4 5 ok}
810 db null NULL
811 do_execsql_test 34.2 {
812 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
813 CREATE INDEX idx ON t1(b,a);
814 INSERT INTO t1(a,b) VALUES (1, 22);
815 SELECT * FROM t1 INDEXED BY idx WHERE (b,a) IN (SELECT b,a FROM t1);
816 } {1 22 NULL NULL}
817 do_execsql_test 34.3 {
818 DROP TABLE t1;
819 CREATE TABLE t1(a, b, c, d);
820 CREATE INDEX idx ON t1(b,a,a);
821 INSERT INTO t1(a,b) VALUES (1, 22);
822 SELECT * FROM t1 INDEXED BY idx WHERE (b,a) IN (SELECT b,a FROM t1);
823 } {1 22 NULL NULL}
824 do_execsql_test 34.4 {
825 DROP TABLE t1;
826 CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT);
827 CREATE INDEX t1a ON t1(a,id); -- index includes PRIMARY KEY
828 CREATE TABLE t2(id INTEGER PRIMARY KEY);
829 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100)
830 INSERT INTO t1(id,a) SELECT n, 777 FROM c;
831 INSERT INTO t2 SELECT id FROM t1;
832 SELECT *
833 FROM t1 JOIN t2 USING(id)
834 WHERE t1.a=777 AND t2.id>999
835 ORDER BY t1.id;
836 } {}
837 do_execsql_test 34.5 {
838 EXPLAIN QUERY PLAN
839 SELECT *
840 FROM t1 JOIN t2 USING(id)
841 WHERE t1.a=777 AND t2.id>999
842 ORDER BY t1.id;
843 } {/SEARCH t1 USING COVERING INDEX t1a .a=. AND id>../}
844
845 # 2025-08-04 forum post eab63506cf
846 #
847 do_execsql_test 35.0 {
848 DROP TABLE IF EXISTS t1;
849 CREATE TABLE t1(c1 TEXT, c2 INTEGER, PRIMARY KEY(c1, c2));
850 INSERT INTO t1(c1, c2) VALUES ('a', 1);
851 SELECT ('a', 1) IN (SELECT c1, c2 from t1);
852 } 1
853 do_execsql_test 35.1 {
854 SELECT (1, 'a') IN (SELECT c2, c1 from t1);
855 } 1
856
857 # Forum post 2026-05-09T07:55:45Z
858 #
859 do_execsql_test 36.0 {
860 DROP TABLE t1;
861 CREATE TABLE t1(x);
862 INSERT INTO t1 VALUES(1),(2),(3);
863 SELECT (1,2,3,max(x)) IN () FROM t1;
864 } 0
865 do_execsql_test 36.1 {
866 SELECT (1,2,max(x),3) IN () FROM t1;
867 } 0
868 do_execsql_test 36.2 {
869 SELECT (max(x),1,23) IN () FROM t1;
870 } 0
871
872 finish_test