From 63847496f14c813a5d80efd5b7de0f1294ffe1e3 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 16:07:11 +0200 Subject: Adding upstream version 3.45.1. Signed-off-by: Daniel Baumann --- test/joinF.test | 613 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 613 insertions(+) create mode 100644 test/joinF.test (limited to 'test/joinF.test') diff --git a/test/joinF.test b/test/joinF.test new file mode 100644 index 0000000..0848e37 --- /dev/null +++ b/test/joinF.test @@ -0,0 +1,613 @@ +# 2022-05-31 +# +# 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 +# +# 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. +# +# A few extra tests that were not generated from postgresql output are +# added at the end. +# +set testdir [file dirname $argv0] +source $testdir/tester.tcl +db nullvalue - +db eval { + CREATE TABLE t1(x INT); + CREATE TABLE t2(y INT); + CREATE TABLE t3(z INT); + CREATE TABLE t4(w INT); + INSERT INTO t1 VALUES(10); + INSERT INTO t3 VALUES(20),(30); + INSERT INTO t4 VALUES(50); +} +do_execsql_test joinF-1 { + SELECT * + FROM t1 INNER JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-2 { + SELECT * + FROM t1 INNER JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-3 { + SELECT * + FROM t1 INNER JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-4 { + SELECT * + FROM t1 INNER JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-5 { + SELECT * + FROM t1 INNER JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - - 50 +} +do_execsql_test joinF-6 { + SELECT * + FROM t1 INNER JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-7 { + SELECT * + FROM t1 INNER JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-8 { + SELECT * + FROM t1 INNER JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-9 { + SELECT * + FROM t1 INNER JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-10 { + SELECT * + FROM t1 INNER JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-11 { + SELECT * + FROM t1 INNER JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - - 50 +} +do_execsql_test joinF-12 { + SELECT * + FROM t1 INNER JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-13 { + SELECT * + FROM t1 INNER JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-14 { + SELECT * + FROM t1 INNER JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-15 { + SELECT * + FROM t1 INNER JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-16 { + SELECT * + FROM t1 INNER JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-17 { + SELECT * + FROM t1 INNER JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-18 { + SELECT * + FROM t1 INNER JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-19 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-20 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-21 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-22 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-23 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - - 50 +} +do_execsql_test joinF-24 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-25 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + 10 - - 50 +} +do_execsql_test joinF-26 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-27 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + 10 - - 50 +} +do_execsql_test joinF-28 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-29 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + 10 - - 50 +} +do_execsql_test joinF-30 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-31 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-32 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-33 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-34 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-35 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-36 { + SELECT * + FROM t1 LEFT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-37 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-38 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-39 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-40 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-41 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - - 50 +} +do_execsql_test joinF-42 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + INNER JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-43 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-44 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-45 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-46 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-47 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - - 50 +} +do_execsql_test joinF-48 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + LEFT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { +} +do_execsql_test joinF-49 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-50 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + INNER JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-51 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-52 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + LEFT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-53 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +do_execsql_test joinF-54 { + SELECT * + FROM t1 RIGHT JOIN t2 ON true + RIGHT JOIN t3 ON t2.y IS NOT NULL + RIGHT JOIN t4 ON true + WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600) + ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0); +} { + - - 20 50 + - - 30 50 +} +finish_test + +############################################################################ +# This is the TCL script used to generate the psql script that generated +# the data above. +# +# puts " +# \\pset border off +# \\pset tuples_only on +# \\pset null - +# +# DROP TABLE IF EXISTS t1; +# DROP TABLE IF EXISTS t2; +# DROP TABLE IF EXISTS t3; +# DROP TABLE IF EXISTS t4; +# CREATE TABLE t1(x INT); +# CREATE TABLE t2(y INT); +# CREATE TABLE t3(z INT); +# CREATE TABLE t4(w INT); +# INSERT INTO t1 VALUES(10); +# INSERT INTO t3 VALUES(20),(30); +# INSERT INTO t4 VALUES(50); +# " +# +# proc echo {prefix txt} { +# regsub -all {\n} $txt \n$prefix txt +# puts "$prefix$txt" +# } +# +# set n 0 +# foreach j1 {INNER LEFT RIGHT} { +# foreach j2 {INNER LEFT RIGHT} { +# foreach j3 {INNER LEFT RIGHT} { +# +# incr n +# set q1 "" +# append q1 "SELECT *\n" +# append q1 " FROM t1 $j1 JOIN t2 ON true\n" +# append q1 " $j2 JOIN t3 ON t2.y IS NOT NULL\n" +# append q1 " $j3 JOIN t4 ON true\n" +# append q1 " ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);" +# +# echo "\\qecho " "do_execsql_test joinF-$n \{" +# echo "\\qecho X " $q1 +# echo "\\qecho " "\} \{" +# puts $q1 +# echo "\\qecho " "\}" +# +# incr n +# set q1 "" +# append q1 "SELECT *\n" +# append q1 " FROM t1 $j1 JOIN t2 ON true\n" +# append q1 " $j2 JOIN t3 ON t2.y IS NOT NULL\n" +# append q1 " $j3 JOIN t4 ON true\n" +# append q1 " WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600)\n" +# append q1 " ORDER BY coalesce(t1.x,t2.y,t3.z,t4.w,0);" +# +# echo "\\qecho " "do_execsql_test joinF-$n \{" +# echo "\\qecho X " $q1 +# echo "\\qecho " "\} \{" +# puts $q1 +# echo "\\qecho " "\}" +# +# } +# } +# } +# -- cgit v1.2.3