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 20# Drop all auxiliary indexes from the main database opened by handle [db]. 21# 22proc drop_all_indexes {} { 23 set L [db eval { 24 SELECT name FROM sqlite_master WHERE type='index' AND sql LIKE 'create%' 25 }] 26 foreach idx $L { db eval "DROP INDEX $idx" } 27} 28 29do_execsql_test 1.0 { 30 CREATE TABLE t1(a, b, c); 31 CREATE INDEX i1 ON t1(a, b); 32 INSERT INTO t1 VALUES(1, 2, 3); 33 INSERT INTO t1 VALUES(4, 5, 6); 34 INSERT INTO t1 VALUES(7, 8, 9); 35} 36 37foreach {tn sql res} { 38 1 "SELECT 1 WHERE (4, 5) IN (SELECT a, b FROM t1)" 1 39 2 "SELECT 1 WHERE (5, 5) IN (SELECT a, b FROM t1)" {} 40 3 "SELECT 1 WHERE (5, 4) IN (SELECT a, b FROM t1)" {} 41 4 "SELECT 1 WHERE (5, 4) IN (SELECT b, a FROM t1)" 1 42 5 "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1 43 6 "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0 44 7 "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)" 1 45 8 "SELECT (5, 4) IN (SELECT +b, +a FROM t1)" 1 46 9 "SELECT (1, 2) IN (SELECT rowid, b FROM t1)" 1 47 10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)" 1 48 11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)" {} 49 12 "SELECT 1 FROM t1 WHERE (a, b) = (SELECT +a, +b FROM t1)" {1} 50} { 51 do_execsql_test 1.$tn $sql $res 52} 53 54#------------------------------------------------------------------------- 55 56do_execsql_test 2.0 { 57 CREATE TABLE z1(x, y, z); 58 CREATE TABLE kk(a, b); 59 60 INSERT INTO z1 VALUES('a', 'b', 'c'); 61 INSERT INTO z1 VALUES('d', 'e', 'f'); 62 INSERT INTO z1 VALUES('g', 'h', 'i'); 63 64 -- INSERT INTO kk VALUES('y', 'y'); 65 INSERT INTO kk VALUES('d', 'e'); 66 -- INSERT INTO kk VALUES('x', 'x'); 67 68} 69 70foreach {tn idx} { 71 1 { } 72 2 { CREATE INDEX z1idx ON z1(x, y) } 73 3 { CREATE UNIQUE INDEX z1idx ON z1(x, y) } 74 4 { CREATE INDEX z1idx ON kk(a, b) } 75} { 76 execsql "DROP INDEX IF EXISTS z1idx" 77 execsql $idx 78 79 do_execsql_test 2.$tn.1 { 80 SELECT * FROM z1 WHERE x IN (SELECT a FROM kk) 81 } {d e f} 82 83 do_execsql_test 2.$tn.2 { 84 SELECT * FROM z1 WHERE (x,y) IN (SELECT a, b FROM kk) 85 } {d e f} 86 87 do_execsql_test 2.$tn.3 { 88 SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b FROM kk) 89 } {d e f} 90 91 do_execsql_test 2.$tn.4 { 92 SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b||'x' FROM kk) 93 } {} 94 95 do_execsql_test 2.$tn.5 { 96 SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk) 97 } {d e f} 98} 99 100#------------------------------------------------------------------------- 101# 102 103do_execsql_test 3.0 { 104 CREATE TABLE c1(a, b, c, d); 105 INSERT INTO c1(rowid, a, b) VALUES(1, NULL, 1); 106 INSERT INTO c1(rowid, a, b) VALUES(2, 2, NULL); 107 INSERT INTO c1(rowid, a, b) VALUES(3, 2, 2); 108 INSERT INTO c1(rowid, a, b) VALUES(4, 3, 3); 109 110 INSERT INTO c1(rowid, a, b, c, d) VALUES(101, 'a', 'b', 1, 1); 111 INSERT INTO c1(rowid, a, b, c, d) VALUES(102, 'a', 'b', 1, 2); 112 INSERT INTO c1(rowid, a, b, c, d) VALUES(103, 'a', 'b', 1, 3); 113 INSERT INTO c1(rowid, a, b, c, d) VALUES(104, 'a', 'b', 2, 1); 114 INSERT INTO c1(rowid, a, b, c, d) VALUES(105, 'a', 'b', 2, 2); 115 INSERT INTO c1(rowid, a, b, c, d) VALUES(106, 'a', 'b', 2, 3); 116 INSERT INTO c1(rowid, a, b, c, d) VALUES(107, 'a', 'b', 3, 1); 117 INSERT INTO c1(rowid, a, b, c, d) VALUES(108, 'a', 'b', 3, 2); 118 INSERT INTO c1(rowid, a, b, c, d) VALUES(109, 'a', 'b', 3, 3); 119} 120 121 122foreach {tn idx} { 123 1 { } 124 2 { CREATE INDEX c1ab ON c1(a, b); } 125 3 { CREATE INDEX c1ba ON c1(b, a); } 126 127 4 { CREATE INDEX c1cd ON c1(c, d); } 128 5 { CREATE INDEX c1dc ON c1(d, c); } 129} { 130 drop_all_indexes 131 132 foreach {tn2 sql res} { 133 1 "SELECT (1, 2) IN (SELECT a, b FROM c1)" {0} 134 2 "SELECT (1, 1) IN (SELECT a, b FROM c1)" {{}} 135 3 "SELECT (2, 1) IN (SELECT a, b FROM c1)" {{}} 136 4 "SELECT (2, 2) IN (SELECT a, b FROM c1)" {1} 137 5 "SELECT c, d FROM c1 WHERE (c, d) IN (SELECT d, c FROM c1)" 138 { 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 } 139 140 6 "SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c DESC" 141 { 3 1 3 2 3 3 2 1 2 2 2 3 1 1 1 2 1 3 } 142 143 7 { 144 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 145 ORDER BY c DESC, d ASC 146 } { 3 1 3 2 3 3 2 1 2 2 2 3 1 1 1 2 1 3 } 147 148 8 { 149 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 150 ORDER BY c ASC, d DESC 151 } { 1 3 1 2 1 1 2 3 2 2 2 1 3 3 3 2 3 1 } 152 153 9 { 154 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 155 ORDER BY c ASC, d ASC 156 } { 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 } 157 10 { 158 SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 159 ORDER BY c DESC, d DESC 160 } { 3 3 3 2 3 1 2 3 2 2 2 1 1 3 1 2 1 1 } 161 162 } { 163 do_execsql_test 3.$tn.$tn2 $sql $res 164 } 165} 166 167#------------------------------------------------------------------------- 168 169do_execsql_test 4.0 { 170 CREATE TABLE hh(a, b, c); 171 172 INSERT INTO hh VALUES('a', 'a', 1); 173 INSERT INTO hh VALUES('a', 'b', 2); 174 INSERT INTO hh VALUES('b', 'a', 3); 175 INSERT INTO hh VALUES('b', 'b', 4); 176 177 CREATE TABLE k1(x, y); 178 INSERT INTO k1 VALUES('a', 'a'); 179 INSERT INTO k1 VALUES('b', 'b'); 180 INSERT INTO k1 VALUES('a', 'b'); 181 INSERT INTO k1 VALUES('b', 'a'); 182} 183 184foreach {tn idx} { 185 1 { } 186 2 { CREATE INDEX h1 ON hh(a, b); } 187 3 { CREATE UNIQUE INDEX k1idx ON k1(x, y) } 188 4 { CREATE UNIQUE INDEX k1idx ON k1(x, y DESC) } 189 5 { 190 CREATE INDEX h1 ON hh(a, b); 191 CREATE UNIQUE INDEX k1idx ON k1(x, y); 192 } 193 6 { 194 CREATE INDEX h1 ON hh(a, b); 195 CREATE UNIQUE INDEX k1idx ON k1(x, y DESC); 196 } 197} { 198 drop_all_indexes 199 execsql $idx 200 foreach {tn2 orderby res} { 201 1 "a ASC, b ASC" {1 2 3 4} 202 2 "a ASC, b DESC" {2 1 4 3} 203 3 "a DESC, b ASC" {3 4 1 2} 204 4 "a DESC, b DESC" {4 3 2 1} 205 } { 206 do_execsql_test 4.$tn.$tn2 " 207 SELECT c FROM hh WHERE (a, b) in (SELECT x, y FROM k1) ORDER BY $orderby 208 " $res 209 } 210} 211 212#------------------------------------------------------------------------- 213 214 215finish_test 216 217 218