AryaWu/sqlite
0
1# 2024-05-012#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#12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix in717 18do_execsql_test 1.0 {19 CREATE TABLE t1(a, b, c PRIMARY KEY);20 CREATE TABLE t2(x, y, z);21}22 23foreach {tn nNext idx sql} {24 1 1 {25 CREATE INDEX i1 ON t1(a, b);26 } {27 SELECT * FROM t1 WHERE (a, b) IN (SELECT x, y FROM t2)28 } 29 30 2 0 {31 CREATE UNIQUE INDEX i1 ON t1(a, b);32 } {33 SELECT * FROM t1 WHERE (a, b) IN (SELECT x, y FROM t2)34 } 35 36 3 0 {37 CREATE UNIQUE INDEX i1 ON t1(a, b);38 } {39 SELECT * FROM t1 WHERE a = ? AND b = ?40 } 41 42 3 1 {43 CREATE UNIQUE INDEX i1 ON t1(a, b);44 } {45 SELECT * FROM t1 WHERE a = ? AND b IS ?46 } 47 48 4 0 {49 CREATE UNIQUE INDEX i1 ON t1(a, b);50 } {51 SELECT * FROM t1 WHERE a = ? AND b IN (?, ?, ?);52 } 53 54 5 1 {55 CREATE UNIQUE INDEX i1 ON t1(a, b, c);56 } {57 SELECT * FROM t1 WHERE a = ? AND b = ?58 } 59 60 6 0 {61 } {62 SELECT * FROM t1 WHERE c IN (SELECT z FROM t2)63 } 64 65 7 0 {66 } {67 SELECT * FROM t1 WHERE (a, c) IN (SELECT z, x FROM t2)68 } 69 70 8 1 {71 } {72 SELECT * FROM t1 WHERE a IN (SELECT z FROM t2)73 } 74 75 9 1 {76 CREATE UNIQUE INDEX i1 ON t1(a, b);77 } {78 SELECT * FROM t1 WHERE a IN (SELECT z FROM t2) AND b IS ?79 } 80 10 0 {81 CREATE UNIQUE INDEX i1 ON t1(a, b);82 } {83 SELECT * FROM t1 WHERE a IN (SELECT z FROM t2) AND b = ?84 } 85 11 1 {86 CREATE UNIQUE INDEX i1 ON t1(a, b);87 } {88 SELECT * FROM t1 WHERE a IS NULL AND b IN (SELECT z FROM t2)89 } 90 12 0 {91 CREATE UNIQUE INDEX i1 ON t1(a, b);92 } {93 SELECT * FROM t1 WHERE a = ? AND b IN (SELECT z FROM t2)94 } 95} {96 do_test 1.1.$tn {97 execsql BEGIN98 execsql $idx99 100 catch { array unset root_to_tbl }101 catch { array unset csr_to_root }102 103 db eval {SELECT rootpage, tbl_name FROM sqlite_schema} {104 set root_to_tbl($rootpage) $tbl_name105 }106 107 set nSeen 0108 db eval "explain $sql" {109 if {$opcode=="OpenRead"} {110 set csr_to_root($p1) $p2111 }112 if {$opcode=="Next"} {113 catch {114 set root $csr_to_root($p1)115 set tbl $root_to_tbl($root)116 if {$tbl=="t1"} {incr nSeen}117 }118 }119 }120 121 execsql ROLLBACK122 123 set nSeen124 } $nNext125}126 127#-------------------------------------------------------------------------128reset_db129do_execsql_test 2.0 {130 CREATE TABLE t1(a TEXT PRIMARY KEY, b TEXT) WITHOUT ROWID;131 INSERT INTO t1 VALUES('1', 'one');132 INSERT INTO t1 VALUES('2', NULL);133 INSERT INTO t1 VALUES('3', 'three');134}135 136do_execsql_test 2.1 {137 SELECT b FROM t1 WHERE a IN (1,2,3) ORDER BY b ASC NULLS LAST;138} {one three {}}139 140#-------------------------------------------------------------------------141reset_db142do_execsql_test 3.0 {143 CREATE TABLE x1(a);144 INSERT INTO x1 VALUES(1), (2), (3);145 146 CREATE TABLE x2(b);147 INSERT INTO x2 VALUES(4), (5), (6);148 149 CREATE TABLE t1(u);150 INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6);151 152 CREATE VIEW v1 AS SELECT u FROM t1 WHERE u IN (153 SELECT a FROM x1154 );155 CREATE VIEW v2 AS SELECT u FROM t1 WHERE u IN (156 SELECT b FROM x2157 );158}159 160do_execsql_test 3.1 {161 SELECT * FROM v1162} {163 1 2 3164}165 166do_execsql_test 3.2 {167 SELECT * FROM v2168} {169 4 5 6 170}171 172do_execsql_test 3.3 {173 SELECT * FROM v2174 UNION ALL175 SELECT * FROM v1176} {177 4 5 6178 1 2 3 179}180 181do_execsql_test 3.4 {182 WITH w1 AS (183 SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3184 ),185 w2 AS (186 SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6187 )188 SELECT * FROM v1 WHERE u IN w1189 UNION ALL190 SELECT * FROM v2 WHERE u IN w2191} {192 1 2 3 4 5 6193}194 195# 2024-11-20 https://sqlite.org/forum/forumpost/0b9ded2f8428ac00196#197# Bug in SubrtnSig logic. If a SELECT statement is copied and the copy198# is subsequently modified, we need to change the Select.selId on the199# copy so that when the copy is used to generate code, the SubrtnSig200# logic won't try to substitute the original SELECT in place of the201# copy which is now different.202#203do_execsql_test 3.5 {204 DROP TABLE IF EXISTS t1;205 DROP TABLE IF EXISTS t2;206 CREATE TABLE t1 (a int UNIQUE);207 CREATE TABLE t2 (b int UNIQUE);208 INSERT INTO t1 VALUES (1);209 INSERT INTO t2 VALUES (1), (2);210 SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) = (1, 1);211} {1 1}212do_execsql_test 3.6 {213 SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 1));214} {1 1}215do_execsql_test 3.7 {216 SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) = (1, 2);217} {1 2}218do_execsql_test 3.8 {219 SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 2));220} {1 2}221 222# 2025-01-30 Inifinite loop in byte-code discovered by dbsqlfuzz223# having to do with SubrtnSig logic. The code was using a Subroutine224# from within itself resulting in infinite recursion.225#226# This test will spin forever if the bug has not been fixed, or if227# it reappears.228#229reset_db230do_execsql_test 4.0 {231 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);232 INSERT INTO t1 VALUES(1,x'1111');233 CREATE TABLE t2(c);234 CREATE TABLE t3(d);235 CREATE TRIGGER t1tr UPDATE ON t1 BEGIN236 UPDATE t1 SET b=x'2222' FROM t2;237 UPDATE t1238 SET b = (SELECT a IN (SELECT a239 FROM t1240 WHERE (b,a) IN (SELECT rowid, d241 FROM t3242 )243 )244 FROM t1 NATURAL RIGHT JOIN t1245 );246 END;247 UPDATE t1 SET b=x'3333';248 SELECT quote(b) FROM t1;249} {X'3333'}250 251finish_test252