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 ifcapable subquery {
  67   do_test join2-1.7 {
  68     execsql {
  69       SELECT * FROM
  70         t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
  71     }
  72   } {1 11 111 1111 2 22 {} {} 3 33 {} {}}
  73 }
  74
  75 #-------------------------------------------------------------------------
  76 # Check that ticket [25e335f802ddc] has been resolved. It should be an
  77 # error for the ON clause of a LEFT JOIN to refer to a table to its right.
  78 #
  79 do_execsql_test 2.0 {
  80   CREATE TABLE aa(a);
  81   CREATE TABLE bb(b);
  82   CREATE TABLE cc(c);
  83   INSERT INTO aa VALUES('one');
  84   INSERT INTO bb VALUES('one');
  85   INSERT INTO cc VALUES('one');
  86 }
  87
  88 do_catchsql_test 2.1 {
  89   SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
  90 } {1 {ON clause references tables to its right}}
  91 do_catchsql_test 2.2 {
  92   SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
  93 } {0 {one one one}}
  94
  95 #-------------------------------------------------------------------------
  96 # Test that a problem causing where.c to overlook opportunities to
  97 # omit unnecessary tables from a LEFT JOIN when UNIQUE, NOT NULL column
  98 # that makes this possible happens to be the leftmost in its table.
  99 #
 100 reset_db
 101 do_execsql_test 3.0 {
 102   CREATE TABLE t1(k1 INTEGER PRIMARY KEY, k2, k3);
 103   CREATE TABLE t2(k2 INTEGER PRIMARY KEY, v2);
 104
 105   -- Prior to this problem being fixed, table t3_2 would be omitted from
 106   -- the join queries below, but if t3_1 were used in its place it would
 107   -- not.
 108   CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID;
 109   CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID;
 110 }
 111
 112 do_eqp_test 3.1 {
 113   SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3);
 114 } {
 115   QUERY PLAN
 116   |--SCAN t1
 117   `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
 118 }
 119
 120 do_eqp_test 3.2 {
 121   SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3);
 122 } {
 123   QUERY PLAN
 124   |--SCAN t1
 125   `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
 126 }
 127
 128 #-------------------------------------------------------------------------
 129 # Test that tables other than the rightmost can be omitted from a
 130 # LEFT JOIN query.
 131 #
 132 do_execsql_test 4.0 {
 133   CREATE TABLE c1(k INTEGER PRIMARY KEY, v1);
 134   CREATE TABLE c2(k INTEGER PRIMARY KEY, v2);
 135   CREATE TABLE c3(k INTEGER PRIMARY KEY, v3);
 136
 137   INSERT INTO c1 VALUES(1, 2);
 138   INSERT INTO c2 VALUES(2, 3);
 139   INSERT INTO c3 VALUES(3, 'v3');
 140
 141   INSERT INTO c1 VALUES(111, 1112);
 142   INSERT INTO c2 VALUES(112, 1113);
 143   INSERT INTO c3 VALUES(113, 'v1113');
 144 }
 145 do_execsql_test 4.1.1 {
 146   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 147 } {2 v3 1112 {}}
 148 do_execsql_test 4.1.2 {
 149   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 150 } {2 v3 1112 {}}
 151
 152 do_execsql_test 4.1.3 {
 153   SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 154 } {2 v3 1112 {}}
 155
 156 do_execsql_test 4.1.4 {
 157   SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 158 } {2 v3 2 v3 1112 {} 1112 {}}
 159
 160 do_eqp_test 4.1.5 {
 161   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 162 } {
 163   QUERY PLAN
 164   |--SCAN c1
 165   |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?)
 166   `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
 167 }
 168 do_eqp_test 4.1.6 {
 169   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 170 } {
 171   QUERY PLAN
 172   |--SCAN c1
 173   `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
 174 }
 175
 176 do_execsql_test 4.2.0 {
 177   DROP TABLE c1;
 178   DROP TABLE c2;
 179   DROP TABLE c3;
 180   CREATE TABLE c1(k UNIQUE, v1);
 181   CREATE TABLE c2(k UNIQUE, v2);
 182   CREATE TABLE c3(k UNIQUE, v3);
 183
 184   INSERT INTO c1 VALUES(1, 2);
 185   INSERT INTO c2 VALUES(2, 3);
 186   INSERT INTO c3 VALUES(3, 'v3');
 187
 188   INSERT INTO c1 VALUES(111, 1112);
 189   INSERT INTO c2 VALUES(112, 1113);
 190   INSERT INTO c3 VALUES(113, 'v1113');
 191 }
 192 do_execsql_test 4.2.1 {
 193   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 194 } {2 v3 1112 {}}
 195 do_execsql_test 4.2.2 {
 196   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 197 } {2 v3 1112 {}}
 198
 199 do_execsql_test 4.2.3 {
 200   SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 201 } {2 v3 1112 {}}
 202
 203 do_execsql_test 4.2.4 {
 204   SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
 205 } {2 v3 2 v3 1112 {} 1112 {}}
 206
 207 do_eqp_test 4.2.5 {
 208   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
 209 } {
 210   QUERY PLAN
 211   |--SCAN c1
 212   |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?)
 213   `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
 214 }
 215 do_eqp_test 4.2.6 {
 216   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 217 } {
 218   QUERY PLAN
 219   |--SCAN c1
 220   `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
 221 }
 222
 223 # 2017-11-23 (Thanksgiving day)
 224 # OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.
 225 #
 226 do_execsql_test 4.3.0 {
 227   DROP TABLE IF EXISTS t1;
 228   DROP TABLE IF EXISTS t2;
 229   CREATE TABLE t1(x PRIMARY KEY) WITHOUT ROWID;
 230   CREATE TABLE t2(x);
 231   SELECT a.x
 232     FROM t1 AS a
 233     LEFT JOIN t1 AS b ON (a.x=b.x)
 234     LEFT JOIN t2 AS c ON (a.x=c.x);
 235 } {}
 236 do_execsql_test 4.3.1 {
 237   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)
 238     INSERT INTO t1(x) SELECT x FROM c;
 239   INSERT INTO t2(x) SELECT x+9 FROM t1;
 240   SELECT a.x, c.x
 241     FROM t1 AS a
 242     LEFT JOIN t1 AS b ON (a.x=b.x)
 243     LEFT JOIN t2 AS c ON (a.x=c.x);
 244 } {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10}
 245
 246 do_execsql_test 5.0 {
 247   CREATE TABLE s1 (a INTEGER PRIMARY KEY);
 248   CREATE TABLE s2 (a INTEGER PRIMARY KEY);
 249   CREATE TABLE s3 (a INTEGER);
 250   CREATE UNIQUE INDEX ndx on s3(a);
 251 }
 252 do_eqp_test 5.1 {
 253   SELECT s1.a FROM s1 left join s2 using (a);
 254 } {SCAN s1}
 255
 256 do_eqp_test 5.2 {
 257   SELECT s1.a FROM s1 left join s3 using (a);
 258 } {SCAN s1}
 259
 260 do_execsql_test 6.0 {
 261   CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c);
 262   CREATE TABLE u2(a INTEGER PRIMARY KEY, b, c);
 263   CREATE INDEX u1ab ON u1(b, c);
 264 }
 265 do_eqp_test 6.1 {
 266   SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c );
 267 } {SCAN u2}
 268
 269 db close
 270 sqlite3 db :memory:
 271 do_execsql_test 7.0 {
 272   CREATE TABLE t1(a,b);  INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
 273   CREATE TABLE t2(c,d);  INSERT INTO t2 VALUES(2,4),(3,6);
 274   CREATE TABLE t3(x);    INSERT INTO t3 VALUES(9);
 275   CREATE VIEW test AS
 276     SELECT *, 'x'
 277       FROM t1 LEFT JOIN (SELECT * FROM t2, t3) ON (c=b AND x=9)
 278       WHERE c IS NULL;
 279   SELECT * FROM test;
 280 } {3 4 {} {} {} x 5 6 {} {} {} x}
 281
 282 #-------------------------------------------------------------------------
 283 # Ticket [dfd66334].
 284 #
 285 reset_db
 286 do_execsql_test 8.0 {
 287   CREATE TABLE t0(c0);
 288   CREATE TABLE t1(c0);
 289 }
 290
 291 do_execsql_test 8.1 {
 292   SELECT * FROM t0 LEFT JOIN t1
 293   WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0);
 294 }
 295
 296 #-------------------------------------------------------------------------
 297 # Ticket [45f4bf4eb] reported by Manuel Rigger (2020-04-25)
 298 #
 299 # Follow up error reported by Eric Speckman on the SQLite forum
 300 # https://sqlite.org/forum/info/c49496d24d35bd7c (2020-08-19)
 301 #
 302 reset_db
 303 do_execsql_test 9.0 {
 304   CREATE TABLE t0(c0 INT);
 305   CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;
 306   INSERT INTO t0(c0) VALUES (0);
 307 }
 308
 309 do_execsql_test 9.1 {
 310   SELECT typeof(c0), c0 FROM v0 WHERE c0>='0'
 311 } {integer 0}
 312
 313 do_execsql_test 9.2 {
 314   SELECT * FROM t0, v0 WHERE v0.c0 >= '0';
 315 } {0 0}
 316
 317 do_execsql_test 9.3 {
 318   SELECT * FROM t0 LEFT JOIN v0 WHERE v0.c0 >= '0';
 319 } {0 0}
 320
 321 do_execsql_test 9.4 {
 322   SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0';
 323 } {0 0}
 324
 325 do_execsql_test 9.5 {
 326   SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0' WHERE TRUE
 327   UNION SELECT 0,0 WHERE 0;
 328 } {0 0}
 329
 330 do_execsql_test 9.10 {
 331   CREATE TABLE t1 (aaa);
 332   INSERT INTO t1 VALUES(23456);
 333   CREATE TABLE t2(bbb);
 334   CREATE VIEW v2(ccc) AS SELECT bbb IS 1234 FROM t2;
 335   SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
 336 } {{} 1}
 337 optimization_control db query-flattener 0
 338 do_execsql_test 9.11 {
 339   SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
 340 } {{} 1}
 341
 342
 343 finish_test