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 #-------------------------------------------------------------------------
 245 #
 246 reset_db
 247 do_execsql_test 7.0 {
 248   CREATE TABLE t1(x);
 249   INSERT INTO t1 VALUES(1);
 250 }
 251
 252 do_execsql_test 7.1 {
 253   CREATE TABLE t2(x, y, z);
 254   CREATE INDEX t2xy ON t2(x, y);
 255   WITH s(i) AS (
 256     SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000
 257   )
 258   INSERT INTO t2 SELECT i/10, i, NULL FROM s;
 259   ANALYZE;
 260 }
 261
 262 do_eqp_test 7.2 {
 263   SELECT * FROM t1 LEFT JOIN t2 ON (
 264     t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
 265   );
 266 } {
 267   QUERY PLAN
 268   |--SCAN TABLE t1
 269   `--MULTI-INDEX OR
 270      |--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
 271      `--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
 272 }
 273
 274 do_execsql_test 7.3 {
 275   CREATE TABLE t3(x);
 276
 277   CREATE TABLE t4(x, y, z);
 278   CREATE INDEX t4xy ON t4(x, y);
 279   CREATE INDEX t4xz ON t4(x, z);
 280
 281   WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000)
 282   INSERT INTO t4 SELECT i/10, i, i FROM s;
 283
 284   ANALYZE;
 285 }
 286
 287 do_eqp_test 7.4 {
 288   SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?);
 289 } {
 290   QUERY PLAN
 291   |--SCAN TABLE t3
 292   `--SEARCH TABLE t4 USING INDEX t4xz (x=?)
 293 }
 294
 295 finish_test