github.com/dolthub/go-mysql-server@v0.18.0/enginetest/sqllogictest/testdata/join/join.txt

# Copyright 2023 Dolthub, Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
# TEST ADAPTED FROM COCKROACHDB; HEAVILY MODIFIED TO WORK WITH MYSQL
# SOURCE https://github.com/cockroachdb/cockroach/blob/7a2796a8d45e4a4031c1861747483e5390fbff6c/pkg/sql/logictest/testdata/logic_test/join

# The join condition logic is tricky to get right with NULL
# values. Simple implementations can deal well with NULLs on the first
# or last row but fail to handle them in the middle. So the test table
# must contain at least 3 rows with a null in the middle. This test
# table also contains the pair 44/42 so that a test with a non-trivial
# ON condition can be written.
statement ok
CREATE TABLE onecolumn (x INT)

statement ok
INSERT INTO onecolumn(x) VALUES (44), (NULL), (42)

query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a CROSS JOIN onecolumn AS b ORDER BY x, y
----
NULL
NULL
NULL
42
NULL
44
42
NULL
42
42
42
44
44
NULL
44
42
44
44

# Check that name resolution chokes on ambiguity when it needs to.
statement error
SELECT x FROM onecolumn AS a, onecolumn AS b

query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a JOIN onecolumn AS b ON a.x = b.x
----
44
44
42
42

query I nosort
SELECT * FROM onecolumn AS a JOIN onecolumn as b USING(x) ORDER BY x
----
42
44

query I nosort
SELECT * FROM onecolumn AS a NATURAL JOIN onecolumn as b
----
44
42

query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a LEFT OUTER JOIN onecolumn AS b ON a.x = b.x
----
44
44
NULL
NULL
42
42

query I nosort
SELECT * FROM onecolumn AS a LEFT OUTER JOIN onecolumn AS b USING(x) ORDER BY x
----
NULL
42
44

# Check that ORDER BY chokes on ambiguity if no table less columns
# were introduced by USING. (#12239)
statement error
SELECT * FROM onecolumn AS a, onecolumn AS b ORDER BY x

query I nosort
SELECT * FROM onecolumn AS a NATURAL LEFT OUTER JOIN onecolumn AS b
----
44
NULL
42

query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a RIGHT OUTER JOIN onecolumn AS b ON a.x = b.x ORDER BY x, y
----
NULL
NULL
42
42
44
44

query I nosort
SELECT * FROM onecolumn AS a RIGHT OUTER JOIN onecolumn AS b USING(x) ORDER BY x
----
NULL
42
44

query I nosort
SELECT * FROM onecolumn AS a NATURAL RIGHT OUTER JOIN onecolumn AS b ORDER BY x
----
NULL
42
44

statement ok
CREATE TABLE onecolumn_w(w INT)

statement ok
INSERT INTO onecolumn_w(w) VALUES (42),(43)

query II nosort
SELECT * FROM onecolumn AS a NATURAL JOIN onecolumn_w as b ORDER BY x, w
----
NULL
42
NULL
43
42
42
42
43
44
42
44
43

statement ok
CREATE TABLE othercolumn (x INT)

statement ok
INSERT INTO othercolumn(x) VALUES (43),(42),(16)

# Check that the source columns can be selected separately from the
# USING column (#12033).
# Check that a limit on the JOIN's result do not cause rows from the
# JOIN operands to become invisible to the JOIN.
query I nosort
SELECT * FROM (SELECT x FROM onecolumn ORDER BY x DESC) sq NATURAL JOIN (SELECT column_0 as x from (VALUES ROW(42)) v) AS v LIMIT 1
----
42

statement ok
CREATE TABLE `empty` (x INT)

query II nosort
SELECT a.x AS x, b.x as y FROM onecolumn AS a CROSS JOIN `empty` AS b
----

query II nosort
SELECT * FROM `empty` AS a CROSS JOIN onecolumn AS b
----

query II nosort
SELECT a.x AS x, b.x as y FROM onecolumn AS a JOIN `empty` AS b ON a.x = b.x
----

