test/whereD.test

   1 # 2012 August 24
   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 that an index may be used as a covering
  13 # index when there are OR expressions in the WHERE clause.
  14 #
  15
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19 set ::testprefix whereD
  20
  21 do_execsql_test 1.1 {
  22   CREATE TABLE t(i,j,k,m,n);
  23   CREATE INDEX ijk ON t(i,j,k);
  24   CREATE INDEX jmn ON t(j,m,n);
  25
  26   INSERT INTO t VALUES(3, 3, 'three', 3, 'tres');
  27   INSERT INTO t VALUES(2, 2, 'two', 2, 'dos');
  28   INSERT INTO t VALUES(1, 1, 'one', 1, 'uno');
  29   INSERT INTO t VALUES(4, 4, 'four', 4, 'cuatro');
  30 }
  31
  32 do_execsql_test 1.2 {
  33   SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2);
  34 } {one two}
  35 do_execsql_test 1.3 {
  36   SELECT k FROM t WHERE (i=1 AND j=1) OR (+i=2 AND j=2);
  37 } {one two}
  38 do_execsql_test 1.4 {
  39   SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2);
  40 } {uno dos}
  41 do_execsql_test 1.5 {
  42   SELECT k, n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2);
  43 } {one uno two dos}
  44 do_execsql_test 1.6 {
  45   SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3);
  46 } {one two three}
  47 do_execsql_test 1.7 {
  48   SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3);
  49 } {uno dos tres}
  50 do_execsql_test 1.8 {
  51   SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2);
  52 } {one two}
  53 do_execsql_test 1.9 {
  54   SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3);
  55 } {one two three}
  56 do_execsql_test 1.10 {
  57   SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3);
  58 } {uno dos tres}
  59 do_execsql_test 1.11 {
  60   SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3);
  61 } {one two three}
  62 do_execsql_test 1.12 {
  63   SELECT n FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3);
  64 } {uno dos tres}
  65 do_execsql_test 1.13 {
  66   SELECT k FROM t WHERE (j=1 AND m=1) OR (i=2 AND j=2) OR (i=3 AND j=3);
  67 } {one two three}
  68 do_execsql_test 1.14 {
  69   SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND i=2) OR (i=3 AND j=3);
  70 } {one two three}
  71 do_execsql_test 1.15 {
  72   SELECT k FROM t WHERE (i=1 AND j=2) OR (i=2 AND j=1) OR (i=3 AND j=4);
  73 } {}
  74 do_execsql_test 1.16 {
  75   SELECT k FROM t WHERE (i=1 AND (j=1 or j=2)) OR (i=3 AND j=3);
  76 } {one three}
  77
  78 do_execsql_test 2.0 {
  79   CREATE TABLE t1(a,b,c,d);
  80   CREATE INDEX t1b ON t1(b);
  81   CREATE INDEX t1c ON t1(c);
  82   CREATE INDEX t1d ON t1(d);
  83   CREATE TABLE t2(x,y);
  84   CREATE INDEX t2y ON t2(y);
  85
  86   INSERT INTO t1 VALUES(1,2,3,4);
  87   INSERT INTO t1 VALUES(5,6,7,8);
  88   INSERT INTO t2 VALUES(1,2);
  89   INSERT INTO t2 VALUES(2,7);
  90   INSERT INTO t2 VALUES(3,4);
  91 } {}
  92 do_execsql_test 2.1 {
  93   SELECT a, x FROM t1 JOIN t2 ON +y=d OR x=7 ORDER BY a, x;
  94 } {1 3}
  95 do_execsql_test 2.2 {
  96   SELECT a, x FROM t1 JOIN t2 ON y=d OR x=7 ORDER BY a, x;
  97 } {1 3}
  98
  99
 100 # Similar to [do_execsql_test], except that two elements are appended
 101 # to the result - the string "search" and the number of times test variable
 102 # sqlite3_search_count is incremented by running the supplied SQL. e.g.
 103 #
 104 #   do_searchcount_test 1.0 { SELECT * FROM t1 } {x y search 2}
 105 #
 106 proc do_searchcount_test {tn sql res} {
 107   uplevel [subst -nocommands {
 108     do_test $tn {
 109       set ::sqlite_search_count 0
 110       concat [db eval {$sql}] search [set ::sqlite_search_count]
 111     } [list $res]
 112   }]
 113 }
 114
 115 do_execsql_test 3.0 {
 116   CREATE TABLE t3(a, b, c);
 117   CREATE UNIQUE INDEX i3 ON t3(a, b);
 118   INSERT INTO t3 VALUES(1, 'one', 'i');
 119   INSERT INTO t3 VALUES(3, 'three', 'iii');
 120   INSERT INTO t3 VALUES(6, 'six', 'vi');
 121   INSERT INTO t3 VALUES(2, 'two', 'ii');
 122   INSERT INTO t3 VALUES(4, 'four', 'iv');
 123   INSERT INTO t3 VALUES(5, 'five', 'v');
 124
 125   CREATE TABLE t4(x PRIMARY KEY, y);
 126   INSERT INTO t4 VALUES('a', 'one');
 127   INSERT INTO t4 VALUES('b', 'two');
 128 }
 129
 130 do_searchcount_test 3.1 {
 131   SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two')
 132 } {1 one 2 two search 4}
 133
 134 do_searchcount_test 3.2 {
 135   SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two')
 136 } {1 i 2 ii search 6}
 137
 138 do_searchcount_test 3.4.1 {
 139   SELECT y FROM t4 WHERE x='a'
 140 } {one search 2}
 141 do_searchcount_test 3.4.2 {
 142   SELECT a, b FROM t3 WHERE
 143         (a=1 AND b=(SELECT y FROM t4 WHERE x='a'))
 144      OR (a=2 AND b='two')
 145 } {1 one 2 two search 6}
 146 do_searchcount_test 3.4.3 {
 147   SELECT a, b FROM t3 WHERE
 148         (a=2 AND b='two')
 149      OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a'))
 150 } {2 two 1 one search 6}
 151 do_searchcount_test 3.4.4 {
 152   SELECT a, b FROM t3 WHERE
 153         (a=2 AND b=(SELECT y FROM t4 WHERE x='b'))
 154      OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a'))
 155 } {2 two 1 one search 8}
 156
 157 do_searchcount_test 3.5.1 {
 158   SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR rowid=4
 159 } {1 one 2 two search 2}
 160 do_searchcount_test 3.5.2 {
 161   SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR rowid=4
 162 } {1 i 2 ii search 3}
 163
 164 # Ticket [d02e1406a58ea02d] (2012-10-04)
 165 # LEFT JOIN with an OR in the ON clause causes segfault
 166 #
 167 do_test 4.1 {
 168   db eval {
 169     CREATE TABLE t41(a,b,c);
 170     INSERT INTO t41 VALUES(1,2,3), (4,5,6);
 171     CREATE TABLE t42(d,e,f);
 172     INSERT INTO t42 VALUES(3,6,9), (4,8,12);
 173     SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b);
 174   }
 175 } {1 2 3 3 6 9 4 5 6 {} {} {}}
 176 do_test 4.2 {
 177   db eval {
 178     CREATE INDEX t42d ON t42(d);
 179     CREATE INDEX t42e ON t42(e);
 180     SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b);
 181   }
 182 } {1 2 3 3 6 9 4 5 6 {} {} {}}
 183 do_test 4.3 {
 184   db eval {
 185     SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.d=x.b);
 186   }
 187 } {1 2 3 3 6 9 4 5 6 {} {} {}}
 188
 189 # Ticket [bc1aea7b725f276177]
 190 # Incorrect result on LEFT JOIN with OR constraints and an ORDER BY clause.
 191 #
 192 do_execsql_test 4.4 {
 193   CREATE TABLE t44(a INTEGER, b INTEGER);
 194   INSERT INTO t44 VALUES(1,2);
 195   INSERT INTO t44 VALUES(3,4);
 196   SELECT *
 197     FROM t44 AS x
 198        LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c
 199    WHERE d=4 OR d IS NULL;
 200 } {3 4 3 4}
 201 do_execsql_test 4.5 {
 202   SELECT *
 203     FROM t44 AS x
 204        LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c
 205    WHERE d=4 OR d IS NULL
 206    ORDER BY a;
 207 } {3 4 3 4}
 208 do_execsql_test 4.6 {
 209   CREATE TABLE t46(c INTEGER, d INTEGER);
 210   INSERT INTO t46 SELECT a, b FROM t44;
 211   SELECT * FROM t44 LEFT JOIN t46 ON a=c
 212    WHERE d=4 OR d IS NULL;
 213 } {3 4 3 4}
 214 do_execsql_test 4.7 {
 215   SELECT * FROM t44 LEFT JOIN t46 ON a=c
 216    WHERE d=4 OR d IS NULL
 217    ORDER BY a;
 218 } {3 4 3 4}
 219
 220 # Verify fix of a bug reported on the mailing list by Peter Reid
 221 #
 222 do_execsql_test 5.1 {
 223   DROP TABLE IF EXISTS t;
 224   CREATE TABLE t(c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17);
 225   CREATE INDEX tc0 ON t(c0);
 226   CREATE INDEX tc1 ON t(c1);
 227   CREATE INDEX tc2 ON t(c2);
 228   CREATE INDEX tc3 ON t(c3);
 229   CREATE INDEX tc4 ON t(c4);
 230   CREATE INDEX tc5 ON t(c5);
 231   CREATE INDEX tc6 ON t(c6);
 232   CREATE INDEX tc7 ON t(c7);
 233   CREATE INDEX tc8 ON t(c8);
 234   CREATE INDEX tc9 ON t(c9);
 235   CREATE INDEX tc10 ON t(c10);
 236   CREATE INDEX tc11 ON t(c11);
 237   CREATE INDEX tc12 ON t(c12);
 238   CREATE INDEX tc13 ON t(c13);
 239   CREATE INDEX tc14 ON t(c14);
 240   CREATE INDEX tc15 ON t(c15);
 241   CREATE INDEX tc16 ON t(c16);
 242   CREATE INDEX tc17 ON t(c17);
 243
 244   INSERT INTO t(c0, c16) VALUES (1,1);
 245
 246   SELECT * FROM t WHERE
 247     c0=1 or  c1=1 or  c2=1 or  c3=1 or
 248     c4=1 or  c5=1 or  c6=1 or  c7=1 or
 249     c8=1 or  c9=1 or c10=1 or c11=1 or
 250     c12=1 or c13=1 or c14=1 or c15=1 or
 251     c16=1 or c17=1;
 252 } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {}}
 253 do_execsql_test 5.2 {
 254   DELETE FROM t;
 255   INSERT INTO t(c0,c17) VALUES(1,1);
 256   SELECT * FROM t WHERE
 257     c0=1 or  c1=1 or  c2=1 or  c3=1 or
 258     c4=1 or  c5=1 or  c6=1 or  c7=1 or
 259     c8=1 or  c9=1 or c10=1 or c11=1 or
 260     c12=1 or c13=1 or c14=1 or c15=1 or
 261     c16=1 or c17=1;
 262 } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1}
 263 do_execsql_test 5.3 {
 264   DELETE FROM t;
 265   INSERT INTO t(c0,c15) VALUES(1,1);
 266   SELECT * FROM t WHERE
 267     c0=1 or  c1=1 or  c2=1 or  c3=1 or
 268     c4=1 or  c5=1 or  c6=1 or  c7=1 or
 269     c8=1 or  c9=1 or c10=1 or c11=1 or
 270     c12=1 or c13=1 or c14=1 or c15=1 or
 271     c16=1 or c17=1;
 272 } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {} {}}
 273
 274 #-------------------------------------------------------------------------
 275 do_execsql_test 6.1 {
 276   CREATE TABLE x1(a, b, c, d, e);
 277   CREATE INDEX x1a  ON x1(a);
 278   CREATE INDEX x1bc ON x1(b, c);
 279   CREATE INDEX x1cd ON x1(c, d);
 280
 281   INSERT INTO x1 VALUES(1, 2, 3, 4, 'A');
 282   INSERT INTO x1 VALUES(5, 6, 7, 8, 'B');
 283   INSERT INTO x1 VALUES(9, 10, 11, 12, 'C');
 284   INSERT INTO x1 VALUES(13, 14, 15, 16, 'D');
 285 }
 286
 287 do_searchcount_test 6.2.1 {
 288   SELECT e FROM x1 WHERE b=2 OR c=7;
 289 } {A B search 6}
 290 do_searchcount_test 6.2.2 {
 291   SELECT c FROM x1 WHERE b=2 OR c=7;
 292 } {3 7 search 4}
 293
 294 do_searchcount_test 6.3.1 {
 295   SELECT e FROM x1 WHERE a=1 OR b=10;
 296 } {A C search 6}
 297 do_searchcount_test 6.3.2 {
 298   SELECT c FROM x1 WHERE a=1 OR b=10;
 299 } {3 11 search 5}
 300 do_searchcount_test 6.3.3 {
 301   SELECT rowid FROM x1 WHERE a=1 OR b=10;
 302 } {1 3 search 4}
 303
 304 do_searchcount_test 6.4.1 {
 305   SELECT a FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12
 306 } {1 9 search 6}
 307 do_searchcount_test 6.4.2 {
 308   SELECT b, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12
 309 } {2 3 10 11 search 5}
 310 do_searchcount_test 6.4.3 {
 311   SELECT rowid, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12
 312 } {1 3 3 11 search 4}
 313
 314 do_searchcount_test 6.5.1 {
 315   SELECT a FROM x1 WHERE rowid = 2 OR c=11
 316 } {5 9 search 3}
 317 do_searchcount_test 6.5.2 {
 318   SELECT d FROM x1 WHERE rowid = 2 OR c=11
 319 } {8 12 search 2}
 320 do_searchcount_test 6.5.3 {
 321   SELECT d FROM x1 WHERE c=11 OR rowid = 2
 322 } {12 8 search 2}
 323 do_searchcount_test 6.5.4 {
 324   SELECT a FROM x1 WHERE c=11 OR rowid = 2
 325 } {9 5 search 3}
 326
 327 do_searchcount_test 6.6.1 {
 328   SELECT rowid FROM x1 WHERE a=1 OR b=6 OR c=11
 329 } {1 2 3 search 6}
 330 do_searchcount_test 6.6.2 {
 331   SELECT c FROM x1 WHERE a=1 OR b=6 OR c=11
 332 } {3 7 11 search 7}
 333 do_searchcount_test 6.6.3 {
 334   SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6
 335 } {11 3 7 search 7}
 336 do_searchcount_test 6.6.4 {
 337   SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1
 338 } {7 11 3 search 7}
 339
 340 # 2020-02-22 ticket aa4378693018aa99
 341 # In the OP_Column opcode, if a cursor is marked with OP_NullRow
 342 # (because it is the right table of a LEFT JOIN that does not match)
 343 # then do not substitute index cursors, as the index cursors do not
 344 # have the VdbeCursor.nullRow flag set.
 345 #
 346 do_execsql_test 6.7 {
 347   DROP TABLE IF EXISTS t1;
 348   DROP TABLE IF EXISTS t2;
 349   CREATE TABLE t1(a UNIQUE, b UNIQUE);
 350   INSERT INTO t1(a,b) VALUES(null,2);
 351   CREATE VIEW t2 AS SELECT * FROM t1 WHERE b<10 OR a<7 ORDER BY b;
 352   SELECT t1.* FROM t1 LEFT JOIN t2 ON abs(t1.a)=abs(t2.b);
 353 } {{} 2}
 354
 355
 356 #-------------------------------------------------------------------------
 357 #
 358 do_execsql_test 7.0 {
 359   CREATE TABLE y1(a, b);
 360   CREATE TABLE y2(x, y);
 361   CREATE INDEX y2xy ON y2(x, y);
 362   INSERT INTO y1 VALUES(1, 1);
 363   INSERT INTO y2 VALUES(3, 3);
 364 }
 365
 366 do_execsql_test 7.1 {
 367   SELECT * FROM y1 LEFT JOIN y2 ON ((x=1 AND y=b) OR (x=2 AND y=b))
 368 } {1 1 {} {}}
 369
 370 do_execsql_test 7.3 {
 371   CREATE TABLE foo (Id INTEGER PRIMARY KEY, fa INTEGER, fb INTEGER);
 372   CREATE TABLE bar (Id INTEGER PRIMARY KEY, ba INTEGER, bb INTEGER);
 373
 374   INSERT INTO foo VALUES(1, 1, 1);
 375   INSERT INTO foo VALUES(2, 1, 2);
 376   INSERT INTO foo VALUES(3, 1, 3);
 377   INSERT INTO foo VALUES(4, 1, 4);
 378   INSERT INTO foo VALUES(5, 1, 5);
 379   INSERT INTO foo VALUES(6, 1, 6);
 380   INSERT INTO foo VALUES(7, 1, 7);
 381   INSERT INTO foo VALUES(8, 1, 8);
 382   INSERT INTO foo VALUES(9, 1, 9);
 383
 384   INSERT INTO bar VALUES(NULL, 1, 1);
 385   INSERT INTO bar VALUES(NULL, 2, 2);
 386   INSERT INTO bar VALUES(NULL, 3, 3);
 387   INSERT INTO bar VALUES(NULL, 1, 4);
 388   INSERT INTO bar VALUES(NULL, 2, 5);
 389   INSERT INTO bar VALUES(NULL, 3, 6);
 390   INSERT INTO bar VALUES(NULL, 1, 7);
 391   INSERT INTO bar VALUES(NULL, 2, 8);
 392   INSERT INTO bar VALUES(NULL, 3, 9);
 393 }
 394
 395 do_execsql_test 7.4 {
 396   SELECT
 397     bar.Id, bar.ba, bar.bb, foo.fb
 398     FROM foo LEFT JOIN bar
 399            ON (bar.ba = 1 AND bar.bb = foo.fb)
 400            OR (bar.ba = 5 AND bar.bb = foo.fb);
 401 } {
 402   1 1 1 1
 403   {} {} {} 2
 404   {} {} {} 3
 405   4 1 4 4
 406   {} {} {} 5
 407   {} {} {} 6
 408   7 1 7 7
 409   {} {} {} 8
 410   {} {} {} 9
 411 }
 412
 413 do_execsql_test 7.5 {
 414   CREATE INDEX idx_bar ON bar(ba, bb);
 415   SELECT
 416     bar.Id, bar.ba, bar.bb, foo.fb
 417     FROM foo LEFT JOIN bar
 418            ON (bar.ba = 1 AND bar.bb = foo.fb)
 419            OR (bar.ba = 5 AND bar.bb = foo.fb);
 420 } {
 421   1 1 1 1
 422   {} {} {} 2
 423   {} {} {} 3
 424   4 1 4 4
 425   {} {} {} 5
 426   {} {} {} 6
 427   7 1 7 7
 428   {} {} {} 8
 429   {} {} {} 9
 430 }
 431
 432
 433 finish_test