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 21do_execsql_test 1.0.1 { 22 CREATE TABLE a1(c, b INTEGER, a TEXT, PRIMARY KEY(a, b)); 23 24 INSERT INTO a1 (rowid, c, b, a) VALUES(3, '0x03', 1, 1); 25 INSERT INTO a1 (rowid, c, b, a) VALUES(14, '0x0E', 2, 2); 26 INSERT INTO a1 (rowid, c, b, a) VALUES(15, '0x0F', 3, 3); 27 INSERT INTO a1 (rowid, c, b, a) VALUES(92, '0x5C', 4, 4); 28 29 CREATE TABLE a2(x BLOB, y BLOB); 30 INSERT INTO a2(x, y) VALUES(1, 1); 31 INSERT INTO a2(x, y) VALUES(2, '2'); 32 INSERT INTO a2(x, y) VALUES('3', 3); 33 INSERT INTO a2(x, y) VALUES('4', '4'); 34} 35 36do_execsql_test 1.0.2 { 37 SELECT x, typeof(x), y, typeof(y) FROM a2 ORDER BY rowid 38} { 39 1 integer 1 integer 40 2 integer 2 text 41 3 text 3 integer 42 4 text 4 text 43} 44 45do_execsql_test 1.1.1 { 46 SELECT (SELECT rowid FROM a1 WHERE a=x AND b=y) FROM a2 47} {{} {} 15 92} 48do_execsql_test 1.1.2 { 49 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (x, y)) FROM a2 50} {{} {} 15 92} 51 52do_execsql_test 1.2.3 { 53 SELECT a1.rowid FROM a1, a2 WHERE a=x AND b=y; 54} {15 92} 55do_execsql_test 1.2.4 { 56 SELECT a1.rowid FROM a1, a2 WHERE (a, b) = (x, y) 57} {15 92} 58 59 60do_execsql_test 1.3.1 { 61 SELECT a1.rowid FROM a1, a2 WHERE coalesce(NULL,x)=a AND coalesce(NULL,y)=b 62} {3 14 15 92} 63do_execsql_test 1.3.2 { 64 SELECT a1.rowid FROM a1, a2 65 WHERE (coalesce(NULL,x), coalesce(NULL,y)) = (a, b) 66} {3 14 15 92} 67 68do_execsql_test 1.4.1 { 69 SELECT a1.rowid FROM a1, a2 WHERE +x=a AND +y=b 70} {3 14 15 92} 71do_execsql_test 1.4.2 { 72 SELECT a1.rowid FROM a1, a2 WHERE (+x, +y) = (a, b) 73} {3 14 15 92} 74 75do_execsql_test 1.5.1 { 76 SELECT (SELECT rowid FROM a1 WHERE a=+x AND b=+y) FROM a2 77} {3 14 15 92} 78do_execsql_test 1.5.2 { 79 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (+x, +y)) FROM a2 80} {3 14 15 92} 81do_execsql_test 1.5.3 { 82 SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a2 83} {3 14 15 92} 84 85do_execsql_test 1.6.1 { 86 SELECT a1.rowid FROM a1 WHERE (a, b) IN (SELECT x, y FROM a2) 87} {15 92} 88do_execsql_test 1.6.2 { 89 SELECT a1.rowid FROM a1, a2 WHERE EXISTS ( 90 SELECT 1 FROM a1 WHERE a=x AND b=y 91 ) 92} {3 14 15 92 3 14 15 92} 93 94# Test that [199df416] is fixed. 95# 96do_execsql_test 2.1 { 97 CREATE TABLE b1(a TEXT); 98 CREATE TABLE b2(x BLOB); 99 INSERT INTO b1 VALUES(1); 100 INSERT INTO b2 VALUES(1); 101} 102do_execsql_test 2.2 { SELECT * FROM b1, b2 WHERE a=x; } {} 103do_execsql_test 2.3 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {} 104do_execsql_test 2.4 { CREATE UNIQUE INDEX b1a ON b1(a); } 105do_execsql_test 2.5 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {} 106 107# Test that a multi-column version of the query that revealed problem 108# [199df416] also works. 109# 110do_execsql_test 3.1 { 111 CREATE TABLE c1(a INTEGER, b TEXT); 112 INSERT INTO c1 VALUES(1, 1); 113 CREATE TABLE c2(x BLOB, y BLOB); 114 INSERT INTO c2 VALUES(1, 1); 115} 116do_execsql_test 3.2 { 117 SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2) 118} {} 119do_execsql_test 3.3 { 120 CREATE UNIQUE INDEX c1ab ON c1(a, b); 121 SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2) 122} {} 123do_execsql_test 3.4 { 124 SELECT * FROM c1 WHERE (a, +b) IN (SELECT x, y FROM c2) 125} {} 126 127do_execsql_test 3.5 { 128 SELECT c1.rowid FROM c1 WHERE b = (SELECT y FROM c2); 129} {} 130do_execsql_test 3.6 { 131 SELECT c1.rowid FROM c1 WHERE (a, b) = (SELECT x, y FROM c2); 132} {} 133 134 135#------------------------------------------------------------------------- 136# 137do_execsql_test 4.0 { 138 CREATE TABLE d1(a TEXT, b INTEGER, c NUMERIC); 139 CREATE TABLE d2(x BLOB, y BLOB); 140 141 INSERT INTO d1 VALUES(1, 1, 1); 142 INSERT INTO d1 VALUES(2, 2, 2); 143 INSERT INTO d1 VALUES(3, 3, 3); 144 INSERT INTO d1 VALUES(4, 4, 4); 145 146 INSERT INTO d2 VALUES (1, 1); 147 INSERT INTO d2 VALUES (2, '2'); 148 INSERT INTO d2 VALUES ('3', 3); 149 INSERT INTO d2 VALUES ('4', '4'); 150} 151 152foreach {tn idx} { 153 1 {} 154 2 { CREATE INDEX idx ON d1(a) } 155 3 { CREATE INDEX idx ON d1(a, c) } 156 4 { CREATE INDEX idx ON d1(c) } 157 5 { CREATE INDEX idx ON d1(c, a) } 158 159 6 { 160 CREATE INDEX idx ON d1(c, a) ; 161 CREATE INDEX idx1 ON d2(x, y); 162 } 163 164 7 { 165 CREATE INDEX idx ON d1(c, a) ; 166 CREATE UNIQUE INDEX idx2 ON d2(x, y) ; 167 } 168 169 8 { 170 CREATE INDEX idx ON d1(c) ; 171 CREATE UNIQUE INDEX idx2 ON d2(x); 172 } 173 174} { 175 execsql { DROP INDEX IF EXISTS idx } 176 execsql { DROP INDEX IF EXISTS idx2 } 177 execsql { DROP INDEX IF EXISTS idx3 } 178 execsql $idx 179 180 do_execsql_test 4.$tn.1 { 181 SELECT rowid FROM d1 WHERE (a, c) IN (SELECT x, y FROM d2); 182 } {3 4} 183 184 do_execsql_test 4.$tn.2 { 185 SELECT rowid FROM d1 WHERE (c, a) IN (SELECT x, y FROM d2); 186 } {2 4} 187 188 do_execsql_test 4.$tn.3 { 189 SELECT rowid FROM d1 WHERE (+c, a) IN (SELECT x, y FROM d2); 190 } {2} 191 192 do_execsql_test 4.$tn.4 { 193 SELECT rowid FROM d1 WHERE (c, a) = ( 194 SELECT x, y FROM d2 WHERE d2.rowid=d1.rowid 195 ); 196 } {2 4} 197 198 do_execsql_test 4.$tn.5 { 199 SELECT d1.rowid FROM d1, d2 WHERE a = y; 200 } {2 4} 201 202 do_execsql_test 4.$tn.6 { 203 SELECT d1.rowid FROM d1 WHERE a = ( 204 SELECT y FROM d2 where d2.rowid=d1.rowid 205 ); 206 } {2 4} 207} 208 209finish_test 210 211