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