test/rowvalue.test

   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 the SELECT statement.
  13 #
  14
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set ::testprefix rowvalue
  19
  20 do_execsql_test 0.0 {
  21   CREATE TABLE one(o);
  22   INSERT INTO one VALUES(1);
  23 }
  24
  25 foreach {tn v1 v2 eq ne is isnot} {
  26   1 "1, 2, 3"    "1, 2, 3"                   1  0     1 0
  27   2 "1, 0, 3"    "1, 2, 3"                   0  1     0 1
  28   3 "1, 2, NULL" "1, 2, 3"                   {} {}    0 1
  29   4 "1, 2, NULL" "1, 2, NULL"                {} {}    1 0
  30   5 "NULL, NULL, NULL" "NULL, NULL, NULL"    {} {}    1 0
  31
  32   6 "1, NULL, 1" "1, 1, 1"                   {} {}    0 1
  33   7 "1, NULL, 1" "1, 1, 2"                   0  1     0 1
  34 } {
  35   do_execsql_test 1.$tn.eq "SELECT ($v1) == ($v2)" [list $eq]
  36   do_execsql_test 1.$tn.ne "SELECT ($v1) != ($v2)" [list $ne]
  37
  38   do_execsql_test 1.$tn.is    "SELECT ($v1) IS ($v2)"     [list $is]
  39   do_execsql_test 1.$tn.isnot "SELECT ($v1) IS NOT ($v2)" [list $isnot]
  40
  41   do_execsql_test 1.$tn.2.eq "SELECT (SELECT $v1) == (SELECT $v2)" [list $eq]
  42   do_execsql_test 1.$tn.2.ne "SELECT (SELECT $v1) != (SELECT $v2)" [list $ne]
  43 }
  44
  45 foreach {tn v1 v2 lt gt le ge} {
  46   1 "(1, 1, 3)"    "(1, 2, 3)"                   1 0      1 0
  47   2 "(1, 2, 3)"    "(1, 2, 3)"                   0 0      1 1
  48   3 "(1, 3, 3)"    "(1, 2, 3)"                   0 1      0 1
  49
  50   4 "(1, NULL, 3)"    "(1, 2, 3)"                {} {}      {} {}
  51   5 "(1, 3, 3)"    "(1, NULL, 3)"                {} {}      {} {}
  52   6 "(1, NULL, 3)"    "(1, NULL, 3)"             {} {}      {} {}
  53 } {
  54   foreach {tn2 expr res} [list \
  55     2.$tn.lt "$v1 < $v2" $lt   \
  56     2.$tn.gt "$v1 > $v2" $gt   \
  57     2.$tn.le "$v1 <= $v2" $le   \
  58     2.$tn.ge "$v1 >= $v2" $ge   \
  59   ] {
  60     do_execsql_test $tn2 "SELECT $expr" [list $res]
  61
  62     set map(0) [list]
  63     set map() [list]
  64     set map(1) [list 1]
  65     do_execsql_test $tn2.where1 "SELECT * FROM one WHERE $expr" $map($res)
  66
  67     set map(0) [list 1]
  68     set map() [list]
  69     set map(1) [list]
  70     do_execsql_test $tn2.where2 "SELECT * FROM one WHERE NOT $expr" $map($res)
  71   }
  72 }
  73
  74 do_execsql_test 3.0 {
  75   CREATE TABLE t1(x, y);
  76   INSERT INTO t1 VALUES(1, 1);
  77   INSERT INTO t1 VALUES(1, 2);
  78   INSERT INTO t1 VALUES(2, 3);
  79   INSERT INTO t1 VALUES(2, 4);
  80   INSERT INTO t1 VALUES(3, 5);
  81   INSERT INTO t1 VALUES(3, 6);
  82 }
  83
  84 foreach {tn r order} {
  85   1 "(1, 1)"           "ORDER BY y"
  86   2 "(1, 1)"           "ORDER BY x, y"
  87   3 "(1, 2)"           "ORDER BY x, y DESC"
  88   4 "(3, 6)"           "ORDER BY x DESC, y DESC"
  89   5 "((3, 5))"         "ORDER BY x DESC, y"
  90   6 "(SELECT 3, 5)"    "ORDER BY x DESC, y"
  91 } {
  92   do_execsql_test 3.$tn.1 "SELECT $r == (SELECT x,y FROM t1 $order)" 1
  93   do_execsql_test 3.$tn.2 "SELECT $r == (SELECT * FROM t1 $order)" 1
  94
  95   do_execsql_test 3.$tn.3 "
  96     SELECT (SELECT * FROM t1 $order) == (SELECT * FROM t1 $order)
  97   " 1
  98   do_execsql_test 3.$tn.4 "
  99     SELECT (SELECT 0, 0) == (SELECT * FROM t1 $order)
 100   " 0
 101 }
 102
 103 foreach {tn expr res} {
 104   1 {(2, 2) BETWEEN (2, 2) AND (3, 3)} 1
 105   2 {(2, 2) BETWEEN (2, NULL) AND (3, 3)} {}
 106   3 {(2, 2) BETWEEN (3, NULL) AND (3, 3)} 0
 107 } {
 108   do_execsql_test 4.$tn "SELECT $expr" [list $res]
 109 }
 110
 111 foreach {tn expr res} {
 112   1 {(2, 4) IN (SELECT * FROM t1)} 1
 113   2 {(3, 4) IN (SELECT * FROM t1)} 0
 114
 115   3 {(NULL, 4) IN (SELECT * FROM t1)} {}
 116   4 {(NULL, 0) IN (SELECT * FROM t1)} 0
 117
 118   5 {(NULL, 4) NOT IN (SELECT * FROM t1)} {}
 119   6 {(NULL, 0) NOT IN (SELECT * FROM t1)} 1
 120 } {
 121   do_execsql_test 5.$tn "SELECT $expr" [list $res]
 122 }
 123
 124 do_execsql_test 6.0 {
 125   CREATE TABLE hh(a, b, c);
 126   INSERT INTO hh VALUES('abc', 1, 'i');
 127   INSERT INTO hh VALUES('ABC', 1, 'ii');
 128   INSERT INTO hh VALUES('def', 2, 'iii');
 129   INSERT INTO hh VALUES('DEF', 2, 'iv');
 130   INSERT INTO hh VALUES('GHI', 3, 'v');
 131   INSERT INTO hh VALUES('ghi', 3, 'vi');
 132
 133   CREATE INDEX hh_ab ON hh(a, b);
 134 }
 135
 136 do_execsql_test 6.1 {
 137   SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1);
 138 } {i}
 139 do_execsql_test 6.2 {
 140   SELECT c FROM hh WHERE (a, b) = (SELECT 'abc' COLLATE nocase, 1);
 141 } {i}
 142 do_execsql_test 6.3 {
 143   SELECT c FROM hh WHERE a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1);
 144 } {i}
 145 do_execsql_test 6.4 {
 146   SELECT c FROM hh WHERE +a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1);
 147 } {i}
 148 do_execsql_test 6.5 {
 149   SELECT c FROM hh WHERE a = (SELECT 'abc') COLLATE nocase AND b = (SELECT 1);
 150 } {i ii}
 151 do_catchsql_test 6.6 {
 152   SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1) COLLATE nocase;
 153 } {1 {row value misused}}
 154 do_catchsql_test 6.7 {
 155   SELECT c FROM hh WHERE (a, b) = 1;
 156 } {1 {row value misused}}
 157 do_execsql_test 6.8 {
 158   SELECT c FROM hh WHERE (a COLLATE nocase, b) = (SELECT 'def', 2);
 159 } {iii iv}
 160 do_execsql_test 6.9 {
 161   SELECT c FROM hh WHERE (a COLLATE nocase, b) IS NOT (SELECT 'def', 2);
 162 } {i ii v vi}
 163 do_execsql_test 6.10 {
 164   SELECT c FROM hh WHERE (b, a) = (SELECT 2, 'def');
 165 } {iii}
 166
 167 do_execsql_test 7.0 {
 168   CREATE TABLE xy(i INTEGER PRIMARY KEY, j, k);
 169   INSERT INTO xy VALUES(1, 1, 1);
 170   INSERT INTO xy VALUES(2, 2, 2);
 171   INSERT INTO xy VALUES(3, 3, 3);
 172   INSERT INTO xy VALUES(4, 4, 4);
 173 }
 174
 175
 176 foreach {tn sql res eqp} {
 177   1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2}
 178     "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)}"
 179
 180   2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2}
 181     "0 0 0 {SCAN TABLE xy}"
 182
 183   3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2}
 184     "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid<?)}"
 185
 186   4 "SELECT * FROM xy WHERE (i, j) > (2, 1)" {2 2 2 3 3 3 4 4 4}
 187     "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}"
 188
 189   5 "SELECT * FROM xy WHERE (i, j) > ('2', 1)" {2 2 2 3 3 3 4 4 4}
 190     "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}"
 191
 192 } {
 193   do_eqp_test 7.$tn.1 $sql $eqp
 194   do_execsql_test 7.$tn.2 $sql $res
 195 }
 196
 197 do_execsql_test 8.0 {
 198   CREATE TABLE j1(a);
 199 }
 200 do_execsql_test 8.1 {
 201   SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?)
 202 }
 203
 204 do_execsql_test 9.0 {
 205   CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
 206   INSERT INTO t2 VALUES(1, 1, 1);
 207   INSERT INTO t2 VALUES(2, 2, 2);
 208   INSERT INTO t2 VALUES(3, 3, 3);
 209   INSERT INTO t2 VALUES(4, 4, 4);
 210   INSERT INTO t2 VALUES(5, 5, 5);
 211 }
 212
 213 foreach {tn q res} {
 214   1 "(a, b) > (2, 1)" {2 3 4 5}
 215   2 "(a, b) > (2, 2)" {3 4 5}
 216   3 "(a, b) < (4, 5)" {1 2 3 4}
 217   4 "(a, b) < (4, 3)" {1 2 3}
 218 } {
 219   do_execsql_test 9.$tn "SELECT c FROM t2 WHERE $q" $res
 220 }
 221
 222 do_execsql_test 10.0 {
 223   CREATE TABLE dual(dummy); INSERT INTO dual(dummy) VALUES('X');
 224   CREATE TABLE t3(a TEXT,b TEXT,c TEXT,d TEXT,e TEXT,f TEXT);
 225   CREATE INDEX t3x ON t3(b,c,d,e,f);
 226
 227   SELECT a FROM t3
 228     WHERE (c,d) IN (SELECT 'c','d' FROM dual)
 229     AND (a,b,e) IN (SELECT 'a','b','d' FROM dual);
 230 }
 231
 232 do_catchsql_test 11.1 {
 233   CREATE TABLE t11(a);
 234   SELECT * FROM t11 WHERE (a,a)<=1;
 235 } {1 {row value misused}}
 236 do_catchsql_test 11.2 {
 237   SELECT * FROM t11 WHERE (a,a)<1;
 238 } {1 {row value misused}}
 239 do_catchsql_test 11.3 {
 240   SELECT * FROM t11 WHERE (a,a)>=1;
 241 } {1 {row value misused}}
 242 do_catchsql_test 11.4 {
 243   SELECT * FROM t11 WHERE (a,a)>1;
 244 } {1 {row value misused}}
 245 do_catchsql_test 11.5 {
 246   SELECT * FROM t11 WHERE (a,a)==1;
 247 } {1 {row value misused}}
 248 do_catchsql_test 11.6 {
 249   SELECT * FROM t11 WHERE (a,a)<>1;
 250 } {1 {row value misused}}
 251 do_catchsql_test 11.7 {
 252   SELECT * FROM t11 WHERE (a,a) IS 1;
 253 } {1 {row value misused}}
 254 do_catchsql_test 11.8 {
 255   SELECT * FROM t11 WHERE (a,a) IS NOT 1;
 256 } {1 {row value misused}}
 257
 258 # 2016-10-27: https://www.sqlite.org/src/tktview/fef4bb4bd9185ec8f
 259 # Incorrect result from a LEFT JOIN with a row-value constraint
 260 #
 261 do_execsql_test 12.1 {
 262   DROP TABLE IF EXISTS t1;
 263   CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2);
 264   DROP TABLE IF EXISTS t2;
 265   CREATE TABLE t2(x,y); INSERT INTO t2 VALUES(3,4);
 266   SELECT *,'x' FROM t1 LEFT JOIN t2 ON (a,b)=(x,y);
 267 } {1 2 {} {} x}
 268
 269
 270 foreach {tn sql} {
 271   0 "SELECT (1,2) AS x WHERE x=3"
 272   1 "SELECT (1,2) BETWEEN 1 AND 2"
 273   2 "SELECT 1 BETWEEN (1,2) AND 2"
 274   3 "SELECT 2 BETWEEN 1 AND (1,2)"
 275   4 "SELECT (1,2) FROM (SELECT 1) ORDER BY 1"
 276   5 "SELECT (1,2) FROM (SELECT 1) GROUP BY 1"
 277 } {
 278   do_catchsql_test 13.$tn $sql {1 {row value misused}}
 279 }
 280
 281 do_execsql_test 14.0 {
 282   CREATE TABLE t12(x);
 283   INSERT INTO t12 VALUES(2), (4);
 284 }
 285 do_execsql_test 14.1 "SELECT 1 WHERE (2,2) BETWEEN (1,1) AND (3,3)" 1
 286 do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
 287 do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
 288 do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1
 289 do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1
 290 do_execsql_test 14.6 {
 291   SELECT 1 FROM t12 WHERE (1,x) BETWEEN (1,1) AND (3,3)
 292 } {1 1}
 293
 294 #-------------------------------------------------------------------------
 295 # Test that errors are not concealed by the SELECT flattening or
 296 # WHERE-clause push-down optimizations.
 297 do_execsql_test 14.1 {
 298   CREATE TABLE x1(a PRIMARY KEY, b);
 299   CREATE TABLE x2(a INTEGER PRIMARY KEY, b);
 300 }
 301
 302 foreach {tn n sql} {
 303   1 0 "SELECT * FROM (SELECT (1, 1) AS c FROM x1) WHERE c=1"
 304   2 2 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9) AS y) WHERE y<1"
 305   3 3 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9,10) AS y) WHERE y<1"
 306   4 0 "SELECT * FROM (SELECT (a, b) AS c FROM x1), x2 WHERE c=a"
 307   5 0 "SELECT * FROM (SELECT a AS c, (1, 2, 3) FROM x1), x2 WHERE c=a"
 308   6 0 "SELECT * FROM (SELECT 1 AS c, (1, 2, 3) FROM x1) WHERE c=1"
 309 } {
 310   if {$n==0} {
 311     set err "row value misused"
 312   } else {
 313     set err "sub-select returns $n columns - expected 1"
 314   }
 315   do_catchsql_test 14.2.$tn $sql [list 1 $err]
 316 }
 317
 318 #--------------------------------------------------------------------------
 319 # Test for vector size mismatches concealed by unexpanded subqueries.
 320 #
 321 do_catchsql_test 15.1 {
 322   DETACH (SELECT * FROM (SELECT 1,2))<3;
 323 } {1 {row value misused}}
 324 do_catchsql_test 15.2 {
 325   UPDATE x1 SET a=(SELECT * FROM (SELECT b,2))<3;
 326 } {1 {row value misused}}
 327 do_catchsql_test 15.3 {
 328   UPDATE x1 SET a=NULL WHERE  a<(SELECT * FROM (SELECT b,2));
 329 } {1 {sub-select returns 2 columns - expected 1}}
 330 do_catchsql_test 15.4 {
 331   DELETE FROM x1 WHERE  a<(SELECT * FROM (SELECT b,2));
 332 } {1 {sub-select returns 2 columns - expected 1}}
 333 do_catchsql_test 15.5 {
 334   INSERT INTO x1(a,b) VALUES(1,(SELECT * FROM (SELECT 1,2))<3);
 335 } {1 {row value misused}}
 336
 337 #-------------------------------------------------------------------------
 338 # Row-values used in UPDATE statements within TRIGGERs
 339 #
 340 # Ticket https://www.sqlite.org/src/info/8c9458e703666e1a
 341 #
 342 do_execsql_test 16.1 {
 343   CREATE TABLE t16a(a,b,c);
 344   INSERT INTO t16a VALUES(1,2,3);
 345   CREATE TABLE t16b(x);
 346   INSERT INTO t16b(x) VALUES(1);
 347   CREATE TRIGGER t16r AFTER UPDATE ON t16b BEGIN
 348      UPDATE t16a SET (a,b,c)=(SELECT new.x,new.x+1,new.x+2);
 349   END;
 350   UPDATE t16b SET x=7;
 351   SELECT * FROM t16a;
 352 } {7 8 9}
 353 do_execsql_test 16.2 {
 354   UPDATE t16b SET x=97;
 355   SELECT * FROM t16a;
 356 } {97 98 99}
 357
 358 do_execsql_test 16.3 {
 359   CREATE TABLE t16c(a, b, c, d, e);
 360   INSERT INTO t16c VALUES(1, 'a', 'b', 'c', 'd');
 361   CREATE TRIGGER t16c1 AFTER INSERT ON t16c BEGIN
 362     UPDATE t16c SET (c, d) = (SELECT 'A', 'B'), (e, b) = (SELECT 'C', 'D')
 363       WHERE a = new.a-1;
 364   END;
 365
 366   SELECT * FROM t16c;
 367 } {1 a b c d}
 368
 369 do_execsql_test 16.4 {
 370   INSERT INTO t16c VALUES(2, 'w', 'x', 'y', 'z');
 371   SELECT * FROM t16c;
 372 } {
 373   1 D A B C
 374   2 w x y z
 375 }
 376
 377 do_execsql_test 16.5 {
 378   DROP TRIGGER t16c1;
 379   PRAGMA recursive_triggers = 1;
 380   INSERT INTO t16c VALUES(3, 'i', 'ii', 'iii', 'iv');
 381   CREATE TRIGGER t16c1 AFTER UPDATE ON t16c WHEN new.a>1 BEGIN
 382     UPDATE t16c SET (e, d) = (
 383       SELECT b, c FROM t16c WHERE a = new.a-1
 384     ), (c, b) = (
 385       SELECT d, e FROM t16c WHERE a = new.a-1
 386     ) WHERE a = new.a-1;
 387   END;
 388
 389   UPDATE t16c SET a=a WHERE a=3;
 390   SELECT * FROM t16c;
 391 } {
 392   1 C B A D
 393   2 z y x w
 394   3 i ii iii iv
 395 }
 396
 397 finish_test