test/join2.test

   1 # 2002 May 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.
  12 #
  13 # This file implements tests for joins, including outer joins.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set testprefix join2
  19
  20 do_test join2-1.1 {
  21   execsql {
  22     CREATE TABLE t1(a,b);
  23     INSERT INTO t1 VALUES(1,11);
  24     INSERT INTO t1 VALUES(2,22);
  25     INSERT INTO t1 VALUES(3,33);
  26     SELECT * FROM t1;
  27   }
  28 } {1 11 2 22 3 33}
  29 do_test join2-1.2 {
  30   execsql {
  31     CREATE TABLE t2(b,c);
  32     INSERT INTO t2 VALUES(11,111);
  33     INSERT INTO t2 VALUES(33,333);
  34     INSERT INTO t2 VALUES(44,444);
  35     SELECT * FROM t2;
  36   }
  37 } {11 111 33 333 44 444};
  38 do_test join2-1.3 {
  39   execsql {
  40     CREATE TABLE t3(c,d);
  41     INSERT INTO t3 VALUES(111,1111);
  42     INSERT INTO t3 VALUES(444,4444);
  43     INSERT INTO t3 VALUES(555,5555);
  44     SELECT * FROM t3;
  45   }
  46 } {111 1111 444 4444 555 5555}
  47
  48 do_test join2-1.4 {
  49   execsql {
  50     SELECT * FROM
  51       t1 NATURAL JOIN t2 NATURAL JOIN t3
  52   }
  53 } {1 11 111 1111}
  54 do_test join2-1.5 {
  55   execsql {
  56     SELECT * FROM
  57       t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t3
  58   }
  59 } {1 11 111 1111 3 33 333 {}}
  60 do_test join2-1.6 {
  61   execsql {
  62     SELECT * FROM
  63       t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
  64   }
  65 } {1 11 111 1111}
  66 do_test join2-1.6-rj {
  67   execsql {
  68     SELECT * FROM
  69       t2 NATURAL RIGHT OUTER JOIN t1 NATURAL JOIN t3
  70   }
  71 } {11 111 1 1111}
  72 ifcapable subquery {
  73   do_test join2-1.7 {
  74     execsql {
  75       SELECT * FROM
  76         t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
  77     }
  78   } {1 11 111 1111 2 22 {} {} 3 33 {} {}}
  79   do_test join2-1.7-rj {
  80     execsql {
  81       SELECT a, b, c, d FROM
  82         t2 NATURAL JOIN t3 NATURAL RIGHT JOIN t1
  83     }
  84   } {1 11 111 1111 2 22 {} {} 3 33 {} {}}
  85 }
  86
  87 #-------------------------------------------------------------------------
  88 # Check that ticket [25e335f802ddc] has been resolved. It should be an
  89 # error for the ON clause of a LEFT JOIN to refer to a table to its right.
  90 #
  91 do_execsql_test 2.0 {
  92   CREATE TABLE aa(a);
  93   CREATE TABLE bb(b);
  94   CREATE TABLE cc(c);
  95   INSERT INTO aa VALUES('one');
  96   INSERT INTO bb VALUES('one');
  97   INSERT INTO cc VALUES('one');
  98 }
  99
 100 do_catchsql_test 2.1 {
 101   SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
 102 } {1 {ON clause references tables to its right}}
 103 do_catchsql_test 2.1b {
 104   SELECT * FROM aa RIGHT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
 105 } {1 {ON clause references tables to its right}}
 106 do_catchsql_test 2.2 {
 107   SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
 108 } {0 {one one one}}
 109
 110 #-------------------------------------------------------------------------
 111 # Test that a problem causing where.c to overlook opportunities to
 112 # omit unnecessary tables from a LEFT JOIN when UNIQUE, NOT NULL column
 113 # that makes this possible happens to be the leftmost in its table.
 114 #
 115 reset_db
 116 do_execsql_test 3.0 {
 117   CREATE TABLE t1(k1 INTEGER PRIMARY KEY, k2, k3);
 118   CREATE TABLE t2(k2 INTEGER PRIMARY KEY, v2);
 119
 120   -- Prior to this problem being fixed, table t3_2 would be omitted from
 121   -- the join queries below, but if t3_1 were used in its place it would
 122   -- not.
 123   CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID;
 124   CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID;
 125 }
 126
 127 do_eqp_test 3.1 {
 128   SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3);
 129 } {
 130   QUERY PLAN
 131   |--SCAN t1
 132   `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 133 }
 134
 135 do_eqp_test 3.2 {
 136   SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3);
 137 } {
 138   QUERY PLAN
 139   |--SCAN t1
 140   `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 141 }
 142
 143 #-------------------------------------------------------------------------
 144 # Test that tables other than the rightmost can be omitted from a
 145 # LEFT JOIN query.
 146 #
 147 do_execsql_test 4.0 {
 148   CREATE TABLE c1(k INTEGER PRIMARY KEY, v1);
 149   CREATE TABLE c2(k INTEGER PRIMARY KEY, v2);
 150   CREATE TABLE c3(k INTEGER PRIMARY KEY, v3);
 151
 152   INSERT INTO c1 VALUES(1, 2);
 153   INSERT INTO c2 VALUES(2, 3);
 154   INSERT INTO c3 VALUES(3, 'v3');
 155
 156   INSERT INTO c1 VALUES(111, 1112);
 157   INSERT INTO c2 VALUES(112, 1113);
 158   INSERT INTO c3 VALUES(113, 'v1113');
 159 }
 160 do_execsql_test 4.1.1 {
 161   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 162 } {2 v3 1112 {}}
 163 do_execsql_test 4.1.2 {
 164   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 165 } {2 v3 1112 {}}
 166
 167 do_execsql_test 4.1.3 {
 168   SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 169 } {2 v3 1112 {}}
 170
 171 do_execsql_test 4.1.4 {
 172   SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 173 } {2 v3 2 v3 1112 {} 1112 {}}
 174
 175 do_eqp_test 4.1.5 {
 176   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 177 } {
 178   QUERY PLAN
 179   |--SCAN c1
 180   |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 181   `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 182 }
 183 do_eqp_test 4.1.6 {
 184   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 185 } {
 186   QUERY PLAN
 187   |--SCAN c1
 188   `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 189 }
 190
 191 do_execsql_test 4.2.0 {
 192   DROP TABLE c1;
 193   DROP TABLE c2;
 194   DROP TABLE c3;
 195   CREATE TABLE c1(k UNIQUE, v1);
 196   CREATE TABLE c2(k UNIQUE, v2);
 197   CREATE TABLE c3(k UNIQUE, v3);
 198
 199   INSERT INTO c1 VALUES(1, 2);
 200   INSERT INTO c2 VALUES(2, 3);
 201   INSERT INTO c3 VALUES(3, 'v3');
 202
 203   INSERT INTO c1 VALUES(111, 1112);
 204   INSERT INTO c2 VALUES(112, 1113);
 205   INSERT INTO c3 VALUES(113, 'v1113');
 206 }
 207 do_execsql_test 4.2.1 {
 208   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 209 } {2 v3 1112 {}}
 210 do_execsql_test 4.2.2 {
 211   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 212 } {2 v3 1112 {}}
 213
 214 do_execsql_test 4.2.3 {
 215   SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 216 } {2 v3 1112 {}}
 217
 218 do_execsql_test 4.2.4 {
 219   SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 220 } {2 v3 2 v3 1112 {} 1112 {}}
 221
 222 do_eqp_test 4.2.5 {
 223   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 224 } {
 225   QUERY PLAN
 226   |--SCAN c1
 227   |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?) LEFT-JOIN
 228   `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
 229 }
 230 do_eqp_test 4.2.6 {
 231   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 232 } {
 233   QUERY PLAN
 234   |--SCAN c1
 235   `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
 236 }
 237
 238 # 2017-11-23 (Thanksgiving day)
 239 # OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.
 240 #
 241 do_execsql_test 4.3.0 {
 242   DROP TABLE IF EXISTS t1;
 243   DROP TABLE IF EXISTS t2;
 244   CREATE TABLE t1(x PRIMARY KEY) WITHOUT ROWID;
 245   CREATE TABLE t2(x);
 246   SELECT a.x
 247     FROM t1 AS a
 248     LEFT JOIN t1 AS b ON (a.x=b.x)
 249     LEFT JOIN t2 AS c ON (a.x=c.x);
 250 } {}
 251 do_execsql_test 4.3.1 {
 252   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)
 253     INSERT INTO t1(x) SELECT x FROM c;
 254   INSERT INTO t2(x) SELECT x+9 FROM t1;
 255   SELECT a.x, c.x
 256     FROM t1 AS a
 257     LEFT JOIN t1 AS b ON (a.x=b.x)
 258     LEFT JOIN t2 AS c ON (a.x=c.x);
 259 } {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10}
 260
 261 do_execsql_test 5.0 {
 262   CREATE TABLE s1 (a INTEGER PRIMARY KEY);
 263   CREATE TABLE s2 (a INTEGER PRIMARY KEY);
 264   CREATE TABLE s3 (a INTEGER);
 265   CREATE UNIQUE INDEX ndx on s3(a);
 266 }
 267 do_eqp_test 5.1 {
 268   SELECT s1.a FROM s1 left join s2 using (a);
 269 } {SCAN s1}
 270
 271 do_eqp_test 5.2 {
 272   SELECT s1.a FROM s1 left join s3 using (a);
 273 } {SCAN s1}
 274
 275 do_execsql_test 6.0 {
 276   CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c);
 277   CREATE TABLE u2(a INTEGER PRIMARY KEY, b, c);
 278   CREATE INDEX u1ab ON u1(b, c);
 279 }
 280 do_eqp_test 6.1 {
 281   SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c );
 282 } {SCAN u2}
 283
 284 db close
 285 sqlite3 db :memory:
 286 do_execsql_test 7.0 {
 287   CREATE TABLE t1(a,b);  INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
 288   CREATE TABLE t2(c,d);  INSERT INTO t2 VALUES(2,4),(3,6);
 289   CREATE TABLE t3(x);    INSERT INTO t3 VALUES(9);
 290   CREATE VIEW test AS
 291     SELECT *, 'x'
 292       FROM t1 LEFT JOIN (SELECT * FROM t2, t3) ON (c=b AND x=9)
 293       WHERE c IS NULL;
 294   SELECT * FROM test;
 295 } {3 4 {} {} {} x 5 6 {} {} {} x}
 296
 297 #-------------------------------------------------------------------------
 298 # Ticket [dfd66334].
 299 #
 300 reset_db
 301 do_execsql_test 8.0 {
 302   CREATE TABLE t0(c0);
 303   CREATE TABLE t1(c0);
 304 }
 305
 306 do_execsql_test 8.1 {
 307   SELECT * FROM t0 LEFT JOIN t1
 308   WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0);
 309 }
 310
 311 #-------------------------------------------------------------------------
 312 # Ticket [45f4bf4eb] reported by Manuel Rigger (2020-04-25)
 313 #
 314 # Follow up error reported by Eric Speckman on the SQLite forum
 315 # https://sqlite.org/forum/info/c49496d24d35bd7c (2020-08-19)
 316 #
 317 reset_db
 318 do_execsql_test 9.0 {
 319   CREATE TABLE t0(c0 INT);
 320   CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;
 321   INSERT INTO t0(c0) VALUES (0);
 322 }
 323
 324 do_execsql_test 9.1 {
 325   SELECT typeof(c0), c0 FROM v0 WHERE c0>='0'
 326 } {integer 0}
 327
 328 do_execsql_test 9.2 {
 329   SELECT * FROM t0, v0 WHERE v0.c0 >= '0';
 330 } {0 0}
 331
 332 do_execsql_test 9.3 {
 333   SELECT * FROM t0 LEFT JOIN v0 WHERE v0.c0 >= '0';
 334 } {0 0}
 335
 336 do_execsql_test 9.4 {
 337   SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0';
 338 } {0 0}
 339
 340 do_execsql_test 9.5 {
 341   SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0' WHERE TRUE
 342   UNION SELECT 0,0 WHERE 0;
 343 } {0 0}
 344
 345 do_execsql_test 9.10 {
 346   CREATE TABLE t1 (aaa);
 347   INSERT INTO t1 VALUES(23456);
 348   CREATE TABLE t2(bbb);
 349   CREATE VIEW v2(ccc) AS SELECT bbb IS 1234 FROM t2;
 350   SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
 351 } {{} 1}
 352 optimization_control db query-flattener 0
 353 do_execsql_test 9.11 {
 354   SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
 355 } {{} 1}
 356
 357 # 2023-03-01 https://sqlite.org/forum/forumpost/26387ea7ef
 358 # When flattening a VIEW which is the RHS of a LEFT JOIN, always put
 359 # an TK_IF_NULL_ROW operator on all accesses, even TK_COLUMN nodes, since
 360 # the TK_COLUMN might reference an outer subquery.
 361 #
 362 reset_db
 363 db null NULL
 364 do_execsql_test 10.1 {
 365   CREATE TABLE t1 (x INTEGER);
 366   INSERT INTO t1 VALUES(1);   -- Some true value
 367   CREATE TABLE t2 (z TEXT);
 368   INSERT INTO t2 VALUES('some value');
 369   CREATE TABLE t3(w TEXT);
 370   INSERT INTO t3 VALUES('some other value');
 371 }
 372 do_execsql_test 10.2 {
 373   SELECT (
 374     SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
 375   ) FROM t1;
 376 } NULL
 377 do_execsql_test 10.3 {
 378   SELECT (
 379     SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v)
 380   ) FROM t1;
 381 } NULL
 382 optimization_control db all 0
 383 do_execsql_test 10.4 {
 384   SELECT (
 385     SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
 386   ) FROM t1;
 387 } NULL
 388
 389 # 2023-03-02 https://sqlite.org/forum/forumpost/402f05296d
 390 #
 391 # The TK_IF_NULL_ROW expression node must ensure that it does not overwrite
 392 # the result register of an OP_Once subroutine.
 393 #
 394 optimization_control db all 1
 395 do_execsql_test 11.1 {
 396   DROP TABLE t1;
 397   DROP TABLE t2;
 398   DROP TABLE t3;
 399   CREATE TABLE t1(x TEXT, y INTEGER);
 400   INSERT INTO t1(x,y) VALUES(NULL,-2),(NULL,1),('0',2);
 401   CREATE TABLE t2(z INTEGER);
 402   INSERT INTO t2(z) VALUES(2),(-2);
 403   CREATE VIEW t3 AS SELECT z, (SELECT count(*) FROM t1) AS w FROM t2;
 404   SELECT * FROM t1 LEFT JOIN t3 ON y=z;
 405 } {NULL -2 -2 3 NULL 1 NULL NULL 0 2 2 3}
 406
 407 # 2023-03-11 https://sqlite.org/forum/forumpost/b405033490fa56d9
 408 # The fix that test 11.1 above checks also caused a performance regression.
 409 # This test case verifies that the performance regression has been resolved.
 410 #
 411 do_execsql_test 12.1 {
 412   DROP TABLE t1;
 413   DROP TABLE t2;
 414   DROP VIEW t3;
 415   CREATE TABLE t1(a INTEGER PRIMARY KEY);
 416   WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100)
 417     INSERT INTO t1(a) SELECT n FROM c;
 418   CREATE VIEW t2(b) AS SELECT a FROM t1;
 419 }
 420 do_vmstep_test 12.2 {
 421   SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;
 422 } 2000 {99 99 100 100}
 423 do_eqp_test 12.3 {
 424   SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;
 425 } {
 426   QUERY PLAN
 427   |--SCAN t1
 428   `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 429 }
 430
 431 finish_test