test/join7.test

   1 # 2022-04-09
   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 RIGHT and FULL OUTER JOINs.
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 foreach {id schema} {
  19   1 {
  20     CREATE TABLE t1(a INT, b INT);
  21     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  22     CREATE INDEX t1a ON t1(a);
  23     CREATE TABLE t2(c INT, d INT);
  24     INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
  25     CREATE INDEX t2c ON t2(c);
  26     CREATE VIEW dual(dummy) AS VALUES('x');
  27   }
  28   2 {
  29     CREATE TABLE t1(a INT, b INT);
  30     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  31     CREATE INDEX t1ab ON t1(a,b);
  32     CREATE TABLE t2(c INT, d INT);
  33     INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
  34     CREATE INDEX t2cd ON t2(c,d);
  35     CREATE VIEW dual(dummy) AS VALUES('x');
  36   }
  37   3 {
  38     CREATE TABLE t1(a INT, b INT);
  39     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  40     CREATE INDEX t1a ON t1(a);
  41     CREATE TABLE t2(c INT, d INT PRIMARY KEY) WITHOUT ROWID;
  42     INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
  43     CREATE INDEX t2c ON t2(c);
  44     CREATE VIEW dual(dummy) AS VALUES('x');
  45   }
  46   4 {
  47     CREATE TABLE t1(a INT, b INT);
  48     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  49     CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
  50     INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
  51     CREATE VIEW dual(dummy) AS VALUES('x');
  52   }
  53   5 {
  54     CREATE TABLE t1(a INT, b INT);
  55     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  56     CREATE TABLE t2(c INT PRIMARY KEY, d INT) WITHOUT ROWID;
  57     INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
  58     CREATE VIEW dual(dummy) AS VALUES('x');
  59   }
  60   6 {
  61     CREATE TABLE t1(a INT, b INT);
  62     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  63     CREATE VIEW t2(c,d) AS VALUES(3,33),(4,44),(5,55);
  64     CREATE VIEW dual(dummy) AS VALUES('x');
  65   }
  66   7 {
  67     CREATE VIEW t1(a,b) AS VALUES(1,2),(1,3),(1,4);
  68     CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
  69     INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
  70     CREATE VIEW dual(dummy) AS VALUES('x');
  71   }
  72   8 {
  73     CREATE TABLE t1(a INT, b INT);
  74     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  75     CREATE TABLE t2(c INT, d INT);
  76     INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
  77     CREATE VIEW dual(dummy) AS VALUES('x');
  78   }
  79   9 {
  80     CREATE TABLE t1(a INT, b INT);
  81     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  82     CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
  83     CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
  84     CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
  85     INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
  86     INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
  87     CREATE TABLE dual(dummy TEXT);
  88     INSERT INTO dual(dummy) VALUES('x');
  89   }
  90   10 {
  91     CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a,b)) WITHOUT ROWID;
  92     INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
  93     CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
  94     CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
  95     CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
  96     INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
  97     INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
  98     CREATE TABLE dual(dummy TEXT);
  99     INSERT INTO dual(dummy) VALUES('x');
 100   }
 101 } {
 102   reset_db
 103   db nullvalue NULL
 104   do_execsql_test join7-$id.setup $schema {}
 105
 106   # Verified against PG-14 for case 1
 107   do_execsql_test join7-$id.10 {
 108     SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
 109   } {
 110     NULL 55
 111     2    NULL
 112     3    33
 113     4    44
 114   }
 115
 116   # Verified against PG-14 for case 1
 117   do_execsql_test join7-$id.20 {
 118     SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
 119   } {
 120     NULL  5
 121     1     NULL
 122     1     3
 123     1     4
 124   }
 125
 126   do_execsql_test join7-$id.30 {
 127     SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
 128   } {
 129     NULL NULL 5    55
 130     1    2    NULL NULL
 131     1    3    3    33
 132     1    4    4    44
 133   }
 134   do_execsql_test join7-$id.31 {
 135     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c ORDER BY +b;
 136   } {
 137     NULL NULL 5    55
 138     1    2    NULL NULL
 139     1    3    3    33
 140     1    4    4    44
 141   }
 142   do_execsql_test join7-$id.32 {
 143     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
 144      WHERE b=c
 145      ORDER BY +b;
 146   } {
 147     1    3    3    33
 148     1    4    4    44
 149   }
 150   do_execsql_test join7-$id.33 {
 151     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
 152      WHERE b>0
 153      ORDER BY +b;
 154   } {
 155     1    2    NULL NULL
 156     1    3    3    33
 157     1    4    4    44
 158   }
 159   do_execsql_test join7-$id.34 {
 160     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
 161      WHERE b>0 OR b IS NULL
 162      ORDER BY +b;
 163   } {
 164     NULL NULL 5    55
 165     1    2    NULL NULL
 166     1    3    3    33
 167     1    4    4    44
 168   }
 169   do_execsql_test join7-$id.35 {
 170     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND b>3 AND c>4
 171      ORDER BY coalesce(b,c,0);
 172   } {
 173     1    2    NULL NULL
 174     NULL NULL 3    33
 175     1    3    NULL NULL
 176     NULL NULL 4    44
 177     1    4    NULL NULL
 178     NULL NULL 5    55
 179   }
 180   do_execsql_test join7-$id.36 {
 181     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND b>3 WHERE c>4
 182      ORDER BY coalesce(b,c,0);
 183   } {
 184     NULL NULL 5    55
 185   }
 186   do_execsql_test join7-$id.37 {
 187     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE b>3 AND c>4
 188      ORDER BY coalesce(b,c,0);
 189   } {
 190   }
 191   do_execsql_test join7-$id.38 {
 192     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE b>3 OR c>4
 193      ORDER BY coalesce(b,c,0);
 194   } {
 195     1    4    4    44
 196     NULL NULL 5    55
 197   }
 198   do_execsql_test join7-$id.39 {
 199     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND (b>3 OR c>4)
 200      ORDER BY coalesce(b,c,0);
 201   } {
 202     1    2    NULL NULL
 203     NULL NULL 3    33
 204     1    3    NULL NULL
 205     1    4    4    44
 206     NULL NULL 5    55
 207   }
 208   do_execsql_test join7-$id.40 {
 209     SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
 210   } {
 211     NULL NULL 5    55
 212     1    3    3    33
 213     1    4    4    44
 214   }
 215   do_execsql_test join7-$id.50 {
 216     SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
 217   } {
 218     NULL NULL 5    55
 219     1    3    3    33
 220     1    4    4    44
 221   }
 222   do_execsql_test join7-$id.60 {
 223     SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
 224   } {
 225     NULL NULL NULL 5    55
 226     x    1    3    3    33
 227     x    1    4    4    44
 228   }
 229   do_execsql_test join7-$id.70 {
 230     SELECT t1.*, t2.*
 231       FROM t2 LEFT JOIN (dual JOIN t1 ON true) ON b=c ORDER BY +b;
 232   } {
 233     NULL NULL 5    55
 234     1    3    3    33
 235     1    4    4    44
 236   }
 237   do_execsql_test join7-$id.80 {
 238     SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
 239   } {
 240     NULL NULL NULL 5    55
 241     x    1    3    3    33
 242     x    1    4    4    44
 243   }
 244   do_execsql_test join7-$id.81 {
 245     SELECT dual.*, t1.*, t2.*
 246       FROM t1 CROSS JOIN dual RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
 247   } {
 248     NULL NULL NULL 5    55
 249     x    1    3    3    33
 250     x    1    4    4    44
 251   }
 252   do_execsql_test join7-$id.90 {
 253     SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b;
 254   } {
 255     1    2    NULL NULL
 256     1    3    3    33
 257     1    4    4    44
 258   }
 259   do_execsql_test join7-$id.100 {
 260     SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b;
 261   } {
 262     NULL NULL 5    55
 263     1    2    NULL NULL
 264     1    3    3    33
 265     1    4    4    44
 266   }
 267   do_execsql_test join7-$id.101 {
 268     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND a=1 ORDER BY +b;
 269   } {
 270     NULL NULL 5    55
 271     1    2    NULL NULL
 272     1    3    3    33
 273     1    4    4    44
 274   }
 275
 276   # Verified against PG-14 for case 1
 277   do_execsql_test join7-$id.110 {
 278     SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b;
 279   } {
 280     1    2    NULL NULL
 281     1    3    3    33
 282     1    4    4    44
 283   }
 284
 285   do_execsql_test join7-$id.111 {
 286     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE a=1 ORDER BY +b;
 287   } {
 288     1    2    NULL NULL
 289     1    3    3    33
 290     1    4    4    44
 291   }
 292
 293   # Verified against PG-14 for case 1
 294   do_execsql_test join7-$id.115 {
 295     SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c
 296      WHERE a=1 OR a IS NULL ORDER BY +b;
 297   } {
 298     NULL NULL 5    55
 299     1    2    NULL NULL
 300     1    3    3    33
 301     1    4    4    44
 302   }
 303
 304   do_execsql_test join7-$id.116 {
 305     SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
 306      WHERE a=1 OR a IS NULL ORDER BY +b;
 307   } {
 308     NULL NULL 5    55
 309     1    2    NULL NULL
 310     1    3    3    33
 311     1    4    4    44
 312   }
 313
 314   # Verified against PG-14 for case 1:
 315   do_execsql_test join7-$id.120 {
 316     SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d;
 317   } {
 318     NULL NULL 5    55
 319   }
 320
 321   # Verified against PG-14 for case 1:
 322   do_execsql_test join7-$id.130 {
 323     SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d;
 324   } {
 325     NULL NULL 3    33
 326     NULL NULL 4    44
 327     NULL NULL 5    55
 328     1    2    NULL NULL
 329     1    3    NULL NULL
 330     1    4    NULL NULL
 331   }
 332
 333   # Verified against PG-14 for case 1:
 334   do_execsql_test join7-$id.140 {
 335     SELECT a, b, c, d
 336       FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 ORDER BY +b, +d;
 337   } {
 338     NULL NULL 3    33
 339     NULL NULL 4    44
 340     NULL NULL 5    55
 341     1    2    NULL NULL
 342     1    3    NULL NULL
 343     1    4    NULL NULL
 344   }
 345
 346   do_execsql_test join7-$id.141 {
 347     SELECT a, b, c, d
 348       FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0
 349      ORDER BY +b, +d LIMIT 2 OFFSET 2
 350   } {
 351     NULL NULL 5    55
 352     1    2    NULL NULL
 353   }
 354 }
 355 finish_test