AryaWu/sqlite
0
1# 2016 September 32#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing SQL statements that use row value13# constructors.14#15 16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set ::testprefix rowvalue920 21# Tests:22#23# 1.*: Test that affinities are handled correctly by various row-value24# 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-value31# 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 rowid51} {52 1 integer 1 integer 53 2 integer 2 text 54 3 text 3 integer 55 4 text 4 text56}57 58do_execsql_test 1.1.1 {59 SELECT (SELECT rowid FROM a1 WHERE a=x AND b=y) FROM a260} {{} {} 15 92}61do_execsql_test 1.1.2 {62 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (x, y)) FROM a263} {{} {} 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)=b75} {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=b83} {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 a290} {3 14 15 92}91do_execsql_test 1.5.2 {92 SELECT (SELECT rowid FROM a1 WHERE (a, b) = (+x, +y)) FROM a293} {3 14 15 92}94do_execsql_test 1.5.3 {95 SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a296} {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=y104 )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 $idx192 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.rowid208 );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.rowid218 );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 f2256 );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 f2262 );263}264 265 266#-------------------------------------------------------------------------267#268do_execsql_test 7.0 {269 CREATE TABLE g1(a, b); 270 INSERT INTO g1 VALUES271 (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 10285 );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 10291 );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 10297 );298} { 1 4 1 5 }299 300#-------------------------------------------------------------------------301#302do_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 307do_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#313reset_db314do_execsql_test 9.0 {315 CREATE TABLE t1(a, b);316 INSERT INTO t1 VALUES(1, 1), (1, 2), (2, 2), (2, 3), (3, 3), (3, 4), (4, 4);317}318 319do_execsql_test 9.1 {320 SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );321} {322 2 2 3 3323}324do_execsql_test 9.2 {325 CREATE INDEX i1 ON t1(a);326}327 328do_execsql_test 9.4 {329 SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );330} {331 2 2 3 3332}333do_eqp_test 9.4e {334 SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );335} {336 *SEARCH t1 USING INDEX i1*337}338 339do_execsql_test 9.5 {340 CREATE INDEX i2 ON t1(b, a);341 SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );342} {343 2 2 3 3344}345do_eqp_test 9.5e {346 SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );347} {348 *SEARCH t1 USING COVERING INDEX i2*349}350 351 352finish_test353