diff options
Diffstat (limited to 'test/joinE.test')
| -rw-r--r-- | test/joinE.test | 443 |
1 files changed, 443 insertions, 0 deletions
diff --git a/test/joinE.test b/test/joinE.test new file mode 100644 index 0000000..8c8e72a --- /dev/null +++ b/test/joinE.test @@ -0,0 +1,443 @@ +# 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 " "\}" +# +# } +# } |