SQLite

Check-in [d91e57e49f]
Login

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

Overview
Comment:Add another test case for bug [5e3c8867].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d91e57e49f23414ec9211b775eb11cd6230a4f96
User & Date: dan 2016-03-09 15:14:54.990
Context
2016-03-09
15:34
Slight simplification to the query optimizer logic associated with IN (SELECT). (check-in: 2c55c3c295 user: drh tags: trunk)
15:14
Add another test case for bug [5e3c8867]. (check-in: d91e57e49f user: dan tags: trunk)
15:09
When optimizing expressions of the form "x IN (SELECT ...)" make sure that the subquery is not correlated. Fix for ticket [5e3c886796e5512]. (check-in: 1ed6b06ea3 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/in5.test.
178
179
180
181
182
183
184



































185
186
do_execsql_test 6.3.1 {
  CREATE TABLE x1(a);
  CREATE TABLE x2(b);
  INSERT INTO x1 VALUES(1), (1), (2);
  INSERT INTO x2 VALUES(1), (2);
  SELECT count(*) FROM x2 WHERE b IN (SELECT DISTINCT a FROM x1 LIMIT 2);
} {2}




































finish_test







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


178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
do_execsql_test 6.3.1 {
  CREATE TABLE x1(a);
  CREATE TABLE x2(b);
  INSERT INTO x1 VALUES(1), (1), (2);
  INSERT INTO x2 VALUES(1), (2);
  SELECT count(*) FROM x2 WHERE b IN (SELECT DISTINCT a FROM x1 LIMIT 2);
} {2}

#-------------------------------------------------------------------------
# Test to confirm that bug [5e3c886796e5] is fixed.
#
do_execsql_test 7.1 {
  CREATE TABLE y1(a, b);
  CREATE TABLE y2(c);

  INSERT INTO y1 VALUES(1,     'one');
  INSERT INTO y1 VALUES('two', 'two');
  INSERT INTO y1 VALUES(3,     'three');

  INSERT INTO y2 VALUES('one');
  INSERT INTO y2 VALUES('two');
  INSERT INTO y2 VALUES('three');
} {}

do_execsql_test 7.2.1 {
  SELECT a FROM y1 WHERE b NOT IN (SELECT a FROM y2);
} {1 3}
do_execsql_test 7.2.2 {
  SELECT a FROM y1 WHERE b IN (SELECT a FROM y2);
} {two}

do_execsql_test 7.3.1 {
  CREATE INDEX y2c ON y2(c);
  SELECT a FROM y1 WHERE b NOT IN (SELECT a FROM y2);
} {1 3}
do_execsql_test 7.3.2 {
  SELECT a FROM y1 WHERE b IN (SELECT a FROM y2);
} {two}

finish_test



finish_test