test/join5.test

   1 # 2005 September 19
   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.
  12 #
  13 # This file implements tests for left outer joins containing ON
  14 # clauses that restrict the scope of the left term of the join.
  15 #
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19 set testprefix join5
  20
  21
  22 do_test join5-1.1 {
  23   execsql {
  24     BEGIN;
  25     CREATE TABLE t1(a integer primary key, b integer, c integer);
  26     CREATE TABLE t2(x integer primary key, y);
  27     CREATE TABLE t3(p integer primary key, q);
  28     INSERT INTO t3 VALUES(11,'t3-11');
  29     INSERT INTO t3 VALUES(12,'t3-12');
  30     INSERT INTO t2 VALUES(11,'t2-11');
  31     INSERT INTO t2 VALUES(12,'t2-12');
  32     INSERT INTO t1 VALUES(1, 5, 0);
  33     INSERT INTO t1 VALUES(2, 11, 2);
  34     INSERT INTO t1 VALUES(3, 12, 1);
  35     COMMIT;
  36   }
  37 } {}
  38 do_test join5-1.2 {
  39   execsql {
  40     select * from t1 left join t2 on t1.b=t2.x and t1.c=1
  41   }
  42 } {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12}
  43 do_test join5-1.3 {
  44   execsql {
  45     select * from t1 left join t2 on t1.b=t2.x where t1.c=1
  46   }
  47 } {3 12 1 12 t2-12}
  48 do_test join5-1.4 {
  49   execsql {
  50     select * from t1 left join t2 on t1.b=t2.x and t1.c=1
  51                      left join t3 on t1.b=t3.p and t1.c=2
  52   }
  53 } {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}}
  54 do_test join5-1.5 {
  55   execsql {
  56     select * from t1 left join t2 on t1.b=t2.x and t1.c=1
  57                      left join t3 on t1.b=t3.p where t1.c=2
  58   }
  59 } {2 11 2 {} {} 11 t3-11}
  60
  61 # Ticket #2403
  62 #
  63 do_test join5-2.1 {
  64   execsql {
  65     CREATE TABLE ab(a,b);
  66     INSERT INTO "ab" VALUES(1,2);
  67     INSERT INTO "ab" VALUES(3,NULL);
  68
  69     CREATE TABLE xy(x,y);
  70     INSERT INTO "xy" VALUES(2,3);
  71     INSERT INTO "xy" VALUES(NULL,1);
  72   }
  73   execsql {SELECT * FROM xy LEFT JOIN ab ON 0}
  74 } {2 3 {} {} {} 1 {} {}}
  75 do_test join5-2.2 {
  76   execsql {SELECT * FROM xy LEFT JOIN ab ON 1}
  77 } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}
  78 do_test join5-2.3 {
  79   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL}
  80 } {2 3 {} {} {} 1 {} {}}
  81 do_test join5-2.4 {
  82   execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0}
  83 } {}
  84 do_test join5-2.5 {
  85   execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0}
  86 } {}
  87 do_test join5-2.6 {
  88   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0}
  89 } {}
  90 do_test join5-2.7 {
  91   execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1}
  92 } {2 3 {} {} {} 1 {} {}}
  93 do_test join5-2.8 {
  94   execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1}
  95 } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}}
  96 do_test join5-2.9 {
  97   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1}
  98 } {2 3 {} {} {} 1 {} {}}
  99 do_test join5-2.10 {
 100   execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL}
 101 } {}
 102 do_test join5-2.11 {
 103   execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL}
 104 } {}
 105 do_test join5-2.12 {
 106   execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL}
 107 } {}
 108
 109 # Ticket https://www.sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601
 110 # Incorrect output on a LEFT JOIN.
 111 #
 112 do_execsql_test join5-3.1 {
 113   DROP TABLE IF EXISTS t1;
 114   DROP TABLE IF EXISTS t2;
 115   DROP TABLE IF EXISTS t3;
 116   CREATE TABLE x1(a);
 117   INSERT INTO x1 VALUES(1);
 118   CREATE TABLE x2(b NOT NULL);
 119   CREATE TABLE x3(c, d);
 120   INSERT INTO x3 VALUES('a', NULL);
 121   INSERT INTO x3 VALUES('b', NULL);
 122   INSERT INTO x3 VALUES('c', NULL);
 123   SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b;
 124 } {1 {} {} {}}
 125 do_execsql_test join5-3.2 {
 126   DROP TABLE IF EXISTS t1;
 127   DROP TABLE IF EXISTS t2;
 128   DROP TABLE IF EXISTS t3;
 129   DROP TABLE IF EXISTS t4;
 130   DROP TABLE IF EXISTS t5;
 131   CREATE TABLE t1(x text NOT NULL, y text);
 132   CREATE TABLE t2(u text NOT NULL, x text NOT NULL);
 133   CREATE TABLE t3(w text NOT NULL, v text);
 134   CREATE TABLE t4(w text NOT NULL, z text NOT NULL);
 135   CREATE TABLE t5(z text NOT NULL, m text);
 136   INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL);
 137   INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL);
 138   INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL);
 139   INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL);
 140   INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c',
 141                         'f6d7661f-4efe-4c90-87b5-858e61cd178b');
 142   SELECT *
 143     FROM t3
 144          INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL
 145          LEFT JOIN t4  ON t4.w = t3.w
 146          LEFT JOIN t5  ON t5.z = t4.z
 147          LEFT JOIN t2  ON t2.u = t5.m
 148          LEFT JOIN t1 xyz ON xyz.y = t2.x;
 149 } {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}}
 150 do_execsql_test join5-3.3 {
 151   DROP TABLE IF EXISTS x1;
 152   DROP TABLE IF EXISTS x2;
 153   DROP TABLE IF EXISTS x3;
 154   CREATE TABLE x1(a);
 155   INSERT INTO x1 VALUES(1);
 156   CREATE TABLE x2(b NOT NULL);
 157   CREATE TABLE x3(c, d);
 158   INSERT INTO x3 VALUES('a', NULL);
 159   INSERT INTO x3 VALUES('b', NULL);
 160   INSERT INTO x3 VALUES('c', NULL);
 161   SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
 162 } {}
 163
 164 # Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on
 165 # 2015-08-20.  LEFT JOIN and the push-down optimization.
 166 #
 167 do_execsql_test join5-4.1 {
 168   SELECT *
 169   FROM (
 170       SELECT 'apple' fruit
 171       UNION ALL SELECT 'banana'
 172   ) a
 173   JOIN (
 174       SELECT 'apple' fruit
 175       UNION ALL SELECT 'banana'
 176   ) b ON a.fruit=b.fruit
 177   LEFT JOIN (
 178       SELECT 1 isyellow
 179   ) c ON b.fruit='banana';
 180 } {apple apple {} banana banana 1}
 181 do_execsql_test join5-4.2 {
 182   SELECT *
 183     FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
 184          LEFT JOIN (SELECT 1) ON fruit='banana';
 185 } {apple {} banana 1}
 186
 187 #-------------------------------------------------------------------------
 188 do_execsql_test 5.0 {
 189   CREATE TABLE y1(x, y, z);
 190   INSERT INTO y1 VALUES(0, 0, 1);
 191   CREATE TABLE y2(a);
 192 }
 193
 194 do_execsql_test 5.1 {
 195   SELECT count(z) FROM y1 LEFT JOIN y2 ON x GROUP BY y;
 196 } 1
 197
 198 do_execsql_test 5.2 {
 199   SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x GROUP BY y;
 200 } 1
 201
 202 do_execsql_test 5.3 {
 203   CREATE VIEW v1 AS SELECT x, y, z FROM y1;
 204   SELECT count(z) FROM v1 LEFT JOIN y2 ON x GROUP BY y;
 205 } 1
 206
 207 do_execsql_test 5.4 {
 208   SELECT count(z) FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
 209 } 1
 210
 211 do_execsql_test 5.5 {
 212   SELECT * FROM ( SELECT * FROM y1 ) LEFT JOIN y2 ON x
 213 } {0 0 1 {}}
 214
 215 #-------------------------------------------------------------------------
 216 #
 217 reset_db
 218 do_execsql_test 6.1 {
 219   CREATE TABLE t1(x);
 220   INSERT INTO t1 VALUES(1);
 221
 222   CREATE TABLE t2(y INTEGER PRIMARY KEY,a,b);
 223   INSERT INTO t2 VALUES(1,2,3);
 224   CREATE INDEX t2a ON t2(a);
 225   CREATE INDEX t2b ON t2(b);
 226 }
 227
 228 do_execsql_test 6.2 {
 229   SELECT * FROM t1 LEFT JOIN t2 ON a=2 OR b=3 WHERE y IS NULL;
 230 } {}
 231
 232 do_execsql_test 6.3.1 {
 233   CREATE TABLE t3(x);
 234   INSERT INTO t3 VALUES(1);
 235   CREATE TABLE t4(y, z);
 236   SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);
 237 } {!!!}
 238
 239 do_execsql_test 6.3.2 {
 240   CREATE INDEX t4i ON t4(y, ifnull(z, '!!!'));
 241   SELECT ifnull(z, '!!!') FROM t3 LEFT JOIN t4 ON (x=y);
 242 } {!!!}
 243
 244 # 2019-02-08 https://sqlite.org/src/info/4e8e4857d32d401f
 245 reset_db
 246 do_execsql_test 6.100 {
 247   CREATE TABLE t1(aa, bb);
 248   CREATE INDEX t1x1 on t1(abs(aa), abs(bb));
 249   INSERT INTO t1 VALUES(-2,-3),(+2,-3),(-2,+3),(+2,+3);
 250   SELECT * FROM (t1)
 251    WHERE ((abs(aa)=1 AND 1=2) OR abs(aa)=2)
 252      AND abs(bb)=3
 253   ORDER BY +1, +2;
 254 } {-2 -3 -2 3 2 -3 2 3}
 255
 256 #-------------------------------------------------------------------------
 257 #
 258 reset_db
 259 do_execsql_test 7.0 {
 260   CREATE TABLE t1(x);
 261   INSERT INTO t1 VALUES(1);
 262 }
 263
 264 do_execsql_test 7.1 {
 265   CREATE TABLE t2(x, y, z);
 266   CREATE INDEX t2xy ON t2(x, y);
 267   WITH s(i) AS (
 268     SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000
 269   )
 270   INSERT INTO t2 SELECT i/10, i, NULL FROM s;
 271   ANALYZE;
 272 }
 273
 274 do_eqp_test 7.2 {
 275   SELECT * FROM t1 LEFT JOIN t2 ON (
 276     t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
 277   );
 278 } {
 279   QUERY PLAN
 280   |--SCAN t1
 281   `--MULTI-INDEX OR
 282      |--INDEX 1
 283      |  `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
 284      `--INDEX 2
 285         `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
 286 }
 287
 288 do_execsql_test 7.3 {
 289   CREATE TABLE t3(x);
 290   INSERT INTO t3(x) VALUES(1);
 291   CREATE INDEX t3x ON t3(x);
 292
 293   CREATE TABLE t4(x, y, z);
 294   CREATE INDEX t4xy ON t4(x, y);
 295   CREATE INDEX t4xz ON t4(x, z);
 296
 297   WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000)
 298   INSERT INTO t4 SELECT i/10, i, i FROM s;
 299
 300   ANALYZE;
 301   UPDATE sqlite_stat1 SET stat='1000000 10 1' WHERE idx='t3x';
 302   ANALYZE sqlite_schema;
 303 }
 304
 305 # If both sides of the OR reference the right-hand side of the LEFT JOIN
 306 # then simplify the LEFT JOIN.
 307 #
 308 do_eqp_test 7.4 {
 309   SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?);
 310 } {
 311   QUERY PLAN
 312   |--SCAN t4
 313   `--SEARCH t3 USING COVERING INDEX t3x (x=?)
 314 }
 315 # If only one side of the OR references the right-hand side of the LEFT JOIN
 316 # then do not do the simplification
 317 #
 318 do_eqp_test 7.4b {
 319   SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t3.x = ?);
 320 } {
 321   QUERY PLAN
 322   |--SCAN t3
 323   `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN
 324 }
 325 do_eqp_test 7.4c {
 326   SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t3.x = ? OR t4.z = ?);
 327 } {
 328   QUERY PLAN
 329   |--SCAN t3
 330   `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN
 331 }
 332 do_eqp_test 7.4d {
 333   SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?);
 334 } {
 335   QUERY PLAN
 336   |--SCAN t3
 337   |--BLOOM FILTER ON t4 (x=?)
 338   `--SEARCH t4 USING INDEX t4xz (x=?)
 339 }
 340
 341 reset_db
 342 do_execsql_test 8.0 {
 343   CREATE TABLE t0 (c0, c1, PRIMARY KEY (c0, c1));
 344   CREATE TABLE t1 (c0);
 345
 346   INSERT INTO t1 VALUES (2);
 347
 348   INSERT INTO t0 VALUES(0, 10);
 349   INSERT INTO t0 VALUES(1, 10);
 350   INSERT INTO t0 VALUES(2, 10);
 351   INSERT INTO t0 VALUES(3, 10);
 352 }
 353
 354 do_execsql_test 8.1 {
 355   SELECT * FROM t0, t1
 356   WHERE (t0.c1 >= 1 OR t0.c1 < 1) AND t0.c0 IN (1, t1.c0) ORDER BY 1;
 357 } {
 358   1 10 2
 359   2 10 2
 360 }
 361
 362
 363 # 2022-01-31 dbsqlfuzz 787d9bd73164c6f0c85469e2e48b2aff19af6938
 364 #
 365 reset_db
 366 do_execsql_test 9.1 {
 367   CREATE TABLE t1(a ,b FLOAT);
 368   INSERT INTO t1 VALUES(1,1);
 369   CREATE INDEX t1x1 ON t1(a,b,a,a,a,a,a,a,a,a,a,b);
 370   ANALYZE sqlite_schema;
 371   INSERT INTO sqlite_stat1 VALUES('t1','t1x1','648 324 81 81 81 81 81 81 81081 81 81 81');
 372   ANALYZE sqlite_schema;
 373 }
 374 ifcapable allow_rowid_in_view {
 375   set res {1 {no such column: rowid}}
 376 } else {
 377   set res {0 1}
 378 }
 379 do_catchsql_test 9.2 {
 380   SELECT a FROM
 381       (SELECT a FROM t1 NATURAL LEFT JOIN t1) NATURAL LEFT JOIN t1
 382   WHERE (rowid,1)<=(5,0);
 383 } $res
 384
 385 # 2022-03-02 https://sqlite.org/forum/info/50a1bbe08ce4c29c
 386 # Bloom-filter pulldown is incompatible with skip-scan.
 387 #
 388 reset_db
 389 do_execsql_test 10.1 {
 390   CREATE TABLE t1(x INT);
 391   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
 392     INSERT INTO t1(x) SELECT 0 FROM c;
 393   CREATE INDEX t1x1 ON t1(x BETWEEN 0 AND 10, x);
 394   ANALYZE;
 395   DELETE FROM t1;
 396   INSERT INTO t1 VALUES(0),(0);
 397   CREATE VIEW v1 AS SELECT * FROM t1 NATURAL JOIN t1 WHERE (x BETWEEN 0 AND 10) OR true;
 398   CREATE VIEW v2 AS SELECT * FROM v1 NATURAL JOIN v1;
 399   CREATE VIEW v3 AS SELECT * FROM v2, v1 USING (x) GROUP BY x;
 400   SELECT x FROM v3;
 401 } {0}
 402
 403 # 2022-03-24 https://sqlite.org/forum/forumpost/031e262a89b6a9d2
 404 # Bloom-filter on a LEFT JOIN with NULL-based WHERE constraints.
 405 #
 406 reset_db
 407 do_execsql_test 11.1 {
 408   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
 409   CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
 410   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8)
 411   INSERT INTO t1(a,b) SELECT x, 10*x FROM c;
 412   INSERT INTO t2(c,d) SELECT b*2, 100*a FROM t1;
 413   ANALYZE;
 414   DELETE FROM sqlite_stat1;
 415   INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES
 416     ('t1',NULL,150105),('t2',NULL,98747);
 417   ANALYZE sqlite_schema;
 418 } {}
 419 do_execsql_test 11.2 {
 420   SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d IS NULL;
 421 } {4}
 422 do_execsql_test 11.3 {
 423   SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d=100;
 424 } {1}
 425 do_execsql_test 11.4 {
 426   SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d>=300;
 427 } {2}
 428
 429 # 2022-05-03 https://sqlite.org/forum/forumpost/2482b32700384a0f
 430 # Bloom-filter pull-down does not handle NOT NULL constraints correctly.
 431 #
 432 reset_db
 433 do_execsql_test 12.1 {
 434   CREATE TABLE t1(a INT, b INT, c INT);
 435   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
 436     INSERT INTO t1(a,b,c) SELECT x, x*1000, x*1000000 FROM c;
 437   CREATE TABLE t2(b INT, x INT);
 438   INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%3==0;
 439   CREATE INDEX t2b ON t2(b);
 440   CREATE TABLE t3(c INT, y INT);
 441   INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%4==0;
 442   CREATE INDEX t3c ON t3(c);
 443   INSERT INTO t1(a,b,c) VALUES(200, 200000, NULL);
 444   ANALYZE;
 445 } {}
 446 do_execsql_test 12.2 {
 447   SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 WHERE x>0 AND y>0
 448   ORDER BY +a;
 449 } {
 450   12  12000  12000000  12  12
 451   24  24000  24000000  24  24
 452   36  36000  36000000  36  36
 453   48  48000  48000000  48  48
 454   60  60000  60000000  60  60
 455   72  72000  72000000  72  72
 456   84  84000  84000000  84  84
 457   96  96000  96000000  96  96
 458 }
 459
 460
 461
 462
 463 finish_test