1# 2016 June 17 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this file is testing "(...) IN (SELECT ...)" expressions 13# where the SELECT statement returns more than one column. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set ::testprefix rowvalue3 19 20do_execsql_test 1.0 { 21 CREATE TABLE t1(a, b, c); 22 CREATE INDEX i1 ON t1(a, b); 23 INSERT INTO t1 VALUES(1, 2, 3); 24 INSERT INTO t1 VALUES(4, 5, 6); 25 INSERT INTO t1 VALUES(7, 8, 9); 26} 27 28foreach {tn sql res} { 29 1 "SELECT 1 WHERE (4, 5) IN (SELECT a, b FROM t1)" 1 30 2 "SELECT 1 WHERE (5, 5) IN (SELECT a, b FROM t1)" {} 31 3 "SELECT 1 WHERE (5, 4) IN (SELECT a, b FROM t1)" {} 32 4 "SELECT 1 WHERE (5, 4) IN (SELECT b, a FROM t1)" 1 33 5 "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1 34 6 "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0 35 7 "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)" 1 36 8 "SELECT (5, 4) IN (SELECT +b, +a FROM t1)" 1 37 9 "SELECT (1, 2) IN (SELECT rowid, b FROM t1)" 1 38 10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)" 1 39 11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)" {} 40 12 "SELECT 1 FROM t1 WHERE (a, b) = (SELECT +a, +b FROM t1)" {1} 41} { 42 do_execsql_test 1.$tn $sql $res 43} 44 45#------------------------------------------------------------------------- 46 47do_execsql_test 2.0 { 48 CREATE TABLE z1(x, y, z); 49 CREATE TABLE kk(a, b); 50 51 INSERT INTO z1 VALUES('a', 'b', 'c'); 52 INSERT INTO z1 VALUES('d', 'e', 'f'); 53 INSERT INTO z1 VALUES('g', 'h', 'i'); 54 55 -- INSERT INTO kk VALUES('y', 'y'); 56 INSERT INTO kk VALUES('d', 'e'); 57 -- INSERT INTO kk VALUES('x', 'x'); 58 59} 60 61foreach {tn idx} { 62 1 { } 63 2 { CREATE INDEX z1idx ON z1(x, y) } 64 3 { CREATE UNIQUE INDEX z1idx ON z1(x, y) } 65 4 { CREATE INDEX z1idx ON kk(a, b) } 66} { 67 execsql "DROP INDEX IF EXISTS z1idx" 68 execsql $idx 69 70 do_execsql_test 2.$tn.1 { 71 SELECT * FROM z1 WHERE x IN (SELECT a FROM kk) 72 } {d e f} 73 74 do_execsql_test 2.$tn.2 { 75 SELECT * FROM z1 WHERE (x,y) IN (SELECT a, b FROM kk) 76 } {d e f} 77 78 do_execsql_test 2.$tn.3 { 79 SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b FROM kk) 80 } {d e f} 81 82 do_execsql_test 2.$tn.4 { 83 SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b||'x' FROM kk) 84 } {} 85 86 do_execsql_test 2.$tn.5 { 87 SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk) 88 } {d e f} 89} 90 91#------------------------------------------------------------------------- 92# 93 94do_execsql_test 3.0 { 95 CREATE TABLE c1(a, b, c, d); 96 INSERT INTO c1(rowid, a, b) VALUES(1, NULL, 1); 97 INSERT INTO c1(rowid, a, b) VALUES(2, 2, NULL); 98 INSERT INTO c1(rowid, a, b) VALUES(3, 2, 2); 99 INSERT INTO c1(rowid, a, b) VALUES(4, 3, 3); 100 101 INSERT INTO c1(rowid, a, b, c, d) VALUES(101, 'a', 'b', 1, 1); 102 INSERT INTO c1(rowid, a, b, c, d) VALUES(102, 'a', 'b', 1, 2); 103 INSERT INTO c1(rowid, a, b, c, d) VALUES(103, 'a', 'b', 1, 3); 104 INSERT INTO c1(rowid, a, b, c, d) VALUES(104, 'a', 'b', 2, 1); 105 INSERT INTO c1(rowid, a, b, c, d) VALUES(105, 'a', 'b', 2, 2); 106 INSERT INTO c1(rowid, a, b, c, d) VALUES(106, 'a', 'b', 2, 3); 107 INSERT INTO c1(rowid, a, b, c, d) VALUES(107, 'a', 'b', 3, 1); 108 INSERT INTO c1(rowid, a, b, c, d) VALUES(108, 'a', 'b', 3, 2); 109 INSERT INTO c1(rowid, a, b, c, d) VALUES(109, 'a', 'b', 3, 3); 110} 111 112 113foreach {tn idx} { 114 1 { } 115 2 { CREATE INDEX c1ab ON c1(a, b); } 116 3 { CREATE INDEX c1ba ON c1(b, a); } 117 118 4 { CREATE INDEX c1cd ON c1(c, d); } 119 5 { CREATE INDEX c1dc ON c1(d, c); } 120} { 121 drop_all_indexes 122 123 foreach {tn2 sql res} { 124 1 "SELECT (1, 2) IN (SELECT a, b FROM c1)" {0} 125 2 "SELECT (1, 1) IN (SELECT a, b FROM c1)" {{}} 126 3 "SELECT (2, 1) IN (SELECT a, b FROM c1)" {{}} 127 4 "SELECT (2, 2) IN (SELECT a, b FROM c1)" {1} 128 5 "SELECT c, d FROM c1 WHERE (c, d) IN (SELECT d, c FROM c1)" 129 { 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 } 130 131 6 "SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c DESC" 132 { 3 1 3 2 3 3 2 1 2 2 2 3 1 1 1 2 1 3 } 133 134 7 { 135 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 136 ORDER BY c DESC, d ASC 137 } { 3 1 3 2 3 3 2 1 2 2 2 3 1 1 1 2 1 3 } 138 139 8 { 140 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 141 ORDER BY c ASC, d DESC 142 } { 1 3 1 2 1 1 2 3 2 2 2 1 3 3 3 2 3 1 } 143 144 9 { 145 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 146 ORDER BY c ASC, d ASC 147 } { 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 } 148 10 { 149 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 150 ORDER BY c DESC, d DESC 151 } { 3 3 3 2 3 1 2 3 2 2 2 1 1 3 1 2 1 1 } 152 153 } { 154 do_execsql_test 3.$tn.$tn2 $sql $res 155 } 156} 157 158#------------------------------------------------------------------------- 159 160do_execsql_test 4.0 { 161 CREATE TABLE hh(a, b, c); 162 163 INSERT INTO hh VALUES('a', 'a', 1); 164 INSERT INTO hh VALUES('a', 'b', 2); 165 INSERT INTO hh VALUES('b', 'a', 3); 166 INSERT INTO hh VALUES('b', 'b', 4); 167 168 CREATE TABLE k1(x, y); 169 INSERT INTO k1 VALUES('a', 'a'); 170 INSERT INTO k1 VALUES('b', 'b'); 171 INSERT INTO k1 VALUES('a', 'b'); 172 INSERT INTO k1 VALUES('b', 'a'); 173} 174 175foreach {tn idx} { 176 1 { } 177 2 { CREATE INDEX h1 ON hh(a, b); } 178 3 { CREATE UNIQUE INDEX k1idx ON k1(x, y) } 179 4 { CREATE UNIQUE INDEX k1idx ON k1(x, y DESC) } 180 5 { 181 CREATE INDEX h1 ON hh(a, b); 182 CREATE UNIQUE INDEX k1idx ON k1(x, y); 183 } 184 6 { 185 CREATE INDEX h1 ON hh(a, b); 186 CREATE UNIQUE INDEX k1idx ON k1(x, y DESC); 187 } 188} { 189 drop_all_indexes 190 execsql $idx 191 foreach {tn2 orderby res} { 192 1 "a ASC, b ASC" {1 2 3 4} 193 2 "a ASC, b DESC" {2 1 4 3} 194 3 "a DESC, b ASC" {3 4 1 2} 195 4 "a DESC, b DESC" {4 3 2 1} 196 } { 197 do_execsql_test 4.$tn.$tn2 " 198 SELECT c FROM hh WHERE (a, b) in (SELECT x, y FROM k1) ORDER BY $orderby 199 " $res 200 } 201} 202 203#------------------------------------------------------------------------- 204 205# 2016-11-17. Query flattening in a vector SELECT on the RHS of an IN 206# operator. Ticket https://www.sqlite.org/src/info/da7841375186386c 207# 208do_execsql_test 5.0 { 209 DROP TABLE IF EXISTS t1; 210 DROP TABLE IF EXISTS t2; 211 CREATE TABLE T1(a TEXT); 212 INSERT INTO T1(a) VALUES ('aaa'); 213 CREATE TABLE T2(a TEXT PRIMARY KEY,n INT); 214 INSERT INTO T2(a, n) VALUES('aaa',0); 215 SELECT * FROM T2 216 WHERE (a,n) IN (SELECT T1.a, V.n 217 FROM T1, (SELECT * FROM (SELECT 0 n) T3) V); 218} {aaa 0} 219 220 221finish_test 222