query I nosort
SELECT * FROM onecolumn AS a JOIN `empty` AS b USING(x)
----

query II nosort
SELECT a.x AS x, b.x AS y FROM `empty` AS a JOIN onecolumn AS b ON a.x = b.x
----

query I nosort
SELECT * FROM `empty` AS a JOIN onecolumn AS b USING(x)
----

query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a LEFT OUTER JOIN `empty` AS b ON a.x = b.x ORDER BY a.x
----
NULL
NULL
42
NULL
44
NULL

query I nosort
SELECT * FROM onecolumn AS a LEFT OUTER JOIN `empty` AS b USING(x) ORDER BY x
----
NULL
42
44

query II nosort
SELECT a.x AS x, b.x AS y FROM `empty` AS a LEFT OUTER JOIN onecolumn AS b ON a.x = b.x
----

query I nosort
SELECT * FROM `empty` AS a LEFT OUTER JOIN onecolumn AS b USING(x)
----

query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a RIGHT OUTER JOIN `empty` AS b ON a.x = b.x
----

query I nosort
SELECT * FROM onecolumn AS a RIGHT OUTER JOIN `empty` AS b USING(x)
----

statement ok
CREATE TABLE twocolumn (x INT, y INT)

statement ok
INSERT INTO twocolumn(x, y) VALUES (44,51), (NULL,52), (42,53), (45,45)

# Natural joins with partial match
query II nosort
SELECT * FROM onecolumn NATURAL JOIN twocolumn
----
44
51
42
53

query IIII nosort
SELECT * FROM twocolumn AS a JOIN twocolumn AS b ON a.x = a.y
----
45
45
44
51
45
45
NULL
52
45
45
42
53
45
45
45
45

# Inner join with filter predicate
query II nosort
SELECT o.x, t.y FROM onecolumn o INNER JOIN twocolumn t ON (o.x=t.x AND t.y=53)
----
42
53

# Outer joins with filter predicate
query II nosort
SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND t.y=53)
----
44
NULL
NULL
NULL
42
53

query II nosort
SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND o.x=44)
----
44
51
NULL
NULL
42
NULL

query II nosort
SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND t.x=44)
----
44
51
NULL
NULL
42
NULL

# Computed columns with NATURAL FULL JOIN.
## Simple test cases for inner, left, right, and outer joins

statement ok
CREATE TABLE a (i int)

statement ok
INSERT INTO a VALUES (1), (2), (3)

statement ok
CREATE TABLE b (i int, b bool)

statement ok
INSERT INTO b VALUES (2, true), (3, true), (4, false)

query IIB nosort
SELECT * FROM a INNER JOIN b ON a.i = b.i ORDER BY a.i, b.i, b.b
----
2
2
1
3
3
1

query IIB nosort
SELECT * FROM a LEFT OUTER JOIN b ON a.i = b.i ORDER BY a.i, b.i, b.b
----
1
NULL
NULL
2
2
1
3
3
1

query IIB nosort
SELECT * FROM a RIGHT OUTER JOIN b ON a.i = b.i ORDER BY a.i, b.i, b.b
----
NULL
4
0
2
2
1
3
3
1

# Full outer join with filter predicate
# Duplicate right matches for a single left row
statement ok
INSERT INTO b VALUES (3, false)

query IIB nosort
SELECT * FROM a RIGHT OUTER JOIN b ON a.i=b.i ORDER BY b.i, b.b
----
2
2
1
3
3
0
3
3
1
NULL
4
0


# Check column orders and names.
query IIIIII nosort
SELECT * FROM (onecolumn CROSS JOIN twocolumn JOIN (SELECT a.x AS b FROM onecolumn AS a) a ON a.b=twocolumn.x JOIN (SELECT c.x as d, c.y as e FROM twocolumn AS c) c ON a.b=c.d AND c.d=onecolumn.x) ORDER BY 1 LIMIT 1
----
42
42
53
42
42
53

