1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
# name: test/sql/join/full_outer/test_full_outer_join.test
# description: Test FULL OUTER JOIN
# group: [full_outer]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
statement ok
pragma verify_external
statement ok
CREATE TABLE integers(i INTEGER, j INTEGER)
statement ok
INSERT INTO integers VALUES (1, 1), (3, 3)
statement ok
CREATE TABLE integers2(k INTEGER, l INTEGER)
statement ok
INSERT INTO integers2 VALUES (1, 10), (2, 20)
# equality join
query IIII
SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.i=integers2.k ORDER BY i
----
NULL NULL 2 20
1 1 1 10
3 3 NULL NULL
# equality join with additional non-equality predicate
query IIII
SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.i=integers2.k AND integers.j > integers2.l ORDER BY 1, 2, 3, 4
----
NULL NULL 1 10
NULL NULL 2 20
1 1 NULL NULL
3 3 NULL NULL
# equality join with varchar values
query IIIT
SELECT i, j, k, l FROM integers FULL OUTER JOIN (SELECT k, l::VARCHAR AS l FROM integers2) integers2 ON integers.i=integers2.k ORDER BY 1, 2, 3, 4
----
NULL NULL 2 20
1 1 1 10
3 3 NULL NULL
# empty RHS
query IIII
SELECT i, j, k, l FROM integers FULL OUTER JOIN (SELECT * FROM integers2 WHERE 1=0) integers2 ON integers.i=integers2.k ORDER BY 1, 2, 3, 4
----
1 1 NULL NULL
3 3 NULL NULL
|
