AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the IN and BETWEEN operator.13#14# $Id: in.test,v 1.22 2008/08/04 03:51:24 danielk1977 Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# Generate the test data we will need for the first squences of tests.20#21do_test in-1.0 {22 execsql {23 BEGIN;24 CREATE TABLE t1(a int, b int);25 }26 for {set i 1} {$i<=10} {incr i} {27 execsql "INSERT INTO t1 VALUES($i,[expr {1<<$i}])"28 }29 execsql {30 COMMIT;31 SELECT count(*) FROM t1;32 }33} {10}34 35# Do basic testing of BETWEEN.36#37do_test in-1.1 {38 execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}39} {4 5}40do_test in-1.2 {41 execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}42} {1 2 3 6 7 8 9 10}43do_test in-1.3 {44 execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}45} {1 2 3 4}46do_test in-1.4 {47 execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}48} {5 6 7 8 9 10}49do_test in-1.6 {50 execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}51} {1 2 3 4 9}52do_test in-1.7 {53 execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}54} {101 102 103 4 5 6 7 8 9 10}55 56# The rest of this file concentrates on testing the IN operator.57# Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY 58# (because the IN operator is unavailable).59#60ifcapable !subquery {61 finish_test62 return63}64 65# Testing of the IN operator using static lists on the right-hand side.66#67do_test in-2.1 {68 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}69} {3 4 5}70do_test in-2.2 {71 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}72} {1 2 6 7 8 9 10}73do_test in-2.3 {74 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}75} {3 4 5 9}76do_test in-2.4 {77 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}78} {1 2 6 7 8 9 10}79do_test in-2.5 {80 execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}81} {1 2 103 104 5 6 7 8 9 10}82 83do_test in-2.6 {84 execsql {SELECT a FROM t1 WHERE b IN (b+8,64)}85} {6}86do_test in-2.7 {87 execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}88} {4 5 6 7 8 9 10}89do_test in-2.8 {90 execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}91} {4 5}92do_test in-2.9 {93 execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}94} {}95do_test in-2.10 {96 execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}97} {}98do_test in-2.11 {99 set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]100 lappend v $msg101} {1 {no such column: c}}102 103# Testing the IN operator where the right-hand side is a SELECT104#105do_test in-3.1 {106 execsql {107 SELECT a FROM t1108 WHERE b IN (SELECT b FROM t1 WHERE a<5)109 ORDER BY a110 }111} {1 2 3 4}112do_test in-3.2 {113 execsql {114 SELECT a FROM t1115 WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512116 ORDER BY a117 }118} {1 2 3 4 9}119do_test in-3.3 {120 execsql {121 SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b122 }123} {101 102 103 104 5 6 7 8 9 10}124 125# Make sure the UPDATE and DELETE commands work with IN-SELECT126#127do_test in-4.1 {128 execsql {129 UPDATE t1 SET b=b*2 130 WHERE b IN (SELECT b FROM t1 WHERE a>8)131 }132 execsql {SELECT b FROM t1 ORDER BY b}133} {2 4 8 16 32 64 128 256 1024 2048}134do_test in-4.2 {135 execsql {136 DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)137 }138 execsql {SELECT a FROM t1 ORDER BY a}139} {1 2 3 4 5 6 7 8}140do_test in-4.3 {141 execsql {142 DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)143 }144 execsql {SELECT a FROM t1 ORDER BY a}145} {5 6 7 8}146 147# Do an IN with a constant RHS but where the RHS has many, many148# elements. We need to test that collisions in the hash table149# are resolved properly.150#151do_test in-5.1 {152 execsql {153 INSERT INTO t1 VALUES('hello', 'world');154 SELECT * FROM t1155 WHERE a IN (156 'Do','an','IN','with','a','constant','RHS','but','where','the',157 'has','many','elements','We','need','to','test','that',158 'collisions','hash','table','are','resolved','properly',159 'This','in-set','contains','thirty','one','entries','hello');160 }161} {hello world}162 163# Make sure the IN operator works with INTEGER PRIMARY KEY fields.164#165do_test in-6.1 {166 execsql {167 CREATE TABLE ta(a INTEGER PRIMARY KEY, b);168 INSERT INTO ta VALUES(1,1);169 INSERT INTO ta VALUES(2,2);170 INSERT INTO ta VALUES(3,3);171 INSERT INTO ta VALUES(4,4);172 INSERT INTO ta VALUES(6,6);173 INSERT INTO ta VALUES(8,8);174 INSERT INTO ta VALUES(10,175 'This is a key that is long enough to require a malloc in the VDBE');176 SELECT * FROM ta WHERE a<10;177 }178} {1 1 2 2 3 3 4 4 6 6 8 8}179do_test in-6.2 {180 execsql {181 CREATE TABLE tb(a INTEGER PRIMARY KEY, b);182 INSERT INTO tb VALUES(1,1);183 INSERT INTO tb VALUES(2,2);184 INSERT INTO tb VALUES(3,3);185 INSERT INTO tb VALUES(5,5);186 INSERT INTO tb VALUES(7,7);187 INSERT INTO tb VALUES(9,9);188 INSERT INTO tb VALUES(11,189 'This is a key that is long enough to require a malloc in the VDBE');190 SELECT * FROM tb WHERE a<10;191 }192} {1 1 2 2 3 3 5 5 7 7 9 9}193do_test in-6.3 {194 execsql {195 SELECT a FROM ta WHERE b IN (SELECT a FROM tb);196 }197} {1 2 3}198do_test in-6.4 {199 execsql {200 SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);201 }202} {4 6 8 10}203do_test in-6.5 {204 execsql {205 SELECT a FROM ta WHERE b IN (SELECT b FROM tb);206 }207} {1 2 3 10}208do_test in-6.6 {209 execsql {210 SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);211 }212} {4 6 8}213do_test in-6.7 {214 execsql {215 SELECT a FROM ta WHERE a IN (SELECT a FROM tb);216 }217} {1 2 3}218do_test in-6.8 {219 execsql {220 SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);221 }222} {4 6 8 10}223do_test in-6.9 {224 execsql {225 SELECT a FROM ta WHERE a IN (SELECT b FROM tb);226 }227} {1 2 3}228do_test in-6.10 {229 execsql {230 SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);231 }232} {4 6 8 10}233 234# Tests of IN operator against empty sets. (Ticket #185)235#236do_test in-7.1 {237 execsql {238 SELECT a FROM t1 WHERE a IN ();239 }240} {}241do_test in-7.2 {242 execsql {243 SELECT a FROM t1 WHERE a IN (5);244 }245} {5}246do_test in-7.3 {247 execsql {248 SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;249 }250} {5 6 7 8 hello}251do_test in-7.4 {252 execsql {253 SELECT a FROM t1 WHERE a IN (5) AND b IN ();254 }255} {}256do_test in-7.5 {257 execsql {258 SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();259 }260} {5}261do_test in-7.6.1 {262 execsql {263 SELECT a FROM ta WHERE a IN ();264 }265} {}266do_test in-7.6.2 {267 db status step268} {0}269do_test in-7.7 {270 execsql {271 SELECT a FROM ta WHERE a NOT IN ();272 }273} {1 2 3 4 6 8 10}274 275do_test in-7.8.1 {276 execsql {277 SELECT * FROM ta LEFT JOIN tb ON (ta.b=tb.b) WHERE ta.a IN ();278 }279} {}280do_test in-7.8.2 {281 db status step282} {0}283 284do_test in-8.3 {285 execsql {286 SELECT b FROM t1 WHERE a IN ('hello','there')287 }288} {world}289do_test in-8.4 {290 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1291 execsql {292 SELECT b FROM t1 WHERE a IN ("hello",'there')293 }294} {world}295 296# Test constructs of the form: expr IN tablename297#298do_test in-9.1 {299 execsql {300 CREATE TABLE t4 AS SELECT a FROM tb;301 SELECT * FROM t4; 302 }303} {1 2 3 5 7 9 11}304do_test in-9.2 {305 execsql {306 SELECT b FROM t1 WHERE a IN t4;307 }308} {32 128}309do_test in-9.3 {310 execsql {311 SELECT b FROM t1 WHERE a NOT IN t4;312 }313} {64 256 world}314do_test in-9.4 {315 catchsql {316 SELECT b FROM t1 WHERE a NOT IN tb;317 }318} {1 {sub-select returns 2 columns - expected 1}}319 320# IN clauses in CHECK constraints. Ticket #1645321#322do_test in-10.1 {323 execsql {324 CREATE TABLE t5(325 a INTEGER,326 CHECK( a IN (111,222,333) )327 );328 INSERT INTO t5 VALUES(111);329 SELECT * FROM t5;330 }331} {111}332do_test in-10.2 {333 catchsql {334 INSERT INTO t5 VALUES(4);335 }336} {1 {CHECK constraint failed: a IN (111,222,333)}}337 338# Ticket #1821339#340# Type affinity applied to the right-hand side of an IN operator.341#342do_test in-11.1 {343 execsql {344 CREATE TABLE t6(a,b NUMERIC);345 INSERT INTO t6 VALUES(1,2);346 INSERT INTO t6 VALUES(2,3);347 SELECT * FROM t6 WHERE b IN (2);348 }349} {1 2}350do_test in-11.2 {351 # The '2' should be coerced into 2 because t6.b is NUMERIC352 execsql {353 SELECT * FROM t6 WHERE b IN ('2');354 }355} {1 2}356do_test in-11.3 {357 # No coercion should occur here because of the unary + before b.358 execsql {359 SELECT * FROM t6 WHERE +b IN ('2');360 }361} {}362do_test in-11.4 {363 # No coercion because column a as affinity NONE364 execsql {365 SELECT * FROM t6 WHERE a IN ('2');366 }367} {}368do_test in-11.5 {369 execsql {370 SELECT * FROM t6 WHERE a IN (2);371 }372} {2 3}373do_test in-11.6 {374 # No coercion because column a as affinity NONE375 execsql {376 SELECT * FROM t6 WHERE +a IN ('2');377 }378} {}379 380# Test error conditions with expressions of the form IN(<compound select>).381#382ifcapable compound {383do_test in-12.1 {384 execsql {385 CREATE TABLE t2(a, b, c);386 CREATE TABLE t3(a, b, c);387 }388} {}389do_test in-12.2 {390 catchsql {391 SELECT * FROM t2 WHERE a IN (392 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2393 );394 }395} {1 {sub-select returns 2 columns - expected 1}}396do_test in-12.3 {397 catchsql {398 SELECT * FROM t2 WHERE a IN (399 SELECT a, b FROM t3 UNION SELECT a, b FROM t2400 );401 }402} {1 {sub-select returns 2 columns - expected 1}}403do_test in-12.4 {404 catchsql {405 SELECT * FROM t2 WHERE a IN (406 SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2407 );408 }409} {1 {sub-select returns 2 columns - expected 1}}410do_test in-12.5 {411 catchsql {412 SELECT * FROM t2 WHERE a IN (413 SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2414 );415 }416} {1 {sub-select returns 2 columns - expected 1}}417do_test in-12.6 {418 catchsql {419 SELECT * FROM t2 WHERE a IN (420 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2421 );422 }423} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}424do_test in-12.7 {425 catchsql {426 SELECT * FROM t2 WHERE a IN (427 SELECT a, b FROM t3 UNION SELECT a FROM t2428 );429 }430} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}431do_test in-12.8 {432 catchsql {433 SELECT * FROM t2 WHERE a IN (434 SELECT a, b FROM t3 EXCEPT SELECT a FROM t2435 );436 }437} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}438do_test in-12.9 {439 catchsql {440 SELECT * FROM t2 WHERE a IN (441 SELECT a, b FROM t3 INTERSECT SELECT a FROM t2442 );443 }444} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}445}446 447ifcapable compound {448do_test in-12.10 {449 catchsql {450 SELECT * FROM t2 WHERE a IN (451 SELECT a FROM t3 UNION ALL SELECT a, b FROM t2452 );453 }454} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}455do_test in-12.11 {456 catchsql {457 SELECT * FROM t2 WHERE a IN (458 SELECT a FROM t3 UNION SELECT a, b FROM t2459 );460 }461} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}462do_test in-12.12 {463 catchsql {464 SELECT * FROM t2 WHERE a IN (465 SELECT a FROM t3 EXCEPT SELECT a, b FROM t2466 );467 }468} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}469do_test in-12.13 {470 catchsql {471 SELECT * FROM t2 WHERE a IN (472 SELECT a FROM t3 INTERSECT SELECT a, b FROM t2473 );474 }475} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}476do_test in-12.14 {477 catchsql {478 SELECT * FROM t2 WHERE a IN (479 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2480 );481 }482} {1 {sub-select returns 2 columns - expected 1}}483do_test in-12.15 {484 catchsql {485 SELECT * FROM t2 WHERE a IN (486 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2487 );488 }489} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}490}; #ifcapable compound491 492 493#------------------------------------------------------------------------494# The following tests check that NULL is handled correctly when it 495# appears as part of a set of values on the right-hand side of an496# IN or NOT IN operator.497#498# When it appears in such a set, NULL is handled as an "unknown value".499# If, because of the unknown value in the set, the result of the expression 500# cannot be determined, then it itself evaluates to NULL.501#502 503# Warm body test to demonstrate the principles being tested:504#505do_test in-13.1 {506 db nullvalue "null"507 execsql { SELECT 508 1 IN (NULL, 1, 2), -- The value 1 is a member of the set, return true.509 3 IN (NULL, 1, 2), -- Ambiguous, return NULL.510 1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.511 3 NOT IN (NULL, 1, 2) -- Ambiguous, return NULL.512 }513} {1 null 0 null}514 515do_test in-13.2 {516 execsql { 517 CREATE TABLE t7(a, b, c NOT NULL);518 INSERT INTO t7 VALUES(1, 1, 1);519 INSERT INTO t7 VALUES(2, 2, 2);520 INSERT INTO t7 VALUES(3, 3, 3);521 INSERT INTO t7 VALUES(NULL, 4, 4);522 INSERT INTO t7 VALUES(NULL, 5, 5);523 }524} {}525 526do_test in-13.3 {527 execsql { SELECT 2 IN (SELECT a FROM t7) }528} {1}529do_test in-13.4 {530 execsql { SELECT 6 IN (SELECT a FROM t7) }531} {null}532 533do_test in-13.5 {534 execsql { SELECT 2 IN (SELECT b FROM t7) }535} {1}536do_test in-13.6 {537 execsql { SELECT 6 IN (SELECT b FROM t7) }538} {0}539 540do_test in-13.7 {541 execsql { SELECT 2 IN (SELECT c FROM t7) }542} {1}543do_test in-13.8 {544 execsql { SELECT 6 IN (SELECT c FROM t7) }545} {0}546 547do_test in-13.9 {548 execsql {549 SELECT550 2 NOT IN (SELECT a FROM t7),551 6 NOT IN (SELECT a FROM t7),552 2 NOT IN (SELECT b FROM t7),553 6 NOT IN (SELECT b FROM t7),554 2 NOT IN (SELECT c FROM t7),555 6 NOT IN (SELECT c FROM t7)556 } 557} {0 null 0 1 0 1}558 559do_test in-13.10 {560 execsql { 561 SELECT b IN (562 SELECT inside.a 563 FROM t7 AS inside 564 WHERE inside.b BETWEEN outside.b+1 AND outside.b+2565 )566 FROM t7 AS outside ORDER BY b;567 }568} {0 null null null 0}569 570do_test in-13.11 {571 execsql {572 SELECT b NOT IN (573 SELECT inside.a 574 FROM t7 AS inside 575 WHERE inside.b BETWEEN outside.b+1 AND outside.b+2576 )577 FROM t7 AS outside ORDER BY b;578 }579} {1 null null null 1}580 581do_test in-13.12 {582 execsql {583 CREATE INDEX i1 ON t7(a);584 CREATE INDEX i2 ON t7(b);585 CREATE INDEX i3 ON t7(c);586 }587 execsql {588 SELECT589 2 IN (SELECT a FROM t7),590 6 IN (SELECT a FROM t7),591 2 IN (SELECT b FROM t7),592 6 IN (SELECT b FROM t7),593 2 IN (SELECT c FROM t7),594 6 IN (SELECT c FROM t7)595 } 596} {1 null 1 0 1 0}597 598do_test in-13.13 {599 execsql {600 SELECT601 2 NOT IN (SELECT a FROM t7),602 6 NOT IN (SELECT a FROM t7),603 2 NOT IN (SELECT b FROM t7),604 6 NOT IN (SELECT b FROM t7),605 2 NOT IN (SELECT c FROM t7),606 6 NOT IN (SELECT c FROM t7)607 } 608} {0 null 0 1 0 1}609 610do_test in-13.14 {611 execsql {612 BEGIN TRANSACTION;613 CREATE TABLE a(id INTEGER);614 INSERT INTO a VALUES(1);615 INSERT INTO a VALUES(2);616 INSERT INTO a VALUES(3);617 CREATE TABLE b(id INTEGER);618 INSERT INTO b VALUES(NULL);619 INSERT INTO b VALUES(3);620 INSERT INTO b VALUES(4);621 INSERT INTO b VALUES(5);622 COMMIT;623 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);624 }625} {}626do_test in-13.14 {627 execsql {628 CREATE INDEX i5 ON b(id);629 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);630 }631} {}632 633do_test in-13.15 {634 catchsql {635 SELECT 0 WHERE (SELECT 0,0) OR (0 IN (1,2));636 }637} {1 {sub-select returns 2 columns - expected 1}}638 639 640do_test in-13.X {641 db nullvalue ""642} {}643 644# At one point the following was causing valgrind to report a "jump645# depends on unitialized location" problem.646#647do_execsql_test in-14.0 {648 CREATE TABLE c1(a);649 INSERT INTO c1 VALUES(1), (2), (4), (3);650}651do_execsql_test in-14.1 {652 SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1653} {1 2 3 4}654 655# 2019-02-20 Ticket https://sqlite.org/src/tktview/df46dfb631f75694fbb97033b69656#657do_execsql_test in-15.0 {658 DROP TABLE IF EXISTS t1;659 CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY);660 INSERT INTO t1 VALUES(1);661 SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);662} {1}663do_execsql_test in-15.1 {664 DROP TABLE IF EXISTS t2;665 CREATE TABLE t2(a INTEGER PRIMARY KEY,b);666 INSERT INTO t2 VALUES(1,11);667 INSERT INTO t2 VALUES(2,22);668 INSERT INTO t2 VALUES(3,33);669 SELECT b, a IN (3,4,5) FROM t2 ORDER BY b;670} {11 0 22 0 33 1}671do_execsql_test in-15.2 {672 DROP TABLE IF EXISTS t3;673 CREATE TABLE t3(x INTEGER PRIMARY KEY);674 INSERT INTO t3 VALUES(8);675 SELECT CASE WHEN x NOT IN (5,6,7) THEN 'yes' ELSE 'no' END FROM t3;676 SELECT CASE WHEN x NOT IN (NULL,6,7) THEN 'yes' ELSE 'no' END FROM t3;677} {yes no}678do_execsql_test in-15.3 {679 SELECT CASE WHEN x NOT IN (5,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;680 SELECT CASE WHEN x NOT IN (NULL,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;681} {yes no}682do_execsql_test in-15.4 {683 DROP TABLE IF EXISTS t4;684 CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT);685 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)686 INSERT INTO t4(a,b) SELECT x, x+100 FROM c;687 SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b;688} {103 108}689do_execsql_test in-15.5 {690 SELECT b FROM t4 WHERE a NOT IN (3,null,8);691} {}692do_execsql_test in-15.6 {693 DROP TABLE IF EXISTS t5;694 DROP TABLE IF EXISTS t6;695 CREATE TABLE t5(id INTEGER PRIMARY KEY, name TEXT);696 CREATE TABLE t6(id INTEGER PRIMARY KEY, name TEXT, t5_id INT);697 INSERT INTO t5 VALUES(1,'Alice'),(2,'Emma');698 INSERT INTO t6 VALUES(1,'Bob',1),(2,'Cindy',1),(3,'Dave',2);699 SELECT a.*700 FROM t5 AS 'a' JOIN t5 AS 'b' ON b.id=a.id701 WHERE b.id IN (702 SELECT t6.t5_id703 FROM t6704 WHERE name='Bob'705 AND t6.t5_id IS NOT NULL706 AND t6.id IN (707 SELECT id708 FROM (SELECT t6.id, count(*) AS x709 FROM t6710 WHERE name='Bob'711 ) AS 't'712 WHERE x=1713 )714 AND t6.id IN (1,id)715 );716} {1 Alice}717 718#-------------------------------------------------------------------------719reset_db720do_execsql_test in-16.0 {721 CREATE TABLE x1(a, b);722 INSERT INTO x1(a) VALUES(1), (2), (3), (4), (5), (6);723 CREATE INDEX x1i ON x1(a, b);724}725 726do_execsql_test in-16.1 {727 SELECT * FROM x1 728 WHERE a IN (SELECT a FROM x1 WHERE (a%2)==0) 729 ORDER BY a DESC, b;730} {6 {} 4 {} 2 {}}731 732do_execsql_test in-16.2 {733 SELECT * FROM x1 734 WHERE a IN (SELECT a FROM x1 WHERE (a%7)==0) 735 ORDER BY a DESC, b;736} {}737 738# 2019-06-11739# https://sqlite.org/src/info/57353f8243c637c0740#741do_execsql_test in-17.1 {742 SELECT 1 IN ('1');743} 0744do_execsql_test in-17.2 {745 SELECT 1 IN ('1' COLLATE nocase);746} 0747do_execsql_test in-17.3 {748 SELECT 1 IN (CAST('1' AS text));749} 0750do_execsql_test in-17.4 {751 SELECT 1 IN (CAST('1' AS text) COLLATE nocase);752} 0753 754# 2019-08-27 ticket https://sqlite.org/src/info/dbaf8a6820be1ece755# 756do_execsql_test in-18.1 {757 DROP TABLE IF EXISTS t0;758 CREATE TABLE t0(c0 INT UNIQUE);759 INSERT INTO t0(c0) VALUES (1);760 SELECT * FROM t0 WHERE '1' IN (t0.c0);761} {}762 763# 2019-09-02 ticket https://sqlite.org/src/info/2841e99d104c6436764# For the IN_INDEX_NOOP optimization, apply REAL affinity to the LHS765# values prior to comparison if the RHS has REAL affinity.766#767# Also ticket https://sqlite.org/src/info/29f635e0af71234b768#769do_execsql_test in-19.10 {770 DROP TABLE IF EXISTS t0;771 CREATE TABLE t0(c0 REAL UNIQUE);772 INSERT INTO t0(c0) VALUES(2.0625E00);773 SELECT 1 FROM t0 WHERE c0 IN ('2.0625');774} {1}775do_execsql_test in-19.20 {776 SELECT c0 IN ('2.0625') FROM t0;777} {1}778do_execsql_test in-19.21 {779 SELECT c0 = ('2.0625') FROM t0;780} {1}781do_execsql_test in-19.22 {782 SELECT c0 = ('0.20625e+01') FROM t0;783} {1}784do_execsql_test in-19.30 {785 SELECT c0 IN ('2.0625',2,3) FROM t0;786} {1}787do_execsql_test in-19.40 {788 DROP TABLE t0;789 CREATE TABLE t0(c0 TEXT, c1 REAL, c2, PRIMARY KEY(c2, c0, c1));790 CREATE INDEX i0 ON t0(c1 IN (c0));791 INSERT INTO t0(c0, c2) VALUES (0, NULL) ON CONFLICT(c2, c1, c0) DO NOTHING;792 PRAGMA integrity_check;793} {ok}794 795# Ticket f3ff1472887796#797do_execsql_test in-20.1 {798 SELECT (1 IN (2 IS TRUE));799} {1}800 801# Forum post: https://sqlite.org/forum/forumpost/5782619992.802#803reset_db804do_execsql_test in-21.1 {805 CREATE TABLE t0(c0);806 SELECT COUNT(*) FROM t0 ORDER BY (t0.c0 IN ());807} {0}808 809# Ignore extra parentheses around a subquery on the RHS of an IN operator,810# because that is what PostgreSQL does.811#812do_execsql_test in-22.1 {813 DROP TABLE IF EXISTS t1;814 CREATE TABLE t1(x INT PRIMARY KEY, y INT);815 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8)816 INSERT INTO t1(x,y) SELECT x, x*100 FROM c;817 DROP TABLE IF EXISTS t2;818 CREATE TABLE t2(a INT);819 INSERT INTO t2 VALUES(2),(4),(6);820 SELECT * FROM t1 WHERE x IN (SELECT a FROM t2);821} {2 200 4 400 6 600}822do_execsql_test in-22.2 {823 SELECT * FROM t1 WHERE x IN ((SELECT a FROM t2));824} {2 200 4 400 6 600}825do_execsql_test in-22.3 {826 SELECT * FROM t1 WHERE x IN (((SELECT a FROM t2)));827} {2 200 4 400 6 600}828do_execsql_test in-22.4 {829 SELECT * FROM t1 WHERE x IN ((((((SELECT a FROM t2))))));830} {2 200 4 400 6 600}831 832# 2023-04-04 https://sqlite.org/forum/forumpost/dc16ec63d3833# Faulty assert() statement in the IN optimization.834#835do_execsql_test in-23.0 {836 DROP TABLE IF EXISTS t4;837 CREATE TABLE t4(a TEXT, b INT);838 INSERT INTO t4(a,b) VALUES('abc',0),('ABC',1),('def',2);839 CREATE INDEX t4x ON t4(a, +a COLLATE NOCASE);840 SELECT a0.a, group_concat(a1.a) AS b841 FROM t4 AS a0 JOIN t4 AS a1842 GROUP BY a0.a843 HAVING (SELECT sum( (a1.a == +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));844} {ABC abc,ABC,def abc abc,ABC,def def abc,ABC,def}845do_execsql_test in-23.0-b {846 SELECT a0.a, group_concat(a1.a) AS b847 FROM t4 AS a0 JOIN t4 AS a1848 GROUP BY a0.a849 HAVING (SELECT sum( (a1.a GLOB +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));850} {ABC abc,ABC,def abc abc,ABC,def def abc,ABC,def}851#852# Follow-up forum/forumpost/0713a16a44853#854do_execsql_test in-23.1 {855 CREATE VIEW t5 AS856 SELECT 1 AS b857 WHERE (SELECT count(0=NOT+a COLLATE NOCASE IN (SELECT 0))858 FROM t4859 GROUP BY a);860 SELECT * FROM t5;861} 1862 863finish_test864