test/joinE.test

   1 # 2022-05-13
   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 #
  12 # This file implements tests for JOINs that use Bloom filters.
  13 #
  14 # The test case output is (mostly) all generated by PostgreSQL 14.  This
  15 # test module was created as follows:
  16 #
  17 #   1.   Run a TCL script (included at the bottom of this file) that
  18 #        generates an input script for "psql" that will run man
  19 #        diverse tests on joins.
  20 #
  21 #   2.   Run the script from step (1) through psql and collect the
  22 #        output.
  23 #
  24 #   3.   Make a few minor global search-and-replace operations to convert
  25 #        the psql output into a form suitable for this test module.
  26 #
  27 #   4.   Add this header, and the script content at the footer.
  28 #
  29 set testdir [file dirname $argv0]
  30 source $testdir/tester.tcl
  31 db nullvalue -
  32 db eval {
  33   CREATE TABLE t1(a INT);
  34   INSERT INTO t1 VALUES(1),(NULL);
  35   CREATE TABLE t2(b INT);
  36   INSERT INTO t2 VALUES(2),(NULL);
  37 }
  38 do_execsql_test joinE-1 {
  39   SELECT a, b
  40   FROM t1 INNER JOIN t2 ON true
  41   ORDER BY coalesce(a,b,3);
  42 } {
  43   1 2
  44   1 -
  45   - 2
  46   - -
  47 }
  48 do_execsql_test joinE-2 {
  49   SELECT a, b
  50   FROM t1 INNER JOIN t2 ON true WHERE a IS NULL
  51   ORDER BY coalesce(a,b,3);
  52 } {
  53   - 2
  54   - -
  55 }
  56 do_execsql_test joinE-3 {
  57   SELECT a, b
  58   FROM t1 INNER JOIN t2 ON a IS NULL
  59   ORDER BY coalesce(a,b,3);
  60 } {
  61   - 2
  62   - -
  63 }
  64 do_execsql_test joinE-4 {
  65   SELECT a, b
  66   FROM t1 INNER JOIN t2 ON true WHERE b IS NULL
  67   ORDER BY coalesce(a,b,3);
  68 } {
  69   1 -
  70   - -
  71 }
  72 do_execsql_test joinE-5 {
  73   SELECT a, b
  74   FROM t1 INNER JOIN t2 ON b IS NULL
  75   ORDER BY coalesce(a,b,3);
  76 } {
  77   1 -
  78   - -
  79 }
  80 do_execsql_test joinE-6 {
  81   SELECT a, b
  82   FROM t1 LEFT JOIN t2 ON true
  83   ORDER BY coalesce(a,b,3);
  84 } {
  85   1 2
  86   1 -
  87   - 2
  88   - -
  89 }
  90 do_execsql_test joinE-7 {
  91   SELECT a, b
  92   FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL
  93   ORDER BY coalesce(a,b,3);
  94 } {
  95   - 2
  96   - -
  97 }
  98 do_execsql_test joinE-8 {
  99   SELECT a, b
 100   FROM t1 LEFT JOIN t2 ON a IS NULL
 101   ORDER BY coalesce(a,b,3);
 102 } {
 103   1 -
 104   - 2
 105   - -
 106 }
 107 do_execsql_test joinE-9 {
 108   SELECT a, b
 109   FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL
 110   ORDER BY coalesce(a,b,3);
 111 } {
 112   1 -
 113   - -
 114 }
 115 do_execsql_test joinE-10 {
 116   SELECT a, b
 117   FROM t1 LEFT JOIN t2 ON b IS NULL
 118   ORDER BY coalesce(a,b,3);
 119 } {
 120   1 -
 121   - -
 122 }
 123 do_execsql_test joinE-11 {
 124   SELECT a, b
 125   FROM t1 RIGHT JOIN t2 ON true
 126   ORDER BY coalesce(a,b,3);
 127 } {
 128   1 2
 129   1 -
 130   - 2
 131   - -
 132 }
 133 do_execsql_test joinE-12 {
 134   SELECT a, b
 135   FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL
 136   ORDER BY coalesce(a,b,3);
 137 } {
 138   - 2
 139   - -
 140 }
 141 do_execsql_test joinE-13 {
 142   SELECT a, b
 143   FROM t1 RIGHT JOIN t2 ON a IS NULL
 144   ORDER BY coalesce(a,b,3);
 145 } {
 146   - 2
 147   - -
 148 }
 149 do_execsql_test joinE-14 {
 150   SELECT a, b
 151   FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL
 152   ORDER BY coalesce(a,b,3);
 153 } {
 154   1 -
 155   - -
 156 }
 157 do_execsql_test joinE-15 {
 158   SELECT a, b
 159   FROM t1 RIGHT JOIN t2 ON b IS NULL
 160   ORDER BY coalesce(a,b,3);
 161 } {
 162   1 -
 163   - 2
 164   - -
 165 }
 166 do_execsql_test joinE-16 {
 167   SELECT a, b
 168   FROM t1 FULL JOIN t2 ON true
 169   ORDER BY coalesce(a,b,3);
 170 } {
 171   1 2
 172   1 -
 173   - 2
 174   - -
 175 }
 176 do_execsql_test joinE-17 {
 177   SELECT a, b
 178   FROM t1 FULL JOIN t2 ON true WHERE a IS NULL
 179   ORDER BY coalesce(a,b,3);
 180 } {
 181   - 2
 182   - -
 183 }
 184
 185 # PG-14 is unable to perform this join.  It says:  FULL JOIN is only
 186 # supported with merge-joinable or hash-joinable join conditions
 187 #
 188 # do_execsql_test joinE-18 {
 189 #  SELECT a, b
 190 #  FROM t1 FULL JOIN t2 ON a IS NULL
 191 #  ORDER BY coalesce(a,b,3);
 192 # } {
 193 # }
 194
 195 do_execsql_test joinE-19 {
 196   SELECT a, b
 197   FROM t1 FULL JOIN t2 ON true WHERE b IS NULL
 198   ORDER BY coalesce(a,b,3);
 199 } {
 200   1 -
 201   - -
 202 }
 203
 204 # PG-14 is unable to perform this join.  It says:  FULL JOIN is only
 205 # supported with merge-joinable or hash-joinable join conditions
 206 #
 207 # do_execsql_test joinE-20 {
 208 #   SELECT a, b
 209 #   FROM t1 FULL JOIN t2 ON b IS NULL
 210 #   ORDER BY coalesce(a,b,3);
 211 # } {
 212 # }
 213
 214 db eval {
 215   DELETE FROM t1;
 216   INSERT INTO t1 VALUES(1);
 217   DELETE FROM t2;
 218   INSERT INTO t2 VALUES(NULL);
 219 }
 220
 221 do_execsql_test joinE-21 {
 222   SELECT a, b
 223   FROM t1 INNER JOIN t2 ON true
 224   ORDER BY coalesce(a,b,3);
 225 } {
 226   1 -
 227 }
 228 do_execsql_test joinE-22 {
 229   SELECT a, b
 230   FROM t1 INNER JOIN t2 ON true WHERE a IS NULL
 231   ORDER BY coalesce(a,b,3);
 232 } {
 233 }
 234 do_execsql_test joinE-23 {
 235   SELECT a, b
 236   FROM t1 INNER JOIN t2 ON a IS NULL
 237   ORDER BY coalesce(a,b,3);
 238 } {
 239 }
 240 do_execsql_test joinE-24 {
 241   SELECT a, b
 242   FROM t1 INNER JOIN t2 ON true WHERE b IS NULL
 243   ORDER BY coalesce(a,b,3);
 244 } {
 245   1 -
 246 }
 247 do_execsql_test joinE-25 {
 248   SELECT a, b
 249   FROM t1 INNER JOIN t2 ON b IS NULL
 250   ORDER BY coalesce(a,b,3);
 251 } {
 252   1 -
 253 }
 254 do_execsql_test joinE-26 {
 255   SELECT a, b
 256   FROM t1 LEFT JOIN t2 ON true
 257   ORDER BY coalesce(a,b,3);
 258 } {
 259   1 -
 260 }
 261 do_execsql_test joinE-27 {
 262   SELECT a, b
 263   FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL
 264   ORDER BY coalesce(a,b,3);
 265 } {
 266 }
 267 do_execsql_test joinE-28 {
 268   SELECT a, b
 269   FROM t1 LEFT JOIN t2 ON a IS NULL
 270   ORDER BY coalesce(a,b,3);
 271 } {
 272   1 -
 273 }
 274 do_execsql_test joinE-29 {
 275   SELECT a, b
 276   FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL
 277   ORDER BY coalesce(a,b,3);
 278 } {
 279   1 -
 280 }
 281 do_execsql_test joinE-30 {
 282   SELECT a, b
 283   FROM t1 LEFT JOIN t2 ON b IS NULL
 284   ORDER BY coalesce(a,b,3);
 285 } {
 286   1 -
 287 }
 288 do_execsql_test joinE-31 {
 289   SELECT a, b
 290   FROM t1 RIGHT JOIN t2 ON true
 291   ORDER BY coalesce(a,b,3);
 292 } {
 293   1 -
 294 }
 295
 296 do_execsql_test joinE-32 {
 297   SELECT a, b
 298   FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL
 299   ORDER BY coalesce(a,b,3);
 300 } {
 301 }
 302
 303 do_execsql_test joinE-33 {
 304   SELECT a, b
 305   FROM t1 RIGHT JOIN t2 ON a IS NULL
 306   ORDER BY coalesce(a,b,3);
 307 } {
 308   - -
 309 }
 310 do_execsql_test joinE-34 {
 311   SELECT a, b
 312   FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL
 313   ORDER BY coalesce(a,b,3);
 314 } {
 315   1 -
 316 }
 317 do_execsql_test joinE-35 {
 318   SELECT a, b
 319   FROM t1 RIGHT JOIN t2 ON b IS NULL
 320   ORDER BY coalesce(a,b,3);
 321 } {
 322   1 -
 323 }
 324 do_execsql_test joinE-36 {
 325   SELECT a, b
 326   FROM t1 FULL JOIN t2 ON true
 327   ORDER BY coalesce(a,b,3);
 328 } {
 329   1 -
 330 }
 331 do_execsql_test joinE-37 {
 332   SELECT a, b
 333   FROM t1 FULL JOIN t2 ON true WHERE a IS NULL
 334   ORDER BY coalesce(a,b,3);
 335 } {
 336 }
 337
 338 # PG-14 is unable
 339 #
 340 # do_execsql_test joinE-38 {
 341 #   SELECT a, b
 342 #   FROM t1 FULL JOIN t2 ON a IS NULL
 343 #   ORDER BY coalesce(a,b,3);
 344 # } {
 345 # }
 346
 347 do_execsql_test joinE-39 {
 348   SELECT a, b
 349   FROM t1 FULL JOIN t2 ON true WHERE b IS NULL
 350   ORDER BY coalesce(a,b,3);
 351 } {
 352   1 -
 353 }
 354
 355 # PG-14 is unable
 356 # do_execsql_test joinE-40 {
 357 #   SELECT a, b
 358 #   FROM t1 FULL JOIN t2 ON b IS NULL
 359 #   ORDER BY coalesce(a,b,3);
 360 # } {
 361 # }
 362
 363 finish_test
 364
 365 ##############################################################################
 366 # This is the PG-14 test script generator
 367 #
 368 # puts "
 369 # \\pset border off
 370 # \\pset tuples_only on
 371 # \\pset null -
 372 #
 373 # DROP TABLE IF EXISTS t1;
 374 # DROP TABLE IF EXISTS t2;
 375 # CREATE TABLE t1(a INT);
 376 # INSERT INTO t1 VALUES(1),(NULL);
 377 # CREATE TABLE t2(b INT);
 378 # INSERT INTO t2 VALUES(2),(NULL);
 379 # "
 380 #
 381 # proc echo {prefix txt} {
 382 #   regsub -all {\n} $txt \n$prefix txt
 383 #   puts "$prefix$txt"
 384 # }
 385 #
 386 # set n 0
 387 # set k 0
 388 # foreach j1 {INNER LEFT RIGHT FULL} {
 389 #   foreach on1 {
 390 #      true
 391 #      {true WHERE a IS NULL}
 392 #      {a IS NULL}
 393 #      {true WHERE b IS NULL}
 394 #      {b IS NULL}
 395 #   } {
 396 #
 397 # incr n
 398 # incr k
 399 # set q1 ""
 400 # append q1 "SELECT a, b\n"
 401 # append q1 "  FROM t1 $j1 JOIN t2 ON $on1\n"
 402 # append q1 " ORDER BY coalesce(a,b,3);"
 403 #
 404 # echo "\\qecho " "do_execsql_test joinE-$n \{"
 405 # echo "\\qecho X  " $q1
 406 # echo "\\qecho " "\} \{"
 407 # puts $q1
 408 # echo "\\qecho " "\}"
 409 #
 410 #   }
 411 # }
 412 #
 413 # puts "
 414 # DELETE FROM t1;
 415 # INSERT INTO t1 VALUES(1);
 416 # DELETE FROM t2;
 417 # INSERT INTO t2 VALUES(NULL);
 418 # "
 419 #
 420 # foreach j1 {INNER LEFT RIGHT FULL} {
 421 #   foreach on1 {
 422 #      true
 423 #      {true WHERE a IS NULL}
 424 #      {a IS NULL}
 425 #      {true WHERE b IS NULL}
 426 #      {b IS NULL}
 427 #   } {
 428 #
 429 # incr n
 430 # incr k
 431 # set q1 ""
 432 # append q1 "SELECT a, b\n"
 433 # append q1 "  FROM t1 $j1 JOIN t2 ON $on1\n"
 434 # append q1 " ORDER BY coalesce(a,b,3);"
 435 #
 436 # echo "\\qecho " "do_execsql_test joinE-$n \{"
 437 # echo "\\qecho X  " $q1
 438 # echo "\\qecho " "\} \{"
 439 # puts $q1
 440 # echo "\\qecho " "\}"
 441 #
 442 #   }
 443 # }