# Check sub-queries in ON conditions.
query III nosort
SELECT * FROM onecolumn JOIN twocolumn ON twocolumn.x = onecolumn.x AND onecolumn.x IN (SELECT x FROM twocolumn WHERE y >= 52)
----
42
42
53

# Check sub-queries as data sources.
query I nosort
SELECT * FROM onecolumn JOIN (SELECT column_0 as x FROM (VALUES ROW(41), ROW(42), ROW(43)) a) AS a USING(x)
----
42

query I nosort
SELECT * FROM onecolumn JOIN (SELECT x + 2 AS x FROM onecolumn) sq USING(x)
----
44

# Check that a single column can have multiple table aliases.
query IIII nosort
SELECT * FROM (twocolumn AS a JOIN twocolumn AS b USING(x) JOIN twocolumn AS c USING(x)) ORDER BY x LIMIT 1
----
42
53
53
53

query IIIIII nosort
SELECT a.x AS s, b.x, c.x, a.y, b.y, c.y FROM (twocolumn AS a JOIN twocolumn AS b USING(x) JOIN twocolumn AS c USING(x)) ORDER BY s
----
42
42
42
53
53
53
44
44
44
51
51
51
45
45
45
45
45
45

statement error
SELECT * FROM (onecolumn AS a JOIN onecolumn AS b USING(y))

# This is valid in MySQL
# statement error
# SELECT * FROM (onecolumn AS a JOIN onecolumn AS b USING(x, x))

statement ok
CREATE TABLE othertype (x TEXT)

# This is valid in MySQL
# statement error
# SELECT * FROM (onecolumn AS a JOIN othertype AS b USING(x))

statement error
SELECT * FROM (onecolumn JOIN onecolumn USING(x))

statement error
SELECT * FROM (onecolumn JOIN twocolumn USING(x) JOIN onecolumn USING(x))

# Check that star expansion works across anonymous sources.
query II nosort
SELECT * FROM (SELECT * FROM onecolumn) sq1, (SELECT * FROM onecolumn) sq2 ORDER BY sq1.x, sq2.x
----
NULL
NULL
NULL
42
NULL
44
42
NULL
42
42
42
44
44
NULL
44
42
44
44

# Check that anonymous sources are properly looked up without ambiguity.
query I nosort
SELECT x FROM (onecolumn JOIN othercolumn USING (x)) JOIN (onecolumn AS a JOIN othercolumn AS b USING(x)) USING(x)
----
42

# Check that multiple anonymous sources cause proper ambiguity errors.
statement error
SELECT x FROM (SELECT * FROM onecolumn), (SELECT * FROM onecolumn)

statement error
SELECT * FROM (onecolumn AS a JOIN onecolumn AS b ON x > 32)

statement error
SELECT * FROM (onecolumn AS a JOIN onecolumn AS b ON a.y > y)

statement ok
CREATE TABLE s(x INT)

statement ok
INSERT INTO s(x) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

# Ensure that large cross-joins are optimized somehow (#10633)
statement ok
CREATE TABLE customers(id INT PRIMARY KEY NOT NULL)

statement ok
CREATE TABLE orders(id INT, cust INT REFERENCES customers(id))

