summaryrefslogtreecommitdiffstats
path: root/test/joinE.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/joinE.test')
-rw-r--r--test/joinE.test443
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 " "\}"
+#
+# }
+# }
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-23 18:29:00 +0000