test/joinA.test

   1 # 2022-04-18
   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, c INT, d INT);
  21     CREATE TABLE t2(c INT, d INT, e INT, f INT);
  22     CREATE TABLE t3(a INT, b INT, e INT, f INT);
  23     CREATE TABLE t4(a INT, c INT, d INT, f INT);
  24     INSERT INTO t1 VALUES(11,21,31,41),(12,22,32,42),(15,25,35,45),(18,28,38,48);
  25     INSERT INTO t2 VALUES(12,22,32,42),(13,23,33,43),(15,25,35,45),(17,27,37,47);
  26     INSERT INTO t3 VALUES(14,24,34,44),(15,25,35,45),(16,26,36,46);
  27     INSERT INTO t4 VALUES(11,21,31,41),(13,23,33,43),(16,26,36,46),(19,29,39,49);
  28   }
  29   2 {
  30     CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
  31     CREATE TABLE t2(c INT, d INTEGER PRIMARY KEY, e INT, f INT);
  32     CREATE TABLE t3(a INT, b INT, e INTEGER PRIMARY KEY, f INT);
  33     CREATE TABLE t4(a INT, c INT, d INT, f INT PRIMARY KEY) WITHOUT ROWID;
  34     INSERT INTO t1 VALUES(11,21,31,41),(12,22,32,42),(15,25,35,45),(18,28,38,48);
  35     INSERT INTO t2 VALUES(12,22,32,42),(13,23,33,43),(15,25,35,45),(17,27,37,47);
  36     INSERT INTO t3 VALUES(14,24,34,44),(15,25,35,45),(16,26,36,46);
  37     INSERT INTO t4 VALUES(11,21,31,41),(13,23,33,43),(16,26,36,46),(19,29,39,49);
  38   }
  39   3 {
  40     CREATE TABLE t1a(a INT, b INT, c INT, d INT);
  41     CREATE TABLE t2a(c INT, d INT, e INT, f INT);
  42     CREATE TABLE t3a(a INT, b INT, e INT, f INT);
  43     CREATE TABLE t4a(a INT, c INT, d INT, f INT);
  44     INSERT INTO t1a VALUES(11,21,31,41),(12,22,32,42);
  45     INSERT INTO t2a VALUES(12,22,32,42),(13,23,33,43);
  46     INSERT INTO t3a VALUES(14,24,34,44),(15,25,35,45);
  47     INSERT INTO t4a VALUES(11,21,31,41),(13,23,33,43);
  48     CREATE TABLE t1b(a INT, b INT, c INT, d INT);
  49     CREATE TABLE t2b(c INT, d INT, e INT, f INT);
  50     CREATE TABLE t3b(a INT, b INT, e INT, f INT);
  51     CREATE TABLE t4b(a INT, c INT, d INT, f INT);
  52     INSERT INTO t1b VALUES(15,25,35,45),(18,28,38,48);
  53     INSERT INTO t2b VALUES(15,25,35,45),(17,27,37,47);
  54     INSERT INTO t3b VALUES(15,25,35,45),(16,26,36,46);
  55     INSERT INTO t4b VALUES(16,26,36,46),(19,29,39,49);
  56     CREATE VIEW t1 AS SELECT * FROM t1a UNION SELECT * FROM t1b;
  57     CREATE VIEW t2 AS SELECT * FROM t2a UNION SELECT * FROM t2b;
  58     CREATE VIEW t3 AS SELECT * FROM t3a UNION SELECT * FROM t3b;
  59     CREATE VIEW t4 AS SELECT * FROM t4a UNION SELECT * FROM t4b;
  60   }
  61 } {
  62   reset_db
  63   db nullvalue -
  64   do_execsql_test joinA-$id.setup $schema {}
  65
  66   # Verified by PG-14
  67   do_execsql_test joinA-$id.100 {
  68     SELECT a,b,c,d,t2.e,f,t3.e
  69       FROM t1
  70            INNER JOIN t2 USING(c,d)
  71            INNER JOIN t3 USING(a,b,f)
  72            INNER JOIN t4 USING(a,c,d,f)
  73     ORDER BY 1 nulls first, 3 nulls first;
  74   } {}
  75
  76
  77   # Verified by PG-14
  78   do_execsql_test joinA-$id.110 {
  79     SELECT a,b,c,d,t2.e,f,t3.e
  80       FROM t1
  81            LEFT JOIN t2 USING(c,d)
  82            LEFT JOIN t3 USING(a,b,f)
  83            LEFT JOIN t4 USING(a,c,d,f)
  84     ORDER BY 1 nulls first, 3 nulls first;
  85   } {
  86     11  21  31  41  -  -  -
  87     12  22  32  42  -  -  -
  88     15  25  35  45  -  -  -
  89     18  28  38  48  -  -  -
  90   }
  91
  92   # Verified by PG-14
  93   do_execsql_test joinA-$id.120 {
  94     SELECT a,b,c,d,t2.e,f,t3.e
  95       FROM t1
  96            LEFT JOIN t2 USING(c,d)
  97            RIGHT JOIN t3 USING(a,b,f)
  98            LEFT JOIN t4 USING(a,c,d,f)
  99     ORDER BY 1 nulls first, 3 nulls first;
 100   } {
 101     14  24  -  -  -  44  34
 102     15  25  -  -  -  45  35
 103     16  26  -  -  -  46  36
 104   }
 105
 106   # Verified by PG-14
 107   do_execsql_test joinA-$id.130 {
 108     SELECT a,b,c,d,t2.e,f,t3.e
 109       FROM t1
 110            RIGHT JOIN t2 USING(c,d)
 111            LEFT JOIN t3 USING(a,b,f)
 112            RIGHT JOIN t4 USING(a,c,d,f)
 113     ORDER BY 1 nulls first, 3 nulls first;
 114   } {
 115     11  -  21  31  -  41  -
 116     13  -  23  33  -  43  -
 117     16  -  26  36  -  46  -
 118     19  -  29  39  -  49  -
 119   }
 120
 121   # Verified by PG-14
 122   do_execsql_test joinA-$id.140 {
 123     SELECT a,b,c,d,t2.e,f,t3.e
 124       FROM t1
 125            FULL JOIN t2 USING(c,d)
 126            LEFT JOIN t3 USING(a,b,f)
 127            RIGHT JOIN t4 USING(a,c,d,f)
 128     ORDER BY 1 nulls first, 3 nulls first;
 129   } {
 130     11  -  21  31  -  41  -
 131     13  -  23  33  -  43  -
 132     16  -  26  36  -  46  -
 133     19  -  29  39  -  49  -
 134   }
 135
 136   # Verified by PG-14
 137   do_execsql_test joinA-$id.150 {
 138     SELECT a,b,c,d,t2.e,f,t3.e
 139       FROM t1
 140            RIGHT JOIN t2 USING(c,d)
 141            FULL JOIN t3 USING(a,b,f)
 142            RIGHT JOIN t4 USING(a,c,d,f)
 143     ORDER BY 1 nulls first, 3 nulls first;
 144   } {
 145     11  -  21  31  -  41  -
 146     13  -  23  33  -  43  -
 147     16  -  26  36  -  46  -
 148     19  -  29  39  -  49  -
 149   }
 150
 151   # Verified by PG-14
 152   do_execsql_test joinA-$id.160 {
 153     SELECT a,b,c,d,t2.e,f,t3.e
 154       FROM t1
 155            RIGHT JOIN t2 USING(c,d)
 156            LEFT JOIN t3 USING(a,b,f)
 157            FULL JOIN t4 USING(a,c,d,f)
 158     ORDER BY 1 nulls first, 3 nulls first;
 159   } {
 160     -   -  12  22  32  42  -
 161     -   -  13  23  33  43  -
 162     -   -  15  25  35  45  -
 163     -   -  17  27  37  47  -
 164     11  -  21  31  -   41  -
 165     13  -  23  33  -   43  -
 166     16  -  26  36  -   46  -
 167     19  -  29  39  -   49  -
 168   }
 169
 170   # Verified by PG-14
 171   do_execsql_test joinA-$id.170 {
 172     SELECT a,b,c,d,t2.e,f,t3.e
 173       FROM t1
 174            LEFT JOIN t2 USING(c,d)
 175            RIGHT JOIN t3 USING(a,b,f)
 176            FULL JOIN t4 USING(a,c,d,f)
 177     ORDER BY 1 nulls first, 3 nulls first;
 178   } {
 179     11  -   21  31  -  41  -
 180     13  -   23  33  -  43  -
 181     14  24  -   -   -  44  34
 182     15  25  -   -   -  45  35
 183     16  26  -   -   -  46  36
 184     16  -   26  36  -  46  -
 185     19  -   29  39  -  49  -
 186   }
 187
 188   # Verified by PG-14
 189   do_execsql_test joinA-$id.200 {
 190     SELECT a,b,c,d,t2.e,f,t3.e
 191       FROM t1
 192            FULL JOIN t2 USING(c,d)
 193            FULL JOIN t3 USING(a,b,f)
 194            FULL JOIN t4 USING(a,c,d,f)
 195     ORDER BY 1 nulls first, 3 nulls first;
 196   } {
 197     -   -   12  22  32  42  -
 198     -   -   13  23  33  43  -
 199     -   -   15  25  35  45  -
 200     -   -   17  27  37  47  -
 201     11  -   21  31  -   41  -
 202     11  21  31  41  -   -   -
 203     12  22  32  42  -   -   -
 204     13  -   23  33  -   43  -
 205     14  24  -   -   -   44  34
 206     15  25  -   -   -   45  35
 207     15  25  35  45  -   -   -
 208     16  26  -   -   -   46  36
 209     16  -   26  36  -   46  -
 210     18  28  38  48  -   -   -
 211     19  -   29  39  -   49  -
 212   }
 213
 214   # Verified by PG-14
 215   do_execsql_test joinA-$id.201 {
 216     SELECT a,b,c,d,t2.e,f,t3.e,t1.a
 217       FROM t1
 218            FULL JOIN t2 USING(c,d)
 219            FULL JOIN t3 USING(a,b,f)
 220            FULL JOIN t4 USING(a,c,d,f)
 221      WHERE t1.a!=0
 222     ORDER BY 1 nulls first, 3 nulls first;
 223   } {
 224     11  21  31  41  -   -   -  11
 225     12  22  32  42  -   -   -  12
 226     15  25  35  45  -   -   -  15
 227     18  28  38  48  -   -   -  18
 228   }
 229
 230   # Verified by PG-14
 231   do_execsql_test joinA-$id.202 {
 232     SELECT a,b,c,d,t2.e,f,t3.e,t3.a
 233       FROM t1
 234            FULL JOIN t2 USING(c,d)
 235            FULL JOIN t3 USING(a,b,f)
 236            FULL JOIN t4 USING(a,c,d,f)
 237      WHERE t3.a!=0
 238     ORDER BY 1 nulls first, 3 nulls first;
 239   } {
 240     14  24  -   -   -   44  34  14
 241     15  25  -   -   -   45  35  15
 242     16  26  -   -   -   46  36  16
 243   }
 244
 245   # Verified by PG-14
 246   do_execsql_test joinA-$id.203 {
 247     SELECT a,b,c,d,t2.e,f,t3.e,t4.a
 248       FROM t1
 249            FULL JOIN t2 USING(c,d)
 250            FULL JOIN t3 USING(a,b,f)
 251            FULL JOIN t4 USING(a,c,d,f)
 252      WHERE t4.a!=0
 253     ORDER BY 1 nulls first, 3 nulls first;
 254   } {
 255     11  -   21  31  -   41  -  11
 256     13  -   23  33  -   43  -  13
 257     16  -   26  36  -   46  -  16
 258     19  -   29  39  -   49  -  19
 259   }
 260
 261   # Verified by PG-14
 262   do_execsql_test joinA-$id.204 {
 263     SELECT a,b,c,d,t2.e,f,t3.e
 264       FROM t1
 265            FULL JOIN t2 USING(c,d)
 266            FULL JOIN t3 USING(a,b,f)
 267            FULL JOIN t4 USING(a,c,d,f)
 268      WHERE t2.e!=0
 269     ORDER BY 1 nulls first, 3 nulls first;
 270   } {
 271     -   -   12  22  32  42  -
 272     -   -   13  23  33  43  -
 273     -   -   15  25  35  45  -
 274     -   -   17  27  37  47  -
 275   }
 276 }
 277 finish_test