Debian Codegraph @

test/in7.test

raw
1 # 2024-05-01
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix in7
17
18 do_execsql_test 1.0 {
19 CREATE TABLE t1(a, b, c PRIMARY KEY);
20 CREATE TABLE t2(x, y, z);
21 }
22
23 foreach {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 BEGIN
98 execsql $idx
99
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_name
105 }
106
107 set nSeen 0
108 db eval "explain $sql" {
109 if {$opcode=="OpenRead"} {
110 set csr_to_root($p1) $p2
111 }
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 ROLLBACK
122
123 set nSeen
124 } $nNext
125 }
126
127 #-------------------------------------------------------------------------
128 reset_db
129 do_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
136 do_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 #-------------------------------------------------------------------------
141 reset_db
142 do_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 x1
154 );
155 CREATE VIEW v2 AS SELECT u FROM t1 WHERE u IN (
156 SELECT b FROM x2
157 );
158 }
159
160 do_execsql_test 3.1 {
161 SELECT * FROM v1
162 } {
163 1 2 3
164 }
165
166 do_execsql_test 3.2 {
167 SELECT * FROM v2
168 } {
169 4 5 6
170 }
171
172 do_execsql_test 3.3 {
173 SELECT * FROM v2
174 UNION ALL
175 SELECT * FROM v1
176 } {
177 4 5 6
178 1 2 3
179 }
180
181 do_execsql_test 3.4 {
182 WITH w1 AS (
183 SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
184 ),
185 w2 AS (
186 SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
187 )
188 SELECT * FROM v1 WHERE u IN w1
189 UNION ALL
190 SELECT * FROM v2 WHERE u IN w2
191 } {
192 1 2 3 4 5 6
193 }
194
195 # 2024-11-20 https://sqlite.org/forum/forumpost/0b9ded2f8428ac00
196 #
197 # Bug in SubrtnSig logic. If a SELECT statement is copied and the copy
198 # is subsequently modified, we need to change the Select.selId on the
199 # copy so that when the copy is used to generate code, the SubrtnSig
200 # logic won't try to substitute the original SELECT in place of the
201 # copy which is now different.
202 #
203 do_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}
212 do_execsql_test 3.6 {
213 SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 1));
214 } {1 1}
215 do_execsql_test 3.7 {
216 SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) = (1, 2);
217 } {1 2}
218 do_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 dbsqlfuzz
223 # having to do with SubrtnSig logic. The code was using a Subroutine
224 # from within itself resulting in infinite recursion.
225 #
226 # This test will spin forever if the bug has not been fixed, or if
227 # it reappears.
228 #
229 reset_db
230 do_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 BEGIN
236 UPDATE t1 SET b=x'2222' FROM t2;
237 UPDATE t1
238 SET b = (SELECT a IN (SELECT a
239 FROM t1
240 WHERE (b,a) IN (SELECT rowid, d
241 FROM t3
242 )
243 )
244 FROM t1 NATURAL RIGHT JOIN t1
245 );
246 END;
247 UPDATE t1 SET b=x'3333';
248 SELECT quote(b) FROM t1;
249 } {X'3333'}
250
251 finish_test