diff options
Diffstat (limited to '')
| -rw-r--r-- | test/joinB.test | 7252 |
1 files changed, 7252 insertions, 0 deletions
diff --git a/test/joinB.test b/test/joinB.test new file mode 100644 index 0000000..baaeae6 --- /dev/null +++ b/test/joinB.test @@ -0,0 +1,7252 @@ +set testdir [file dirname $argv0] +# 2022-04-19 +# +# 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 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. +# +source $testdir/tester.tcl +db nullvalue - +db eval { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + DROP TABLE IF EXISTS t3; + DROP TABLE IF EXISTS t4; + DROP TABLE IF EXISTS t5; + CREATE TABLE t1(a INT, b INT, c INT); + CREATE TABLE t2(a INT, b INT, d INT); + CREATE TABLE t3(a INT, b INT, e INT); + CREATE TABLE t4(a INT, b INT, f INT); + CREATE TABLE t5(a INT, b INT, g INT); + INSERT INTO t1 VALUES(11,21,31),(12,22,32),(15,25,35),(17,27,37); + INSERT INTO t2 VALUES(12,22,32),(13,23,33),(15,25,35),(18,28,38), + (NULL,NULL,36); + INSERT INTO t4 VALUES(11,21,31),(13,23,33),(15,25,35),(19,29,39); + INSERT INTO t3 SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t4; + INSERT INTO t5 SELECT * FROM t3 WHERE a>=15; +} +do_execsql_test joinB-1 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-2 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-3 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-4 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-5 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-6 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-7 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-8 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL INNER JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-9 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-10 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-11 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 12 12 12 12 - - + 15 15 15 15 15 15 +} +do_execsql_test joinB-12 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 12 32 32 32 - - + 15 35 35 35 35 35 +} +do_execsql_test joinB-13 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-14 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-15 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 12 12 12 12 - - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-16 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL INNER JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-17 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-18 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-19 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-20 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-21 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-22 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-23 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-24 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL INNER JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-25 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-26 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-27 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-28 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-29 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-30 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-31 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-32 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL INNER JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-33 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-34 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-35 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-36 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-37 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-38 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-39 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-40 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-41 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-42 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-43 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 12 12 12 12 - - + 15 15 15 15 15 15 +} +do_execsql_test joinB-44 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 12 32 32 32 - - + 15 35 35 35 35 35 +} +do_execsql_test joinB-45 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-46 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-47 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 12 12 12 12 - - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-48 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-49 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-50 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-51 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-52 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-53 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-54 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-55 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-56 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-57 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-58 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-59 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-60 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-61 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-62 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-63 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-64 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-65 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-66 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-67 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-68 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-69 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-70 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-71 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-72 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-73 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-74 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-75 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-76 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-77 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-78 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-79 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-80 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-81 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-82 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-83 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-84 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-85 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-86 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-87 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-88 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-89 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-90 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-91 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-92 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-93 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-94 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-95 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-96 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-97 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-98 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-99 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-100 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-101 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-102 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-103 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-104 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL FULL JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-105 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-106 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-107 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-108 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-109 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-110 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-111 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-112 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL FULL JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-113 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-114 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-115 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-116 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-117 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-118 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-119 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-120 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL FULL JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-121 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-122 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-123 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-124 { + SELECT a, c, d, e, f, g + FROM t1 + INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-125 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-126 { + SELECT a, b, c, d, e, f, g + FROM t1 + INNER JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-127 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + INNER JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-128 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL INNER JOIN t2 + NATURAL FULL JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-129 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-130 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-131 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 15 15 15 15 15 15 +} +do_execsql_test joinB-132 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-133 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-134 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-135 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-136 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-137 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 +} +do_execsql_test joinB-138 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 +} +do_execsql_test joinB-139 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 15 15 15 15 15 15 + 17 17 - 17 - 17 +} +do_execsql_test joinB-140 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 15 35 35 35 35 35 + 17 37 - - - - +} +do_execsql_test joinB-141 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-142 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-143 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-144 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-145 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-146 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-147 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-148 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-149 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-150 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-151 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-152 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-153 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 19 - - - 19 19 +} +do_execsql_test joinB-154 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 19 - - - 39 39 +} +do_execsql_test joinB-155 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 19 - - - 19 19 +} +do_execsql_test joinB-156 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 + 17 37 - - - - +} +do_execsql_test joinB-157 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-158 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-159 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-160 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-161 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-162 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-163 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 15 15 15 15 15 15 +} +do_execsql_test joinB-164 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-165 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-166 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-167 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-168 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-169 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 +} +do_execsql_test joinB-170 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 +} +do_execsql_test joinB-171 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 15 15 15 15 15 15 + 17 17 - 17 - 17 +} +do_execsql_test joinB-172 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 15 35 35 35 35 35 + 17 37 - - - - +} +do_execsql_test joinB-173 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-174 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-175 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-176 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-177 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-178 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-179 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-180 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-181 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-182 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-183 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-184 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-185 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 19 - - - 19 19 +} +do_execsql_test joinB-186 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 19 - - - 39 39 +} +do_execsql_test joinB-187 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 19 - - - 19 19 +} +do_execsql_test joinB-188 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 + 17 37 - - - - +} +do_execsql_test joinB-189 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-190 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-191 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - - 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-192 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-193 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-194 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-195 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-196 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-197 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-198 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-199 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-200 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-201 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-202 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-203 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-204 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-205 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-206 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-207 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-208 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-209 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-210 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-211 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-212 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-213 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-214 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-215 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-216 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-217 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-218 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-219 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-220 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-221 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-222 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-223 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-224 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-225 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-226 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-227 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-228 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-229 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-230 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-231 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-232 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-233 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-234 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-235 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-236 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-237 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-238 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-239 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-240 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-241 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-242 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-243 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-244 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - - - 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-245 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-246 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-247 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-248 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-249 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-250 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - - 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-251 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-252 { + SELECT a, c, d, e, f, g + FROM t1 + LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - - - 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - +} +do_execsql_test joinB-253 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-254 { + SELECT a, b, c, d, e, f, g + FROM t1 + LEFT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - - 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-255 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + LEFT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - - 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - - 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-256 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL LEFT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-257 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-258 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-259 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 13 - 13 13 13 - + 15 15 15 15 15 15 +} +do_execsql_test joinB-260 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-261 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-262 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-263 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-264 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-265 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 18 - 18 18 - 18 +} +do_execsql_test joinB-266 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 18 - 38 38 - 38 +} +do_execsql_test joinB-267 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 18 - 18 18 - 18 +} +do_execsql_test joinB-268 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 18 - 38 38 - - +} +do_execsql_test joinB-269 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-270 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - 38 38 - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-271 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-272 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-273 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-274 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-275 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-276 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-277 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-278 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-279 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-280 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-281 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-282 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 18 - 38 38 - 38 + 19 - - - 39 39 +} +do_execsql_test joinB-283 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-284 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 18 - 38 38 - - +} +do_execsql_test joinB-285 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-286 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - 38 38 - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-287 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-288 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL INNER JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-289 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-290 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-291 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 13 - 13 13 13 - + 15 15 15 15 15 15 +} +do_execsql_test joinB-292 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-293 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-294 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-295 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-296 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-297 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 18 - 18 18 - 18 +} +do_execsql_test joinB-298 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 18 - 38 38 - 38 +} +do_execsql_test joinB-299 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 18 - 18 18 - 18 +} +do_execsql_test joinB-300 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 18 - 38 38 - - +} +do_execsql_test joinB-301 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-302 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - 38 38 - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-303 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-304 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-305 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-306 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-307 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-308 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-309 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-310 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-311 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-312 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-313 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-314 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 18 - 38 38 - 38 + 19 - - - 39 39 +} +do_execsql_test joinB-315 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - - 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-316 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 - - - 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 18 - 38 38 - - +} +do_execsql_test joinB-317 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-318 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - 38 38 - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-319 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - - 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-320 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-321 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-322 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-323 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-324 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-325 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-326 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-327 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-328 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-329 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-330 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-331 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-332 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-333 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-334 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-335 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-336 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-337 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-338 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-339 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-340 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-341 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-342 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-343 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-344 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-345 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-346 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-347 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-348 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-349 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-350 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-351 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-352 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-353 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-354 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-355 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-356 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-357 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-358 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-359 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-360 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-361 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-362 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-363 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-364 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-365 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-366 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-367 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-368 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-369 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-370 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-371 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-372 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-373 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-374 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-375 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 - - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-376 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-377 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-378 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 - - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-379 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-380 { + SELECT a, c, d, e, f, g + FROM t1 + RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-381 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-382 { + SELECT a, b, c, d, e, f, g + FROM t1 + RIGHT JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-383 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + RIGHT JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 - - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-384 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL RIGHT JOIN t2 + NATURAL FULL JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-385 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-386 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-387 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 +} +do_execsql_test joinB-388 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-389 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-390 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-391 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-392 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL INNER JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-393 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 +} +do_execsql_test joinB-394 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 +} +do_execsql_test joinB-395 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 +} +do_execsql_test joinB-396 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - - - - + 18 - 38 38 - - +} +do_execsql_test joinB-397 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-398 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-399 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-400 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL INNER JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-401 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-402 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-403 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-404 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-405 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-406 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-407 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-408 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL INNER JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-409 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-410 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 + 19 - - - 39 39 +} +do_execsql_test joinB-411 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-412 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - - - - + 18 - 38 38 - - +} +do_execsql_test joinB-413 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-414 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + INNER JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-415 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + INNER JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-416 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL INNER JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-417 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 +} +do_execsql_test joinB-418 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 +} +do_execsql_test joinB-419 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 +} +do_execsql_test joinB-420 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-421 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-422 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-423 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - - 19 +} +do_execsql_test joinB-424 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-425 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 +} +do_execsql_test joinB-426 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 +} +do_execsql_test joinB-427 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 +} +do_execsql_test joinB-428 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - - - - + 18 - 38 38 - - +} +do_execsql_test joinB-429 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-430 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - - - 39 +} +do_execsql_test joinB-431 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - - 19 +} +do_execsql_test joinB-432 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-433 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-434 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - - 39 39 +} +do_execsql_test joinB-435 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - - 19 19 +} +do_execsql_test joinB-436 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-437 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-438 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-439 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-440 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-441 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-442 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 + 19 - - - 39 39 +} +do_execsql_test joinB-443 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-444 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - - 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - - - - + 18 - 38 38 - - +} +do_execsql_test joinB-445 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-446 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + LEFT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - - 39 39 +} +do_execsql_test joinB-447 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + LEFT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - - 19 19 +} +do_execsql_test joinB-448 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL LEFT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-449 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-450 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-451 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-452 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-453 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-454 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-455 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-456 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-457 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-458 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-459 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-460 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-461 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-462 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-463 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-464 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-465 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-466 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-467 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-468 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-469 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-470 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-471 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-472 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-473 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-474 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-475 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-476 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-477 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-478 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + RIGHT JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-479 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + RIGHT JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-480 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL RIGHT JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-481 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-482 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-483 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-484 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-485 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-486 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + INNER JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-487 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + INNER JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-488 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL FULL JOIN t3 + NATURAL INNER JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-489 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-490 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-491 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-492 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-493 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-494 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + LEFT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-495 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + LEFT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-496 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL FULL JOIN t3 + NATURAL LEFT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-497 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-498 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 19 - - 39 39 39 +} +do_execsql_test joinB-499 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 19 - - 19 19 19 +} +do_execsql_test joinB-500 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 13 - 33 33 33 - + 15 35 35 35 35 35 +} +do_execsql_test joinB-501 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-502 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + RIGHT JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 - - - - 37 + 18 28 - - - - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-503 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + RIGHT JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 11 11 - 11 11 - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 - - - - 17 + 18 - - - - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-504 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL FULL JOIN t3 + NATURAL RIGHT JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +do_execsql_test joinB-505 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + INNER JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-506 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + INNER JOIN t5 USING(a,b) + WHERE a<>13 + ORDER BY 1 NULLS FIRST; +} { + 15 35 35 35 35 35 + 17 37 - 37 - 37 + 18 - 38 38 - 38 + 19 - - 39 39 39 +} +do_execsql_test joinB-507 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + LEFT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-508 { + SELECT a, c, d, e, f, g + FROM t1 + FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) + FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) + WHERE a<=18 + ORDER BY 1 NULLS FIRST; +} { + 11 31 - 31 31 - + 12 32 32 32 - - + 13 - 33 33 33 - + 15 35 35 35 35 35 + 17 37 - 37 - - + 18 - 38 38 - - +} +do_execsql_test joinB-509 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + RIGHT JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-510 { + SELECT a, b, c, d, e, f, g + FROM t1 + FULL JOIN t2 USING(a,b) + FULL JOIN t3 USING(a,b) + FULL JOIN t4 USING(a,b) + RIGHT JOIN t5 USING(a,b) + WHERE d<>33 OR d IS NULL + ORDER BY 1 NULLS FIRST; +} { + 15 25 35 35 35 35 35 + 17 27 37 - 37 - 37 + 18 28 - 38 38 - 38 + 19 29 - - 39 39 39 +} +do_execsql_test joinB-511 { + SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a + FROM t1 + FULL JOIN t2 USING(a) + FULL JOIN t3 USING(a) + FULL JOIN t4 USING(a) + FULL JOIN t5 USING(a) + ORDER BY 1 NULLS FIRST; +} { + - - - - - - + - - - - - - + 11 11 - 11 11 - + 12 12 12 12 - - + 13 - 13 13 13 - + 15 15 15 15 15 15 + 17 17 - 17 - 17 + 18 - 18 18 - 18 + 19 - - 19 19 19 +} +do_execsql_test joinB-512 { + SELECT b, c, d, e, f, g + FROM t1 + NATURAL FULL JOIN t2 + NATURAL FULL JOIN t3 + NATURAL FULL JOIN t4 + NATURAL FULL JOIN t5 + WHERE b BETWEEN 12 AND 17 + ORDER BY 1 NULLS FIRST; +} { +} +finish_test + +############################################################################## +# Here is the original TCL script that generated the psql input file: +# +# +# 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; +# DROP TABLE IF EXISTS t5; +# CREATE TABLE t1(a INT, b INT, c INT); +# CREATE TABLE t2(a INT, b INT, d INT); +# CREATE TABLE t3(a INT, b INT, e INT); +# CREATE TABLE t4(a INT, b INT, f INT); +# CREATE TABLE t5(a INT, b INT, g INT); +# INSERT INTO t1 VALUES(11,21,31),(12,22,32),(15,25,35),(17,27,37); +# INSERT INTO t2 VALUES(12,22,32),(13,23,33),(15,25,35),(18,28,38),(NULL,NULL,36); +# INSERT INTO t4 VALUES(11,21,31),(13,23,33),(15,25,35),(19,29,39); +# INSERT INTO t3 SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t4; +# INSERT INTO t5 SELECT * FROM t3 WHERE a>=15; +# " +# +# 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 j2 {INNER LEFT RIGHT FULL} { +# foreach j3 {INNER LEFT RIGHT FULL} { +# foreach j4 {INNER LEFT RIGHT FULL} { +# +# incr n +# incr k +# set q1 "" +# append q1 "SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a\n" +# append q1 " FROM t1\n" +# append q1 " $j1 JOIN t2 USING(a)\n" +# append q1 " $j2 JOIN t3 USING(a)\n" +# append q1 " $j3 JOIN t4 USING(a)\n" +# append q1 " $j4 JOIN t5 USING(a)\n" +# append q1 " ORDER BY 1 NULLS FIRST;" +# +# echo "\\qecho " "do_execsql_test joinB-$n \{" +# echo "\\qecho X " $q1 +# echo "\\qecho " "\} \{" +# puts $q1 +# echo "\\qecho " "\}" +# +# switch [expr {$k%4}] { +# 0 { +# set q2 "" +# append q2 "SELECT b, c, d, e, f, g\n" +# append q2 " FROM t1\n" +# append q2 " NATURAL $j1 JOIN t2\n" +# append q2 " NATURAL $j2 JOIN t3\n" +# append q2 " NATURAL $j3 JOIN t4\n" +# append q2 " NATURAL $j4 JOIN t5\n" +# append q2 " WHERE b BETWEEN 12 AND 17\n" +# append q2 " ORDER BY 1 NULLS FIRST;" +# incr n +# echo "\\qecho " "do_execsql_test joinB-$n \{" +# echo "\\qecho X " $q2 +# echo "\\qecho " "\} \{" +# puts $q2 +# echo "\\qecho " "\}" +# } +# 1 { +# set q2 "" +# append q2 "SELECT a, c, d, e, f, g\n" +# append q2 " FROM t1\n" +# append q2 " $j1 JOIN t2 USING(a,b)\n" +# append q2 " $j2 JOIN t3 USING(a,b)\n" +# append q2 " $j3 JOIN t4 USING(a,b)\n" +# append q2 " $j4 JOIN t5 USING(a,b)\n" +# append q2 " WHERE a<>13\n" +# append q2 " ORDER BY 1 NULLS FIRST;" +# incr n +# echo "\\qecho " "do_execsql_test joinB-$n \{" +# echo "\\qecho X " $q2 +# echo "\\qecho " "\} \{" +# puts $q2 +# echo "\\qecho " "\}" +# } +# 2 { +# set q2 "" +# append q2 "SELECT a, c, d, e, f, g\n" +# append q2 " FROM t1\n" +# append q2 " $j1 JOIN (t2 $j2 JOIN t3 USING(a)) USING(a)\n" +# append q2 " $j3 JOIN (t4 $j4 JOIN t5 USING(a)) USING(a)\n" +# append q2 " WHERE a<=18\n" +# append q2 " ORDER BY 1 NULLS FIRST;" +# incr n +# echo "\\qecho " "do_execsql_test joinB-$n \{" +# echo "\\qecho X " $q2 +# echo "\\qecho " "\} \{" +# puts $q2 +# echo "\\qecho " "\}" +# } +# 3 { +# set q2 "" +# append q2 "SELECT a, b, c, d, e, f, g\n" +# append q2 " FROM t1\n" +# append q2 " $j1 JOIN t2 USING(a,b)\n" +# append q2 " $j2 JOIN t3 USING(a,b)\n" +# append q2 " $j3 JOIN t4 USING(a,b)\n" +# append q2 " $j4 JOIN t5 USING(a,b)\n" +# append q2 " WHERE d<>33 OR d IS NULL\n" +# append q2 " ORDER BY 1 NULLS FIRST;" +# incr n +# echo "\\qecho " "do_execsql_test joinB-$n \{" +# echo "\\qecho X " $q2 +# echo "\\qecho " "\} \{" +# puts $q2 +# echo "\\qecho " "\}" +# } +# } +# +# } +# } +# } +# } +############################################################################## |