# No way am I going to convert this
# query TTTTTTTTIIITTI nosort
# SELECT     NULL::text  AS pktable_cat,
#        pkn.nspname AS pktable_schem,
#        pkc.relname AS pktable_name,
#        pka.attname AS pkcolumn_name,
#        NULL::text  AS fktable_cat,
#        fkn.nspname AS fktable_schem,
#        fkc.relname AS fktable_name,
#        fka.attname AS fkcolumn_name,
#        pos.n       AS key_seq,
#        CASE con.confupdtype
#             WHEN 'c' THEN 0
#             WHEN 'n' THEN 2
#             WHEN 'd' THEN 4
#             WHEN 'r' THEN 1
#             WHEN 'a' THEN 3
#             ELSE NULL
#        END AS update_rule,
#        CASE con.confdeltype
#             WHEN 'c' THEN 0
#             WHEN 'n' THEN 2
#             WHEN 'd' THEN 4
#             WHEN 'r' THEN 1
#             WHEN 'a' THEN 3
#             ELSE NULL
#        END          AS delete_rule,
#        con.conname  AS fk_name,
#        pkic.relname AS pk_name,
#        CASE
#             WHEN con.condeferrable
#             AND      con.condeferred THEN 5
#             WHEN con.condeferrable THEN 6
#             ELSE 7
#        END AS deferrability
#   FROM     pg_catalog.pg_namespace pkn,
#        pg_catalog.pg_class pkc,
#        pg_catalog.pg_attribute pka,
#        pg_catalog.pg_namespace fkn,
#        pg_catalog.pg_class fkc,
#        pg_catalog.pg_attribute fka,
#        pg_catalog.pg_constraint con,
#        pg_catalog.generate_series(1, 32) pos(n),
#        pg_catalog.pg_depend dep,
#        pg_catalog.pg_class pkic
#   WHERE    pkn.oid = pkc.relnamespace
#   AND      pkc.oid = pka.attrelid
#   AND      pka.attnum = con.confkey[pos.n]
#   AND      con.confrelid = pkc.oid
#   AND      fkn.oid = fkc.relnamespace
#   AND      fkc.oid = fka.attrelid
#   AND      fka.attnum = con.conkey[pos.n]
#   AND      con.conrelid = fkc.oid
#   AND      con.contype = 'f'
#   AND      con.oid = dep.objid
#   AND      pkic.oid = dep.refobjid
#   AND      pkic.relkind = 'i'
#   AND      fkn.nspname = 'public'
#   AND      fkc.relname = 'orders'
#   ORDER BY pkn.nspname,
#        pkc.relname,
#        con.conname,
#        pos.n
# ----
# NULL
# public
# customers
# id
# NULL
# public
# orders
# cust
# 1
# 3
# 3
# orders_cust_fkey
# customers_pkey
# 7


# Tests for filter propagation through joins.

statement ok
CREATE TABLE square (n INT PRIMARY KEY, sq INT)

statement ok
INSERT INTO square VALUES (1,1), (2,4), (3,9), (4,16), (5,25), (6,36)

statement ok
CREATE TABLE pairs (a INT, b INT)

statement ok
INSERT INTO pairs VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,3), (2,4), (2,5), (2,6), (3,4), (3,5), (3,6), (4,5), (4,6)

query IIII nosort
SELECT * FROM pairs, square WHERE pairs.b = square.n
----
1
1
1
1
1
2
2
4
1
3
3
9
1
4
4
16
1
5
5
25
1
6
6
36
2
3
3
9
2
4
4
16
2
5
5
25
2
6
6
36
3
4
4
16
3
5
5
25
3
6
6
36
4
5
5
25
4
6
6
36

query IIII nosort
SELECT * FROM pairs, square WHERE pairs.a + pairs.b = square.sq
----
1
3
2
4
3
6
3
9
4
5
3
9

query IIII nosort
SELECT a, b, n, sq FROM (SELECT a, b, a * b / 2 AS `div`, n, sq FROM pairs, square) sqa WHERE `div` = sq ORDER BY a, b, n, sq
----
1
2
1
1
2
4
2
4
3
6
3
9

# Filter propagation through outer joins.

query IIII nosort
SELECT *  FROM (SELECT * FROM pairs LEFT JOIN square ON b = sq AND a > 1 AND n < 6) sqa WHERE b > 1 AND (n IS NULL OR n > 1) AND (n IS NULL OR a  < sq)
----
1
2
NULL
NULL
1
3
NULL
NULL
1
4
NULL
NULL
1
5
NULL
NULL
1
6
NULL
NULL
2
3
NULL
NULL
2
4
2
4
2
5
NULL
NULL
2
6
NULL
NULL
3
4
2
4
3
5
NULL
NULL
3
6
NULL
NULL
4
5
NULL
NULL
4
6
NULL
NULL

