From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Thu, 16 Oct 2014 22:51:35 -0400 Subject: Imported Upstream version 3.2.0 --- test/in4.test | 177 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 177 insertions(+) (limited to 'test/in4.test') diff --git a/test/in4.test b/test/in4.test index 470f4f0..a89961f 100644 --- a/test/in4.test +++ b/test/in4.test @@ -159,4 +159,181 @@ do_test in4-3.12 { execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()} } {} +# Tests for "... IN (?)" and "... NOT IN (?)". In other words, tests +# for when the RHS of IN is a single expression. This should work the +# same as the == and <> operators. +# +do_execsql_test in4-3.21 { + SELECT * FROM t3 WHERE x=10 AND y IN (10); +} {10 10 10} +do_execsql_test in4-3.22 { + SELECT * FROM t3 WHERE x IN (10) AND y=10; +} {10 10 10} +do_execsql_test in4-3.23 { + SELECT * FROM t3 WHERE x IN (10) AND y IN (10); +} {10 10 10} +do_execsql_test in4-3.24 { + SELECT * FROM t3 WHERE x=1 AND y NOT IN (10); +} {1 1 1} +do_execsql_test in4-3.25 { + SELECT * FROM t3 WHERE x NOT IN (10) AND y=1; +} {1 1 1} +do_execsql_test in4-3.26 { + SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10); +} {1 1 1} + +# The query planner recognizes that "x IN (?)" only generates a +# single match and can use this information to optimize-out ORDER BY +# clauses. +# +do_execsql_test in4-3.31 { + DROP INDEX t3i1; + CREATE UNIQUE INDEX t3xy ON t3(x,y); + + SELECT *, '|' FROM t3 A, t3 B + WHERE A.x=10 AND A.y IN (10) + AND B.x=1 AND B.y IN (1); +} {10 10 10 1 1 1 |} +do_execsql_test in4-3.32 { + EXPLAIN QUERY PLAN + SELECT *, '|' FROM t3 A, t3 B + WHERE A.x=10 AND A.y IN (10) + AND B.x=1 AND B.y IN (1); +} {~/B-TREE/} ;# No separate sorting pass +do_execsql_test in4-3.33 { + SELECT *, '|' FROM t3 A, t3 B + WHERE A.x IN (10) AND A.y=10 + AND B.x IN (1) AND B.y=1; +} {10 10 10 1 1 1 |} +do_execsql_test in4-3.34 { + EXPLAIN QUERY PLAN + SELECT *, '|' FROM t3 A, t3 B + WHERE A.x IN (10) AND A.y=10 + AND B.x IN (1) AND B.y=1; +} {~/B-TREE/} ;# No separate sorting pass + +# An expression of the form "x IN (?,?)" creates an ephemeral table to +# hold the list of values on the RHS. But "x IN (?)" does not create +# an ephemeral table. +# +do_execsql_test in4-3.41 { + SELECT * FROM t3 WHERE x IN (10,11); +} {10 10 10} +do_execsql_test in4-3.42 { + EXPLAIN + SELECT * FROM t3 WHERE x IN (10,11); +} {/OpenEphemeral/} +do_execsql_test in4-3.43 { + SELECT * FROM t3 WHERE x IN (10); +} {10 10 10} +do_execsql_test in4-3.44 { + EXPLAIN + SELECT * FROM t3 WHERE x IN (10); +} {~/OpenEphemeral/} +do_execsql_test in4-3.45 { + SELECT * FROM t3 WHERE x NOT IN (10,11,99999); +} {1 1 1} +do_execsql_test in4-3.46 { + EXPLAIN + SELECT * FROM t3 WHERE x NOT IN (10,11,99999); +} {/OpenEphemeral/} +do_execsql_test in4-3.47 { + SELECT * FROM t3 WHERE x NOT IN (10); +} {1 1 1} +do_execsql_test in4-3.48 { + EXPLAIN + SELECT * FROM t3 WHERE x NOT IN (10); +} {~/OpenEphemeral/} + +# Make sure that when "x IN (?)" is converted into "x==?" that collating +# sequence and affinity computations do not get messed up. +# +do_execsql_test in4-4.1 { + CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c); + INSERT INTO t4a VALUES('ABC','abc',1); + INSERT INTO t4a VALUES('def','xyz',2); + INSERT INTO t4a VALUES('ghi','ghi',3); + SELECT c FROM t4a WHERE a=b ORDER BY c; +} {3} +do_execsql_test in4-4.2 { + SELECT c FROM t4a WHERE b=a ORDER BY c; +} {1 3} +do_execsql_test in4-4.3 { + SELECT c FROM t4a WHERE (a||'')=b ORDER BY c; +} {1 3} +do_execsql_test in4-4.4 { + SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c; +} {3} +do_execsql_test in4-4.5 { + SELECT c FROM t4a WHERE a IN (b) ORDER BY c; +} {3} +do_execsql_test in4-4.6 { + SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c; +} {3} + + +do_execsql_test in4-4.11 { + CREATE TABLE t4b(a TEXT, b NUMERIC, c); + INSERT INTO t4b VALUES('1.0',1,4); + SELECT c FROM t4b WHERE a=b; +} {4} +do_execsql_test in4-4.12 { + SELECT c FROM t4b WHERE b=a; +} {4} +do_execsql_test in4-4.13 { + SELECT c FROM t4b WHERE +a=b; +} {4} +do_execsql_test in4-4.14 { + SELECT c FROM t4b WHERE a=+b; +} {} +do_execsql_test in4-4.15 { + SELECT c FROM t4b WHERE +b=a; +} {} +do_execsql_test in4-4.16 { + SELECT c FROM t4b WHERE b=+a; +} {4} +do_execsql_test in4-4.17 { + SELECT c FROM t4b WHERE a IN (b); +} {} +do_execsql_test in4-4.18 { + SELECT c FROM t4b WHERE b IN (a); +} {4} +do_execsql_test in4-4.19 { + SELECT c FROM t4b WHERE +b IN (a); +} {} + +do_execsql_test in4-5.1 { + CREATE TABLE t5(c INTEGER PRIMARY KEY, d TEXT COLLATE nocase); + INSERT INTO t5 VALUES(17, 'fuzz'); + SELECT 1 FROM t5 WHERE 'fuzz' IN (d); -- match + SELECT 2 FROM t5 WHERE 'FUZZ' IN (d); -- no match + SELECT 3 FROM t5 WHERE d IN ('fuzz'); -- match + SELECT 4 FROM t5 WHERE d IN ('FUZZ'); -- match +} {1 3 4} + +# An expression of the form "x IN (y)" can be used as "x=y" by the +# query planner when computing transitive constraints or to run the +# query using an index on y. +# +do_execsql_test in4-6.1 { + CREATE TABLE t6a(a INTEGER PRIMARY KEY, b); + INSERT INTO t6a VALUES(1,2),(3,4),(5,6); + CREATE TABLE t6b(c INTEGER PRIMARY KEY, d); + INSERT INTO t6b VALUES(4,44),(5,55),(6,66); + + SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); +} {3 4 4 44} +do_execsql_test in4-6.1-eqp { + EXPLAIN QUERY PLAN + SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); +} {~/SCAN/} +do_execsql_test in4-6.2 { + SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); +} {3 4 4 44} +do_execsql_test in4-6.2-eqp { + EXPLAIN QUERY PLAN + SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); +} {~/SCAN/} + + finish_test -- cgit v1.2.3