Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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 " "\}"
+# 
+#   }
+# }