query IIII nosort
SELECT *  FROM (SELECT * FROM pairs RIGHT JOIN square ON b = sq AND a > 1 AND n < 6) sqa WHERE (a IS NULL OR a > 2) AND n > 1 AND (a IS NULL OR a < sq)
----
3
4
2
4
NULL
NULL
3
9
NULL
NULL
4
16
NULL
NULL
5
25
NULL
NULL
6
36


statement ok
CREATE TABLE t1 (col1 INT, x INT, col2 INT, y INT)

statement ok
CREATE TABLE t2 (col3 INT, y INT, x INT, col4 INT)

statement ok
INSERT INTO t1 VALUES (10, 1, 11, 1), (20, 2, 21, 1), (30, 3, 31, 1)

statement ok
INSERT INTO t2 VALUES (100, 1, 1, 101), (200, 1, 201, 2), (400, 1, 401, 4)

query IIIIIII nosort
SELECT * FROM t1 JOIN t2 USING(x)
----
1
10
11
1
100
1
101

query IIIIII nosort
SELECT * FROM t1 NATURAL JOIN t2
----
1
1
10
11
100
101

query IIIIIIII nosort
SELECT * FROM t1 JOIN t2 ON t2.x=t1.x
----
10
1
11
1
100
1
1
101

query III nosort
SELECT t2.x, t1.x, x FROM t1 JOIN t2 USING(x)
----
1
1
1

# Test for #19536.
query I nosort
SELECT x FROM t1 NATURAL JOIN (SELECT * FROM t2) sqa
----
1

# Tests for merge join ordering information.
statement ok
CREATE TABLE pkBA (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a))

statement ok
CREATE TABLE pkBC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,c))

statement ok
CREATE TABLE pkBAC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,c))

statement ok
CREATE TABLE pkBAD (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,d))

# Tests with joins with merged columns of collated string type.
statement ok
CREATE TABLE str1 (a INT PRIMARY KEY, s TEXT COLLATE utf8mb4_0900_ai_ci)

statement ok
INSERT INTO str1 VALUES (1, 'a' COLLATE utf8mb4_0900_ai_ci), (2, 'A' COLLATE utf8mb4_0900_ai_ci), (3, 'c' COLLATE utf8mb4_0900_ai_ci), (4, 'D' COLLATE utf8mb4_0900_ai_ci)

statement ok
CREATE TABLE str2 (a INT PRIMARY KEY, s TEXT COLLATE utf8mb4_0900_ai_ci)

statement ok
INSERT INTO str2 VALUES (1, 'A' COLLATE utf8mb4_0900_ai_ci), (2, 'B' COLLATE utf8mb4_0900_ai_ci), (3, 'C' COLLATE utf8mb4_0900_ai_ci), (4, 'E' COLLATE utf8mb4_0900_ai_ci)

query TTT nosort
SELECT s, str1.s, str2.s FROM str1 INNER JOIN str2 USING(s)
----
A
A
A
a
a
A
c
c
C

query TTT nosort
SELECT s, str1.s, str2.s FROM str1 LEFT OUTER JOIN str2 USING(s)
----
a
a
A
A
A
A
c
c
C
D
D
NULL

query TTT nosort
SELECT s, str1.s, str2.s FROM str1 RIGHT OUTER JOIN str2 USING(s)
----
A
A
A
A
a
A
B
NULL
B
C
c
C
E
NULL
E


statement ok
CREATE TABLE xyu (x INT, y INT, u INT, PRIMARY KEY(x,y,u))

statement ok
INSERT INTO xyu VALUES (0, 0, 0), (1, 1, 1), (3, 1, 31), (3, 2, 32), (4, 4, 44)

statement ok
CREATE TABLE xyv (x INT, y INT, v INT, PRIMARY KEY(x,y,v))

statement ok
INSERT INTO xyv VALUES (1, 1, 1), (2, 2, 2), (3, 1, 31), (3, 3, 33), (5, 5, 55)

