1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/in.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,580 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the IN and BETWEEN operator.
1.16 +#
1.17 +# $Id: in.test,v 1.22 2008/08/04 03:51:24 danielk1977 Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Generate the test data we will need for the first squences of tests.
1.23 +#
1.24 +do_test in-1.0 {
1.25 + execsql {
1.26 + BEGIN;
1.27 + CREATE TABLE t1(a int, b int);
1.28 + }
1.29 + for {set i 1} {$i<=10} {incr i} {
1.30 + execsql "INSERT INTO t1 VALUES($i,[expr {1<<$i}])"
1.31 + }
1.32 + execsql {
1.33 + COMMIT;
1.34 + SELECT count(*) FROM t1;
1.35 + }
1.36 +} {10}
1.37 +
1.38 +# Do basic testing of BETWEEN.
1.39 +#
1.40 +do_test in-1.1 {
1.41 + execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
1.42 +} {4 5}
1.43 +do_test in-1.2 {
1.44 + execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}
1.45 +} {1 2 3 6 7 8 9 10}
1.46 +do_test in-1.3 {
1.47 + execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}
1.48 +} {1 2 3 4}
1.49 +do_test in-1.4 {
1.50 + execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}
1.51 +} {5 6 7 8 9 10}
1.52 +do_test in-1.6 {
1.53 + execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}
1.54 +} {1 2 3 4 9}
1.55 +do_test in-1.7 {
1.56 + execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}
1.57 +} {101 102 103 4 5 6 7 8 9 10}
1.58 +
1.59 +# The rest of this file concentrates on testing the IN operator.
1.60 +# Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY
1.61 +# (because the IN operator is unavailable).
1.62 +#
1.63 +ifcapable !subquery {
1.64 + finish_test
1.65 + return
1.66 +}
1.67 +
1.68 +# Testing of the IN operator using static lists on the right-hand side.
1.69 +#
1.70 +do_test in-2.1 {
1.71 + execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}
1.72 +} {3 4 5}
1.73 +do_test in-2.2 {
1.74 + execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}
1.75 +} {1 2 6 7 8 9 10}
1.76 +do_test in-2.3 {
1.77 + execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}
1.78 +} {3 4 5 9}
1.79 +do_test in-2.4 {
1.80 + execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}
1.81 +} {1 2 6 7 8 9 10}
1.82 +do_test in-2.5 {
1.83 + execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}
1.84 +} {1 2 103 104 5 6 7 8 9 10}
1.85 +
1.86 +do_test in-2.6 {
1.87 + execsql {SELECT a FROM t1 WHERE b IN (b+8,64)}
1.88 +} {6}
1.89 +do_test in-2.7 {
1.90 + execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}
1.91 +} {4 5 6 7 8 9 10}
1.92 +do_test in-2.8 {
1.93 + execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}
1.94 +} {4 5}
1.95 +do_test in-2.9 {
1.96 + execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}
1.97 +} {}
1.98 +do_test in-2.10 {
1.99 + execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}
1.100 +} {}
1.101 +do_test in-2.11 {
1.102 + set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
1.103 + lappend v $msg
1.104 +} {1 {no such column: c}}
1.105 +
1.106 +# Testing the IN operator where the right-hand side is a SELECT
1.107 +#
1.108 +do_test in-3.1 {
1.109 + execsql {
1.110 + SELECT a FROM t1
1.111 + WHERE b IN (SELECT b FROM t1 WHERE a<5)
1.112 + ORDER BY a
1.113 + }
1.114 +} {1 2 3 4}
1.115 +do_test in-3.2 {
1.116 + execsql {
1.117 + SELECT a FROM t1
1.118 + WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512
1.119 + ORDER BY a
1.120 + }
1.121 +} {1 2 3 4 9}
1.122 +do_test in-3.3 {
1.123 + execsql {
1.124 + SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b
1.125 + }
1.126 +} {101 102 103 104 5 6 7 8 9 10}
1.127 +
1.128 +# Make sure the UPDATE and DELETE commands work with IN-SELECT
1.129 +#
1.130 +do_test in-4.1 {
1.131 + execsql {
1.132 + UPDATE t1 SET b=b*2
1.133 + WHERE b IN (SELECT b FROM t1 WHERE a>8)
1.134 + }
1.135 + execsql {SELECT b FROM t1 ORDER BY b}
1.136 +} {2 4 8 16 32 64 128 256 1024 2048}
1.137 +do_test in-4.2 {
1.138 + execsql {
1.139 + DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)
1.140 + }
1.141 + execsql {SELECT a FROM t1 ORDER BY a}
1.142 +} {1 2 3 4 5 6 7 8}
1.143 +do_test in-4.3 {
1.144 + execsql {
1.145 + DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)
1.146 + }
1.147 + execsql {SELECT a FROM t1 ORDER BY a}
1.148 +} {5 6 7 8}
1.149 +
1.150 +# Do an IN with a constant RHS but where the RHS has many, many
1.151 +# elements. We need to test that collisions in the hash table
1.152 +# are resolved properly.
1.153 +#
1.154 +do_test in-5.1 {
1.155 + execsql {
1.156 + INSERT INTO t1 VALUES('hello', 'world');
1.157 + SELECT * FROM t1
1.158 + WHERE a IN (
1.159 + 'Do','an','IN','with','a','constant','RHS','but','where','the',
1.160 + 'has','many','elements','We','need','to','test','that',
1.161 + 'collisions','hash','table','are','resolved','properly',
1.162 + 'This','in-set','contains','thirty','one','entries','hello');
1.163 + }
1.164 +} {hello world}
1.165 +
1.166 +# Make sure the IN operator works with INTEGER PRIMARY KEY fields.
1.167 +#
1.168 +do_test in-6.1 {
1.169 + execsql {
1.170 + CREATE TABLE ta(a INTEGER PRIMARY KEY, b);
1.171 + INSERT INTO ta VALUES(1,1);
1.172 + INSERT INTO ta VALUES(2,2);
1.173 + INSERT INTO ta VALUES(3,3);
1.174 + INSERT INTO ta VALUES(4,4);
1.175 + INSERT INTO ta VALUES(6,6);
1.176 + INSERT INTO ta VALUES(8,8);
1.177 + INSERT INTO ta VALUES(10,
1.178 + 'This is a key that is long enough to require a malloc in the VDBE');
1.179 + SELECT * FROM ta WHERE a<10;
1.180 + }
1.181 +} {1 1 2 2 3 3 4 4 6 6 8 8}
1.182 +do_test in-6.2 {
1.183 + execsql {
1.184 + CREATE TABLE tb(a INTEGER PRIMARY KEY, b);
1.185 + INSERT INTO tb VALUES(1,1);
1.186 + INSERT INTO tb VALUES(2,2);
1.187 + INSERT INTO tb VALUES(3,3);
1.188 + INSERT INTO tb VALUES(5,5);
1.189 + INSERT INTO tb VALUES(7,7);
1.190 + INSERT INTO tb VALUES(9,9);
1.191 + INSERT INTO tb VALUES(11,
1.192 + 'This is a key that is long enough to require a malloc in the VDBE');
1.193 + SELECT * FROM tb WHERE a<10;
1.194 + }
1.195 +} {1 1 2 2 3 3 5 5 7 7 9 9}
1.196 +do_test in-6.3 {
1.197 + execsql {
1.198 + SELECT a FROM ta WHERE b IN (SELECT a FROM tb);
1.199 + }
1.200 +} {1 2 3}
1.201 +do_test in-6.4 {
1.202 + execsql {
1.203 + SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);
1.204 + }
1.205 +} {4 6 8 10}
1.206 +do_test in-6.5 {
1.207 + execsql {
1.208 + SELECT a FROM ta WHERE b IN (SELECT b FROM tb);
1.209 + }
1.210 +} {1 2 3 10}
1.211 +do_test in-6.6 {
1.212 + execsql {
1.213 + SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);
1.214 + }
1.215 +} {4 6 8}
1.216 +do_test in-6.7 {
1.217 + execsql {
1.218 + SELECT a FROM ta WHERE a IN (SELECT a FROM tb);
1.219 + }
1.220 +} {1 2 3}
1.221 +do_test in-6.8 {
1.222 + execsql {
1.223 + SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);
1.224 + }
1.225 +} {4 6 8 10}
1.226 +do_test in-6.9 {
1.227 + execsql {
1.228 + SELECT a FROM ta WHERE a IN (SELECT b FROM tb);
1.229 + }
1.230 +} {1 2 3}
1.231 +do_test in-6.10 {
1.232 + execsql {
1.233 + SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);
1.234 + }
1.235 +} {4 6 8 10}
1.236 +
1.237 +# Tests of IN operator against empty sets. (Ticket #185)
1.238 +#
1.239 +do_test in-7.1 {
1.240 + execsql {
1.241 + SELECT a FROM t1 WHERE a IN ();
1.242 + }
1.243 +} {}
1.244 +do_test in-7.2 {
1.245 + execsql {
1.246 + SELECT a FROM t1 WHERE a IN (5);
1.247 + }
1.248 +} {5}
1.249 +do_test in-7.3 {
1.250 + execsql {
1.251 + SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;
1.252 + }
1.253 +} {5 6 7 8 hello}
1.254 +do_test in-7.4 {
1.255 + execsql {
1.256 + SELECT a FROM t1 WHERE a IN (5) AND b IN ();
1.257 + }
1.258 +} {}
1.259 +do_test in-7.5 {
1.260 + execsql {
1.261 + SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();
1.262 + }
1.263 +} {5}
1.264 +do_test in-7.6 {
1.265 + execsql {
1.266 + SELECT a FROM ta WHERE a IN ();
1.267 + }
1.268 +} {}
1.269 +do_test in-7.7 {
1.270 + execsql {
1.271 + SELECT a FROM ta WHERE a NOT IN ();
1.272 + }
1.273 +} {1 2 3 4 6 8 10}
1.274 +
1.275 +do_test in-8.1 {
1.276 + execsql {
1.277 + SELECT b FROM t1 WHERE a IN ('hello','there')
1.278 + }
1.279 +} {world}
1.280 +do_test in-8.2 {
1.281 + execsql {
1.282 + SELECT b FROM t1 WHERE a IN ("hello",'there')
1.283 + }
1.284 +} {world}
1.285 +
1.286 +# Test constructs of the form: expr IN tablename
1.287 +#
1.288 +do_test in-9.1 {
1.289 + execsql {
1.290 + CREATE TABLE t4 AS SELECT a FROM tb;
1.291 + SELECT * FROM t4;
1.292 + }
1.293 +} {1 2 3 5 7 9 11}
1.294 +do_test in-9.2 {
1.295 + execsql {
1.296 + SELECT b FROM t1 WHERE a IN t4;
1.297 + }
1.298 +} {32 128}
1.299 +do_test in-9.3 {
1.300 + execsql {
1.301 + SELECT b FROM t1 WHERE a NOT IN t4;
1.302 + }
1.303 +} {64 256 world}
1.304 +do_test in-9.4 {
1.305 + catchsql {
1.306 + SELECT b FROM t1 WHERE a NOT IN tb;
1.307 + }
1.308 +} {1 {only a single result allowed for a SELECT that is part of an expression}}
1.309 +
1.310 +# IN clauses in CHECK constraints. Ticket #1645
1.311 +#
1.312 +do_test in-10.1 {
1.313 + execsql {
1.314 + CREATE TABLE t5(
1.315 + a INTEGER,
1.316 + CHECK( a IN (111,222,333) )
1.317 + );
1.318 + INSERT INTO t5 VALUES(111);
1.319 + SELECT * FROM t5;
1.320 + }
1.321 +} {111}
1.322 +do_test in-10.2 {
1.323 + catchsql {
1.324 + INSERT INTO t5 VALUES(4);
1.325 + }
1.326 +} {1 {constraint failed}}
1.327 +
1.328 +# Ticket #1821
1.329 +#
1.330 +# Type affinity applied to the right-hand side of an IN operator.
1.331 +#
1.332 +do_test in-11.1 {
1.333 + execsql {
1.334 + CREATE TABLE t6(a,b NUMERIC);
1.335 + INSERT INTO t6 VALUES(1,2);
1.336 + INSERT INTO t6 VALUES(2,3);
1.337 + SELECT * FROM t6 WHERE b IN (2);
1.338 + }
1.339 +} {1 2}
1.340 +do_test in-11.2 {
1.341 + # The '2' should be coerced into 2 because t6.b is NUMERIC
1.342 + execsql {
1.343 + SELECT * FROM t6 WHERE b IN ('2');
1.344 + }
1.345 +} {1 2}
1.346 +do_test in-11.3 {
1.347 + # No coercion should occur here because of the unary + before b.
1.348 + execsql {
1.349 + SELECT * FROM t6 WHERE +b IN ('2');
1.350 + }
1.351 +} {}
1.352 +do_test in-11.4 {
1.353 + # No coercion because column a as affinity NONE
1.354 + execsql {
1.355 + SELECT * FROM t6 WHERE a IN ('2');
1.356 + }
1.357 +} {}
1.358 +do_test in-11.5 {
1.359 + execsql {
1.360 + SELECT * FROM t6 WHERE a IN (2);
1.361 + }
1.362 +} {2 3}
1.363 +do_test in-11.6 {
1.364 + # No coercion because column a as affinity NONE
1.365 + execsql {
1.366 + SELECT * FROM t6 WHERE +a IN ('2');
1.367 + }
1.368 +} {}
1.369 +
1.370 +# Test error conditions with expressions of the form IN(<compound select>).
1.371 +#
1.372 +ifcapable compound {
1.373 +do_test in-12.1 {
1.374 + execsql {
1.375 + CREATE TABLE t2(a, b, c);
1.376 + CREATE TABLE t3(a, b, c);
1.377 + }
1.378 +} {}
1.379 +do_test in-12.2 {
1.380 + catchsql {
1.381 + SELECT * FROM t2 WHERE a IN (
1.382 + SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
1.383 + );
1.384 + }
1.385 +} {1 {only a single result allowed for a SELECT that is part of an expression}}
1.386 +do_test in-12.3 {
1.387 + catchsql {
1.388 + SELECT * FROM t2 WHERE a IN (
1.389 + SELECT a, b FROM t3 UNION SELECT a, b FROM t2
1.390 + );
1.391 + }
1.392 +} {1 {only a single result allowed for a SELECT that is part of an expression}}
1.393 +do_test in-12.4 {
1.394 + catchsql {
1.395 + SELECT * FROM t2 WHERE a IN (
1.396 + SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2
1.397 + );
1.398 + }
1.399 +} {1 {only a single result allowed for a SELECT that is part of an expression}}
1.400 +do_test in-12.5 {
1.401 + catchsql {
1.402 + SELECT * FROM t2 WHERE a IN (
1.403 + SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2
1.404 + );
1.405 + }
1.406 +} {1 {only a single result allowed for a SELECT that is part of an expression}}
1.407 +do_test in-12.6 {
1.408 + catchsql {
1.409 + SELECT * FROM t2 WHERE a IN (
1.410 + SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
1.411 + );
1.412 + }
1.413 +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
1.414 +do_test in-12.7 {
1.415 + catchsql {
1.416 + SELECT * FROM t2 WHERE a IN (
1.417 + SELECT a FROM t3 UNION SELECT a, b FROM t2
1.418 + );
1.419 + }
1.420 +} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
1.421 +do_test in-12.8 {
1.422 + catchsql {
1.423 + SELECT * FROM t2 WHERE a IN (
1.424 + SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
1.425 + );
1.426 + }
1.427 +} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
1.428 +do_test in-12.9 {
1.429 + catchsql {
1.430 + SELECT * FROM t2 WHERE a IN (
1.431 + SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
1.432 + );
1.433 + }
1.434 +} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
1.435 +}
1.436 +
1.437 +
1.438 +#------------------------------------------------------------------------
1.439 +# The following tests check that NULL is handled correctly when it
1.440 +# appears as part of a set of values on the right-hand side of an
1.441 +# IN or NOT IN operator.
1.442 +#
1.443 +# When it appears in such a set, NULL is handled as an "unknown value".
1.444 +# If, because of the unknown value in the set, the result of the expression
1.445 +# cannot be determined, then it itself evaluates to NULL.
1.446 +#
1.447 +
1.448 +# Warm body test to demonstrate the principles being tested:
1.449 +#
1.450 +do_test in-13.1 {
1.451 + db nullvalue "null"
1.452 + execsql { SELECT
1.453 + 1 IN (NULL, 1, 2), -- The value 1 is a member of the set, return true.
1.454 + 3 IN (NULL, 1, 2), -- Ambiguous, return NULL.
1.455 + 1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
1.456 + 3 NOT IN (NULL, 1, 2) -- Ambiguous, return NULL.
1.457 + }
1.458 +} {1 null 0 null}
1.459 +
1.460 +do_test in-13.2 {
1.461 + execsql {
1.462 + CREATE TABLE t7(a, b, c NOT NULL);
1.463 + INSERT INTO t7 VALUES(1, 1, 1);
1.464 + INSERT INTO t7 VALUES(2, 2, 2);
1.465 + INSERT INTO t7 VALUES(3, 3, 3);
1.466 + INSERT INTO t7 VALUES(NULL, 4, 4);
1.467 + INSERT INTO t7 VALUES(NULL, 5, 5);
1.468 + }
1.469 +} {}
1.470 +
1.471 +do_test in-13.3 {
1.472 + execsql { SELECT 2 IN (SELECT a FROM t7) }
1.473 +} {1}
1.474 +do_test in-13.4 {
1.475 + execsql { SELECT 6 IN (SELECT a FROM t7) }
1.476 +} {null}
1.477 +
1.478 +do_test in-13.5 {
1.479 + execsql { SELECT 2 IN (SELECT b FROM t7) }
1.480 +} {1}
1.481 +do_test in-13.6 {
1.482 + execsql { SELECT 6 IN (SELECT b FROM t7) }
1.483 +} {0}
1.484 +
1.485 +do_test in-13.7 {
1.486 + execsql { SELECT 2 IN (SELECT c FROM t7) }
1.487 +} {1}
1.488 +do_test in-13.8 {
1.489 + execsql { SELECT 6 IN (SELECT c FROM t7) }
1.490 +} {0}
1.491 +
1.492 +do_test in-13.9 {
1.493 + execsql {
1.494 + SELECT
1.495 + 2 NOT IN (SELECT a FROM t7),
1.496 + 6 NOT IN (SELECT a FROM t7),
1.497 + 2 NOT IN (SELECT b FROM t7),
1.498 + 6 NOT IN (SELECT b FROM t7),
1.499 + 2 NOT IN (SELECT c FROM t7),
1.500 + 6 NOT IN (SELECT c FROM t7)
1.501 + }
1.502 +} {0 null 0 1 0 1}
1.503 +
1.504 +do_test in-13.10 {
1.505 + execsql {
1.506 + SELECT b IN (
1.507 + SELECT inside.a
1.508 + FROM t7 AS inside
1.509 + WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
1.510 + )
1.511 + FROM t7 AS outside ORDER BY b;
1.512 + }
1.513 +} {0 null null null 0}
1.514 +
1.515 +do_test in-13.11 {
1.516 + execsql {
1.517 + SELECT b NOT IN (
1.518 + SELECT inside.a
1.519 + FROM t7 AS inside
1.520 + WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
1.521 + )
1.522 + FROM t7 AS outside ORDER BY b;
1.523 + }
1.524 +} {1 null null null 1}
1.525 +
1.526 +do_test in-13.12 {
1.527 + execsql {
1.528 + CREATE INDEX i1 ON t7(a);
1.529 + CREATE INDEX i2 ON t7(b);
1.530 + CREATE INDEX i3 ON t7(c);
1.531 + }
1.532 + execsql {
1.533 + SELECT
1.534 + 2 IN (SELECT a FROM t7),
1.535 + 6 IN (SELECT a FROM t7),
1.536 + 2 IN (SELECT b FROM t7),
1.537 + 6 IN (SELECT b FROM t7),
1.538 + 2 IN (SELECT c FROM t7),
1.539 + 6 IN (SELECT c FROM t7)
1.540 + }
1.541 +} {1 null 1 0 1 0}
1.542 +
1.543 +do_test in-13.13 {
1.544 + execsql {
1.545 + SELECT
1.546 + 2 NOT IN (SELECT a FROM t7),
1.547 + 6 NOT IN (SELECT a FROM t7),
1.548 + 2 NOT IN (SELECT b FROM t7),
1.549 + 6 NOT IN (SELECT b FROM t7),
1.550 + 2 NOT IN (SELECT c FROM t7),
1.551 + 6 NOT IN (SELECT c FROM t7)
1.552 + }
1.553 +} {0 null 0 1 0 1}
1.554 +
1.555 +do_test in-13.14 {
1.556 + execsql {
1.557 + BEGIN TRANSACTION;
1.558 + CREATE TABLE a(id INTEGER);
1.559 + INSERT INTO a VALUES(1);
1.560 + INSERT INTO a VALUES(2);
1.561 + INSERT INTO a VALUES(3);
1.562 + CREATE TABLE b(id INTEGER);
1.563 + INSERT INTO b VALUES(NULL);
1.564 + INSERT INTO b VALUES(3);
1.565 + INSERT INTO b VALUES(4);
1.566 + INSERT INTO b VALUES(5);
1.567 + COMMIT;
1.568 + SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
1.569 + }
1.570 +} {}
1.571 +do_test in-13.14 {
1.572 + execsql {
1.573 + CREATE INDEX i5 ON b(id);
1.574 + SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
1.575 + }
1.576 +} {}
1.577 +
1.578 +
1.579 +do_test in-13.X {
1.580 + db nullvalue ""
1.581 +} {}
1.582 +
1.583 +finish_test