1# 2016 September 3 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 SQL statements that use row value 13# constructors. 14# 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set ::testprefix rowvalue9 20 21# Tests: 22# 23# 1.*: Test that affinities are handled correctly by various row-value 24# operations without indexes. 25# 26# 2.*: Test an affinity bug that came up during testing. 27# 28# 3.*: Test a row-value version of the bug tested by 2.*. 29# 30# 4.*: Test that affinities are handled correctly by various row-value 31# operations with assorted indexes. 32# 33 34do_execsql_test 1.0.1 { 35 CREATE TABLE a1(c, b INTEGER, a TEXT, PRIMARY KEY(a, b)); 36 37 INSERT INTO a1 (rowid, c, b, a) VALUES(3, '0x03', 1, 1); 38 INSERT INTO a1 (rowid, c, b, a) VALUES(14, '0x0E', 2, 2); 39 INSERT INTO a1 (rowid, c, b, a) VALUES(15, '0x0F', 3, 3); 40 INSERT INTO a1 (rowid, c, b, a) VALUES(92, '0x5C', 4, 4); 41 42 CREATE TABLE a2(x BLOB, y BLOB); 43 INSERT INTO a2(x, y) VALUES(1, 1); 44 INSERT INTO a2(x, y) VALUES(2, '2'); 45 INSERT INTO a2(x, y) VALUES('3', 3); 46 INSERT INTO a2(x, y) VALUES('4', '4'); 47} 48 49do_execsql_test 1.0.2 { 50 SELECT x, typeof(x), y, typeof(y) FROM a2 ORDER BY rowid 51} { 52 1 integer 1 integer 53 2 integer 2 text 54 3 text 3 integer 55 4 text 4 text 56} 57 58do_execsql_test 1.1.1 { 59 SELECT (SELECT rowid FROM a1 WHERE a=x AND b=y) FROM a2 60} {{} {} 15 92} 61do_execsql_test 1.1.2 { 62 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (x, y)) FROM a2 63} {{} {} 15 92} 64 65do_execsql_test 1.2.3 { 66 SELECT a1.rowid FROM a1, a2 WHERE a=x AND b=y; 67} {15 92} 68do_execsql_test 1.2.4 { 69 SELECT a1.rowid FROM a1, a2 WHERE (a, b) = (x, y) 70} {15 92} 71 72 73do_execsql_test 1.3.1 { 74 SELECT a1.rowid FROM a1, a2 WHERE coalesce(NULL,x)=a AND coalesce(NULL,y)=b 75} {3 14 15 92} 76do_execsql_test 1.3.2 { 77 SELECT a1.rowid FROM a1, a2 78 WHERE (coalesce(NULL,x), coalesce(NULL,y)) = (a, b) 79} {3 14 15 92} 80 81do_execsql_test 1.4.1 { 82 SELECT a1.rowid FROM a1, a2 WHERE +x=a AND +y=b 83} {3 14 15 92} 84do_execsql_test 1.4.2 { 85 SELECT a1.rowid FROM a1, a2 WHERE (+x, +y) = (a, b) 86} {3 14 15 92} 87 88do_execsql_test 1.5.1 { 89 SELECT (SELECT rowid FROM a1 WHERE a=+x AND b=+y) FROM a2 90} {3 14 15 92} 91do_execsql_test 1.5.2 { 92 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (+x, +y)) FROM a2 93} {3 14 15 92} 94do_execsql_test 1.5.3 { 95 SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a2 96} {3 14 15 92} 97 98do_execsql_test 1.6.1 { 99 SELECT a1.rowid FROM a1 WHERE (a, b) IN (SELECT x, y FROM a2) 100} {15 92} 101do_execsql_test 1.6.2 { 102 SELECT a1.rowid FROM a1, a2 WHERE EXISTS ( 103 SELECT 1 FROM a1 WHERE a=x AND b=y 104 ) 105} {3 14 15 92 3 14 15 92} 106 107# Test that [199df416] is fixed. 108# 109do_execsql_test 2.1 { 110 CREATE TABLE b1(a TEXT); 111 CREATE TABLE b2(x BLOB); 112 INSERT INTO b1 VALUES(1); 113 INSERT INTO b2 VALUES(1); 114} 115do_execsql_test 2.2 { SELECT * FROM b1, b2 WHERE a=x; } {} 116do_execsql_test 2.3 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {} 117do_execsql_test 2.4 { CREATE UNIQUE INDEX b1a ON b1(a); } 118do_execsql_test 2.5 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {} 119 120# Test that a multi-column version of the query that revealed problem 121# [199df416] also works. 122# 123do_execsql_test 3.1 { 124 CREATE TABLE c1(a INTEGER, b TEXT); 125 INSERT INTO c1 VALUES(1, 1); 126 CREATE TABLE c2(x BLOB, y BLOB); 127 INSERT INTO c2 VALUES(1, 1); 128} 129do_execsql_test 3.2 { 130 SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2) 131} {} 132do_execsql_test 3.3 { 133 CREATE UNIQUE INDEX c1ab ON c1(a, b); 134 SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2) 135} {} 136do_execsql_test 3.4 { 137 SELECT * FROM c1 WHERE (a, +b) IN (SELECT x, y FROM c2) 138} {} 139 140do_execsql_test 3.5 { 141 SELECT c1.rowid FROM c1 WHERE b = (SELECT y FROM c2); 142} {} 143do_execsql_test 3.6 { 144 SELECT c1.rowid FROM c1 WHERE (a, b) = (SELECT x, y FROM c2); 145} {} 146 147 148#------------------------------------------------------------------------- 149# 150do_execsql_test 4.0 { 151 CREATE TABLE d1(a TEXT, b INTEGER, c NUMERIC); 152 CREATE TABLE d2(x BLOB, y BLOB); 153 154 INSERT INTO d1 VALUES(1, 1, 1); 155 INSERT INTO d1 VALUES(2, 2, 2); 156 INSERT INTO d1 VALUES(3, 3, 3); 157 INSERT INTO d1 VALUES(4, 4, 4); 158 159 INSERT INTO d2 VALUES (1, 1); 160 INSERT INTO d2 VALUES (2, '2'); 161 INSERT INTO d2 VALUES ('3', 3); 162 INSERT INTO d2 VALUES ('4', '4'); 163} 164 165foreach {tn idx} { 166 1 {} 167 2 { CREATE INDEX idx ON d1(a) } 168 3 { CREATE INDEX idx ON d1(a, c) } 169 4 { CREATE INDEX idx ON d1(c) } 170 5 { CREATE INDEX idx ON d1(c, a) } 171 172 6 { 173 CREATE INDEX idx ON d1(c, a) ; 174 CREATE INDEX idx1 ON d2(x, y); 175 } 176 177 7 { 178 CREATE INDEX idx ON d1(c, a) ; 179 CREATE UNIQUE INDEX idx2 ON d2(x, y) ; 180 } 181 182 8 { 183 CREATE INDEX idx ON d1(c) ; 184 CREATE UNIQUE INDEX idx2 ON d2(x); 185 } 186 187} { 188 execsql { DROP INDEX IF EXISTS idx } 189 execsql { DROP INDEX IF EXISTS idx2 } 190 execsql { DROP INDEX IF EXISTS idx3 } 191 execsql $idx 192 193 do_execsql_test 4.$tn.1 { 194 SELECT rowid FROM d1 WHERE (a, c) IN (SELECT x, y FROM d2); 195 } {3 4} 196 197 do_execsql_test 4.$tn.2 { 198 SELECT rowid FROM d1 WHERE (c, a) IN (SELECT x, y FROM d2); 199 } {2 4} 200 201 do_execsql_test 4.$tn.3 { 202 SELECT rowid FROM d1 WHERE (+c, a) IN (SELECT x, y FROM d2); 203 } {2} 204 205 do_execsql_test 4.$tn.4 { 206 SELECT rowid FROM d1 WHERE (c, a) = ( 207 SELECT x, y FROM d2 WHERE d2.rowid=d1.rowid 208 ); 209 } {2 4} 210 211 do_execsql_test 4.$tn.5 { 212 SELECT d1.rowid FROM d1, d2 WHERE a = y; 213 } {2 4} 214 215 do_execsql_test 4.$tn.6 { 216 SELECT d1.rowid FROM d1 WHERE a = ( 217 SELECT y FROM d2 where d2.rowid=d1.rowid 218 ); 219 } {2 4} 220} 221 222do_execsql_test 5.0 { 223 CREATE TABLE e1(a TEXT, c NUMERIC); 224 CREATE TABLE e2(x BLOB, y BLOB); 225 226 INSERT INTO e1 VALUES(2, 2); 227 228 INSERT INTO e2 VALUES ('2', 2); 229 INSERT INTO e2 VALUES ('2', '2'); 230 INSERT INTO e2 VALUES ('2', '2.0'); 231 232 CREATE INDEX e1c ON e1(c); 233} 234 235do_execsql_test 5.1 { 236 SELECT rowid FROM e1 WHERE (a, c) IN (SELECT x, y FROM e2); 237} {1} 238do_execsql_test 5.2 { 239 SELECT rowid FROM e2 WHERE rowid IN (SELECT +c FROM e1); 240} {2} 241do_execsql_test 5.3 { 242 SELECT rowid FROM e2 WHERE rowid IN (SELECT 0+c FROM e1); 243} {2} 244 245#------------------------------------------------------------------------- 246# 247do_execsql_test 6.0 { 248 CREATE TABLE f1(a, b); 249 CREATE TABLE f2(c, d); 250 CREATE TABLE f3(e, f); 251} 252 253do_execsql_test 6.1 { 254 SELECT * FROM f3 WHERE (e, f) IN ( 255 SELECT a, b FROM f1 UNION ALL SELECT c, d FROM f2 256 ); 257} 258do_execsql_test 6.2 { 259 CREATE INDEX f3e ON f3(e); 260 SELECT * FROM f3 WHERE (e, f) IN ( 261 SELECT a, b FROM f1 UNION ALL SELECT c, d FROM f2 262 ); 263} 264 265 266#------------------------------------------------------------------------- 267# 268do_execsql_test 7.0 { 269 CREATE TABLE g1(a, b); 270 INSERT INTO g1 VALUES 271 (1, 1), (1, 2), (1, 3), (1, 'i'), (1, 'j'), 272 (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), 273 (1, 4), (1, 5); 274 275 CREATE TABLE g2(x, y); 276 CREATE INDEX g2x ON g2(x); 277 278 INSERT INTO g2 VALUES(1, 4); 279 INSERT INTO g2 VALUES(1, 5); 280} 281 282do_execsql_test 7.1 { 283 SELECT * FROM g2 WHERE (x, y) IN ( 284 SELECT a, b FROM g1 ORDER BY +a, +b LIMIT 10 285 ); 286} { 1 4 1 5 } 287 288do_execsql_test 7.2 { 289 SELECT * FROM g2 WHERE (x, y) IN ( 290 SELECT a, b FROM g1 ORDER BY a, b LIMIT 10 291 ); 292} { 1 4 1 5 } 293 294do_execsql_test 7.3 { 295 SELECT * FROM g2 WHERE (x, y) IN ( 296 SELECT a, b FROM g1 ORDER BY 1, 2 LIMIT 10 297 ); 298} { 1 4 1 5 } 299 300 301finish_test 302