query IIII nosort
SELECT * FROM xyu INNER JOIN xyv USING(x, y) WHERE x > 2
----
3
1
31
31

query IIII nosort
SELECT * FROM xyu LEFT OUTER JOIN xyv USING(x, y) WHERE x > 2
----
3
1
31
31
3
2
32
NULL
4
4
44
NULL

query IIII nosort
SELECT x, y, u, v FROM xyu RIGHT OUTER JOIN xyv USING(x, y) WHERE x > 2 ORDER BY x, y, u, v
----
3
1
31
31
3
3
NULL
33
5
5
NULL
55

query IIIIII nosort
SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y WHERE xyu.x = 1 AND xyu.y < 10
----
1
1
1
1
1
1

query IIIIII nosort
SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10
----
1
1
1
1
1
1

query IIIIII nosort
SELECT * FROM xyu LEFT OUTER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10
----
0
0
0
NULL
NULL
NULL
1
1
1
1
1
1
3
1
31
NULL
NULL
NULL
3
2
32
NULL
NULL
NULL
4
4
44
NULL
NULL
NULL

query IIIIII nosort
SELECT xyv.x, xyv.y, xyv.v, xyu.x, xyu.y, xyu.u FROM xyu RIGHT OUTER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 ORDER BY xyv.x, xyv.y, xyv.v, xyu.x, xyu.y, xyu.u
----
1
1
1
1
1
1
2
2
2
NULL
NULL
NULL
3
1
31
NULL
NULL
NULL
3
3
33
NULL
NULL
NULL
5
5
55
NULL
NULL
NULL


# Test OUTER joins that are run in the distSQL merge joiner

query IIII nosort
SELECT * FROM (SELECT * FROM xyu ORDER BY x, y) AS xyu LEFT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv USING(x, y) WHERE x > 2
----
3
1
31
31
3
2
32
NULL
4
4
44
NULL

query IIII nosort
SELECT x, y, v, u FROM (SELECT * FROM xyu ORDER BY x, y) AS xyu RIGHT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv USING(x, y) WHERE x > 2
----
3
1
31
31
3
3
33
NULL
5
5
55
NULL

query IIIIII nosort
SELECT * FROM (SELECT * FROM xyu ORDER BY x, y) AS xyu LEFT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10
----
0
0
0
NULL
NULL
NULL
1
1
1
1
1
1
3
1
31
NULL
NULL
NULL
3
2
32
NULL
NULL
NULL
4
4
44
NULL
NULL
NULL

query IIIIII nosort
SELECT * FROM xyu RIGHT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10
----
1
1
1
1
1
1
NULL
NULL
NULL
2
2
2
NULL
NULL
NULL
3
1
31
NULL
NULL
NULL
3
3
33
NULL
NULL
NULL
5
5
55


# Regression test for #20858.

statement ok
CREATE TABLE l (a INT PRIMARY KEY, b1 INT)

statement ok
CREATE TABLE r (a INT PRIMARY KEY, b2 INT)

statement ok
INSERT INTO l VALUES (1, 1), (2, 1), (3, 1)

statement ok
INSERT INTO r VALUES (2, 1), (3, 1), (4, 1)

query III nosort
SELECT * FROM l LEFT OUTER JOIN r USING(a) WHERE a = 1
----
1
1
NULL

query III nosort
SELECT * FROM l LEFT OUTER JOIN r USING(a) WHERE a = 2
----
2
1
1

query III nosort
SELECT * FROM l RIGHT OUTER JOIN r USING(a) WHERE a = 3
----
3
1
1

query III nosort
SELECT a, b1, b2 FROM l RIGHT OUTER JOIN r USING(a) WHERE a = 4
----
4
NULL
1


# Regression tests for mixed-type equality columns (#22514).
statement ok
CREATE TABLE foo (  a INT,  b INT,  c FLOAT,  d FLOAT)

statement ok
INSERT INTO foo VALUES  (1, 1, 1, 1),  (2, 2, 2, 2),  (3, 3, 3, 3)

