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