gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/rowvalue9.test (about) 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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set ::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 34 do_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 49 do_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 58 do_execsql_test 1.1.1 { 59 SELECT (SELECT rowid FROM a1 WHERE a=x AND b=y) FROM a2 60 } {{} {} 15 92} 61 do_execsql_test 1.1.2 { 62 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (x, y)) FROM a2 63 } {{} {} 15 92} 64 65 do_execsql_test 1.2.3 { 66 SELECT a1.rowid FROM a1, a2 WHERE a=x AND b=y; 67 } {15 92} 68 do_execsql_test 1.2.4 { 69 SELECT a1.rowid FROM a1, a2 WHERE (a, b) = (x, y) 70 } {15 92} 71 72 73 do_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} 76 do_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 81 do_execsql_test 1.4.1 { 82 SELECT a1.rowid FROM a1, a2 WHERE +x=a AND +y=b 83 } {3 14 15 92} 84 do_execsql_test 1.4.2 { 85 SELECT a1.rowid FROM a1, a2 WHERE (+x, +y) = (a, b) 86 } {3 14 15 92} 87 88 do_execsql_test 1.5.1 { 89 SELECT (SELECT rowid FROM a1 WHERE a=+x AND b=+y) FROM a2 90 } {3 14 15 92} 91 do_execsql_test 1.5.2 { 92 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (+x, +y)) FROM a2 93 } {3 14 15 92} 94 do_execsql_test 1.5.3 { 95 SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a2 96 } {3 14 15 92} 97 98 do_execsql_test 1.6.1 { 99 SELECT a1.rowid FROM a1 WHERE (a, b) IN (SELECT x, y FROM a2) 100 } {15 92} 101 do_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 # 109 do_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 } 115 do_execsql_test 2.2 { SELECT * FROM b1, b2 WHERE a=x; } {} 116 do_execsql_test 2.3 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {} 117 do_execsql_test 2.4 { CREATE UNIQUE INDEX b1a ON b1(a); } 118 do_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 # 123 do_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 } 129 do_execsql_test 3.2 { 130 SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2) 131 } {} 132 do_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 } {} 136 do_execsql_test 3.4 { 137 SELECT * FROM c1 WHERE (a, +b) IN (SELECT x, y FROM c2) 138 } {} 139 140 do_execsql_test 3.5 { 141 SELECT c1.rowid FROM c1 WHERE b = (SELECT y FROM c2); 142 } {} 143 do_execsql_test 3.6 { 144 SELECT c1.rowid FROM c1 WHERE (a, b) = (SELECT x, y FROM c2); 145 } {} 146 147 148 #------------------------------------------------------------------------- 149 # 150 do_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 165 foreach {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 222 do_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 235 do_execsql_test 5.1 { 236 SELECT rowid FROM e1 WHERE (a, c) IN (SELECT x, y FROM e2); 237 } {1} 238 do_execsql_test 5.2 { 239 SELECT rowid FROM e2 WHERE rowid IN (SELECT +c FROM e1); 240 } {2} 241 do_execsql_test 5.3 { 242 SELECT rowid FROM e2 WHERE rowid IN (SELECT 0+c FROM e1); 243 } {2} 244 245 #------------------------------------------------------------------------- 246 # 247 do_execsql_test 6.0 { 248 CREATE TABLE f1(a, b); 249 CREATE TABLE f2(c, d); 250 CREATE TABLE f3(e, f); 251 } 252 253 do_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 } 258 do_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 # 268 do_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 282 do_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 288 do_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 294 do_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 #------------------------------------------------------------------------- 301 # 302 do_execsql_test 8.1 { 303 CREATE TABLE t1(a ,b FLOAT); 304 CREATE INDEX t1x1 ON t1(a,b,a,a,a,a,a,a,a,a,a,b); 305 } 306 307 do_catchsql_test 8.2 { 308 SELECT a FROM t1 NATURAL JOIN t1 WHERE (a,b)> (SELECT 2 IN (SELECT 2,2), 2); 309 } {1 {sub-select returns 2 columns - expected 1}} 310 311 312 finish_test 313 314