| # 2022-05-13 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # |
| # This file implements tests for JOINs that use Bloom filters. |
| # |
| # The test case output is (mostly) all generated by PostgreSQL 14. This |
| # test module was created as follows: |
| # |
| # 1. Run a TCL script (included at the bottom of this file) that |
| # generates an input script for "psql" that will run man |
| # diverse tests on joins. |
| # |
| # 2. Run the script from step (1) through psql and collect the |
| # output. |
| # |
| # 3. Make a few minor global search-and-replace operations to convert |
| # the psql output into a form suitable for this test module. |
| # |
| # 4. Add this header, and the script content at the footer. |
| # |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| db nullvalue - |
| db eval { |
| CREATE TABLE t1(a INT); |
| INSERT INTO t1 VALUES(1),(NULL); |
| CREATE TABLE t2(b INT); |
| INSERT INTO t2 VALUES(2),(NULL); |
| } |
| do_execsql_test joinE-1 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 2 |
| 1 - |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-2 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-3 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-4 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - - |
| } |
| do_execsql_test joinE-5 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - - |
| } |
| do_execsql_test joinE-6 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 2 |
| 1 - |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-7 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-8 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-9 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - - |
| } |
| do_execsql_test joinE-10 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - - |
| } |
| do_execsql_test joinE-11 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 2 |
| 1 - |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-12 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-13 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-14 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - - |
| } |
| do_execsql_test joinE-15 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-16 { |
| SELECT a, b |
| FROM t1 FULL JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 2 |
| 1 - |
| - 2 |
| - - |
| } |
| do_execsql_test joinE-17 { |
| SELECT a, b |
| FROM t1 FULL JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| - 2 |
| - - |
| } |
| |
| # PG-14 is unable to perform this join. It says: FULL JOIN is only |
| # supported with merge-joinable or hash-joinable join conditions |
| # |
| # do_execsql_test joinE-18 { |
| # SELECT a, b |
| # FROM t1 FULL JOIN t2 ON a IS NULL |
| # ORDER BY coalesce(a,b,3); |
| # } { |
| # } |
| |
| do_execsql_test joinE-19 { |
| SELECT a, b |
| FROM t1 FULL JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| - - |
| } |
| |
| # PG-14 is unable to perform this join. It says: FULL JOIN is only |
| # supported with merge-joinable or hash-joinable join conditions |
| # |
| # do_execsql_test joinE-20 { |
| # SELECT a, b |
| # FROM t1 FULL JOIN t2 ON b IS NULL |
| # ORDER BY coalesce(a,b,3); |
| # } { |
| # } |
| |
| db eval { |
| DELETE FROM t1; |
| INSERT INTO t1 VALUES(1); |
| DELETE FROM t2; |
| INSERT INTO t2 VALUES(NULL); |
| } |
| |
| do_execsql_test joinE-21 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-22 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| } |
| do_execsql_test joinE-23 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| } |
| do_execsql_test joinE-24 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-25 { |
| SELECT a, b |
| FROM t1 INNER JOIN t2 ON b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-26 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-27 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| } |
| do_execsql_test joinE-28 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-29 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-30 { |
| SELECT a, b |
| FROM t1 LEFT JOIN t2 ON b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-31 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| |
| do_execsql_test joinE-32 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| } |
| |
| do_execsql_test joinE-33 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| - - |
| } |
| do_execsql_test joinE-34 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-35 { |
| SELECT a, b |
| FROM t1 RIGHT JOIN t2 ON b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-36 { |
| SELECT a, b |
| FROM t1 FULL JOIN t2 ON true |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| do_execsql_test joinE-37 { |
| SELECT a, b |
| FROM t1 FULL JOIN t2 ON true WHERE a IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| } |
| |
| # PG-14 is unable |
| # |
| # do_execsql_test joinE-38 { |
| # SELECT a, b |
| # FROM t1 FULL JOIN t2 ON a IS NULL |
| # ORDER BY coalesce(a,b,3); |
| # } { |
| # } |
| |
| do_execsql_test joinE-39 { |
| SELECT a, b |
| FROM t1 FULL JOIN t2 ON true WHERE b IS NULL |
| ORDER BY coalesce(a,b,3); |
| } { |
| 1 - |
| } |
| |
| # PG-14 is unable |
| # do_execsql_test joinE-40 { |
| # SELECT a, b |
| # FROM t1 FULL JOIN t2 ON b IS NULL |
| # ORDER BY coalesce(a,b,3); |
| # } { |
| # } |
| |
| finish_test |
| |
| ############################################################################## |
| # This is the PG-14 test script generator |
| # |
| # puts " |
| # \\pset border off |
| # \\pset tuples_only on |
| # \\pset null - |
| # |
| # DROP TABLE IF EXISTS t1; |
| # DROP TABLE IF EXISTS t2; |
| # CREATE TABLE t1(a INT); |
| # INSERT INTO t1 VALUES(1),(NULL); |
| # CREATE TABLE t2(b INT); |
| # INSERT INTO t2 VALUES(2),(NULL); |
| # " |
| # |
| # proc echo {prefix txt} { |
| # regsub -all {\n} $txt \n$prefix txt |
| # puts "$prefix$txt" |
| # } |
| # |
| # set n 0 |
| # set k 0 |
| # foreach j1 {INNER LEFT RIGHT FULL} { |
| # foreach on1 { |
| # true |
| # {true WHERE a IS NULL} |
| # {a IS NULL} |
| # {true WHERE b IS NULL} |
| # {b IS NULL} |
| # } { |
| # |
| # incr n |
| # incr k |
| # set q1 "" |
| # append q1 "SELECT a, b\n" |
| # append q1 " FROM t1 $j1 JOIN t2 ON $on1\n" |
| # append q1 " ORDER BY coalesce(a,b,3);" |
| # |
| # echo "\\qecho " "do_execsql_test joinE-$n \{" |
| # echo "\\qecho X " $q1 |
| # echo "\\qecho " "\} \{" |
| # puts $q1 |
| # echo "\\qecho " "\}" |
| # |
| # } |
| # } |
| # |
| # puts " |
| # DELETE FROM t1; |
| # INSERT INTO t1 VALUES(1); |
| # DELETE FROM t2; |
| # INSERT INTO t2 VALUES(NULL); |
| # " |
| # |
| # foreach j1 {INNER LEFT RIGHT FULL} { |
| # foreach on1 { |
| # true |
| # {true WHERE a IS NULL} |
| # {a IS NULL} |
| # {true WHERE b IS NULL} |
| # {b IS NULL} |
| # } { |
| # |
| # incr n |
| # incr k |
| # set q1 "" |
| # append q1 "SELECT a, b\n" |
| # append q1 " FROM t1 $j1 JOIN t2 ON $on1\n" |
| # append q1 " ORDER BY coalesce(a,b,3);" |
| # |
| # echo "\\qecho " "do_execsql_test joinE-$n \{" |
| # echo "\\qecho X " $q1 |
| # echo "\\qecho " "\} \{" |
| # puts $q1 |
| # echo "\\qecho " "\}" |
| # |
| # } |
| # } |