test/rowvalue9.test

   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 #
 313 reset_db
 314 do_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
 319 do_execsql_test 9.1 {
 320   SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );
 321 } {
 322   2 2 3 3
 323 }
 324 do_execsql_test 9.2 {
 325   CREATE INDEX i1 ON t1(a);
 326 }
 327
 328 do_execsql_test 9.4 {
 329   SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );
 330 } {
 331   2 2 3 3
 332 }
 333 do_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
 339 do_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 3
 344 }
 345 do_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
 352 finish_test