From 63847496f14c813a5d80efd5b7de0f1294ffe1e3 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 16:07:11 +0200 Subject: Adding upstream version 3.45.1. Signed-off-by: Daniel Baumann --- test/whereD.test | 433 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 433 insertions(+) create mode 100644 test/whereD.test (limited to 'test/whereD.test') diff --git a/test/whereD.test b/test/whereD.test new file mode 100644 index 0000000..e727f47 --- /dev/null +++ b/test/whereD.test @@ -0,0 +1,433 @@ +# 2012 August 24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing that an index may be used as a covering +# index when there are OR expressions in the WHERE clause. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix whereD + +do_execsql_test 1.1 { + CREATE TABLE t(i,j,k,m,n); + CREATE INDEX ijk ON t(i,j,k); + CREATE INDEX jmn ON t(j,m,n); + + INSERT INTO t VALUES(3, 3, 'three', 3, 'tres'); + INSERT INTO t VALUES(2, 2, 'two', 2, 'dos'); + INSERT INTO t VALUES(1, 1, 'one', 1, 'uno'); + INSERT INTO t VALUES(4, 4, 'four', 4, 'cuatro'); +} + +do_execsql_test 1.2 { + SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); +} {one two} +do_execsql_test 1.3 { + SELECT k FROM t WHERE (i=1 AND j=1) OR (+i=2 AND j=2); +} {one two} +do_execsql_test 1.4 { + SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); +} {uno dos} +do_execsql_test 1.5 { + SELECT k, n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); +} {one uno two dos} +do_execsql_test 1.6 { + SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); +} {one two three} +do_execsql_test 1.7 { + SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); +} {uno dos tres} +do_execsql_test 1.8 { + SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2); +} {one two} +do_execsql_test 1.9 { + SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); +} {one two three} +do_execsql_test 1.10 { + SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); +} {uno dos tres} +do_execsql_test 1.11 { + SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); +} {one two three} +do_execsql_test 1.12 { + SELECT n FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); +} {uno dos tres} +do_execsql_test 1.13 { + SELECT k FROM t WHERE (j=1 AND m=1) OR (i=2 AND j=2) OR (i=3 AND j=3); +} {one two three} +do_execsql_test 1.14 { + SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND i=2) OR (i=3 AND j=3); +} {one two three} +do_execsql_test 1.15 { + SELECT k FROM t WHERE (i=1 AND j=2) OR (i=2 AND j=1) OR (i=3 AND j=4); +} {} +do_execsql_test 1.16 { + SELECT k FROM t WHERE (i=1 AND (j=1 or j=2)) OR (i=3 AND j=3); +} {one three} + +do_execsql_test 2.0 { + CREATE TABLE t1(a,b,c,d); + CREATE INDEX t1b ON t1(b); + CREATE INDEX t1c ON t1(c); + CREATE INDEX t1d ON t1(d); + CREATE TABLE t2(x,y); + CREATE INDEX t2y ON t2(y); + + INSERT INTO t1 VALUES(1,2,3,4); + INSERT INTO t1 VALUES(5,6,7,8); + INSERT INTO t2 VALUES(1,2); + INSERT INTO t2 VALUES(2,7); + INSERT INTO t2 VALUES(3,4); +} {} +do_execsql_test 2.1 { + SELECT a, x FROM t1 JOIN t2 ON +y=d OR x=7 ORDER BY a, x; +} {1 3} +do_execsql_test 2.2 { + SELECT a, x FROM t1 JOIN t2 ON y=d OR x=7 ORDER BY a, x; +} {1 3} + + +# Similar to [do_execsql_test], except that two elements are appended +# to the result - the string "search" and the number of times test variable +# sqlite3_search_count is incremented by running the supplied SQL. e.g. +# +# do_searchcount_test 1.0 { SELECT * FROM t1 } {x y search 2} +# +proc do_searchcount_test {tn sql res} { + uplevel [subst -nocommands { + do_test $tn { + set ::sqlite_search_count 0 + concat [db eval {$sql}] search [set ::sqlite_search_count] + } [list $res] + }] +} + +do_execsql_test 3.0 { + CREATE TABLE t3(a, b, c); + CREATE UNIQUE INDEX i3 ON t3(a, b); + INSERT INTO t3 VALUES(1, 'one', 'i'); + INSERT INTO t3 VALUES(3, 'three', 'iii'); + INSERT INTO t3 VALUES(6, 'six', 'vi'); + INSERT INTO t3 VALUES(2, 'two', 'ii'); + INSERT INTO t3 VALUES(4, 'four', 'iv'); + INSERT INTO t3 VALUES(5, 'five', 'v'); + + CREATE TABLE t4(x PRIMARY KEY, y); + INSERT INTO t4 VALUES('a', 'one'); + INSERT INTO t4 VALUES('b', 'two'); +} + +do_searchcount_test 3.1 { + SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') +} {1 one 2 two search 4} + +do_searchcount_test 3.2 { + SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') +} {1 i 2 ii search 6} + +do_searchcount_test 3.4.1 { + SELECT y FROM t4 WHERE x='a' +} {one search 2} +do_searchcount_test 3.4.2 { + SELECT a, b FROM t3 WHERE + (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) + OR (a=2 AND b='two') +} {1 one 2 two search 6} +do_searchcount_test 3.4.3 { + SELECT a, b FROM t3 WHERE + (a=2 AND b='two') + OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) +} {2 two 1 one search 6} +do_searchcount_test 3.4.4 { + SELECT a, b FROM t3 WHERE + (a=2 AND b=(SELECT y FROM t4 WHERE x='b')) + OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) +} {2 two 1 one search 8} + +do_searchcount_test 3.5.1 { + SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR rowid=4 +} {1 one 2 two search 2} +do_searchcount_test 3.5.2 { + SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR rowid=4 +} {1 i 2 ii search 3} + +# Ticket [d02e1406a58ea02d] (2012-10-04) +# LEFT JOIN with an OR in the ON clause causes segfault +# +do_test 4.1 { + db eval { + CREATE TABLE t41(a,b,c); + INSERT INTO t41 VALUES(1,2,3), (4,5,6); + CREATE TABLE t42(d,e,f); + INSERT INTO t42 VALUES(3,6,9), (4,8,12); + SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b); + } +} {1 2 3 3 6 9 4 5 6 {} {} {}} +do_test 4.2 { + db eval { + CREATE INDEX t42d ON t42(d); + CREATE INDEX t42e ON t42(e); + SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b); + } +} {1 2 3 3 6 9 4 5 6 {} {} {}} +do_test 4.3 { + db eval { + SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.d=x.b); + } +} {1 2 3 3 6 9 4 5 6 {} {} {}} + +# Ticket [bc1aea7b725f276177] +# Incorrect result on LEFT JOIN with OR constraints and an ORDER BY clause. +# +do_execsql_test 4.4 { + CREATE TABLE t44(a INTEGER, b INTEGER); + INSERT INTO t44 VALUES(1,2); + INSERT INTO t44 VALUES(3,4); + SELECT * + FROM t44 AS x + LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c + WHERE d=4 OR d IS NULL; +} {3 4 3 4} +do_execsql_test 4.5 { + SELECT * + FROM t44 AS x + LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c + WHERE d=4 OR d IS NULL + ORDER BY a; +} {3 4 3 4} +do_execsql_test 4.6 { + CREATE TABLE t46(c INTEGER, d INTEGER); + INSERT INTO t46 SELECT a, b FROM t44; + SELECT * FROM t44 LEFT JOIN t46 ON a=c + WHERE d=4 OR d IS NULL; +} {3 4 3 4} +do_execsql_test 4.7 { + SELECT * FROM t44 LEFT JOIN t46 ON a=c + WHERE d=4 OR d IS NULL + ORDER BY a; +} {3 4 3 4} + +# Verify fix of a bug reported on the mailing list by Peter Reid +# +do_execsql_test 5.1 { + DROP TABLE IF EXISTS t; + CREATE TABLE t(c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17); + CREATE INDEX tc0 ON t(c0); + CREATE INDEX tc1 ON t(c1); + CREATE INDEX tc2 ON t(c2); + CREATE INDEX tc3 ON t(c3); + CREATE INDEX tc4 ON t(c4); + CREATE INDEX tc5 ON t(c5); + CREATE INDEX tc6 ON t(c6); + CREATE INDEX tc7 ON t(c7); + CREATE INDEX tc8 ON t(c8); + CREATE INDEX tc9 ON t(c9); + CREATE INDEX tc10 ON t(c10); + CREATE INDEX tc11 ON t(c11); + CREATE INDEX tc12 ON t(c12); + CREATE INDEX tc13 ON t(c13); + CREATE INDEX tc14 ON t(c14); + CREATE INDEX tc15 ON t(c15); + CREATE INDEX tc16 ON t(c16); + CREATE INDEX tc17 ON t(c17); + + INSERT INTO t(c0, c16) VALUES (1,1); + + SELECT * FROM t WHERE + c0=1 or c1=1 or c2=1 or c3=1 or + c4=1 or c5=1 or c6=1 or c7=1 or + c8=1 or c9=1 or c10=1 or c11=1 or + c12=1 or c13=1 or c14=1 or c15=1 or + c16=1 or c17=1; +} {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {}} +do_execsql_test 5.2 { + DELETE FROM t; + INSERT INTO t(c0,c17) VALUES(1,1); + SELECT * FROM t WHERE + c0=1 or c1=1 or c2=1 or c3=1 or + c4=1 or c5=1 or c6=1 or c7=1 or + c8=1 or c9=1 or c10=1 or c11=1 or + c12=1 or c13=1 or c14=1 or c15=1 or + c16=1 or c17=1; +} {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1} +do_execsql_test 5.3 { + DELETE FROM t; + INSERT INTO t(c0,c15) VALUES(1,1); + SELECT * FROM t WHERE + c0=1 or c1=1 or c2=1 or c3=1 or + c4=1 or c5=1 or c6=1 or c7=1 or + c8=1 or c9=1 or c10=1 or c11=1 or + c12=1 or c13=1 or c14=1 or c15=1 or + c16=1 or c17=1; +} {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {} {}} + +#------------------------------------------------------------------------- +do_execsql_test 6.1 { + CREATE TABLE x1(a, b, c, d, e); + CREATE INDEX x1a ON x1(a); + CREATE INDEX x1bc ON x1(b, c); + CREATE INDEX x1cd ON x1(c, d); + + INSERT INTO x1 VALUES(1, 2, 3, 4, 'A'); + INSERT INTO x1 VALUES(5, 6, 7, 8, 'B'); + INSERT INTO x1 VALUES(9, 10, 11, 12, 'C'); + INSERT INTO x1 VALUES(13, 14, 15, 16, 'D'); +} + +do_searchcount_test 6.2.1 { + SELECT e FROM x1 WHERE b=2 OR c=7; +} {A B search 6} +do_searchcount_test 6.2.2 { + SELECT c FROM x1 WHERE b=2 OR c=7; +} {3 7 search 4} + +do_searchcount_test 6.3.1 { + SELECT e FROM x1 WHERE a=1 OR b=10; +} {A C search 6} +do_searchcount_test 6.3.2 { + SELECT c FROM x1 WHERE a=1 OR b=10; +} {3 11 search 5} +do_searchcount_test 6.3.3 { + SELECT rowid FROM x1 WHERE a=1 OR b=10; +} {1 3 search 4} + +do_searchcount_test 6.4.1 { + SELECT a FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 +} {1 9 search 6} +do_searchcount_test 6.4.2 { + SELECT b, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 +} {2 3 10 11 search 5} +do_searchcount_test 6.4.3 { + SELECT rowid, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 +} {1 3 3 11 search 4} + +do_searchcount_test 6.5.1 { + SELECT a FROM x1 WHERE rowid = 2 OR c=11 +} {5 9 search 3} +do_searchcount_test 6.5.2 { + SELECT d FROM x1 WHERE rowid = 2 OR c=11 +} {8 12 search 2} +do_searchcount_test 6.5.3 { + SELECT d FROM x1 WHERE c=11 OR rowid = 2 +} {12 8 search 2} +do_searchcount_test 6.5.4 { + SELECT a FROM x1 WHERE c=11 OR rowid = 2 +} {9 5 search 3} + +do_searchcount_test 6.6.1 { + SELECT rowid FROM x1 WHERE a=1 OR b=6 OR c=11 +} {1 2 3 search 6} +do_searchcount_test 6.6.2 { + SELECT c FROM x1 WHERE a=1 OR b=6 OR c=11 +} {3 7 11 search 7} +do_searchcount_test 6.6.3 { + SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6 +} {11 3 7 search 7} +do_searchcount_test 6.6.4 { + SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1 +} {7 11 3 search 7} + +# 2020-02-22 ticket aa4378693018aa99 +# In the OP_Column opcode, if a cursor is marked with OP_NullRow +# (because it is the right table of a LEFT JOIN that does not match) +# then do not substitute index cursors, as the index cursors do not +# have the VdbeCursor.nullRow flag set. +# +do_execsql_test 6.7 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a UNIQUE, b UNIQUE); + INSERT INTO t1(a,b) VALUES(null,2); + CREATE VIEW t2 AS SELECT * FROM t1 WHERE b<10 OR a<7 ORDER BY b; + SELECT t1.* FROM t1 LEFT JOIN t2 ON abs(t1.a)=abs(t2.b); +} {{} 2} + + +#------------------------------------------------------------------------- +# +do_execsql_test 7.0 { + CREATE TABLE y1(a, b); + CREATE TABLE y2(x, y); + CREATE INDEX y2xy ON y2(x, y); + INSERT INTO y1 VALUES(1, 1); + INSERT INTO y2 VALUES(3, 3); +} + +do_execsql_test 7.1 { + SELECT * FROM y1 LEFT JOIN y2 ON ((x=1 AND y=b) OR (x=2 AND y=b)) +} {1 1 {} {}} + +do_execsql_test 7.3 { + CREATE TABLE foo (Id INTEGER PRIMARY KEY, fa INTEGER, fb INTEGER); + CREATE TABLE bar (Id INTEGER PRIMARY KEY, ba INTEGER, bb INTEGER); + + INSERT INTO foo VALUES(1, 1, 1); + INSERT INTO foo VALUES(2, 1, 2); + INSERT INTO foo VALUES(3, 1, 3); + INSERT INTO foo VALUES(4, 1, 4); + INSERT INTO foo VALUES(5, 1, 5); + INSERT INTO foo VALUES(6, 1, 6); + INSERT INTO foo VALUES(7, 1, 7); + INSERT INTO foo VALUES(8, 1, 8); + INSERT INTO foo VALUES(9, 1, 9); + + INSERT INTO bar VALUES(NULL, 1, 1); + INSERT INTO bar VALUES(NULL, 2, 2); + INSERT INTO bar VALUES(NULL, 3, 3); + INSERT INTO bar VALUES(NULL, 1, 4); + INSERT INTO bar VALUES(NULL, 2, 5); + INSERT INTO bar VALUES(NULL, 3, 6); + INSERT INTO bar VALUES(NULL, 1, 7); + INSERT INTO bar VALUES(NULL, 2, 8); + INSERT INTO bar VALUES(NULL, 3, 9); +} + +do_execsql_test 7.4 { + SELECT + bar.Id, bar.ba, bar.bb, foo.fb + FROM foo LEFT JOIN bar + ON (bar.ba = 1 AND bar.bb = foo.fb) + OR (bar.ba = 5 AND bar.bb = foo.fb); +} { + 1 1 1 1 + {} {} {} 2 + {} {} {} 3 + 4 1 4 4 + {} {} {} 5 + {} {} {} 6 + 7 1 7 7 + {} {} {} 8 + {} {} {} 9 +} + +do_execsql_test 7.5 { + CREATE INDEX idx_bar ON bar(ba, bb); + SELECT + bar.Id, bar.ba, bar.bb, foo.fb + FROM foo LEFT JOIN bar + ON (bar.ba = 1 AND bar.bb = foo.fb) + OR (bar.ba = 5 AND bar.bb = foo.fb); +} { + 1 1 1 1 + {} {} {} 2 + {} {} {} 3 + 4 1 4 4 + {} {} {} 5 + {} {} {} 6 + 7 1 7 7 + {} {} {} 8 + {} {} {} 9 +} + + +finish_test -- cgit v1.2.3