SQLite

Check-in [07817efc]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test cases to in7.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | reuse-subqueries
Files: files | file ages | folders
SHA3-256: 07817efc10a6ab59dcb18ad218a3bb5a5d49a724c51296d5c8d7e386b0e0c789
User & Date: dan 2024-07-04 18:56:26
Context
2024-07-05
01:05
Small performance optimizations. (Closed-Leaf check-in: 99fd34b5 user: drh tags: reuse-subqueries)
2024-07-04
18:56
Add test cases to in7.test. (check-in: 07817efc user: dan tags: reuse-subqueries)
18:26
Disable the reuse of IN-clause subqueries if the subquery is an explicit "SELECT ALL". The ALL keyword is almost never used in actual practice (most developers don't even know it can be used) so this should not interfere with the optimization, but it does give us a convenient way to turn it off for testing purposes. (check-in: a81299be user: drh tags: reuse-subqueries)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/in7.test.

︙︙
132
133
134
135
136
137
138
























































139
140
141

  INSERT INTO t1 VALUES('2', NULL);
  INSERT INTO t1 VALUES('3', 'three');
}

do_execsql_test 2.1 {
  SELECT b FROM t1 WHERE a IN (1,2,3) ORDER BY b ASC NULLS LAST;
} {one three {}}


























































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197

  INSERT INTO t1 VALUES('2', NULL);
  INSERT INTO t1 VALUES('3', 'three');
}

do_execsql_test 2.1 {
  SELECT b FROM t1 WHERE a IN (1,2,3) ORDER BY b ASC NULLS LAST;
} {one three {}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE x1(a);
  INSERT INTO x1 VALUES(1), (2), (3);

  CREATE TABLE x2(b);
  INSERT INTO x2 VALUES(4), (5), (6);

  CREATE TABLE t1(u);
  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6);

  CREATE VIEW v1 AS SELECT u FROM t1 WHERE u IN (
    SELECT a FROM x1
  );
  CREATE VIEW v2 AS SELECT u FROM t1 WHERE u IN (
    SELECT b FROM x2
  );
}

do_execsql_test 3.1 {
  SELECT * FROM v1
} {
  1 2 3
}

do_execsql_test 3.2 {
  SELECT * FROM v2
} {
  4 5 6 
}

do_execsql_test 3.3 {
  SELECT * FROM v2
  UNION ALL
  SELECT * FROM v1
} {
  4 5 6
  1 2 3 
}

do_execsql_test 3.4 {
  WITH w1 AS (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
  ),
  w2 AS (
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
  )
  SELECT * FROM v1 WHERE u IN w1
  UNION ALL
  SELECT * FROM v2 WHERE u IN w2
} {
  1 2 3 4 5 6
}



finish_test