statement ok
CREATE TABLE bar (  a INT,  b FLOAT,  c FLOAT,  d INT)

statement ok
INSERT INTO bar VALUES  (1, 1, 1, 1),  (2, 2, 2, 2),  (3, 3, 3, 3)

query IIRR nosort
SELECT * FROM foo NATURAL JOIN bar
----
1
1
1
1
2
2
2
2
3
3
3
3

query IIRRIRI nosort
SELECT * FROM foo JOIN bar USING (b)
----
1
1
1
1
1
1
1
2
2
2
2
2
2
2
3
3
3
3
3
3
3

query IIRRRI nosort
SELECT * FROM foo JOIN bar USING (a, b)
----
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3

query IIRRI nosort
SELECT * FROM foo JOIN bar USING (a, b, c)
----
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3

query IIRRIRRI nosort
SELECT * FROM foo JOIN bar ON foo.b = bar.b
----
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3

query IIRRIRRI nosort
SELECT * FROM foo JOIN bar ON foo.a = bar.a AND foo.b = bar.b
----
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3

query IIRRIRRI nosort
SELECT * FROM foo, bar WHERE foo.b = bar.b
----
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3

query IIRRIRRI nosort
SELECT * FROM foo, bar WHERE foo.a = bar.a AND foo.b = bar.b
----
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3

query IIRRRI nosort
SELECT * FROM foo JOIN bar USING (a, b) WHERE foo.c = bar.c AND foo.d = bar.d
----
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3

# Regression test for 23664.
query III nosort
SELECT * FROM onecolumn AS a RIGHT JOIN twocolumn ON false
----
NULL
44
51
NULL
NULL
52
NULL
42
53
NULL
45
45

# Regression test for #23609: make sure that the type of the merged column
# is int (not unknown).
# Regression test for #28817. Do not allow special functions in ON clause.
statement error
SELECT * FROM foo JOIN bar ON generate_series(0, 1) < 2

statement error
SELECT * FROM foo JOIN bar ON max(foo.c) < 2

# Regression test for #44029 (outer join on two single-row clauses, with two
# results).
# Regression test for #44746 (internal error for particular condition).
statement ok
CREATE TABLE t44746_0(c0 INT)

statement ok
CREATE TABLE t44746_1(c1 INT)

# Note: an "error parsing regexp" would also be acceptable here.
statement ok
SELECT * FROM t44746_0 FULL JOIN t44746_1 ON (SUBSTRING('', ')') = '') = (c1 > 0)

# Regression test for #49630.
statement ok
DROP TABLE `empty`;

statement ok
CREATE TABLE xy (x INT PRIMARY KEY, y INT);

statement ok
CREATE TABLE fk_ref (r INT NOT NULL REFERENCES xy (x));

statement ok
CREATE TABLE `empty` (v INT);

statement ok
INSERT INTO xy (VALUES ROW(1, 1));

statement ok
INSERT INTO fk_ref (VALUES ROW(1));

query IIII nosort
SELECT * FROM fk_ref LEFT JOIN (SELECT * FROM xy INNER JOIN `empty` ON True) sqa ON r = x
----
1
NULL
NULL
NULL

statement ok
DROP TABLE `empty`;

statement ok
DROP TABLE fk_ref;

statement ok
DROP TABLE xy;

statement ok
CREATE TABLE abcd (a INT, b INT, c INT, d INT)

statement ok
INSERT INTO abcd VALUES (1, 1, 1, 1), (2, 2, 2, 2)

statement ok
CREATE TABLE dxby (d INT, x INT, b INT, y INT)

statement ok
INSERT INTO dxby VALUES (2, 2, 2, 2), (3, 3, 3, 3)

# Test that qualified stars expand to all table columns (even those that aren't
# directly visible); see #66123.
query IIIIIIII nosort
SELECT abcd.*, dxby.* FROM abcd INNER JOIN dxby USING (d, b)
----
2
2
2
2
2
2
2
2