summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/join.out
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
commit46651ce6fe013220ed397add242004d764fc0153 (patch)
tree6e5299f990f88e60174a1d3ae6e48eedd2688b2b /src/test/regress/expected/join.out
parentInitial commit. (diff)
downloadpostgresql-14-46651ce6fe013220ed397add242004d764fc0153.tar.xz
postgresql-14-46651ce6fe013220ed397add242004d764fc0153.zip
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/test/regress/expected/join.out')
-rw-r--r--src/test/regress/expected/join.out6628
1 files changed, 6628 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
new file mode 100644
index 0000000..5c56304
--- /dev/null
+++ b/src/test/regress/expected/join.out
@@ -0,0 +1,6628 @@
+--
+-- JOIN
+-- Test JOIN clauses
+--
+CREATE TABLE J1_TBL (
+ i integer,
+ j integer,
+ t text
+);
+CREATE TABLE J2_TBL (
+ i integer,
+ k integer
+);
+INSERT INTO J1_TBL VALUES (1, 4, 'one');
+INSERT INTO J1_TBL VALUES (2, 3, 'two');
+INSERT INTO J1_TBL VALUES (3, 2, 'three');
+INSERT INTO J1_TBL VALUES (4, 1, 'four');
+INSERT INTO J1_TBL VALUES (5, 0, 'five');
+INSERT INTO J1_TBL VALUES (6, 6, 'six');
+INSERT INTO J1_TBL VALUES (7, 7, 'seven');
+INSERT INTO J1_TBL VALUES (8, 8, 'eight');
+INSERT INTO J1_TBL VALUES (0, NULL, 'zero');
+INSERT INTO J1_TBL VALUES (NULL, NULL, 'null');
+INSERT INTO J1_TBL VALUES (NULL, 0, 'zero');
+INSERT INTO J2_TBL VALUES (1, -1);
+INSERT INTO J2_TBL VALUES (2, 2);
+INSERT INTO J2_TBL VALUES (3, -3);
+INSERT INTO J2_TBL VALUES (2, 4);
+INSERT INTO J2_TBL VALUES (5, -5);
+INSERT INTO J2_TBL VALUES (5, -5);
+INSERT INTO J2_TBL VALUES (0, NULL);
+INSERT INTO J2_TBL VALUES (NULL, NULL);
+INSERT INTO J2_TBL VALUES (NULL, 0);
+-- useful in some tests below
+create temp table onerow();
+insert into onerow default values;
+analyze onerow;
+--
+-- CORRELATION NAMES
+-- Make sure that table/column aliases are supported
+-- before diving into more complex join syntax.
+--
+SELECT *
+ FROM J1_TBL AS tx;
+ i | j | t
+---+---+-------
+ 1 | 4 | one
+ 2 | 3 | two
+ 3 | 2 | three
+ 4 | 1 | four
+ 5 | 0 | five
+ 6 | 6 | six
+ 7 | 7 | seven
+ 8 | 8 | eight
+ 0 | | zero
+ | | null
+ | 0 | zero
+(11 rows)
+
+SELECT *
+ FROM J1_TBL tx;
+ i | j | t
+---+---+-------
+ 1 | 4 | one
+ 2 | 3 | two
+ 3 | 2 | three
+ 4 | 1 | four
+ 5 | 0 | five
+ 6 | 6 | six
+ 7 | 7 | seven
+ 8 | 8 | eight
+ 0 | | zero
+ | | null
+ | 0 | zero
+(11 rows)
+
+SELECT *
+ FROM J1_TBL AS t1 (a, b, c);
+ a | b | c
+---+---+-------
+ 1 | 4 | one
+ 2 | 3 | two
+ 3 | 2 | three
+ 4 | 1 | four
+ 5 | 0 | five
+ 6 | 6 | six
+ 7 | 7 | seven
+ 8 | 8 | eight
+ 0 | | zero
+ | | null
+ | 0 | zero
+(11 rows)
+
+SELECT *
+ FROM J1_TBL t1 (a, b, c);
+ a | b | c
+---+---+-------
+ 1 | 4 | one
+ 2 | 3 | two
+ 3 | 2 | three
+ 4 | 1 | four
+ 5 | 0 | five
+ 6 | 6 | six
+ 7 | 7 | seven
+ 8 | 8 | eight
+ 0 | | zero
+ | | null
+ | 0 | zero
+(11 rows)
+
+SELECT *
+ FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e);
+ a | b | c | d | e
+---+---+-------+---+----
+ 1 | 4 | one | 1 | -1
+ 2 | 3 | two | 1 | -1
+ 3 | 2 | three | 1 | -1
+ 4 | 1 | four | 1 | -1
+ 5 | 0 | five | 1 | -1
+ 6 | 6 | six | 1 | -1
+ 7 | 7 | seven | 1 | -1
+ 8 | 8 | eight | 1 | -1
+ 0 | | zero | 1 | -1
+ | | null | 1 | -1
+ | 0 | zero | 1 | -1
+ 1 | 4 | one | 2 | 2
+ 2 | 3 | two | 2 | 2
+ 3 | 2 | three | 2 | 2
+ 4 | 1 | four | 2 | 2
+ 5 | 0 | five | 2 | 2
+ 6 | 6 | six | 2 | 2
+ 7 | 7 | seven | 2 | 2
+ 8 | 8 | eight | 2 | 2
+ 0 | | zero | 2 | 2
+ | | null | 2 | 2
+ | 0 | zero | 2 | 2
+ 1 | 4 | one | 3 | -3
+ 2 | 3 | two | 3 | -3
+ 3 | 2 | three | 3 | -3
+ 4 | 1 | four | 3 | -3
+ 5 | 0 | five | 3 | -3
+ 6 | 6 | six | 3 | -3
+ 7 | 7 | seven | 3 | -3
+ 8 | 8 | eight | 3 | -3
+ 0 | | zero | 3 | -3
+ | | null | 3 | -3
+ | 0 | zero | 3 | -3
+ 1 | 4 | one | 2 | 4
+ 2 | 3 | two | 2 | 4
+ 3 | 2 | three | 2 | 4
+ 4 | 1 | four | 2 | 4
+ 5 | 0 | five | 2 | 4
+ 6 | 6 | six | 2 | 4
+ 7 | 7 | seven | 2 | 4
+ 8 | 8 | eight | 2 | 4
+ 0 | | zero | 2 | 4
+ | | null | 2 | 4
+ | 0 | zero | 2 | 4
+ 1 | 4 | one | 5 | -5
+ 2 | 3 | two | 5 | -5
+ 3 | 2 | three | 5 | -5
+ 4 | 1 | four | 5 | -5
+ 5 | 0 | five | 5 | -5
+ 6 | 6 | six | 5 | -5
+ 7 | 7 | seven | 5 | -5
+ 8 | 8 | eight | 5 | -5
+ 0 | | zero | 5 | -5
+ | | null | 5 | -5
+ | 0 | zero | 5 | -5
+ 1 | 4 | one | 5 | -5
+ 2 | 3 | two | 5 | -5
+ 3 | 2 | three | 5 | -5
+ 4 | 1 | four | 5 | -5
+ 5 | 0 | five | 5 | -5
+ 6 | 6 | six | 5 | -5
+ 7 | 7 | seven | 5 | -5
+ 8 | 8 | eight | 5 | -5
+ 0 | | zero | 5 | -5
+ | | null | 5 | -5
+ | 0 | zero | 5 | -5
+ 1 | 4 | one | 0 |
+ 2 | 3 | two | 0 |
+ 3 | 2 | three | 0 |
+ 4 | 1 | four | 0 |
+ 5 | 0 | five | 0 |
+ 6 | 6 | six | 0 |
+ 7 | 7 | seven | 0 |
+ 8 | 8 | eight | 0 |
+ 0 | | zero | 0 |
+ | | null | 0 |
+ | 0 | zero | 0 |
+ 1 | 4 | one | |
+ 2 | 3 | two | |
+ 3 | 2 | three | |
+ 4 | 1 | four | |
+ 5 | 0 | five | |
+ 6 | 6 | six | |
+ 7 | 7 | seven | |
+ 8 | 8 | eight | |
+ 0 | | zero | |
+ | | null | |
+ | 0 | zero | |
+ 1 | 4 | one | | 0
+ 2 | 3 | two | | 0
+ 3 | 2 | three | | 0
+ 4 | 1 | four | | 0
+ 5 | 0 | five | | 0
+ 6 | 6 | six | | 0
+ 7 | 7 | seven | | 0
+ 8 | 8 | eight | | 0
+ 0 | | zero | | 0
+ | | null | | 0
+ | 0 | zero | | 0
+(99 rows)
+
+SELECT t1.a, t2.e
+ FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e)
+ WHERE t1.a = t2.d;
+ a | e
+---+----
+ 0 |
+ 1 | -1
+ 2 | 2
+ 2 | 4
+ 3 | -3
+ 5 | -5
+ 5 | -5
+(7 rows)
+
+--
+-- CROSS JOIN
+-- Qualifications are not allowed on cross joins,
+-- which degenerate into a standard unqualified inner join.
+--
+SELECT *
+ FROM J1_TBL CROSS JOIN J2_TBL;
+ i | j | t | i | k
+---+---+-------+---+----
+ 1 | 4 | one | 1 | -1
+ 2 | 3 | two | 1 | -1
+ 3 | 2 | three | 1 | -1
+ 4 | 1 | four | 1 | -1
+ 5 | 0 | five | 1 | -1
+ 6 | 6 | six | 1 | -1
+ 7 | 7 | seven | 1 | -1
+ 8 | 8 | eight | 1 | -1
+ 0 | | zero | 1 | -1
+ | | null | 1 | -1
+ | 0 | zero | 1 | -1
+ 1 | 4 | one | 2 | 2
+ 2 | 3 | two | 2 | 2
+ 3 | 2 | three | 2 | 2
+ 4 | 1 | four | 2 | 2
+ 5 | 0 | five | 2 | 2
+ 6 | 6 | six | 2 | 2
+ 7 | 7 | seven | 2 | 2
+ 8 | 8 | eight | 2 | 2
+ 0 | | zero | 2 | 2
+ | | null | 2 | 2
+ | 0 | zero | 2 | 2
+ 1 | 4 | one | 3 | -3
+ 2 | 3 | two | 3 | -3
+ 3 | 2 | three | 3 | -3
+ 4 | 1 | four | 3 | -3
+ 5 | 0 | five | 3 | -3
+ 6 | 6 | six | 3 | -3
+ 7 | 7 | seven | 3 | -3
+ 8 | 8 | eight | 3 | -3
+ 0 | | zero | 3 | -3
+ | | null | 3 | -3
+ | 0 | zero | 3 | -3
+ 1 | 4 | one | 2 | 4
+ 2 | 3 | two | 2 | 4
+ 3 | 2 | three | 2 | 4
+ 4 | 1 | four | 2 | 4
+ 5 | 0 | five | 2 | 4
+ 6 | 6 | six | 2 | 4
+ 7 | 7 | seven | 2 | 4
+ 8 | 8 | eight | 2 | 4
+ 0 | | zero | 2 | 4
+ | | null | 2 | 4
+ | 0 | zero | 2 | 4
+ 1 | 4 | one | 5 | -5
+ 2 | 3 | two | 5 | -5
+ 3 | 2 | three | 5 | -5
+ 4 | 1 | four | 5 | -5
+ 5 | 0 | five | 5 | -5
+ 6 | 6 | six | 5 | -5
+ 7 | 7 | seven | 5 | -5
+ 8 | 8 | eight | 5 | -5
+ 0 | | zero | 5 | -5
+ | | null | 5 | -5
+ | 0 | zero | 5 | -5
+ 1 | 4 | one | 5 | -5
+ 2 | 3 | two | 5 | -5
+ 3 | 2 | three | 5 | -5
+ 4 | 1 | four | 5 | -5
+ 5 | 0 | five | 5 | -5
+ 6 | 6 | six | 5 | -5
+ 7 | 7 | seven | 5 | -5
+ 8 | 8 | eight | 5 | -5
+ 0 | | zero | 5 | -5
+ | | null | 5 | -5
+ | 0 | zero | 5 | -5
+ 1 | 4 | one | 0 |
+ 2 | 3 | two | 0 |
+ 3 | 2 | three | 0 |
+ 4 | 1 | four | 0 |
+ 5 | 0 | five | 0 |
+ 6 | 6 | six | 0 |
+ 7 | 7 | seven | 0 |
+ 8 | 8 | eight | 0 |
+ 0 | | zero | 0 |
+ | | null | 0 |
+ | 0 | zero | 0 |
+ 1 | 4 | one | |
+ 2 | 3 | two | |
+ 3 | 2 | three | |
+ 4 | 1 | four | |
+ 5 | 0 | five | |
+ 6 | 6 | six | |
+ 7 | 7 | seven | |
+ 8 | 8 | eight | |
+ 0 | | zero | |
+ | | null | |
+ | 0 | zero | |
+ 1 | 4 | one | | 0
+ 2 | 3 | two | | 0
+ 3 | 2 | three | | 0
+ 4 | 1 | four | | 0
+ 5 | 0 | five | | 0
+ 6 | 6 | six | | 0
+ 7 | 7 | seven | | 0
+ 8 | 8 | eight | | 0
+ 0 | | zero | | 0
+ | | null | | 0
+ | 0 | zero | | 0
+(99 rows)
+
+-- ambiguous column
+SELECT i, k, t
+ FROM J1_TBL CROSS JOIN J2_TBL;
+ERROR: column reference "i" is ambiguous
+LINE 1: SELECT i, k, t
+ ^
+-- resolve previous ambiguity by specifying the table name
+SELECT t1.i, k, t
+ FROM J1_TBL t1 CROSS JOIN J2_TBL t2;
+ i | k | t
+---+----+-------
+ 1 | -1 | one
+ 2 | -1 | two
+ 3 | -1 | three
+ 4 | -1 | four
+ 5 | -1 | five
+ 6 | -1 | six
+ 7 | -1 | seven
+ 8 | -1 | eight
+ 0 | -1 | zero
+ | -1 | null
+ | -1 | zero
+ 1 | 2 | one
+ 2 | 2 | two
+ 3 | 2 | three
+ 4 | 2 | four
+ 5 | 2 | five
+ 6 | 2 | six
+ 7 | 2 | seven
+ 8 | 2 | eight
+ 0 | 2 | zero
+ | 2 | null
+ | 2 | zero
+ 1 | -3 | one
+ 2 | -3 | two
+ 3 | -3 | three
+ 4 | -3 | four
+ 5 | -3 | five
+ 6 | -3 | six
+ 7 | -3 | seven
+ 8 | -3 | eight
+ 0 | -3 | zero
+ | -3 | null
+ | -3 | zero
+ 1 | 4 | one
+ 2 | 4 | two
+ 3 | 4 | three
+ 4 | 4 | four
+ 5 | 4 | five
+ 6 | 4 | six
+ 7 | 4 | seven
+ 8 | 4 | eight
+ 0 | 4 | zero
+ | 4 | null
+ | 4 | zero
+ 1 | -5 | one
+ 2 | -5 | two
+ 3 | -5 | three
+ 4 | -5 | four
+ 5 | -5 | five
+ 6 | -5 | six
+ 7 | -5 | seven
+ 8 | -5 | eight
+ 0 | -5 | zero
+ | -5 | null
+ | -5 | zero
+ 1 | -5 | one
+ 2 | -5 | two
+ 3 | -5 | three
+ 4 | -5 | four
+ 5 | -5 | five
+ 6 | -5 | six
+ 7 | -5 | seven
+ 8 | -5 | eight
+ 0 | -5 | zero
+ | -5 | null
+ | -5 | zero
+ 1 | | one
+ 2 | | two
+ 3 | | three
+ 4 | | four
+ 5 | | five
+ 6 | | six
+ 7 | | seven
+ 8 | | eight
+ 0 | | zero
+ | | null
+ | | zero
+ 1 | | one
+ 2 | | two
+ 3 | | three
+ 4 | | four
+ 5 | | five
+ 6 | | six
+ 7 | | seven
+ 8 | | eight
+ 0 | | zero
+ | | null
+ | | zero
+ 1 | 0 | one
+ 2 | 0 | two
+ 3 | 0 | three
+ 4 | 0 | four
+ 5 | 0 | five
+ 6 | 0 | six
+ 7 | 0 | seven
+ 8 | 0 | eight
+ 0 | 0 | zero
+ | 0 | null
+ | 0 | zero
+(99 rows)
+
+SELECT ii, tt, kk
+ FROM (J1_TBL CROSS JOIN J2_TBL)
+ AS tx (ii, jj, tt, ii2, kk);
+ ii | tt | kk
+----+-------+----
+ 1 | one | -1
+ 2 | two | -1
+ 3 | three | -1
+ 4 | four | -1
+ 5 | five | -1
+ 6 | six | -1
+ 7 | seven | -1
+ 8 | eight | -1
+ 0 | zero | -1
+ | null | -1
+ | zero | -1
+ 1 | one | 2
+ 2 | two | 2
+ 3 | three | 2
+ 4 | four | 2
+ 5 | five | 2
+ 6 | six | 2
+ 7 | seven | 2
+ 8 | eight | 2
+ 0 | zero | 2
+ | null | 2
+ | zero | 2
+ 1 | one | -3
+ 2 | two | -3
+ 3 | three | -3
+ 4 | four | -3
+ 5 | five | -3
+ 6 | six | -3
+ 7 | seven | -3
+ 8 | eight | -3
+ 0 | zero | -3
+ | null | -3
+ | zero | -3
+ 1 | one | 4
+ 2 | two | 4
+ 3 | three | 4
+ 4 | four | 4
+ 5 | five | 4
+ 6 | six | 4
+ 7 | seven | 4
+ 8 | eight | 4
+ 0 | zero | 4
+ | null | 4
+ | zero | 4
+ 1 | one | -5
+ 2 | two | -5
+ 3 | three | -5
+ 4 | four | -5
+ 5 | five | -5
+ 6 | six | -5
+ 7 | seven | -5
+ 8 | eight | -5
+ 0 | zero | -5
+ | null | -5
+ | zero | -5
+ 1 | one | -5
+ 2 | two | -5
+ 3 | three | -5
+ 4 | four | -5
+ 5 | five | -5
+ 6 | six | -5
+ 7 | seven | -5
+ 8 | eight | -5
+ 0 | zero | -5
+ | null | -5
+ | zero | -5
+ 1 | one |
+ 2 | two |
+ 3 | three |
+ 4 | four |
+ 5 | five |
+ 6 | six |
+ 7 | seven |
+ 8 | eight |
+ 0 | zero |
+ | null |
+ | zero |
+ 1 | one |
+ 2 | two |
+ 3 | three |
+ 4 | four |
+ 5 | five |
+ 6 | six |
+ 7 | seven |
+ 8 | eight |
+ 0 | zero |
+ | null |
+ | zero |
+ 1 | one | 0
+ 2 | two | 0
+ 3 | three | 0
+ 4 | four | 0
+ 5 | five | 0
+ 6 | six | 0
+ 7 | seven | 0
+ 8 | eight | 0
+ 0 | zero | 0
+ | null | 0
+ | zero | 0
+(99 rows)
+
+SELECT tx.ii, tx.jj, tx.kk
+ FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e))
+ AS tx (ii, jj, tt, ii2, kk);
+ ii | jj | kk
+----+----+----
+ 1 | 4 | -1
+ 2 | 3 | -1
+ 3 | 2 | -1
+ 4 | 1 | -1
+ 5 | 0 | -1
+ 6 | 6 | -1
+ 7 | 7 | -1
+ 8 | 8 | -1
+ 0 | | -1
+ | | -1
+ | 0 | -1
+ 1 | 4 | 2
+ 2 | 3 | 2
+ 3 | 2 | 2
+ 4 | 1 | 2
+ 5 | 0 | 2
+ 6 | 6 | 2
+ 7 | 7 | 2
+ 8 | 8 | 2
+ 0 | | 2
+ | | 2
+ | 0 | 2
+ 1 | 4 | -3
+ 2 | 3 | -3
+ 3 | 2 | -3
+ 4 | 1 | -3
+ 5 | 0 | -3
+ 6 | 6 | -3
+ 7 | 7 | -3
+ 8 | 8 | -3
+ 0 | | -3
+ | | -3
+ | 0 | -3
+ 1 | 4 | 4
+ 2 | 3 | 4
+ 3 | 2 | 4
+ 4 | 1 | 4
+ 5 | 0 | 4
+ 6 | 6 | 4
+ 7 | 7 | 4
+ 8 | 8 | 4
+ 0 | | 4
+ | | 4
+ | 0 | 4
+ 1 | 4 | -5
+ 2 | 3 | -5
+ 3 | 2 | -5
+ 4 | 1 | -5
+ 5 | 0 | -5
+ 6 | 6 | -5
+ 7 | 7 | -5
+ 8 | 8 | -5
+ 0 | | -5
+ | | -5
+ | 0 | -5
+ 1 | 4 | -5
+ 2 | 3 | -5
+ 3 | 2 | -5
+ 4 | 1 | -5
+ 5 | 0 | -5
+ 6 | 6 | -5
+ 7 | 7 | -5
+ 8 | 8 | -5
+ 0 | | -5
+ | | -5
+ | 0 | -5
+ 1 | 4 |
+ 2 | 3 |
+ 3 | 2 |
+ 4 | 1 |
+ 5 | 0 |
+ 6 | 6 |
+ 7 | 7 |
+ 8 | 8 |
+ 0 | |
+ | |
+ | 0 |
+ 1 | 4 |
+ 2 | 3 |
+ 3 | 2 |
+ 4 | 1 |
+ 5 | 0 |
+ 6 | 6 |
+ 7 | 7 |
+ 8 | 8 |
+ 0 | |
+ | |
+ | 0 |
+ 1 | 4 | 0
+ 2 | 3 | 0
+ 3 | 2 | 0
+ 4 | 1 | 0
+ 5 | 0 | 0
+ 6 | 6 | 0
+ 7 | 7 | 0
+ 8 | 8 | 0
+ 0 | | 0
+ | | 0
+ | 0 | 0
+(99 rows)
+
+SELECT *
+ FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b;
+ i | j | t | i | k | i | k
+---+---+-------+---+----+---+----
+ 1 | 4 | one | 1 | -1 | 1 | -1
+ 1 | 4 | one | 1 | -1 | 2 | 2
+ 1 | 4 | one | 1 | -1 | 3 | -3
+ 1 | 4 | one | 1 | -1 | 2 | 4
+ 1 | 4 | one | 1 | -1 | 5 | -5
+ 1 | 4 | one | 1 | -1 | 5 | -5
+ 1 | 4 | one | 1 | -1 | 0 |
+ 1 | 4 | one | 1 | -1 | |
+ 1 | 4 | one | 1 | -1 | | 0
+ 2 | 3 | two | 1 | -1 | 1 | -1
+ 2 | 3 | two | 1 | -1 | 2 | 2
+ 2 | 3 | two | 1 | -1 | 3 | -3
+ 2 | 3 | two | 1 | -1 | 2 | 4
+ 2 | 3 | two | 1 | -1 | 5 | -5
+ 2 | 3 | two | 1 | -1 | 5 | -5
+ 2 | 3 | two | 1 | -1 | 0 |
+ 2 | 3 | two | 1 | -1 | |
+ 2 | 3 | two | 1 | -1 | | 0
+ 3 | 2 | three | 1 | -1 | 1 | -1
+ 3 | 2 | three | 1 | -1 | 2 | 2
+ 3 | 2 | three | 1 | -1 | 3 | -3
+ 3 | 2 | three | 1 | -1 | 2 | 4
+ 3 | 2 | three | 1 | -1 | 5 | -5
+ 3 | 2 | three | 1 | -1 | 5 | -5
+ 3 | 2 | three | 1 | -1 | 0 |
+ 3 | 2 | three | 1 | -1 | |
+ 3 | 2 | three | 1 | -1 | | 0
+ 4 | 1 | four | 1 | -1 | 1 | -1
+ 4 | 1 | four | 1 | -1 | 2 | 2
+ 4 | 1 | four | 1 | -1 | 3 | -3
+ 4 | 1 | four | 1 | -1 | 2 | 4
+ 4 | 1 | four | 1 | -1 | 5 | -5
+ 4 | 1 | four | 1 | -1 | 5 | -5
+ 4 | 1 | four | 1 | -1 | 0 |
+ 4 | 1 | four | 1 | -1 | |
+ 4 | 1 | four | 1 | -1 | | 0
+ 5 | 0 | five | 1 | -1 | 1 | -1
+ 5 | 0 | five | 1 | -1 | 2 | 2
+ 5 | 0 | five | 1 | -1 | 3 | -3
+ 5 | 0 | five | 1 | -1 | 2 | 4
+ 5 | 0 | five | 1 | -1 | 5 | -5
+ 5 | 0 | five | 1 | -1 | 5 | -5
+ 5 | 0 | five | 1 | -1 | 0 |
+ 5 | 0 | five | 1 | -1 | |
+ 5 | 0 | five | 1 | -1 | | 0
+ 6 | 6 | six | 1 | -1 | 1 | -1
+ 6 | 6 | six | 1 | -1 | 2 | 2
+ 6 | 6 | six | 1 | -1 | 3 | -3
+ 6 | 6 | six | 1 | -1 | 2 | 4
+ 6 | 6 | six | 1 | -1 | 5 | -5
+ 6 | 6 | six | 1 | -1 | 5 | -5
+ 6 | 6 | six | 1 | -1 | 0 |
+ 6 | 6 | six | 1 | -1 | |
+ 6 | 6 | six | 1 | -1 | | 0
+ 7 | 7 | seven | 1 | -1 | 1 | -1
+ 7 | 7 | seven | 1 | -1 | 2 | 2
+ 7 | 7 | seven | 1 | -1 | 3 | -3
+ 7 | 7 | seven | 1 | -1 | 2 | 4
+ 7 | 7 | seven | 1 | -1 | 5 | -5
+ 7 | 7 | seven | 1 | -1 | 5 | -5
+ 7 | 7 | seven | 1 | -1 | 0 |
+ 7 | 7 | seven | 1 | -1 | |
+ 7 | 7 | seven | 1 | -1 | | 0
+ 8 | 8 | eight | 1 | -1 | 1 | -1
+ 8 | 8 | eight | 1 | -1 | 2 | 2
+ 8 | 8 | eight | 1 | -1 | 3 | -3
+ 8 | 8 | eight | 1 | -1 | 2 | 4
+ 8 | 8 | eight | 1 | -1 | 5 | -5
+ 8 | 8 | eight | 1 | -1 | 5 | -5
+ 8 | 8 | eight | 1 | -1 | 0 |
+ 8 | 8 | eight | 1 | -1 | |
+ 8 | 8 | eight | 1 | -1 | | 0
+ 0 | | zero | 1 | -1 | 1 | -1
+ 0 | | zero | 1 | -1 | 2 | 2
+ 0 | | zero | 1 | -1 | 3 | -3
+ 0 | | zero | 1 | -1 | 2 | 4
+ 0 | | zero | 1 | -1 | 5 | -5
+ 0 | | zero | 1 | -1 | 5 | -5
+ 0 | | zero | 1 | -1 | 0 |
+ 0 | | zero | 1 | -1 | |
+ 0 | | zero | 1 | -1 | | 0
+ | | null | 1 | -1 | 1 | -1
+ | | null | 1 | -1 | 2 | 2
+ | | null | 1 | -1 | 3 | -3
+ | | null | 1 | -1 | 2 | 4
+ | | null | 1 | -1 | 5 | -5
+ | | null | 1 | -1 | 5 | -5
+ | | null | 1 | -1 | 0 |
+ | | null | 1 | -1 | |
+ | | null | 1 | -1 | | 0
+ | 0 | zero | 1 | -1 | 1 | -1
+ | 0 | zero | 1 | -1 | 2 | 2
+ | 0 | zero | 1 | -1 | 3 | -3
+ | 0 | zero | 1 | -1 | 2 | 4
+ | 0 | zero | 1 | -1 | 5 | -5
+ | 0 | zero | 1 | -1 | 5 | -5
+ | 0 | zero | 1 | -1 | 0 |
+ | 0 | zero | 1 | -1 | |
+ | 0 | zero | 1 | -1 | | 0
+ 1 | 4 | one | 2 | 2 | 1 | -1
+ 1 | 4 | one | 2 | 2 | 2 | 2
+ 1 | 4 | one | 2 | 2 | 3 | -3
+ 1 | 4 | one | 2 | 2 | 2 | 4
+ 1 | 4 | one | 2 | 2 | 5 | -5
+ 1 | 4 | one | 2 | 2 | 5 | -5
+ 1 | 4 | one | 2 | 2 | 0 |
+ 1 | 4 | one | 2 | 2 | |
+ 1 | 4 | one | 2 | 2 | | 0
+ 2 | 3 | two | 2 | 2 | 1 | -1
+ 2 | 3 | two | 2 | 2 | 2 | 2
+ 2 | 3 | two | 2 | 2 | 3 | -3
+ 2 | 3 | two | 2 | 2 | 2 | 4
+ 2 | 3 | two | 2 | 2 | 5 | -5
+ 2 | 3 | two | 2 | 2 | 5 | -5
+ 2 | 3 | two | 2 | 2 | 0 |
+ 2 | 3 | two | 2 | 2 | |
+ 2 | 3 | two | 2 | 2 | | 0
+ 3 | 2 | three | 2 | 2 | 1 | -1
+ 3 | 2 | three | 2 | 2 | 2 | 2
+ 3 | 2 | three | 2 | 2 | 3 | -3
+ 3 | 2 | three | 2 | 2 | 2 | 4
+ 3 | 2 | three | 2 | 2 | 5 | -5
+ 3 | 2 | three | 2 | 2 | 5 | -5
+ 3 | 2 | three | 2 | 2 | 0 |
+ 3 | 2 | three | 2 | 2 | |
+ 3 | 2 | three | 2 | 2 | | 0
+ 4 | 1 | four | 2 | 2 | 1 | -1
+ 4 | 1 | four | 2 | 2 | 2 | 2
+ 4 | 1 | four | 2 | 2 | 3 | -3
+ 4 | 1 | four | 2 | 2 | 2 | 4
+ 4 | 1 | four | 2 | 2 | 5 | -5
+ 4 | 1 | four | 2 | 2 | 5 | -5
+ 4 | 1 | four | 2 | 2 | 0 |
+ 4 | 1 | four | 2 | 2 | |
+ 4 | 1 | four | 2 | 2 | | 0
+ 5 | 0 | five | 2 | 2 | 1 | -1
+ 5 | 0 | five | 2 | 2 | 2 | 2
+ 5 | 0 | five | 2 | 2 | 3 | -3
+ 5 | 0 | five | 2 | 2 | 2 | 4
+ 5 | 0 | five | 2 | 2 | 5 | -5
+ 5 | 0 | five | 2 | 2 | 5 | -5
+ 5 | 0 | five | 2 | 2 | 0 |
+ 5 | 0 | five | 2 | 2 | |
+ 5 | 0 | five | 2 | 2 | | 0
+ 6 | 6 | six | 2 | 2 | 1 | -1
+ 6 | 6 | six | 2 | 2 | 2 | 2
+ 6 | 6 | six | 2 | 2 | 3 | -3
+ 6 | 6 | six | 2 | 2 | 2 | 4
+ 6 | 6 | six | 2 | 2 | 5 | -5
+ 6 | 6 | six | 2 | 2 | 5 | -5
+ 6 | 6 | six | 2 | 2 | 0 |
+ 6 | 6 | six | 2 | 2 | |
+ 6 | 6 | six | 2 | 2 | | 0
+ 7 | 7 | seven | 2 | 2 | 1 | -1
+ 7 | 7 | seven | 2 | 2 | 2 | 2
+ 7 | 7 | seven | 2 | 2 | 3 | -3
+ 7 | 7 | seven | 2 | 2 | 2 | 4
+ 7 | 7 | seven | 2 | 2 | 5 | -5
+ 7 | 7 | seven | 2 | 2 | 5 | -5
+ 7 | 7 | seven | 2 | 2 | 0 |
+ 7 | 7 | seven | 2 | 2 | |
+ 7 | 7 | seven | 2 | 2 | | 0
+ 8 | 8 | eight | 2 | 2 | 1 | -1
+ 8 | 8 | eight | 2 | 2 | 2 | 2
+ 8 | 8 | eight | 2 | 2 | 3 | -3
+ 8 | 8 | eight | 2 | 2 | 2 | 4
+ 8 | 8 | eight | 2 | 2 | 5 | -5
+ 8 | 8 | eight | 2 | 2 | 5 | -5
+ 8 | 8 | eight | 2 | 2 | 0 |
+ 8 | 8 | eight | 2 | 2 | |
+ 8 | 8 | eight | 2 | 2 | | 0
+ 0 | | zero | 2 | 2 | 1 | -1
+ 0 | | zero | 2 | 2 | 2 | 2
+ 0 | | zero | 2 | 2 | 3 | -3
+ 0 | | zero | 2 | 2 | 2 | 4
+ 0 | | zero | 2 | 2 | 5 | -5
+ 0 | | zero | 2 | 2 | 5 | -5
+ 0 | | zero | 2 | 2 | 0 |
+ 0 | | zero | 2 | 2 | |
+ 0 | | zero | 2 | 2 | | 0
+ | | null | 2 | 2 | 1 | -1
+ | | null | 2 | 2 | 2 | 2
+ | | null | 2 | 2 | 3 | -3
+ | | null | 2 | 2 | 2 | 4
+ | | null | 2 | 2 | 5 | -5
+ | | null | 2 | 2 | 5 | -5
+ | | null | 2 | 2 | 0 |
+ | | null | 2 | 2 | |
+ | | null | 2 | 2 | | 0
+ | 0 | zero | 2 | 2 | 1 | -1
+ | 0 | zero | 2 | 2 | 2 | 2
+ | 0 | zero | 2 | 2 | 3 | -3
+ | 0 | zero | 2 | 2 | 2 | 4
+ | 0 | zero | 2 | 2 | 5 | -5
+ | 0 | zero | 2 | 2 | 5 | -5
+ | 0 | zero | 2 | 2 | 0 |
+ | 0 | zero | 2 | 2 | |
+ | 0 | zero | 2 | 2 | | 0
+ 1 | 4 | one | 3 | -3 | 1 | -1
+ 1 | 4 | one | 3 | -3 | 2 | 2
+ 1 | 4 | one | 3 | -3 | 3 | -3
+ 1 | 4 | one | 3 | -3 | 2 | 4
+ 1 | 4 | one | 3 | -3 | 5 | -5
+ 1 | 4 | one | 3 | -3 | 5 | -5
+ 1 | 4 | one | 3 | -3 | 0 |
+ 1 | 4 | one | 3 | -3 | |
+ 1 | 4 | one | 3 | -3 | | 0
+ 2 | 3 | two | 3 | -3 | 1 | -1
+ 2 | 3 | two | 3 | -3 | 2 | 2
+ 2 | 3 | two | 3 | -3 | 3 | -3
+ 2 | 3 | two | 3 | -3 | 2 | 4
+ 2 | 3 | two | 3 | -3 | 5 | -5
+ 2 | 3 | two | 3 | -3 | 5 | -5
+ 2 | 3 | two | 3 | -3 | 0 |
+ 2 | 3 | two | 3 | -3 | |
+ 2 | 3 | two | 3 | -3 | | 0
+ 3 | 2 | three | 3 | -3 | 1 | -1
+ 3 | 2 | three | 3 | -3 | 2 | 2
+ 3 | 2 | three | 3 | -3 | 3 | -3
+ 3 | 2 | three | 3 | -3 | 2 | 4
+ 3 | 2 | three | 3 | -3 | 5 | -5
+ 3 | 2 | three | 3 | -3 | 5 | -5
+ 3 | 2 | three | 3 | -3 | 0 |
+ 3 | 2 | three | 3 | -3 | |
+ 3 | 2 | three | 3 | -3 | | 0
+ 4 | 1 | four | 3 | -3 | 1 | -1
+ 4 | 1 | four | 3 | -3 | 2 | 2
+ 4 | 1 | four | 3 | -3 | 3 | -3
+ 4 | 1 | four | 3 | -3 | 2 | 4
+ 4 | 1 | four | 3 | -3 | 5 | -5
+ 4 | 1 | four | 3 | -3 | 5 | -5
+ 4 | 1 | four | 3 | -3 | 0 |
+ 4 | 1 | four | 3 | -3 | |
+ 4 | 1 | four | 3 | -3 | | 0
+ 5 | 0 | five | 3 | -3 | 1 | -1
+ 5 | 0 | five | 3 | -3 | 2 | 2
+ 5 | 0 | five | 3 | -3 | 3 | -3
+ 5 | 0 | five | 3 | -3 | 2 | 4
+ 5 | 0 | five | 3 | -3 | 5 | -5
+ 5 | 0 | five | 3 | -3 | 5 | -5
+ 5 | 0 | five | 3 | -3 | 0 |
+ 5 | 0 | five | 3 | -3 | |
+ 5 | 0 | five | 3 | -3 | | 0
+ 6 | 6 | six | 3 | -3 | 1 | -1
+ 6 | 6 | six | 3 | -3 | 2 | 2
+ 6 | 6 | six | 3 | -3 | 3 | -3
+ 6 | 6 | six | 3 | -3 | 2 | 4
+ 6 | 6 | six | 3 | -3 | 5 | -5
+ 6 | 6 | six | 3 | -3 | 5 | -5
+ 6 | 6 | six | 3 | -3 | 0 |
+ 6 | 6 | six | 3 | -3 | |
+ 6 | 6 | six | 3 | -3 | | 0
+ 7 | 7 | seven | 3 | -3 | 1 | -1
+ 7 | 7 | seven | 3 | -3 | 2 | 2
+ 7 | 7 | seven | 3 | -3 | 3 | -3
+ 7 | 7 | seven | 3 | -3 | 2 | 4
+ 7 | 7 | seven | 3 | -3 | 5 | -5
+ 7 | 7 | seven | 3 | -3 | 5 | -5
+ 7 | 7 | seven | 3 | -3 | 0 |
+ 7 | 7 | seven | 3 | -3 | |
+ 7 | 7 | seven | 3 | -3 | | 0
+ 8 | 8 | eight | 3 | -3 | 1 | -1
+ 8 | 8 | eight | 3 | -3 | 2 | 2
+ 8 | 8 | eight | 3 | -3 | 3 | -3
+ 8 | 8 | eight | 3 | -3 | 2 | 4
+ 8 | 8 | eight | 3 | -3 | 5 | -5
+ 8 | 8 | eight | 3 | -3 | 5 | -5
+ 8 | 8 | eight | 3 | -3 | 0 |
+ 8 | 8 | eight | 3 | -3 | |
+ 8 | 8 | eight | 3 | -3 | | 0
+ 0 | | zero | 3 | -3 | 1 | -1
+ 0 | | zero | 3 | -3 | 2 | 2
+ 0 | | zero | 3 | -3 | 3 | -3
+ 0 | | zero | 3 | -3 | 2 | 4
+ 0 | | zero | 3 | -3 | 5 | -5
+ 0 | | zero | 3 | -3 | 5 | -5
+ 0 | | zero | 3 | -3 | 0 |
+ 0 | | zero | 3 | -3 | |
+ 0 | | zero | 3 | -3 | | 0
+ | | null | 3 | -3 | 1 | -1
+ | | null | 3 | -3 | 2 | 2
+ | | null | 3 | -3 | 3 | -3
+ | | null | 3 | -3 | 2 | 4
+ | | null | 3 | -3 | 5 | -5
+ | | null | 3 | -3 | 5 | -5
+ | | null | 3 | -3 | 0 |
+ | | null | 3 | -3 | |
+ | | null | 3 | -3 | | 0
+ | 0 | zero | 3 | -3 | 1 | -1
+ | 0 | zero | 3 | -3 | 2 | 2
+ | 0 | zero | 3 | -3 | 3 | -3
+ | 0 | zero | 3 | -3 | 2 | 4
+ | 0 | zero | 3 | -3 | 5 | -5
+ | 0 | zero | 3 | -3 | 5 | -5
+ | 0 | zero | 3 | -3 | 0 |
+ | 0 | zero | 3 | -3 | |
+ | 0 | zero | 3 | -3 | | 0
+ 1 | 4 | one | 2 | 4 | 1 | -1
+ 1 | 4 | one | 2 | 4 | 2 | 2
+ 1 | 4 | one | 2 | 4 | 3 | -3
+ 1 | 4 | one | 2 | 4 | 2 | 4
+ 1 | 4 | one | 2 | 4 | 5 | -5
+ 1 | 4 | one | 2 | 4 | 5 | -5
+ 1 | 4 | one | 2 | 4 | 0 |
+ 1 | 4 | one | 2 | 4 | |
+ 1 | 4 | one | 2 | 4 | | 0
+ 2 | 3 | two | 2 | 4 | 1 | -1
+ 2 | 3 | two | 2 | 4 | 2 | 2
+ 2 | 3 | two | 2 | 4 | 3 | -3
+ 2 | 3 | two | 2 | 4 | 2 | 4
+ 2 | 3 | two | 2 | 4 | 5 | -5
+ 2 | 3 | two | 2 | 4 | 5 | -5
+ 2 | 3 | two | 2 | 4 | 0 |
+ 2 | 3 | two | 2 | 4 | |
+ 2 | 3 | two | 2 | 4 | | 0
+ 3 | 2 | three | 2 | 4 | 1 | -1
+ 3 | 2 | three | 2 | 4 | 2 | 2
+ 3 | 2 | three | 2 | 4 | 3 | -3
+ 3 | 2 | three | 2 | 4 | 2 | 4
+ 3 | 2 | three | 2 | 4 | 5 | -5
+ 3 | 2 | three | 2 | 4 | 5 | -5
+ 3 | 2 | three | 2 | 4 | 0 |
+ 3 | 2 | three | 2 | 4 | |
+ 3 | 2 | three | 2 | 4 | | 0
+ 4 | 1 | four | 2 | 4 | 1 | -1
+ 4 | 1 | four | 2 | 4 | 2 | 2
+ 4 | 1 | four | 2 | 4 | 3 | -3
+ 4 | 1 | four | 2 | 4 | 2 | 4
+ 4 | 1 | four | 2 | 4 | 5 | -5
+ 4 | 1 | four | 2 | 4 | 5 | -5
+ 4 | 1 | four | 2 | 4 | 0 |
+ 4 | 1 | four | 2 | 4 | |
+ 4 | 1 | four | 2 | 4 | | 0
+ 5 | 0 | five | 2 | 4 | 1 | -1
+ 5 | 0 | five | 2 | 4 | 2 | 2
+ 5 | 0 | five | 2 | 4 | 3 | -3
+ 5 | 0 | five | 2 | 4 | 2 | 4
+ 5 | 0 | five | 2 | 4 | 5 | -5
+ 5 | 0 | five | 2 | 4 | 5 | -5
+ 5 | 0 | five | 2 | 4 | 0 |
+ 5 | 0 | five | 2 | 4 | |
+ 5 | 0 | five | 2 | 4 | | 0
+ 6 | 6 | six | 2 | 4 | 1 | -1
+ 6 | 6 | six | 2 | 4 | 2 | 2
+ 6 | 6 | six | 2 | 4 | 3 | -3
+ 6 | 6 | six | 2 | 4 | 2 | 4
+ 6 | 6 | six | 2 | 4 | 5 | -5
+ 6 | 6 | six | 2 | 4 | 5 | -5
+ 6 | 6 | six | 2 | 4 | 0 |
+ 6 | 6 | six | 2 | 4 | |
+ 6 | 6 | six | 2 | 4 | | 0
+ 7 | 7 | seven | 2 | 4 | 1 | -1
+ 7 | 7 | seven | 2 | 4 | 2 | 2
+ 7 | 7 | seven | 2 | 4 | 3 | -3
+ 7 | 7 | seven | 2 | 4 | 2 | 4
+ 7 | 7 | seven | 2 | 4 | 5 | -5
+ 7 | 7 | seven | 2 | 4 | 5 | -5
+ 7 | 7 | seven | 2 | 4 | 0 |
+ 7 | 7 | seven | 2 | 4 | |
+ 7 | 7 | seven | 2 | 4 | | 0
+ 8 | 8 | eight | 2 | 4 | 1 | -1
+ 8 | 8 | eight | 2 | 4 | 2 | 2
+ 8 | 8 | eight | 2 | 4 | 3 | -3
+ 8 | 8 | eight | 2 | 4 | 2 | 4
+ 8 | 8 | eight | 2 | 4 | 5 | -5
+ 8 | 8 | eight | 2 | 4 | 5 | -5
+ 8 | 8 | eight | 2 | 4 | 0 |
+ 8 | 8 | eight | 2 | 4 | |
+ 8 | 8 | eight | 2 | 4 | | 0
+ 0 | | zero | 2 | 4 | 1 | -1
+ 0 | | zero | 2 | 4 | 2 | 2
+ 0 | | zero | 2 | 4 | 3 | -3
+ 0 | | zero | 2 | 4 | 2 | 4
+ 0 | | zero | 2 | 4 | 5 | -5
+ 0 | | zero | 2 | 4 | 5 | -5
+ 0 | | zero | 2 | 4 | 0 |
+ 0 | | zero | 2 | 4 | |
+ 0 | | zero | 2 | 4 | | 0
+ | | null | 2 | 4 | 1 | -1
+ | | null | 2 | 4 | 2 | 2
+ | | null | 2 | 4 | 3 | -3
+ | | null | 2 | 4 | 2 | 4
+ | | null | 2 | 4 | 5 | -5
+ | | null | 2 | 4 | 5 | -5
+ | | null | 2 | 4 | 0 |
+ | | null | 2 | 4 | |
+ | | null | 2 | 4 | | 0
+ | 0 | zero | 2 | 4 | 1 | -1
+ | 0 | zero | 2 | 4 | 2 | 2
+ | 0 | zero | 2 | 4 | 3 | -3
+ | 0 | zero | 2 | 4 | 2 | 4
+ | 0 | zero | 2 | 4 | 5 | -5
+ | 0 | zero | 2 | 4 | 5 | -5
+ | 0 | zero | 2 | 4 | 0 |
+ | 0 | zero | 2 | 4 | |
+ | 0 | zero | 2 | 4 | | 0
+ 1 | 4 | one | 5 | -5 | 1 | -1
+ 1 | 4 | one | 5 | -5 | 2 | 2
+ 1 | 4 | one | 5 | -5 | 3 | -3
+ 1 | 4 | one | 5 | -5 | 2 | 4
+ 1 | 4 | one | 5 | -5 | 5 | -5
+ 1 | 4 | one | 5 | -5 | 5 | -5
+ 1 | 4 | one | 5 | -5 | 0 |
+ 1 | 4 | one | 5 | -5 | |
+ 1 | 4 | one | 5 | -5 | | 0
+ 2 | 3 | two | 5 | -5 | 1 | -1
+ 2 | 3 | two | 5 | -5 | 2 | 2
+ 2 | 3 | two | 5 | -5 | 3 | -3
+ 2 | 3 | two | 5 | -5 | 2 | 4
+ 2 | 3 | two | 5 | -5 | 5 | -5
+ 2 | 3 | two | 5 | -5 | 5 | -5
+ 2 | 3 | two | 5 | -5 | 0 |
+ 2 | 3 | two | 5 | -5 | |
+ 2 | 3 | two | 5 | -5 | | 0
+ 3 | 2 | three | 5 | -5 | 1 | -1
+ 3 | 2 | three | 5 | -5 | 2 | 2
+ 3 | 2 | three | 5 | -5 | 3 | -3
+ 3 | 2 | three | 5 | -5 | 2 | 4
+ 3 | 2 | three | 5 | -5 | 5 | -5
+ 3 | 2 | three | 5 | -5 | 5 | -5
+ 3 | 2 | three | 5 | -5 | 0 |
+ 3 | 2 | three | 5 | -5 | |
+ 3 | 2 | three | 5 | -5 | | 0
+ 4 | 1 | four | 5 | -5 | 1 | -1
+ 4 | 1 | four | 5 | -5 | 2 | 2
+ 4 | 1 | four | 5 | -5 | 3 | -3
+ 4 | 1 | four | 5 | -5 | 2 | 4
+ 4 | 1 | four | 5 | -5 | 5 | -5
+ 4 | 1 | four | 5 | -5 | 5 | -5
+ 4 | 1 | four | 5 | -5 | 0 |
+ 4 | 1 | four | 5 | -5 | |
+ 4 | 1 | four | 5 | -5 | | 0
+ 5 | 0 | five | 5 | -5 | 1 | -1
+ 5 | 0 | five | 5 | -5 | 2 | 2
+ 5 | 0 | five | 5 | -5 | 3 | -3
+ 5 | 0 | five | 5 | -5 | 2 | 4
+ 5 | 0 | five | 5 | -5 | 5 | -5
+ 5 | 0 | five | 5 | -5 | 5 | -5
+ 5 | 0 | five | 5 | -5 | 0 |
+ 5 | 0 | five | 5 | -5 | |
+ 5 | 0 | five | 5 | -5 | | 0
+ 6 | 6 | six | 5 | -5 | 1 | -1
+ 6 | 6 | six | 5 | -5 | 2 | 2
+ 6 | 6 | six | 5 | -5 | 3 | -3
+ 6 | 6 | six | 5 | -5 | 2 | 4
+ 6 | 6 | six | 5 | -5 | 5 | -5
+ 6 | 6 | six | 5 | -5 | 5 | -5
+ 6 | 6 | six | 5 | -5 | 0 |
+ 6 | 6 | six | 5 | -5 | |
+ 6 | 6 | six | 5 | -5 | | 0
+ 7 | 7 | seven | 5 | -5 | 1 | -1
+ 7 | 7 | seven | 5 | -5 | 2 | 2
+ 7 | 7 | seven | 5 | -5 | 3 | -3
+ 7 | 7 | seven | 5 | -5 | 2 | 4
+ 7 | 7 | seven | 5 | -5 | 5 | -5
+ 7 | 7 | seven | 5 | -5 | 5 | -5
+ 7 | 7 | seven | 5 | -5 | 0 |
+ 7 | 7 | seven | 5 | -5 | |
+ 7 | 7 | seven | 5 | -5 | | 0
+ 8 | 8 | eight | 5 | -5 | 1 | -1
+ 8 | 8 | eight | 5 | -5 | 2 | 2
+ 8 | 8 | eight | 5 | -5 | 3 | -3
+ 8 | 8 | eight | 5 | -5 | 2 | 4
+ 8 | 8 | eight | 5 | -5 | 5 | -5
+ 8 | 8 | eight | 5 | -5 | 5 | -5
+ 8 | 8 | eight | 5 | -5 | 0 |
+ 8 | 8 | eight | 5 | -5 | |
+ 8 | 8 | eight | 5 | -5 | | 0
+ 0 | | zero | 5 | -5 | 1 | -1
+ 0 | | zero | 5 | -5 | 2 | 2
+ 0 | | zero | 5 | -5 | 3 | -3
+ 0 | | zero | 5 | -5 | 2 | 4
+ 0 | | zero | 5 | -5 | 5 | -5
+ 0 | | zero | 5 | -5 | 5 | -5
+ 0 | | zero | 5 | -5 | 0 |
+ 0 | | zero | 5 | -5 | |
+ 0 | | zero | 5 | -5 | | 0
+ | | null | 5 | -5 | 1 | -1
+ | | null | 5 | -5 | 2 | 2
+ | | null | 5 | -5 | 3 | -3
+ | | null | 5 | -5 | 2 | 4
+ | | null | 5 | -5 | 5 | -5
+ | | null | 5 | -5 | 5 | -5
+ | | null | 5 | -5 | 0 |
+ | | null | 5 | -5 | |
+ | | null | 5 | -5 | | 0
+ | 0 | zero | 5 | -5 | 1 | -1
+ | 0 | zero | 5 | -5 | 2 | 2
+ | 0 | zero | 5 | -5 | 3 | -3
+ | 0 | zero | 5 | -5 | 2 | 4
+ | 0 | zero | 5 | -5 | 5 | -5
+ | 0 | zero | 5 | -5 | 5 | -5
+ | 0 | zero | 5 | -5 | 0 |
+ | 0 | zero | 5 | -5 | |
+ | 0 | zero | 5 | -5 | | 0
+ 1 | 4 | one | 5 | -5 | 1 | -1
+ 1 | 4 | one | 5 | -5 | 2 | 2
+ 1 | 4 | one | 5 | -5 | 3 | -3
+ 1 | 4 | one | 5 | -5 | 2 | 4
+ 1 | 4 | one | 5 | -5 | 5 | -5
+ 1 | 4 | one | 5 | -5 | 5 | -5
+ 1 | 4 | one | 5 | -5 | 0 |
+ 1 | 4 | one | 5 | -5 | |
+ 1 | 4 | one | 5 | -5 | | 0
+ 2 | 3 | two | 5 | -5 | 1 | -1
+ 2 | 3 | two | 5 | -5 | 2 | 2
+ 2 | 3 | two | 5 | -5 | 3 | -3
+ 2 | 3 | two | 5 | -5 | 2 | 4
+ 2 | 3 | two | 5 | -5 | 5 | -5
+ 2 | 3 | two | 5 | -5 | 5 | -5
+ 2 | 3 | two | 5 | -5 | 0 |
+ 2 | 3 | two | 5 | -5 | |
+ 2 | 3 | two | 5 | -5 | | 0
+ 3 | 2 | three | 5 | -5 | 1 | -1
+ 3 | 2 | three | 5 | -5 | 2 | 2
+ 3 | 2 | three | 5 | -5 | 3 | -3
+ 3 | 2 | three | 5 | -5 | 2 | 4
+ 3 | 2 | three | 5 | -5 | 5 | -5
+ 3 | 2 | three | 5 | -5 | 5 | -5
+ 3 | 2 | three | 5 | -5 | 0 |
+ 3 | 2 | three | 5 | -5 | |
+ 3 | 2 | three | 5 | -5 | | 0
+ 4 | 1 | four | 5 | -5 | 1 | -1
+ 4 | 1 | four | 5 | -5 | 2 | 2
+ 4 | 1 | four | 5 | -5 | 3 | -3
+ 4 | 1 | four | 5 | -5 | 2 | 4
+ 4 | 1 | four | 5 | -5 | 5 | -5
+ 4 | 1 | four | 5 | -5 | 5 | -5
+ 4 | 1 | four | 5 | -5 | 0 |
+ 4 | 1 | four | 5 | -5 | |
+ 4 | 1 | four | 5 | -5 | | 0
+ 5 | 0 | five | 5 | -5 | 1 | -1
+ 5 | 0 | five | 5 | -5 | 2 | 2
+ 5 | 0 | five | 5 | -5 | 3 | -3
+ 5 | 0 | five | 5 | -5 | 2 | 4
+ 5 | 0 | five | 5 | -5 | 5 | -5
+ 5 | 0 | five | 5 | -5 | 5 | -5
+ 5 | 0 | five | 5 | -5 | 0 |
+ 5 | 0 | five | 5 | -5 | |
+ 5 | 0 | five | 5 | -5 | | 0
+ 6 | 6 | six | 5 | -5 | 1 | -1
+ 6 | 6 | six | 5 | -5 | 2 | 2
+ 6 | 6 | six | 5 | -5 | 3 | -3
+ 6 | 6 | six | 5 | -5 | 2 | 4
+ 6 | 6 | six | 5 | -5 | 5 | -5
+ 6 | 6 | six | 5 | -5 | 5 | -5
+ 6 | 6 | six | 5 | -5 | 0 |
+ 6 | 6 | six | 5 | -5 | |
+ 6 | 6 | six | 5 | -5 | | 0
+ 7 | 7 | seven | 5 | -5 | 1 | -1
+ 7 | 7 | seven | 5 | -5 | 2 | 2
+ 7 | 7 | seven | 5 | -5 | 3 | -3
+ 7 | 7 | seven | 5 | -5 | 2 | 4
+ 7 | 7 | seven | 5 | -5 | 5 | -5
+ 7 | 7 | seven | 5 | -5 | 5 | -5
+ 7 | 7 | seven | 5 | -5 | 0 |
+ 7 | 7 | seven | 5 | -5 | |
+ 7 | 7 | seven | 5 | -5 | | 0
+ 8 | 8 | eight | 5 | -5 | 1 | -1
+ 8 | 8 | eight | 5 | -5 | 2 | 2
+ 8 | 8 | eight | 5 | -5 | 3 | -3
+ 8 | 8 | eight | 5 | -5 | 2 | 4
+ 8 | 8 | eight | 5 | -5 | 5 | -5
+ 8 | 8 | eight | 5 | -5 | 5 | -5
+ 8 | 8 | eight | 5 | -5 | 0 |
+ 8 | 8 | eight | 5 | -5 | |
+ 8 | 8 | eight | 5 | -5 | | 0
+ 0 | | zero | 5 | -5 | 1 | -1
+ 0 | | zero | 5 | -5 | 2 | 2
+ 0 | | zero | 5 | -5 | 3 | -3
+ 0 | | zero | 5 | -5 | 2 | 4
+ 0 | | zero | 5 | -5 | 5 | -5
+ 0 | | zero | 5 | -5 | 5 | -5
+ 0 | | zero | 5 | -5 | 0 |
+ 0 | | zero | 5 | -5 | |
+ 0 | | zero | 5 | -5 | | 0
+ | | null | 5 | -5 | 1 | -1
+ | | null | 5 | -5 | 2 | 2
+ | | null | 5 | -5 | 3 | -3
+ | | null | 5 | -5 | 2 | 4
+ | | null | 5 | -5 | 5 | -5
+ | | null | 5 | -5 | 5 | -5
+ | | null | 5 | -5 | 0 |
+ | | null | 5 | -5 | |
+ | | null | 5 | -5 | | 0
+ | 0 | zero | 5 | -5 | 1 | -1
+ | 0 | zero | 5 | -5 | 2 | 2
+ | 0 | zero | 5 | -5 | 3 | -3
+ | 0 | zero | 5 | -5 | 2 | 4
+ | 0 | zero | 5 | -5 | 5 | -5
+ | 0 | zero | 5 | -5 | 5 | -5
+ | 0 | zero | 5 | -5 | 0 |
+ | 0 | zero | 5 | -5 | |
+ | 0 | zero | 5 | -5 | | 0
+ 1 | 4 | one | 0 | | 1 | -1
+ 1 | 4 | one | 0 | | 2 | 2
+ 1 | 4 | one | 0 | | 3 | -3
+ 1 | 4 | one | 0 | | 2 | 4
+ 1 | 4 | one | 0 | | 5 | -5
+ 1 | 4 | one | 0 | | 5 | -5
+ 1 | 4 | one | 0 | | 0 |
+ 1 | 4 | one | 0 | | |
+ 1 | 4 | one | 0 | | | 0
+ 2 | 3 | two | 0 | | 1 | -1
+ 2 | 3 | two | 0 | | 2 | 2
+ 2 | 3 | two | 0 | | 3 | -3
+ 2 | 3 | two | 0 | | 2 | 4
+ 2 | 3 | two | 0 | | 5 | -5
+ 2 | 3 | two | 0 | | 5 | -5
+ 2 | 3 | two | 0 | | 0 |
+ 2 | 3 | two | 0 | | |
+ 2 | 3 | two | 0 | | | 0
+ 3 | 2 | three | 0 | | 1 | -1
+ 3 | 2 | three | 0 | | 2 | 2
+ 3 | 2 | three | 0 | | 3 | -3
+ 3 | 2 | three | 0 | | 2 | 4
+ 3 | 2 | three | 0 | | 5 | -5
+ 3 | 2 | three | 0 | | 5 | -5
+ 3 | 2 | three | 0 | | 0 |
+ 3 | 2 | three | 0 | | |
+ 3 | 2 | three | 0 | | | 0
+ 4 | 1 | four | 0 | | 1 | -1
+ 4 | 1 | four | 0 | | 2 | 2
+ 4 | 1 | four | 0 | | 3 | -3
+ 4 | 1 | four | 0 | | 2 | 4
+ 4 | 1 | four | 0 | | 5 | -5
+ 4 | 1 | four | 0 | | 5 | -5
+ 4 | 1 | four | 0 | | 0 |
+ 4 | 1 | four | 0 | | |
+ 4 | 1 | four | 0 | | | 0
+ 5 | 0 | five | 0 | | 1 | -1
+ 5 | 0 | five | 0 | | 2 | 2
+ 5 | 0 | five | 0 | | 3 | -3
+ 5 | 0 | five | 0 | | 2 | 4
+ 5 | 0 | five | 0 | | 5 | -5
+ 5 | 0 | five | 0 | | 5 | -5
+ 5 | 0 | five | 0 | | 0 |
+ 5 | 0 | five | 0 | | |
+ 5 | 0 | five | 0 | | | 0
+ 6 | 6 | six | 0 | | 1 | -1
+ 6 | 6 | six | 0 | | 2 | 2
+ 6 | 6 | six | 0 | | 3 | -3
+ 6 | 6 | six | 0 | | 2 | 4
+ 6 | 6 | six | 0 | | 5 | -5
+ 6 | 6 | six | 0 | | 5 | -5
+ 6 | 6 | six | 0 | | 0 |
+ 6 | 6 | six | 0 | | |
+ 6 | 6 | six | 0 | | | 0
+ 7 | 7 | seven | 0 | | 1 | -1
+ 7 | 7 | seven | 0 | | 2 | 2
+ 7 | 7 | seven | 0 | | 3 | -3
+ 7 | 7 | seven | 0 | | 2 | 4
+ 7 | 7 | seven | 0 | | 5 | -5
+ 7 | 7 | seven | 0 | | 5 | -5
+ 7 | 7 | seven | 0 | | 0 |
+ 7 | 7 | seven | 0 | | |
+ 7 | 7 | seven | 0 | | | 0
+ 8 | 8 | eight | 0 | | 1 | -1
+ 8 | 8 | eight | 0 | | 2 | 2
+ 8 | 8 | eight | 0 | | 3 | -3
+ 8 | 8 | eight | 0 | | 2 | 4
+ 8 | 8 | eight | 0 | | 5 | -5
+ 8 | 8 | eight | 0 | | 5 | -5
+ 8 | 8 | eight | 0 | | 0 |
+ 8 | 8 | eight | 0 | | |
+ 8 | 8 | eight | 0 | | | 0
+ 0 | | zero | 0 | | 1 | -1
+ 0 | | zero | 0 | | 2 | 2
+ 0 | | zero | 0 | | 3 | -3
+ 0 | | zero | 0 | | 2 | 4
+ 0 | | zero | 0 | | 5 | -5
+ 0 | | zero | 0 | | 5 | -5
+ 0 | | zero | 0 | | 0 |
+ 0 | | zero | 0 | | |
+ 0 | | zero | 0 | | | 0
+ | | null | 0 | | 1 | -1
+ | | null | 0 | | 2 | 2
+ | | null | 0 | | 3 | -3
+ | | null | 0 | | 2 | 4
+ | | null | 0 | | 5 | -5
+ | | null | 0 | | 5 | -5
+ | | null | 0 | | 0 |
+ | | null | 0 | | |
+ | | null | 0 | | | 0
+ | 0 | zero | 0 | | 1 | -1
+ | 0 | zero | 0 | | 2 | 2
+ | 0 | zero | 0 | | 3 | -3
+ | 0 | zero | 0 | | 2 | 4
+ | 0 | zero | 0 | | 5 | -5
+ | 0 | zero | 0 | | 5 | -5
+ | 0 | zero | 0 | | 0 |
+ | 0 | zero | 0 | | |
+ | 0 | zero | 0 | | | 0
+ 1 | 4 | one | | | 1 | -1
+ 1 | 4 | one | | | 2 | 2
+ 1 | 4 | one | | | 3 | -3
+ 1 | 4 | one | | | 2 | 4
+ 1 | 4 | one | | | 5 | -5
+ 1 | 4 | one | | | 5 | -5
+ 1 | 4 | one | | | 0 |
+ 1 | 4 | one | | | |
+ 1 | 4 | one | | | | 0
+ 2 | 3 | two | | | 1 | -1
+ 2 | 3 | two | | | 2 | 2
+ 2 | 3 | two | | | 3 | -3
+ 2 | 3 | two | | | 2 | 4
+ 2 | 3 | two | | | 5 | -5
+ 2 | 3 | two | | | 5 | -5
+ 2 | 3 | two | | | 0 |
+ 2 | 3 | two | | | |
+ 2 | 3 | two | | | | 0
+ 3 | 2 | three | | | 1 | -1
+ 3 | 2 | three | | | 2 | 2
+ 3 | 2 | three | | | 3 | -3
+ 3 | 2 | three | | | 2 | 4
+ 3 | 2 | three | | | 5 | -5
+ 3 | 2 | three | | | 5 | -5
+ 3 | 2 | three | | | 0 |
+ 3 | 2 | three | | | |
+ 3 | 2 | three | | | | 0
+ 4 | 1 | four | | | 1 | -1
+ 4 | 1 | four | | | 2 | 2
+ 4 | 1 | four | | | 3 | -3
+ 4 | 1 | four | | | 2 | 4
+ 4 | 1 | four | | | 5 | -5
+ 4 | 1 | four | | | 5 | -5
+ 4 | 1 | four | | | 0 |
+ 4 | 1 | four | | | |
+ 4 | 1 | four | | | | 0
+ 5 | 0 | five | | | 1 | -1
+ 5 | 0 | five | | | 2 | 2
+ 5 | 0 | five | | | 3 | -3
+ 5 | 0 | five | | | 2 | 4
+ 5 | 0 | five | | | 5 | -5
+ 5 | 0 | five | | | 5 | -5
+ 5 | 0 | five | | | 0 |
+ 5 | 0 | five | | | |
+ 5 | 0 | five | | | | 0
+ 6 | 6 | six | | | 1 | -1
+ 6 | 6 | six | | | 2 | 2
+ 6 | 6 | six | | | 3 | -3
+ 6 | 6 | six | | | 2 | 4
+ 6 | 6 | six | | | 5 | -5
+ 6 | 6 | six | | | 5 | -5
+ 6 | 6 | six | | | 0 |
+ 6 | 6 | six | | | |
+ 6 | 6 | six | | | | 0
+ 7 | 7 | seven | | | 1 | -1
+ 7 | 7 | seven | | | 2 | 2
+ 7 | 7 | seven | | | 3 | -3
+ 7 | 7 | seven | | | 2 | 4
+ 7 | 7 | seven | | | 5 | -5
+ 7 | 7 | seven | | | 5 | -5
+ 7 | 7 | seven | | | 0 |
+ 7 | 7 | seven | | | |
+ 7 | 7 | seven | | | | 0
+ 8 | 8 | eight | | | 1 | -1
+ 8 | 8 | eight | | | 2 | 2
+ 8 | 8 | eight | | | 3 | -3
+ 8 | 8 | eight | | | 2 | 4
+ 8 | 8 | eight | | | 5 | -5
+ 8 | 8 | eight | | | 5 | -5
+ 8 | 8 | eight | | | 0 |
+ 8 | 8 | eight | | | |
+ 8 | 8 | eight | | | | 0
+ 0 | | zero | | | 1 | -1
+ 0 | | zero | | | 2 | 2
+ 0 | | zero | | | 3 | -3
+ 0 | | zero | | | 2 | 4
+ 0 | | zero | | | 5 | -5
+ 0 | | zero | | | 5 | -5
+ 0 | | zero | | | 0 |
+ 0 | | zero | | | |
+ 0 | | zero | | | | 0
+ | | null | | | 1 | -1
+ | | null | | | 2 | 2
+ | | null | | | 3 | -3
+ | | null | | | 2 | 4
+ | | null | | | 5 | -5
+ | | null | | | 5 | -5
+ | | null | | | 0 |
+ | | null | | | |
+ | | null | | | | 0
+ | 0 | zero | | | 1 | -1
+ | 0 | zero | | | 2 | 2
+ | 0 | zero | | | 3 | -3
+ | 0 | zero | | | 2 | 4
+ | 0 | zero | | | 5 | -5
+ | 0 | zero | | | 5 | -5
+ | 0 | zero | | | 0 |
+ | 0 | zero | | | |
+ | 0 | zero | | | | 0
+ 1 | 4 | one | | 0 | 1 | -1
+ 1 | 4 | one | | 0 | 2 | 2
+ 1 | 4 | one | | 0 | 3 | -3
+ 1 | 4 | one | | 0 | 2 | 4
+ 1 | 4 | one | | 0 | 5 | -5
+ 1 | 4 | one | | 0 | 5 | -5
+ 1 | 4 | one | | 0 | 0 |
+ 1 | 4 | one | | 0 | |
+ 1 | 4 | one | | 0 | | 0
+ 2 | 3 | two | | 0 | 1 | -1
+ 2 | 3 | two | | 0 | 2 | 2
+ 2 | 3 | two | | 0 | 3 | -3
+ 2 | 3 | two | | 0 | 2 | 4
+ 2 | 3 | two | | 0 | 5 | -5
+ 2 | 3 | two | | 0 | 5 | -5
+ 2 | 3 | two | | 0 | 0 |
+ 2 | 3 | two | | 0 | |
+ 2 | 3 | two | | 0 | | 0
+ 3 | 2 | three | | 0 | 1 | -1
+ 3 | 2 | three | | 0 | 2 | 2
+ 3 | 2 | three | | 0 | 3 | -3
+ 3 | 2 | three | | 0 | 2 | 4
+ 3 | 2 | three | | 0 | 5 | -5
+ 3 | 2 | three | | 0 | 5 | -5
+ 3 | 2 | three | | 0 | 0 |
+ 3 | 2 | three | | 0 | |
+ 3 | 2 | three | | 0 | | 0
+ 4 | 1 | four | | 0 | 1 | -1
+ 4 | 1 | four | | 0 | 2 | 2
+ 4 | 1 | four | | 0 | 3 | -3
+ 4 | 1 | four | | 0 | 2 | 4
+ 4 | 1 | four | | 0 | 5 | -5
+ 4 | 1 | four | | 0 | 5 | -5
+ 4 | 1 | four | | 0 | 0 |
+ 4 | 1 | four | | 0 | |
+ 4 | 1 | four | | 0 | | 0
+ 5 | 0 | five | | 0 | 1 | -1
+ 5 | 0 | five | | 0 | 2 | 2
+ 5 | 0 | five | | 0 | 3 | -3
+ 5 | 0 | five | | 0 | 2 | 4
+ 5 | 0 | five | | 0 | 5 | -5
+ 5 | 0 | five | | 0 | 5 | -5
+ 5 | 0 | five | | 0 | 0 |
+ 5 | 0 | five | | 0 | |
+ 5 | 0 | five | | 0 | | 0
+ 6 | 6 | six | | 0 | 1 | -1
+ 6 | 6 | six | | 0 | 2 | 2
+ 6 | 6 | six | | 0 | 3 | -3
+ 6 | 6 | six | | 0 | 2 | 4
+ 6 | 6 | six | | 0 | 5 | -5
+ 6 | 6 | six | | 0 | 5 | -5
+ 6 | 6 | six | | 0 | 0 |
+ 6 | 6 | six | | 0 | |
+ 6 | 6 | six | | 0 | | 0
+ 7 | 7 | seven | | 0 | 1 | -1
+ 7 | 7 | seven | | 0 | 2 | 2
+ 7 | 7 | seven | | 0 | 3 | -3
+ 7 | 7 | seven | | 0 | 2 | 4
+ 7 | 7 | seven | | 0 | 5 | -5
+ 7 | 7 | seven | | 0 | 5 | -5
+ 7 | 7 | seven | | 0 | 0 |
+ 7 | 7 | seven | | 0 | |
+ 7 | 7 | seven | | 0 | | 0
+ 8 | 8 | eight | | 0 | 1 | -1
+ 8 | 8 | eight | | 0 | 2 | 2
+ 8 | 8 | eight | | 0 | 3 | -3
+ 8 | 8 | eight | | 0 | 2 | 4
+ 8 | 8 | eight | | 0 | 5 | -5
+ 8 | 8 | eight | | 0 | 5 | -5
+ 8 | 8 | eight | | 0 | 0 |
+ 8 | 8 | eight | | 0 | |
+ 8 | 8 | eight | | 0 | | 0
+ 0 | | zero | | 0 | 1 | -1
+ 0 | | zero | | 0 | 2 | 2
+ 0 | | zero | | 0 | 3 | -3
+ 0 | | zero | | 0 | 2 | 4
+ 0 | | zero | | 0 | 5 | -5
+ 0 | | zero | | 0 | 5 | -5
+ 0 | | zero | | 0 | 0 |
+ 0 | | zero | | 0 | |
+ 0 | | zero | | 0 | | 0
+ | | null | | 0 | 1 | -1
+ | | null | | 0 | 2 | 2
+ | | null | | 0 | 3 | -3
+ | | null | | 0 | 2 | 4
+ | | null | | 0 | 5 | -5
+ | | null | | 0 | 5 | -5
+ | | null | | 0 | 0 |
+ | | null | | 0 | |
+ | | null | | 0 | | 0
+ | 0 | zero | | 0 | 1 | -1
+ | 0 | zero | | 0 | 2 | 2
+ | 0 | zero | | 0 | 3 | -3
+ | 0 | zero | | 0 | 2 | 4
+ | 0 | zero | | 0 | 5 | -5
+ | 0 | zero | | 0 | 5 | -5
+ | 0 | zero | | 0 | 0 |
+ | 0 | zero | | 0 | |
+ | 0 | zero | | 0 | | 0
+(891 rows)
+
+--
+--
+-- Inner joins (equi-joins)
+--
+--
+--
+-- Inner joins (equi-joins) with USING clause
+-- The USING syntax changes the shape of the resulting table
+-- by including a column in the USING clause only once in the result.
+--
+-- Inner equi-join on specified column
+SELECT *
+ FROM J1_TBL INNER JOIN J2_TBL USING (i);
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+(7 rows)
+
+-- Same as above, slightly different syntax
+SELECT *
+ FROM J1_TBL JOIN J2_TBL USING (i);
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+(7 rows)
+
+SELECT *
+ FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a)
+ ORDER BY a, d;
+ a | b | c | d
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+(7 rows)
+
+SELECT *
+ FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
+ ORDER BY b, t1.a;
+ b | a | c | a
+---+---+-------+---
+ 0 | 5 | five |
+ 0 | | zero |
+ 2 | 3 | three | 2
+ 4 | 1 | one | 2
+(4 rows)
+
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one'; -- ok
+ i | j | t | k
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one'; -- ok
+ i | j | t | k
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one'; -- error
+ERROR: invalid reference to FROM-clause entry for table "j1_tbl"
+LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
+ ^
+HINT: There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1; -- ok
+ i | j | t | k
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one'; -- error
+ERROR: column x.t does not exist
+LINE 1: ...CT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one...
+ ^
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i) AS x) AS xx WHERE x.i = 1; -- error (XXX could use better hint)
+ERROR: missing FROM-clause entry for table "x"
+LINE 1: ...ROM (J1_TBL JOIN J2_TBL USING (i) AS x) AS xx WHERE x.i = 1;
+ ^
+SELECT * FROM J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1; -- error
+ERROR: table name "a1" specified more than once
+SELECT x.* FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ i
+---
+ 1
+(1 row)
+
+SELECT ROW(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ row
+-----
+ (1)
+(1 row)
+
+SELECT row_to_json(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ row_to_json
+-------------
+ {"i":1}
+(1 row)
+
+--
+-- NATURAL JOIN
+-- Inner equi-join on all columns with the same name
+--
+SELECT *
+ FROM J1_TBL NATURAL JOIN J2_TBL;
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+(7 rows)
+
+SELECT *
+ FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
+ a | b | c | d
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+(7 rows)
+
+SELECT *
+ FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
+ a | b | c | d
+---+---+------+---
+ 0 | | zero |
+ 2 | 3 | two | 2
+ 4 | 1 | four | 2
+(3 rows)
+
+-- mismatch number of columns
+-- currently, Postgres will fill in with underlying names
+SELECT *
+ FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
+ a | b | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+(7 rows)
+
+--
+-- Inner joins (equi-joins)
+--
+SELECT *
+ FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
+ i | j | t | i | k
+---+---+-------+---+----
+ 0 | | zero | 0 |
+ 1 | 4 | one | 1 | -1
+ 2 | 3 | two | 2 | 2
+ 2 | 3 | two | 2 | 4
+ 3 | 2 | three | 3 | -3
+ 5 | 0 | five | 5 | -5
+ 5 | 0 | five | 5 | -5
+(7 rows)
+
+SELECT *
+ FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
+ i | j | t | i | k
+---+---+------+---+---
+ 0 | | zero | | 0
+ 2 | 3 | two | 2 | 2
+ 4 | 1 | four | 2 | 4
+(3 rows)
+
+--
+-- Non-equi-joins
+--
+SELECT *
+ FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k);
+ i | j | t | i | k
+---+---+-------+---+---
+ 1 | 4 | one | 2 | 2
+ 2 | 3 | two | 2 | 2
+ 0 | | zero | 2 | 2
+ 1 | 4 | one | 2 | 4
+ 2 | 3 | two | 2 | 4
+ 3 | 2 | three | 2 | 4
+ 4 | 1 | four | 2 | 4
+ 0 | | zero | 2 | 4
+ 0 | | zero | | 0
+(9 rows)
+
+--
+-- Outer joins
+-- Note that OUTER is a noise word
+--
+SELECT *
+ FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 4 | 1 | four |
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+ 6 | 6 | six |
+ 7 | 7 | seven |
+ 8 | 8 | eight |
+ | | null |
+ | 0 | zero |
+(13 rows)
+
+SELECT *
+ FROM J1_TBL LEFT JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 4 | 1 | four |
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+ 6 | 6 | six |
+ 7 | 7 | seven |
+ 8 | 8 | eight |
+ | | null |
+ | 0 | zero |
+(13 rows)
+
+SELECT *
+ FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+ | | |
+ | | | 0
+(9 rows)
+
+SELECT *
+ FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+ | | |
+ | | | 0
+(9 rows)
+
+SELECT *
+ FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 4 | 1 | four |
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+ 6 | 6 | six |
+ 7 | 7 | seven |
+ 8 | 8 | eight |
+ | | | 0
+ | | null |
+ | 0 | zero |
+ | | |
+(15 rows)
+
+SELECT *
+ FROM J1_TBL FULL JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
+ i | j | t | k
+---+---+-------+----
+ 0 | | zero |
+ 1 | 4 | one | -1
+ 2 | 3 | two | 2
+ 2 | 3 | two | 4
+ 3 | 2 | three | -3
+ 4 | 1 | four |
+ 5 | 0 | five | -5
+ 5 | 0 | five | -5
+ 6 | 6 | six |
+ 7 | 7 | seven |
+ 8 | 8 | eight |
+ | | | 0
+ | | null |
+ | 0 | zero |
+ | | |
+(15 rows)
+
+SELECT *
+ FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
+ i | j | t | k
+---+---+---+---
+(0 rows)
+
+SELECT *
+ FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1);
+ i | j | t | k
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+--
+-- semijoin selectivity for <>
+--
+explain (costs off)
+select * from int4_tbl i4, tenk1 a
+where exists(select * from tenk1 b
+ where a.twothousand = b.twothousand and a.fivethous <> b.fivethous)
+ and i4.f1 = a.tenthous;
+ QUERY PLAN
+----------------------------------------------
+ Hash Semi Join
+ Hash Cond: (a.twothousand = b.twothousand)
+ Join Filter: (a.fivethous <> b.fivethous)
+ -> Hash Join
+ Hash Cond: (a.tenthous = i4.f1)
+ -> Seq Scan on tenk1 a
+ -> Hash
+ -> Seq Scan on int4_tbl i4
+ -> Hash
+ -> Seq Scan on tenk1 b
+(10 rows)
+
+--
+-- More complicated constructs
+--
+--
+-- Multiway full join
+--
+CREATE TABLE t1 (name TEXT, n INTEGER);
+CREATE TABLE t2 (name TEXT, n INTEGER);
+CREATE TABLE t3 (name TEXT, n INTEGER);
+INSERT INTO t1 VALUES ( 'bb', 11 );
+INSERT INTO t2 VALUES ( 'bb', 12 );
+INSERT INTO t2 VALUES ( 'cc', 22 );
+INSERT INTO t2 VALUES ( 'ee', 42 );
+INSERT INTO t3 VALUES ( 'bb', 13 );
+INSERT INTO t3 VALUES ( 'cc', 23 );
+INSERT INTO t3 VALUES ( 'dd', 33 );
+SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name);
+ name | n | n | n
+------+----+----+----
+ bb | 11 | 12 | 13
+ cc | | 22 | 23
+ dd | | | 33
+ ee | | 42 |
+(4 rows)
+
+--
+-- Test interactions of join syntax and subqueries
+--
+-- Basic cases (we expect planner to pull up the subquery here)
+SELECT * FROM
+(SELECT * FROM t2) as s2
+INNER JOIN
+(SELECT * FROM t3) s3
+USING (name);
+ name | n | n
+------+----+----
+ bb | 12 | 13
+ cc | 22 | 23
+(2 rows)
+
+SELECT * FROM
+(SELECT * FROM t2) as s2
+LEFT JOIN
+(SELECT * FROM t3) s3
+USING (name);
+ name | n | n
+------+----+----
+ bb | 12 | 13
+ cc | 22 | 23
+ ee | 42 |
+(3 rows)
+
+SELECT * FROM
+(SELECT * FROM t2) as s2
+FULL JOIN
+(SELECT * FROM t3) s3
+USING (name);
+ name | n | n
+------+----+----
+ bb | 12 | 13
+ cc | 22 | 23
+ dd | | 33
+ ee | 42 |
+(4 rows)
+
+-- Cases with non-nullable expressions in subquery results;
+-- make sure these go to null as expected
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL INNER JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s2_n | s2_2 | s3_n | s3_2
+------+------+------+------+------
+ bb | 12 | 2 | 13 | 3
+ cc | 22 | 2 | 23 | 3
+(2 rows)
+
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL LEFT JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s2_n | s2_2 | s3_n | s3_2
+------+------+------+------+------
+ bb | 12 | 2 | 13 | 3
+ cc | 22 | 2 | 23 | 3
+ ee | 42 | 2 | |
+(3 rows)
+
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL FULL JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s2_n | s2_2 | s3_n | s3_2
+------+------+------+------+------
+ bb | 12 | 2 | 13 | 3
+ cc | 22 | 2 | 23 | 3
+ dd | | | 33 | 3
+ ee | 42 | 2 | |
+(4 rows)
+
+SELECT * FROM
+(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
+NATURAL INNER JOIN
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL INNER JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2
+------+------+------+------+------+------+------
+ bb | 11 | 1 | 12 | 2 | 13 | 3
+(1 row)
+
+SELECT * FROM
+(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
+NATURAL FULL JOIN
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL FULL JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2
+------+------+------+------+------+------+------
+ bb | 11 | 1 | 12 | 2 | 13 | 3
+ cc | | | 22 | 2 | 23 | 3
+ dd | | | | | 33 | 3
+ ee | | | 42 | 2 | |
+(4 rows)
+
+SELECT * FROM
+(SELECT name, n as s1_n FROM t1) as s1
+NATURAL FULL JOIN
+ (SELECT * FROM
+ (SELECT name, n as s2_n FROM t2) as s2
+ NATURAL FULL JOIN
+ (SELECT name, n as s3_n FROM t3) as s3
+ ) ss2;
+ name | s1_n | s2_n | s3_n
+------+------+------+------
+ bb | 11 | 12 | 13
+ cc | | 22 | 23
+ dd | | | 33
+ ee | | 42 |
+(4 rows)
+
+SELECT * FROM
+(SELECT name, n as s1_n FROM t1) as s1
+NATURAL FULL JOIN
+ (SELECT * FROM
+ (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+ NATURAL FULL JOIN
+ (SELECT name, n as s3_n FROM t3) as s3
+ ) ss2;
+ name | s1_n | s2_n | s2_2 | s3_n
+------+------+------+------+------
+ bb | 11 | 12 | 2 | 13
+ cc | | 22 | 2 | 23
+ dd | | | | 33
+ ee | | 42 | 2 |
+(4 rows)
+
+-- Constants as join keys can also be problematic
+SELECT * FROM
+ (SELECT name, n as s1_n FROM t1) as s1
+FULL JOIN
+ (SELECT name, 2 as s2_n FROM t2) as s2
+ON (s1_n = s2_n);
+ name | s1_n | name | s2_n
+------+------+------+------
+ | | bb | 2
+ | | cc | 2
+ | | ee | 2
+ bb | 11 | |
+(4 rows)
+
+-- Test for propagation of nullability constraints into sub-joins
+create temp table x (x1 int, x2 int);
+insert into x values (1,11);
+insert into x values (2,22);
+insert into x values (3,null);
+insert into x values (4,44);
+insert into x values (5,null);
+create temp table y (y1 int, y2 int);
+insert into y values (1,111);
+insert into y values (2,222);
+insert into y values (3,333);
+insert into y values (4,null);
+select * from x;
+ x1 | x2
+----+----
+ 1 | 11
+ 2 | 22
+ 3 |
+ 4 | 44
+ 5 |
+(5 rows)
+
+select * from y;
+ y1 | y2
+----+-----
+ 1 | 111
+ 2 | 222
+ 3 | 333
+ 4 |
+(4 rows)
+
+select * from x left join y on (x1 = y1 and x2 is not null);
+ x1 | x2 | y1 | y2
+----+----+----+-----
+ 1 | 11 | 1 | 111
+ 2 | 22 | 2 | 222
+ 3 | | |
+ 4 | 44 | 4 |
+ 5 | | |
+(5 rows)
+
+select * from x left join y on (x1 = y1 and y2 is not null);
+ x1 | x2 | y1 | y2
+----+----+----+-----
+ 1 | 11 | 1 | 111
+ 2 | 22 | 2 | 222
+ 3 | | 3 | 333
+ 4 | 44 | |
+ 5 | | |
+(5 rows)
+
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1);
+ x1 | x2 | y1 | y2 | xx1 | xx2
+----+----+----+-----+-----+-----
+ 1 | 11 | 1 | 111 | 1 | 11
+ 2 | 22 | 2 | 222 | 2 | 22
+ 3 | | 3 | 333 | 3 |
+ 4 | 44 | 4 | | 4 | 44
+ 5 | | | | 5 |
+(5 rows)
+
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1 and x2 is not null);
+ x1 | x2 | y1 | y2 | xx1 | xx2
+----+----+----+-----+-----+-----
+ 1 | 11 | 1 | 111 | 1 | 11
+ 2 | 22 | 2 | 222 | 2 | 22
+ 3 | | 3 | 333 | |
+ 4 | 44 | 4 | | 4 | 44
+ 5 | | | | |
+(5 rows)
+
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1 and y2 is not null);
+ x1 | x2 | y1 | y2 | xx1 | xx2
+----+----+----+-----+-----+-----
+ 1 | 11 | 1 | 111 | 1 | 11
+ 2 | 22 | 2 | 222 | 2 | 22
+ 3 | | 3 | 333 | 3 |
+ 4 | 44 | 4 | | |
+ 5 | | | | |
+(5 rows)
+
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1 and xx2 is not null);
+ x1 | x2 | y1 | y2 | xx1 | xx2
+----+----+----+-----+-----+-----
+ 1 | 11 | 1 | 111 | 1 | 11
+ 2 | 22 | 2 | 222 | 2 | 22
+ 3 | | 3 | 333 | |
+ 4 | 44 | 4 | | 4 | 44
+ 5 | | | | |
+(5 rows)
+
+-- these should NOT give the same answers as above
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1) where (x2 is not null);
+ x1 | x2 | y1 | y2 | xx1 | xx2
+----+----+----+-----+-----+-----
+ 1 | 11 | 1 | 111 | 1 | 11
+ 2 | 22 | 2 | 222 | 2 | 22
+ 4 | 44 | 4 | | 4 | 44
+(3 rows)
+
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1) where (y2 is not null);
+ x1 | x2 | y1 | y2 | xx1 | xx2
+----+----+----+-----+-----+-----
+ 1 | 11 | 1 | 111 | 1 | 11
+ 2 | 22 | 2 | 222 | 2 | 22
+ 3 | | 3 | 333 | 3 |
+(3 rows)
+
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1) where (xx2 is not null);
+ x1 | x2 | y1 | y2 | xx1 | xx2
+----+----+----+-----+-----+-----
+ 1 | 11 | 1 | 111 | 1 | 11
+ 2 | 22 | 2 | 222 | 2 | 22
+ 4 | 44 | 4 | | 4 | 44
+(3 rows)
+
+--
+-- regression test: check for bug with propagation of implied equality
+-- to outside an IN
+--
+select count(*) from tenk1 a where unique1 in
+ (select unique1 from tenk1 b join tenk1 c using (unique1)
+ where b.unique2 = 42);
+ count
+-------
+ 1
+(1 row)
+
+--
+-- regression test: check for failure to generate a plan with multiple
+-- degenerate IN clauses
+--
+select count(*) from tenk1 x where
+ x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and
+ x.unique1 = 0 and
+ x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1);
+ count
+-------
+ 1
+(1 row)
+
+-- try that with GEQO too
+begin;
+set geqo = on;
+set geqo_threshold = 2;
+select count(*) from tenk1 x where
+ x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and
+ x.unique1 = 0 and
+ x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1);
+ count
+-------
+ 1
+(1 row)
+
+rollback;
+--
+-- regression test: be sure we cope with proven-dummy append rels
+--
+explain (costs off)
+select aa, bb, unique1, unique1
+ from tenk1 right join b on aa = unique1
+ where bb < bb and bb is null;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+select aa, bb, unique1, unique1
+ from tenk1 right join b on aa = unique1
+ where bb < bb and bb is null;
+ aa | bb | unique1 | unique1
+----+----+---------+---------
+(0 rows)
+
+--
+-- regression test: check handling of empty-FROM subquery underneath outer join
+--
+explain (costs off)
+select * from int8_tbl i1 left join (int8_tbl i2 join
+ (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
+order by 1, 2;
+ QUERY PLAN
+-------------------------------------------
+ Sort
+ Sort Key: i1.q1, i1.q2
+ -> Hash Left Join
+ Hash Cond: (i1.q2 = i2.q2)
+ -> Seq Scan on int8_tbl i1
+ -> Hash
+ -> Seq Scan on int8_tbl i2
+ Filter: (q1 = 123)
+(8 rows)
+
+select * from int8_tbl i1 left join (int8_tbl i2 join
+ (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
+order by 1, 2;
+ q1 | q2 | q1 | q2 | x
+------------------+-------------------+-----+------------------+-----
+ 123 | 456 | 123 | 456 | 123
+ 123 | 4567890123456789 | 123 | 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789 | | |
+ 4567890123456789 | 123 | | |
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 123
+(5 rows)
+
+--
+-- regression test: check a case where join_clause_is_movable_into() gives
+-- an imprecise result, causing an assertion failure
+--
+select count(*)
+from
+ (select t3.tenthous as x1, coalesce(t1.stringu1, t2.stringu1) as x2
+ from tenk1 t1
+ left join tenk1 t2 on t1.unique1 = t2.unique1
+ join tenk1 t3 on t1.unique2 = t3.unique2) ss,
+ tenk1 t4,
+ tenk1 t5
+where t4.thousand = t5.unique1 and ss.x1 = t4.tenthous and ss.x2 = t5.stringu1;
+ count
+-------
+ 1000
+(1 row)
+
+--
+-- regression test: check a case where we formerly missed including an EC
+-- enforcement clause because it was expected to be handled at scan level
+--
+explain (costs off)
+select a.f1, b.f1, t.thousand, t.tenthous from
+ tenk1 t,
+ (select sum(f1)+1 as f1 from int4_tbl i4a) a,
+ (select sum(f1) as f1 from int4_tbl i4b) b
+where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ -> Aggregate
+ -> Seq Scan on int4_tbl i4b
+ -> Nested Loop
+ Join Filter: ((sum(i4b.f1)) = ((sum(i4a.f1) + 1)))
+ -> Aggregate
+ -> Seq Scan on int4_tbl i4a
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t
+ Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
+(9 rows)
+
+select a.f1, b.f1, t.thousand, t.tenthous from
+ tenk1 t,
+ (select sum(f1)+1 as f1 from int4_tbl i4a) a,
+ (select sum(f1) as f1 from int4_tbl i4b) b
+where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
+ f1 | f1 | thousand | tenthous
+----+----+----------+----------
+(0 rows)
+
+--
+-- check a case where we formerly got confused by conflicting sort orders
+-- in redundant merge join path keys
+--
+explain (costs off)
+select * from
+ j1_tbl full join
+ (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+ on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Merge Full Join
+ Merge Cond: ((j2_tbl.i = j1_tbl.i) AND (j2_tbl.k = j1_tbl.i))
+ -> Sort
+ Sort Key: j2_tbl.i DESC, j2_tbl.k
+ -> Seq Scan on j2_tbl
+ -> Sort
+ Sort Key: j1_tbl.i DESC
+ -> Seq Scan on j1_tbl
+(8 rows)
+
+select * from
+ j1_tbl full join
+ (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+ on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+ i | j | t | i | k
+---+---+-------+---+----
+ | | | | 0
+ | | | |
+ | 0 | zero | |
+ | | null | |
+ 8 | 8 | eight | |
+ 7 | 7 | seven | |
+ 6 | 6 | six | |
+ | | | 5 | -5
+ | | | 5 | -5
+ 5 | 0 | five | |
+ 4 | 1 | four | |
+ | | | 3 | -3
+ 3 | 2 | three | |
+ 2 | 3 | two | 2 | 2
+ | | | 2 | 4
+ | | | 1 | -1
+ | | | 0 |
+ 1 | 4 | one | |
+ 0 | | zero | |
+(19 rows)
+
+--
+-- a different check for handling of redundant sort keys in merge joins
+--
+explain (costs off)
+select count(*) from
+ (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+ left join
+ (select * from tenk1 y order by y.unique2) y
+ on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Aggregate
+ -> Merge Left Join
+ Merge Cond: (x.thousand = y.unique2)
+ Join Filter: ((x.twothousand = y.hundred) AND (x.fivethous = y.unique2))
+ -> Sort
+ Sort Key: x.thousand, x.twothousand, x.fivethous
+ -> Seq Scan on tenk1 x
+ -> Materialize
+ -> Index Scan using tenk1_unique2 on tenk1 y
+(9 rows)
+
+select count(*) from
+ (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+ left join
+ (select * from tenk1 y order by y.unique2) y
+ on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+ count
+-------
+ 10000
+(1 row)
+
+--
+-- Clean up
+--
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+DROP TABLE J1_TBL;
+DROP TABLE J2_TBL;
+-- Both DELETE and UPDATE allow the specification of additional tables
+-- to "join" against to determine which rows should be modified.
+CREATE TEMP TABLE t1 (a int, b int);
+CREATE TEMP TABLE t2 (a int, b int);
+CREATE TEMP TABLE t3 (x int, y int);
+INSERT INTO t1 VALUES (5, 10);
+INSERT INTO t1 VALUES (15, 20);
+INSERT INTO t1 VALUES (100, 100);
+INSERT INTO t1 VALUES (200, 1000);
+INSERT INTO t2 VALUES (200, 2000);
+INSERT INTO t3 VALUES (5, 20);
+INSERT INTO t3 VALUES (6, 7);
+INSERT INTO t3 VALUES (7, 8);
+INSERT INTO t3 VALUES (500, 100);
+DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a;
+SELECT * FROM t3;
+ x | y
+-----+-----
+ 6 | 7
+ 7 | 8
+ 500 | 100
+(3 rows)
+
+DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a;
+SELECT * FROM t3;
+ x | y
+---+---
+ 6 | 7
+ 7 | 8
+(2 rows)
+
+DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y;
+SELECT * FROM t3;
+ x | y
+---+---
+(0 rows)
+
+-- Test join against inheritance tree
+create temp table t2a () inherits (t2);
+insert into t2a values (200, 2001);
+select * from t1 left join t2 on (t1.a = t2.a);
+ a | b | a | b
+-----+------+-----+------
+ 5 | 10 | |
+ 15 | 20 | |
+ 100 | 100 | |
+ 200 | 1000 | 200 | 2000
+ 200 | 1000 | 200 | 2001
+(5 rows)
+
+-- Test matching of column name with wrong alias
+select t1.x from t1 join t3 on (t1.a = t3.x);
+ERROR: column t1.x does not exist
+LINE 1: select t1.x from t1 join t3 on (t1.a = t3.x);
+ ^
+HINT: Perhaps you meant to reference the column "t3.x".
+-- Test matching of locking clause with wrong alias
+select t1.*, t2.*, unnamed_join.* from
+ t1 join t2 on (t1.a = t2.a), t3 as unnamed_join
+ for update of unnamed_join;
+ a | b | a | b | x | y
+---+---+---+---+---+---
+(0 rows)
+
+select foo.*, unnamed_join.* from
+ t1 join t2 using (a) as foo, t3 as unnamed_join
+ for update of unnamed_join;
+ a | x | y
+---+---+---
+(0 rows)
+
+select foo.*, unnamed_join.* from
+ t1 join t2 using (a) as foo, t3 as unnamed_join
+ for update of foo;
+ERROR: FOR UPDATE cannot be applied to a join
+LINE 3: for update of foo;
+ ^
+select bar.*, unnamed_join.* from
+ (t1 join t2 using (a) as foo) as bar, t3 as unnamed_join
+ for update of foo;
+ERROR: relation "foo" in FOR UPDATE clause not found in FROM clause
+LINE 3: for update of foo;
+ ^
+select bar.*, unnamed_join.* from
+ (t1 join t2 using (a) as foo) as bar, t3 as unnamed_join
+ for update of bar;
+ERROR: FOR UPDATE cannot be applied to a join
+LINE 3: for update of bar;
+ ^
+--
+-- regression test for 8.1 merge right join bug
+--
+CREATE TEMP TABLE tt1 ( tt1_id int4, joincol int4 );
+INSERT INTO tt1 VALUES (1, 11);
+INSERT INTO tt1 VALUES (2, NULL);
+CREATE TEMP TABLE tt2 ( tt2_id int4, joincol int4 );
+INSERT INTO tt2 VALUES (21, 11);
+INSERT INTO tt2 VALUES (22, 11);
+set enable_hashjoin to off;
+set enable_nestloop to off;
+-- these should give the same results
+select tt1.*, tt2.* from tt1 left join tt2 on tt1.joincol = tt2.joincol;
+ tt1_id | joincol | tt2_id | joincol
+--------+---------+--------+---------
+ 1 | 11 | 21 | 11
+ 1 | 11 | 22 | 11
+ 2 | | |
+(3 rows)
+
+select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol;
+ tt1_id | joincol | tt2_id | joincol
+--------+---------+--------+---------
+ 1 | 11 | 21 | 11
+ 1 | 11 | 22 | 11
+ 2 | | |
+(3 rows)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+--
+-- regression test for bug #13908 (hash join with skew tuples & nbatch increase)
+--
+set work_mem to '64kB';
+set enable_mergejoin to off;
+set enable_memoize to off;
+explain (costs off)
+select count(*) from tenk1 a, tenk1 b
+ where a.hundred = b.thousand and (b.fivethous % 10) < 10;
+ QUERY PLAN
+------------------------------------------------------------
+ Aggregate
+ -> Hash Join
+ Hash Cond: (a.hundred = b.thousand)
+ -> Index Only Scan using tenk1_hundred on tenk1 a
+ -> Hash
+ -> Seq Scan on tenk1 b
+ Filter: ((fivethous % 10) < 10)
+(7 rows)
+
+select count(*) from tenk1 a, tenk1 b
+ where a.hundred = b.thousand and (b.fivethous % 10) < 10;
+ count
+--------
+ 100000
+(1 row)
+
+reset work_mem;
+reset enable_mergejoin;
+reset enable_memoize;
+--
+-- regression test for 8.2 bug with improper re-ordering of left joins
+--
+create temp table tt3(f1 int, f2 text);
+insert into tt3 select x, repeat('xyzzy', 100) from generate_series(1,10000) x;
+create index tt3i on tt3(f1);
+analyze tt3;
+create temp table tt4(f1 int);
+insert into tt4 values (0),(1),(9999);
+analyze tt4;
+SELECT a.f1
+FROM tt4 a
+LEFT JOIN (
+ SELECT b.f1
+ FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1)
+ WHERE c.f1 IS NULL
+) AS d ON (a.f1 = d.f1)
+WHERE d.f1 IS NULL;
+ f1
+------
+ 0
+ 1
+ 9999
+(3 rows)
+
+--
+-- regression test for proper handling of outer joins within antijoins
+--
+create temp table tt4x(c1 int, c2 int, c3 int);
+explain (costs off)
+select * from tt4x t1
+where not exists (
+ select 1 from tt4x t2
+ left join tt4x t3 on t2.c3 = t3.c1
+ left join ( select t5.c1 as c1
+ from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1
+ ) a1 on t3.c2 = a1.c1
+ where t1.c1 = t2.c2
+);
+ QUERY PLAN
+---------------------------------------------------------
+ Hash Anti Join
+ Hash Cond: (t1.c1 = t2.c2)
+ -> Seq Scan on tt4x t1
+ -> Hash
+ -> Merge Right Join
+ Merge Cond: (t5.c1 = t3.c2)
+ -> Merge Join
+ Merge Cond: (t4.c2 = t5.c1)
+ -> Sort
+ Sort Key: t4.c2
+ -> Seq Scan on tt4x t4
+ -> Sort
+ Sort Key: t5.c1
+ -> Seq Scan on tt4x t5
+ -> Sort
+ Sort Key: t3.c2
+ -> Merge Left Join
+ Merge Cond: (t2.c3 = t3.c1)
+ -> Sort
+ Sort Key: t2.c3
+ -> Seq Scan on tt4x t2
+ -> Sort
+ Sort Key: t3.c1
+ -> Seq Scan on tt4x t3
+(24 rows)
+
+--
+-- regression test for problems of the sort depicted in bug #3494
+--
+create temp table tt5(f1 int, f2 int);
+create temp table tt6(f1 int, f2 int);
+insert into tt5 values(1, 10);
+insert into tt5 values(1, 11);
+insert into tt6 values(1, 9);
+insert into tt6 values(1, 2);
+insert into tt6 values(2, 9);
+select * from tt5,tt6 where tt5.f1 = tt6.f1 and tt5.f1 = tt5.f2 - tt6.f2;
+ f1 | f2 | f1 | f2
+----+----+----+----
+ 1 | 10 | 1 | 9
+(1 row)
+
+--
+-- regression test for problems of the sort depicted in bug #3588
+--
+create temp table xx (pkxx int);
+create temp table yy (pkyy int, pkxx int);
+insert into xx values (1);
+insert into xx values (2);
+insert into xx values (3);
+insert into yy values (101, 1);
+insert into yy values (201, 2);
+insert into yy values (301, NULL);
+select yy.pkyy as yy_pkyy, yy.pkxx as yy_pkxx, yya.pkyy as yya_pkyy,
+ xxa.pkxx as xxa_pkxx, xxb.pkxx as xxb_pkxx
+from yy
+ left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy
+ left join xx xxa on yya.pkxx = xxa.pkxx
+ left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx;
+ yy_pkyy | yy_pkxx | yya_pkyy | xxa_pkxx | xxb_pkxx
+---------+---------+----------+----------+----------
+ 101 | 1 | 101 | 1 | 1
+ 201 | 2 | | | 1
+ 301 | | | | 1
+(3 rows)
+
+--
+-- regression test for improper pushing of constants across outer-join clauses
+-- (as seen in early 8.2.x releases)
+--
+create temp table zt1 (f1 int primary key);
+create temp table zt2 (f2 int primary key);
+create temp table zt3 (f3 int primary key);
+insert into zt1 values(53);
+insert into zt2 values(53);
+select * from
+ zt2 left join zt3 on (f2 = f3)
+ left join zt1 on (f3 = f1)
+where f2 = 53;
+ f2 | f3 | f1
+----+----+----
+ 53 | |
+(1 row)
+
+create temp view zv1 as select *,'dummy'::text AS junk from zt1;
+select * from
+ zt2 left join zt3 on (f2 = f3)
+ left join zv1 on (f3 = f1)
+where f2 = 53;
+ f2 | f3 | f1 | junk
+----+----+----+------
+ 53 | | |
+(1 row)
+
+--
+-- regression test for improper extraction of OR indexqual conditions
+-- (as seen in early 8.3.x releases)
+--
+select a.unique2, a.ten, b.tenthous, b.unique2, b.hundred
+from tenk1 a left join tenk1 b on a.unique2 = b.tenthous
+where a.unique1 = 42 and
+ ((b.unique2 is null and a.ten = 2) or b.hundred = 3);
+ unique2 | ten | tenthous | unique2 | hundred
+---------+-----+----------+---------+---------
+(0 rows)
+
+--
+-- test proper positioning of one-time quals in EXISTS (8.4devel bug)
+--
+prepare foo(bool) as
+ select count(*) from tenk1 a left join tenk1 b
+ on (a.unique2 = b.unique1 and exists
+ (select 1 from tenk1 c where c.thousand = b.unique2 and $1));
+execute foo(true);
+ count
+-------
+ 10000
+(1 row)
+
+execute foo(false);
+ count
+-------
+ 10000
+(1 row)
+
+--
+-- test for sane behavior with noncanonical merge clauses, per bug #4926
+--
+begin;
+set enable_mergejoin = 1;
+set enable_hashjoin = 0;
+set enable_nestloop = 0;
+create temp table a (i integer);
+create temp table b (x integer, y integer);
+select * from a left join b on i = x and i = y and x = i;
+ i | x | y
+---+---+---
+(0 rows)
+
+rollback;
+--
+-- test handling of merge clauses using record_ops
+--
+begin;
+create type mycomptype as (id int, v bigint);
+create temp table tidv (idv mycomptype);
+create index on tidv (idv);
+explain (costs off)
+select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv;
+ QUERY PLAN
+----------------------------------------------------------
+ Merge Join
+ Merge Cond: (a.idv = b.idv)
+ -> Index Only Scan using tidv_idv_idx on tidv a
+ -> Materialize
+ -> Index Only Scan using tidv_idv_idx on tidv b
+(5 rows)
+
+set enable_mergejoin = 0;
+set enable_hashjoin = 0;
+explain (costs off)
+select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv;
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop
+ -> Seq Scan on tidv a
+ -> Index Only Scan using tidv_idv_idx on tidv b
+ Index Cond: (idv = a.idv)
+(4 rows)
+
+rollback;
+--
+-- test NULL behavior of whole-row Vars, per bug #5025
+--
+select t1.q2, count(t2.*)
+from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1)
+group by t1.q2 order by 1;
+ q2 | count
+-------------------+-------
+ -4567890123456789 | 0
+ 123 | 2
+ 456 | 0
+ 4567890123456789 | 6
+(4 rows)
+
+select t1.q2, count(t2.*)
+from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1)
+group by t1.q2 order by 1;
+ q2 | count
+-------------------+-------
+ -4567890123456789 | 0
+ 123 | 2
+ 456 | 0
+ 4567890123456789 | 6
+(4 rows)
+
+select t1.q2, count(t2.*)
+from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1)
+group by t1.q2 order by 1;
+ q2 | count
+-------------------+-------
+ -4567890123456789 | 0
+ 123 | 2
+ 456 | 0
+ 4567890123456789 | 6
+(4 rows)
+
+select t1.q2, count(t2.*)
+from int8_tbl t1 left join
+ (select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2
+ on (t1.q2 = t2.q1)
+group by t1.q2 order by 1;
+ q2 | count
+-------------------+-------
+ -4567890123456789 | 0
+ 123 | 2
+ 456 | 0
+ 4567890123456789 | 6
+(4 rows)
+
+--
+-- test incorrect failure to NULL pulled-up subexpressions
+--
+begin;
+create temp table a (
+ code char not null,
+ constraint a_pk primary key (code)
+);
+create temp table b (
+ a char not null,
+ num integer not null,
+ constraint b_pk primary key (a, num)
+);
+create temp table c (
+ name char not null,
+ a char,
+ constraint c_pk primary key (name)
+);
+insert into a (code) values ('p');
+insert into a (code) values ('q');
+insert into b (a, num) values ('p', 1);
+insert into b (a, num) values ('p', 2);
+insert into c (name, a) values ('A', 'p');
+insert into c (name, a) values ('B', 'q');
+insert into c (name, a) values ('C', null);
+select c.name, ss.code, ss.b_cnt, ss.const
+from c left join
+ (select a.code, coalesce(b_grp.cnt, 0) as b_cnt, -1 as const
+ from a left join
+ (select count(1) as cnt, b.a from b group by b.a) as b_grp
+ on a.code = b_grp.a
+ ) as ss
+ on (c.a = ss.code)
+order by c.name;
+ name | code | b_cnt | const
+------+------+-------+-------
+ A | p | 2 | -1
+ B | q | 0 | -1
+ C | | |
+(3 rows)
+
+rollback;
+--
+-- test incorrect handling of placeholders that only appear in targetlists,
+-- per bug #6154
+--
+SELECT * FROM
+( SELECT 1 as key1 ) sub1
+LEFT JOIN
+( SELECT sub3.key3, sub4.value2, COALESCE(sub4.value2, 66) as value3 FROM
+ ( SELECT 1 as key3 ) sub3
+ LEFT JOIN
+ ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM
+ ( SELECT 1 as key5 ) sub5
+ LEFT JOIN
+ ( SELECT 2 as key6, 42 as value1 ) sub6
+ ON sub5.key5 = sub6.key6
+ ) sub4
+ ON sub4.key5 = sub3.key3
+) sub2
+ON sub1.key1 = sub2.key3;
+ key1 | key3 | value2 | value3
+------+------+--------+--------
+ 1 | 1 | 1 | 1
+(1 row)
+
+-- test the path using join aliases, too
+SELECT * FROM
+( SELECT 1 as key1 ) sub1
+LEFT JOIN
+( SELECT sub3.key3, value2, COALESCE(value2, 66) as value3 FROM
+ ( SELECT 1 as key3 ) sub3
+ LEFT JOIN
+ ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM
+ ( SELECT 1 as key5 ) sub5
+ LEFT JOIN
+ ( SELECT 2 as key6, 42 as value1 ) sub6
+ ON sub5.key5 = sub6.key6
+ ) sub4
+ ON sub4.key5 = sub3.key3
+) sub2
+ON sub1.key1 = sub2.key3;
+ key1 | key3 | value2 | value3
+------+------+--------+--------
+ 1 | 1 | 1 | 1
+(1 row)
+
+--
+-- test case where a PlaceHolderVar is used as a nestloop parameter
+--
+EXPLAIN (COSTS OFF)
+SELECT qq, unique1
+ FROM
+ ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1
+ FULL OUTER JOIN
+ ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
+ USING (qq)
+ INNER JOIN tenk1 c ON qq = unique2;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Nested Loop
+ -> Hash Full Join
+ Hash Cond: ((COALESCE(a.q1, '0'::bigint)) = (COALESCE(b.q2, '-1'::bigint)))
+ -> Seq Scan on int8_tbl a
+ -> Hash
+ -> Seq Scan on int8_tbl b
+ -> Index Scan using tenk1_unique2 on tenk1 c
+ Index Cond: (unique2 = COALESCE((COALESCE(a.q1, '0'::bigint)), (COALESCE(b.q2, '-1'::bigint))))
+(8 rows)
+
+SELECT qq, unique1
+ FROM
+ ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1
+ FULL OUTER JOIN
+ ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
+ USING (qq)
+ INNER JOIN tenk1 c ON qq = unique2;
+ qq | unique1
+-----+---------
+ 123 | 4596
+ 123 | 4596
+ 456 | 7318
+(3 rows)
+
+--
+-- nested nestloops can require nested PlaceHolderVars
+--
+create temp table nt1 (
+ id int primary key,
+ a1 boolean,
+ a2 boolean
+);
+create temp table nt2 (
+ id int primary key,
+ nt1_id int,
+ b1 boolean,
+ b2 boolean,
+ foreign key (nt1_id) references nt1(id)
+);
+create temp table nt3 (
+ id int primary key,
+ nt2_id int,
+ c1 boolean,
+ foreign key (nt2_id) references nt2(id)
+);
+insert into nt1 values (1,true,true);
+insert into nt1 values (2,true,false);
+insert into nt1 values (3,false,false);
+insert into nt2 values (1,1,true,true);
+insert into nt2 values (2,2,true,false);
+insert into nt2 values (3,3,false,false);
+insert into nt3 values (1,1,true);
+insert into nt3 values (2,2,false);
+insert into nt3 values (3,3,true);
+explain (costs off)
+select nt3.id
+from nt3 as nt3
+ left join
+ (select nt2.*, (nt2.b1 and ss1.a3) AS b3
+ from nt2 as nt2
+ left join
+ (select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
+ on ss1.id = nt2.nt1_id
+ ) as ss2
+ on ss2.id = nt3.nt2_id
+where nt3.id = 1 and ss2.b3;
+ QUERY PLAN
+-----------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ -> Index Scan using nt3_pkey on nt3
+ Index Cond: (id = 1)
+ -> Index Scan using nt2_pkey on nt2
+ Index Cond: (id = nt3.nt2_id)
+ -> Index Only Scan using nt1_pkey on nt1
+ Index Cond: (id = nt2.nt1_id)
+ Filter: (nt2.b1 AND (id IS NOT NULL))
+(9 rows)
+
+select nt3.id
+from nt3 as nt3
+ left join
+ (select nt2.*, (nt2.b1 and ss1.a3) AS b3
+ from nt2 as nt2
+ left join
+ (select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
+ on ss1.id = nt2.nt1_id
+ ) as ss2
+ on ss2.id = nt3.nt2_id
+where nt3.id = 1 and ss2.b3;
+ id
+----
+ 1
+(1 row)
+
+--
+-- test case where a PlaceHolderVar is propagated into a subquery
+--
+explain (costs off)
+select * from
+ int8_tbl t1 left join
+ (select q1 as x, 42 as y from int8_tbl t2) ss
+ on t1.q2 = ss.x
+where
+ 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
+order by 1,2;
+ QUERY PLAN
+-----------------------------------------------------------
+ Sort
+ Sort Key: t1.q1, t1.q2
+ -> Hash Left Join
+ Hash Cond: (t1.q2 = t2.q1)
+ Filter: (1 = (SubPlan 1))
+ -> Seq Scan on int8_tbl t1
+ -> Hash
+ -> Seq Scan on int8_tbl t2
+ SubPlan 1
+ -> Limit
+ -> Result
+ One-Time Filter: ((42) IS NOT NULL)
+ -> Seq Scan on int8_tbl t3
+(13 rows)
+
+select * from
+ int8_tbl t1 left join
+ (select q1 as x, 42 as y from int8_tbl t2) ss
+ on t1.q2 = ss.x
+where
+ 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1)
+order by 1,2;
+ q1 | q2 | x | y
+------------------+------------------+------------------+----
+ 123 | 4567890123456789 | 4567890123456789 | 42
+ 123 | 4567890123456789 | 4567890123456789 | 42
+ 123 | 4567890123456789 | 4567890123456789 | 42
+ 4567890123456789 | 123 | 123 | 42
+ 4567890123456789 | 123 | 123 | 42
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 42
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 42
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 42
+(8 rows)
+
+--
+-- variant where a PlaceHolderVar is needed at a join, but not above the join
+--
+explain (costs off)
+select * from
+ int4_tbl as i41,
+ lateral
+ (select 1 as x from
+ (select i41.f1 as lat,
+ i42.f1 as loc from
+ int8_tbl as i81, int4_tbl as i42) as ss1
+ right join int4_tbl as i43 on (i43.f1 > 1)
+ where ss1.loc = ss1.lat) as ss2
+where i41.f1 > 0;
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ -> Seq Scan on int4_tbl i41
+ Filter: (f1 > 0)
+ -> Nested Loop
+ Join Filter: (i41.f1 = i42.f1)
+ -> Seq Scan on int8_tbl i81
+ -> Materialize
+ -> Seq Scan on int4_tbl i42
+ -> Materialize
+ -> Seq Scan on int4_tbl i43
+ Filter: (f1 > 1)
+(12 rows)
+
+select * from
+ int4_tbl as i41,
+ lateral
+ (select 1 as x from
+ (select i41.f1 as lat,
+ i42.f1 as loc from
+ int8_tbl as i81, int4_tbl as i42) as ss1
+ right join int4_tbl as i43 on (i43.f1 > 1)
+ where ss1.loc = ss1.lat) as ss2
+where i41.f1 > 0;
+ f1 | x
+------------+---
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+(20 rows)
+
+--
+-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
+--
+select * from int4_tbl a full join int4_tbl b on true;
+ f1 | f1
+-------------+-------------
+ 0 | 0
+ 0 | 123456
+ 0 | -123456
+ 0 | 2147483647
+ 0 | -2147483647
+ 123456 | 0
+ 123456 | 123456
+ 123456 | -123456
+ 123456 | 2147483647
+ 123456 | -2147483647
+ -123456 | 0
+ -123456 | 123456
+ -123456 | -123456
+ -123456 | 2147483647
+ -123456 | -2147483647
+ 2147483647 | 0
+ 2147483647 | 123456
+ 2147483647 | -123456
+ 2147483647 | 2147483647
+ 2147483647 | -2147483647
+ -2147483647 | 0
+ -2147483647 | 123456
+ -2147483647 | -123456
+ -2147483647 | 2147483647
+ -2147483647 | -2147483647
+(25 rows)
+
+select * from int4_tbl a full join int4_tbl b on false;
+ f1 | f1
+-------------+-------------
+ | 0
+ | 123456
+ | -123456
+ | 2147483647
+ | -2147483647
+ 0 |
+ 123456 |
+ -123456 |
+ 2147483647 |
+ -2147483647 |
+(10 rows)
+
+--
+-- test for ability to use a cartesian join when necessary
+--
+create temp table q1 as select 1 as q1;
+create temp table q2 as select 0 as q2;
+analyze q1;
+analyze q2;
+explain (costs off)
+select * from
+ tenk1 join int4_tbl on f1 = twothousand,
+ q1, q2
+where q1 = thousand or q2 = thousand;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (tenk1.twothousand = int4_tbl.f1)
+ -> Nested Loop
+ -> Nested Loop
+ -> Seq Scan on q1
+ -> Seq Scan on q2
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((q1.q1 = thousand) OR (q2.q2 = thousand))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = q1.q1)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = q2.q2)
+ -> Hash
+ -> Seq Scan on int4_tbl
+(15 rows)
+
+explain (costs off)
+select * from
+ tenk1 join int4_tbl on f1 = twothousand,
+ q1, q2
+where thousand = (q1 + q2);
+ QUERY PLAN
+--------------------------------------------------------------
+ Hash Join
+ Hash Cond: (tenk1.twothousand = int4_tbl.f1)
+ -> Nested Loop
+ -> Nested Loop
+ -> Seq Scan on q1
+ -> Seq Scan on q2
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (thousand = (q1.q1 + q2.q2))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = (q1.q1 + q2.q2))
+ -> Hash
+ -> Seq Scan on int4_tbl
+(12 rows)
+
+--
+-- test ability to generate a suitable plan for a star-schema query
+--
+explain (costs off)
+select * from
+ tenk1, int8_tbl a, int8_tbl b
+where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Nested Loop
+ -> Seq Scan on int8_tbl b
+ Filter: (q2 = 2)
+ -> Nested Loop
+ -> Seq Scan on int8_tbl a
+ Filter: (q2 = 1)
+ -> Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = a.q1) AND (tenthous = b.q1))
+(8 rows)
+
+--
+-- test a corner case in which we shouldn't apply the star-schema optimization
+--
+explain (costs off)
+select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
+ tenk1 t1
+ inner join int4_tbl i1
+ left join (select v1.x2, v2.y1, 11 AS d1
+ from (select 1,0 from onerow) v1(x1,x2)
+ left join (select 3,1 from onerow) v2(y1,y2)
+ on v1.x1 = v2.y2) subq1
+ on (i1.f1 = subq1.x2)
+ on (t1.unique2 = subq1.d1)
+ left join tenk1 t2
+ on (subq1.y1 = t2.unique1)
+where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ Join Filter: (t1.stringu1 > t2.stringu2)
+ -> Nested Loop
+ -> Nested Loop
+ -> Seq Scan on onerow
+ -> Seq Scan on onerow onerow_1
+ -> Index Scan using tenk1_unique2 on tenk1 t1
+ Index Cond: ((unique2 = (11)) AND (unique2 < 42))
+ -> Index Scan using tenk1_unique1 on tenk1 t2
+ Index Cond: (unique1 = (3))
+ -> Seq Scan on int4_tbl i1
+ Filter: (f1 = 0)
+(13 rows)
+
+select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
+ tenk1 t1
+ inner join int4_tbl i1
+ left join (select v1.x2, v2.y1, 11 AS d1
+ from (select 1,0 from onerow) v1(x1,x2)
+ left join (select 3,1 from onerow) v2(y1,y2)
+ on v1.x1 = v2.y2) subq1
+ on (i1.f1 = subq1.x2)
+ on (t1.unique2 = subq1.d1)
+ left join tenk1 t2
+ on (subq1.y1 = t2.unique1)
+where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
+ unique2 | stringu1 | unique1 | stringu2
+---------+----------+---------+----------
+ 11 | WFAAAA | 3 | LKIAAA
+(1 row)
+
+-- variant that isn't quite a star-schema case
+select ss1.d1 from
+ tenk1 as t1
+ inner join tenk1 as t2
+ on t1.tenthous = t2.ten
+ inner join
+ int8_tbl as i8
+ left join int4_tbl as i4
+ inner join (select 64::information_schema.cardinal_number as d1
+ from tenk1 t3,
+ lateral (select abs(t3.unique1) + random()) ss0(x)
+ where t3.fivethous < 0) as ss1
+ on i4.f1 = ss1.d1
+ on i8.q1 = i4.f1
+ on t1.tenthous = ss1.d1
+where t1.unique1 < i4.f1;
+ d1
+----
+(0 rows)
+
+-- this variant is foldable by the remove-useless-RESULT-RTEs code
+explain (costs off)
+select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
+ tenk1 t1
+ inner join int4_tbl i1
+ left join (select v1.x2, v2.y1, 11 AS d1
+ from (values(1,0)) v1(x1,x2)
+ left join (values(3,1)) v2(y1,y2)
+ on v1.x1 = v2.y2) subq1
+ on (i1.f1 = subq1.x2)
+ on (t1.unique2 = subq1.d1)
+ left join tenk1 t2
+ on (subq1.y1 = t2.unique1)
+where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Nested Loop
+ Join Filter: (t1.stringu1 > t2.stringu2)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl i1
+ Filter: (f1 = 0)
+ -> Index Scan using tenk1_unique2 on tenk1 t1
+ Index Cond: ((unique2 = (11)) AND (unique2 < 42))
+ -> Index Scan using tenk1_unique1 on tenk1 t2
+ Index Cond: (unique1 = (3))
+(9 rows)
+
+select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
+ tenk1 t1
+ inner join int4_tbl i1
+ left join (select v1.x2, v2.y1, 11 AS d1
+ from (values(1,0)) v1(x1,x2)
+ left join (values(3,1)) v2(y1,y2)
+ on v1.x1 = v2.y2) subq1
+ on (i1.f1 = subq1.x2)
+ on (t1.unique2 = subq1.d1)
+ left join tenk1 t2
+ on (subq1.y1 = t2.unique1)
+where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
+ unique2 | stringu1 | unique1 | stringu2
+---------+----------+---------+----------
+ 11 | WFAAAA | 3 | LKIAAA
+(1 row)
+
+-- Here's a variant that we can't fold too aggressively, though,
+-- or we end up with noplace to evaluate the lateral PHV
+explain (verbose, costs off)
+select * from
+ (select 1 as x) ss1 left join (select 2 as y) ss2 on (true),
+ lateral (select ss2.y as z limit 1) ss3;
+ QUERY PLAN
+---------------------------
+ Nested Loop
+ Output: 1, (2), ((2))
+ -> Result
+ Output: 2
+ -> Limit
+ Output: ((2))
+ -> Result
+ Output: (2)
+(8 rows)
+
+select * from
+ (select 1 as x) ss1 left join (select 2 as y) ss2 on (true),
+ lateral (select ss2.y as z limit 1) ss3;
+ x | y | z
+---+---+---
+ 1 | 2 | 2
+(1 row)
+
+-- Test proper handling of appendrel PHVs during useless-RTE removal
+explain (costs off)
+select * from
+ (select 0 as z) as t1
+ left join
+ (select true as a) as t2
+ on true,
+ lateral (select true as b
+ union all
+ select a as b) as t3
+where b;
+ QUERY PLAN
+---------------------------------------
+ Nested Loop
+ -> Result
+ -> Append
+ -> Result
+ -> Result
+ One-Time Filter: (true)
+(6 rows)
+
+select * from
+ (select 0 as z) as t1
+ left join
+ (select true as a) as t2
+ on true,
+ lateral (select true as b
+ union all
+ select a as b) as t3
+where b;
+ z | a | b
+---+---+---
+ 0 | t | t
+ 0 | t | t
+(2 rows)
+
+--
+-- test inlining of immutable functions
+--
+create function f_immutable_int4(i integer) returns integer as
+$$ begin return i; end; $$ language plpgsql immutable;
+-- check optimization of function scan with join
+explain (costs off)
+select unique1 from tenk1, (select * from f_immutable_int4(1) x) x
+where x = unique1;
+ QUERY PLAN
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (verbose, costs off)
+select unique1, x.*
+from tenk1, (select *, random() from f_immutable_int4(1) x) x
+where x = unique1;
+ QUERY PLAN
+-----------------------------------------------------------
+ Nested Loop
+ Output: tenk1.unique1, (1), (random())
+ -> Result
+ Output: 1, random()
+ -> Index Only Scan using tenk1_unique1 on public.tenk1
+ Output: tenk1.unique1
+ Index Cond: (tenk1.unique1 = (1))
+(7 rows)
+
+explain (costs off)
+select unique1 from tenk1, f_immutable_int4(1) x where x = unique1;
+ QUERY PLAN
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1;
+ QUERY PLAN
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select 17);
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off)
+select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x;
+ QUERY PLAN
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x;
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: (tenk1.unique1 = 1)
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Materialize
+ -> Result
+(5 rows)
+
+explain (costs off)
+select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x;
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop Left Join
+ -> Result
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
+(4 rows)
+
+explain (costs off)
+select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x;
+ QUERY PLAN
+----------------------------------------------------
+ Merge Full Join
+ Merge Cond: (tenk1.unique1 = (1))
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Sort
+ Sort Key: (1)
+ -> Result
+(6 rows)
+
+-- check that pullup of a const function allows further const-folding
+explain (costs off)
+select unique1 from tenk1, f_immutable_int4(1) x where x = 42;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- test inlining of immutable functions with PlaceHolderVars
+explain (costs off)
+select nt3.id
+from nt3 as nt3
+ left join
+ (select nt2.*, (nt2.b1 or i4 = 42) AS b3
+ from nt2 as nt2
+ left join
+ f_immutable_int4(0) i4
+ on i4 = nt2.nt1_id
+ ) as ss2
+ on ss2.id = nt3.nt2_id
+where nt3.id = 1 and ss2.b3;
+ QUERY PLAN
+----------------------------------------------
+ Nested Loop Left Join
+ Filter: ((nt2.b1 OR ((0) = 42)))
+ -> Index Scan using nt3_pkey on nt3
+ Index Cond: (id = 1)
+ -> Nested Loop Left Join
+ Join Filter: (0 = nt2.nt1_id)
+ -> Index Scan using nt2_pkey on nt2
+ Index Cond: (id = nt3.nt2_id)
+ -> Result
+(9 rows)
+
+drop function f_immutable_int4(int);
+-- test inlining when function returns composite
+create function mki8(bigint, bigint) returns int8_tbl as
+$$select row($1,$2)::int8_tbl$$ language sql;
+create function mki4(int) returns int4_tbl as
+$$select row($1)::int4_tbl$$ language sql;
+explain (verbose, costs off)
+select * from mki8(1,2);
+ QUERY PLAN
+------------------------------------
+ Function Scan on mki8
+ Output: q1, q2
+ Function Call: '(1,2)'::int8_tbl
+(3 rows)
+
+select * from mki8(1,2);
+ q1 | q2
+----+----
+ 1 | 2
+(1 row)
+
+explain (verbose, costs off)
+select * from mki4(42);
+ QUERY PLAN
+-----------------------------------
+ Function Scan on mki4
+ Output: f1
+ Function Call: '(42)'::int4_tbl
+(3 rows)
+
+select * from mki4(42);
+ f1
+----
+ 42
+(1 row)
+
+drop function mki8(bigint, bigint);
+drop function mki4(int);
+--
+-- test extraction of restriction OR clauses from join OR clause
+-- (we used to only do this for indexable clauses)
+--
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.hundred = 4);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 3) AND (b.hundred = 4)))
+ -> Bitmap Heap Scan on tenk1 b
+ Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 4)
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = 3)
+(17 rows)
+
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.ten = 4);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 3) AND (b.ten = 4)))
+ -> Seq Scan on tenk1 b
+ Filter: ((unique1 = 2) OR (ten = 4))
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = 3)
+(12 rows)
+
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+ -> Bitmap Heap Scan on tenk1 b
+ Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 4)
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = 3)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = 7)
+(19 rows)
+
+--
+-- test placement of movable quals in a parameterized join tree
+--
+explain (costs off)
+select * from tenk1 t1 left join
+ (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
+ on t1.hundred = t2.hundred and t1.ten = t3.ten
+where t1.unique1 = 1;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop Left Join
+ -> Index Scan using tenk1_unique1 on tenk1 t1
+ Index Cond: (unique1 = 1)
+ -> Nested Loop
+ Join Filter: (t1.ten = t3.ten)
+ -> Bitmap Heap Scan on tenk1 t2
+ Recheck Cond: (t1.hundred = hundred)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = t1.hundred)
+ -> Index Scan using tenk1_unique2 on tenk1 t3
+ Index Cond: (unique2 = t2.thousand)
+(11 rows)
+
+explain (costs off)
+select * from tenk1 t1 left join
+ (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
+ on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten
+where t1.unique1 = 1;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop Left Join
+ -> Index Scan using tenk1_unique1 on tenk1 t1
+ Index Cond: (unique1 = 1)
+ -> Nested Loop
+ Join Filter: ((t1.ten + t2.ten) = t3.ten)
+ -> Bitmap Heap Scan on tenk1 t2
+ Recheck Cond: (t1.hundred = hundred)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = t1.hundred)
+ -> Index Scan using tenk1_unique2 on tenk1 t3
+ Index Cond: (unique2 = t2.thousand)
+(11 rows)
+
+explain (costs off)
+select count(*) from
+ tenk1 a join tenk1 b on a.unique1 = b.unique2
+ left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand
+ join int4_tbl on b.thousand = f1;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop Left Join
+ Join Filter: (a.unique2 = b.unique1)
+ -> Nested Loop
+ -> Nested Loop
+ -> Seq Scan on int4_tbl
+ -> Bitmap Heap Scan on tenk1 b
+ Recheck Cond: (thousand = int4_tbl.f1)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = int4_tbl.f1)
+ -> Index Scan using tenk1_unique1 on tenk1 a
+ Index Cond: (unique1 = b.unique2)
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 c
+ Index Cond: (thousand = a.thousand)
+(14 rows)
+
+select count(*) from
+ tenk1 a join tenk1 b on a.unique1 = b.unique2
+ left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand
+ join int4_tbl on b.thousand = f1;
+ count
+-------
+ 10
+(1 row)
+
+explain (costs off)
+select b.unique1 from
+ tenk1 a join tenk1 b on a.unique1 = b.unique2
+ left join tenk1 c on b.unique1 = 42 and c.thousand = a.thousand
+ join int4_tbl i1 on b.thousand = f1
+ right join int4_tbl i2 on i2.f1 = b.tenthous
+ order by 1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Sort
+ Sort Key: b.unique1
+ -> Nested Loop Left Join
+ -> Seq Scan on int4_tbl i2
+ -> Nested Loop Left Join
+ Join Filter: (b.unique1 = 42)
+ -> Nested Loop
+ -> Nested Loop
+ -> Seq Scan on int4_tbl i1
+ -> Index Scan using tenk1_thous_tenthous on tenk1 b
+ Index Cond: ((thousand = i1.f1) AND (tenthous = i2.f1))
+ -> Index Scan using tenk1_unique1 on tenk1 a
+ Index Cond: (unique1 = b.unique2)
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 c
+ Index Cond: (thousand = a.thousand)
+(15 rows)
+
+select b.unique1 from
+ tenk1 a join tenk1 b on a.unique1 = b.unique2
+ left join tenk1 c on b.unique1 = 42 and c.thousand = a.thousand
+ join int4_tbl i1 on b.thousand = f1
+ right join int4_tbl i2 on i2.f1 = b.tenthous
+ order by 1;
+ unique1
+---------
+ 0
+
+
+
+
+(5 rows)
+
+explain (costs off)
+select * from
+(
+ select unique1, q1, coalesce(unique1, -1) + q1 as fault
+ from int8_tbl left join tenk1 on (q2 = unique2)
+) ss
+where fault = 122
+order by fault;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Nested Loop Left Join
+ Filter: ((COALESCE(tenk1.unique1, '-1'::integer) + int8_tbl.q1) = 122)
+ -> Seq Scan on int8_tbl
+ -> Index Scan using tenk1_unique2 on tenk1
+ Index Cond: (unique2 = int8_tbl.q2)
+(5 rows)
+
+select * from
+(
+ select unique1, q1, coalesce(unique1, -1) + q1 as fault
+ from int8_tbl left join tenk1 on (q2 = unique2)
+) ss
+where fault = 122
+order by fault;
+ unique1 | q1 | fault
+---------+-----+-------
+ | 123 | 122
+(1 row)
+
+explain (costs off)
+select * from
+(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
+left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
+left join unnest(v1ys) as u1(u1y) on u1y = v2y;
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop Left Join
+ -> Values Scan on "*VALUES*"
+ -> Hash Right Join
+ Hash Cond: (u1.u1y = "*VALUES*_1".column2)
+ Filter: ("*VALUES*_1".column1 = "*VALUES*".column1)
+ -> Function Scan on unnest u1
+ -> Hash
+ -> Values Scan on "*VALUES*_1"
+(8 rows)
+
+select * from
+(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
+left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
+left join unnest(v1ys) as u1(u1y) on u1y = v2y;
+ v1x | v1ys | v2x | v2y | u1y
+-----+---------+-----+-----+-----
+ 1 | {10,20} | 1 | 10 | 10
+ 2 | {20,30} | 2 | 20 | 20
+(2 rows)
+
+--
+-- test handling of potential equivalence clauses above outer joins
+--
+explain (costs off)
+select q1, unique2, thousand, hundred
+ from int8_tbl a left join tenk1 b on q1 = unique2
+ where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123);
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ Filter: ((COALESCE(b.thousand, 123) = a.q1) AND (a.q1 = COALESCE(b.hundred, 123)))
+ -> Seq Scan on int8_tbl a
+ -> Index Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 = a.q1)
+(5 rows)
+
+select q1, unique2, thousand, hundred
+ from int8_tbl a left join tenk1 b on q1 = unique2
+ where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123);
+ q1 | unique2 | thousand | hundred
+----+---------+----------+---------
+(0 rows)
+
+explain (costs off)
+select f1, unique2, case when unique2 is null then f1 else 0 end
+ from int4_tbl a left join tenk1 b on f1 = unique2
+ where (case when unique2 is null then f1 else 0 end) = 0;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Nested Loop Left Join
+ Filter: (CASE WHEN (b.unique2 IS NULL) THEN a.f1 ELSE 0 END = 0)
+ -> Seq Scan on int4_tbl a
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 = a.f1)
+(5 rows)
+
+select f1, unique2, case when unique2 is null then f1 else 0 end
+ from int4_tbl a left join tenk1 b on f1 = unique2
+ where (case when unique2 is null then f1 else 0 end) = 0;
+ f1 | unique2 | case
+----+---------+------
+ 0 | 0 | 0
+(1 row)
+
+--
+-- another case with equivalence clauses above outer joins (bug #8591)
+--
+explain (costs off)
+select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
+ from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)
+ where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Nested Loop Left Join
+ Filter: (COALESCE(b.twothousand, a.twothousand) = 44)
+ -> Index Scan using tenk1_unique2 on tenk1 a
+ Index Cond: (unique2 < 10)
+ -> Bitmap Heap Scan on tenk1 b
+ Recheck Cond: (thousand = a.unique1)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = a.unique1)
+ -> Index Scan using tenk1_unique2 on tenk1 c
+ Index Cond: ((unique2 = COALESCE(b.twothousand, a.twothousand)) AND (unique2 = 44))
+(11 rows)
+
+select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
+ from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)
+ where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44;
+ unique1 | unique1 | unique1 | coalesce
+---------+---------+---------+----------
+(0 rows)
+
+--
+-- check handling of join aliases when flattening multiple levels of subquery
+--
+explain (verbose, costs off)
+select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
+ (values (0),(1)) foo1(join_key)
+left join
+ (select join_key, bug_field from
+ (select ss1.join_key, ss1.bug_field from
+ (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
+ ) foo2
+ left join
+ (select unique2 as join_key from tenk1 i2) ss2
+ using (join_key)
+ ) foo3
+using (join_key);
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Nested Loop Left Join
+ Output: "*VALUES*".column1, i1.f1, (666)
+ Join Filter: ("*VALUES*".column1 = i1.f1)
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1
+ -> Materialize
+ Output: i1.f1, (666)
+ -> Nested Loop Left Join
+ Output: i1.f1, 666
+ -> Seq Scan on public.int4_tbl i1
+ Output: i1.f1
+ -> Index Only Scan using tenk1_unique2 on public.tenk1 i2
+ Output: i2.unique2
+ Index Cond: (i2.unique2 = i1.f1)
+(14 rows)
+
+select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
+ (values (0),(1)) foo1(join_key)
+left join
+ (select join_key, bug_field from
+ (select ss1.join_key, ss1.bug_field from
+ (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
+ ) foo2
+ left join
+ (select unique2 as join_key from tenk1 i2) ss2
+ using (join_key)
+ ) foo3
+using (join_key);
+ foo1_id | foo3_id | bug_field
+---------+---------+-----------
+ 0 | 0 | 666
+ 1 | |
+(2 rows)
+
+--
+-- test successful handling of nested outer joins with degenerate join quals
+--
+explain (verbose, costs off)
+select t1.* from
+ text_tbl t1
+ left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
+ left join int8_tbl i8
+ left join (select *, null::int as d2 from int8_tbl i8b2) b2
+ on (i8.q1 = b2.q1)
+ on (b2.d2 = b1.q2)
+ on (t1.f1 = b1.d1)
+ left join int4_tbl i4
+ on (i8.q2 = i4.f1);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Hash Left Join
+ Output: t1.f1
+ Hash Cond: (i8.q2 = i4.f1)
+ -> Nested Loop Left Join
+ Output: t1.f1, i8.q2
+ Join Filter: (t1.f1 = '***'::text)
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ -> Materialize
+ Output: i8.q2
+ -> Hash Right Join
+ Output: i8.q2
+ Hash Cond: ((NULL::integer) = i8b1.q2)
+ -> Hash Join
+ Output: i8.q2, (NULL::integer)
+ Hash Cond: (i8.q1 = i8b2.q1)
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Hash
+ Output: i8b2.q1, (NULL::integer)
+ -> Seq Scan on public.int8_tbl i8b2
+ Output: i8b2.q1, NULL::integer
+ -> Hash
+ Output: i8b1.q2
+ -> Seq Scan on public.int8_tbl i8b1
+ Output: i8b1.q2
+ -> Hash
+ Output: i4.f1
+ -> Seq Scan on public.int4_tbl i4
+ Output: i4.f1
+(30 rows)
+
+select t1.* from
+ text_tbl t1
+ left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
+ left join int8_tbl i8
+ left join (select *, null::int as d2 from int8_tbl i8b2) b2
+ on (i8.q1 = b2.q1)
+ on (b2.d2 = b1.q2)
+ on (t1.f1 = b1.d1)
+ left join int4_tbl i4
+ on (i8.q2 = i4.f1);
+ f1
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+explain (verbose, costs off)
+select t1.* from
+ text_tbl t1
+ left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
+ left join int8_tbl i8
+ left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2
+ on (i8.q1 = b2.q1)
+ on (b2.d2 = b1.q2)
+ on (t1.f1 = b1.d1)
+ left join int4_tbl i4
+ on (i8.q2 = i4.f1);
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Hash Left Join
+ Output: t1.f1
+ Hash Cond: (i8.q2 = i4.f1)
+ -> Nested Loop Left Join
+ Output: t1.f1, i8.q2
+ Join Filter: (t1.f1 = '***'::text)
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ -> Materialize
+ Output: i8.q2
+ -> Hash Right Join
+ Output: i8.q2
+ Hash Cond: ((NULL::integer) = i8b1.q2)
+ -> Hash Right Join
+ Output: i8.q2, (NULL::integer)
+ Hash Cond: (i8b2.q1 = i8.q1)
+ -> Nested Loop
+ Output: i8b2.q1, NULL::integer
+ -> Seq Scan on public.int8_tbl i8b2
+ Output: i8b2.q1, i8b2.q2
+ -> Materialize
+ -> Seq Scan on public.int4_tbl i4b2
+ -> Hash
+ Output: i8.q1, i8.q2
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Hash
+ Output: i8b1.q2
+ -> Seq Scan on public.int8_tbl i8b1
+ Output: i8b1.q2
+ -> Hash
+ Output: i4.f1
+ -> Seq Scan on public.int4_tbl i4
+ Output: i4.f1
+(34 rows)
+
+select t1.* from
+ text_tbl t1
+ left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
+ left join int8_tbl i8
+ left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2
+ on (i8.q1 = b2.q1)
+ on (b2.d2 = b1.q2)
+ on (t1.f1 = b1.d1)
+ left join int4_tbl i4
+ on (i8.q2 = i4.f1);
+ f1
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+explain (verbose, costs off)
+select t1.* from
+ text_tbl t1
+ left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
+ left join int8_tbl i8
+ left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2
+ where q1 = f1) b2
+ on (i8.q1 = b2.q1)
+ on (b2.d2 = b1.q2)
+ on (t1.f1 = b1.d1)
+ left join int4_tbl i4
+ on (i8.q2 = i4.f1);
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Hash Left Join
+ Output: t1.f1
+ Hash Cond: (i8.q2 = i4.f1)
+ -> Nested Loop Left Join
+ Output: t1.f1, i8.q2
+ Join Filter: (t1.f1 = '***'::text)
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ -> Materialize
+ Output: i8.q2
+ -> Hash Right Join
+ Output: i8.q2
+ Hash Cond: ((NULL::integer) = i8b1.q2)
+ -> Hash Right Join
+ Output: i8.q2, (NULL::integer)
+ Hash Cond: (i8b2.q1 = i8.q1)
+ -> Hash Join
+ Output: i8b2.q1, NULL::integer
+ Hash Cond: (i8b2.q1 = i4b2.f1)
+ -> Seq Scan on public.int8_tbl i8b2
+ Output: i8b2.q1, i8b2.q2
+ -> Hash
+ Output: i4b2.f1
+ -> Seq Scan on public.int4_tbl i4b2
+ Output: i4b2.f1
+ -> Hash
+ Output: i8.q1, i8.q2
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Hash
+ Output: i8b1.q2
+ -> Seq Scan on public.int8_tbl i8b1
+ Output: i8b1.q2
+ -> Hash
+ Output: i4.f1
+ -> Seq Scan on public.int4_tbl i4
+ Output: i4.f1
+(37 rows)
+
+select t1.* from
+ text_tbl t1
+ left join (select *, '***'::text as d1 from int8_tbl i8b1) b1
+ left join int8_tbl i8
+ left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2
+ where q1 = f1) b2
+ on (i8.q1 = b2.q1)
+ on (b2.d2 = b1.q2)
+ on (t1.f1 = b1.d1)
+ left join int4_tbl i4
+ on (i8.q2 = i4.f1);
+ f1
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+explain (verbose, costs off)
+select * from
+ text_tbl t1
+ inner join int8_tbl i8
+ on i8.q2 = 456
+ right join text_tbl t2
+ on t1.f1 = 'doh!'
+ left join int4_tbl i4
+ on i8.q1 = i4.f1;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop Left Join
+ Output: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1
+ -> Seq Scan on public.text_tbl t2
+ Output: t2.f1
+ -> Materialize
+ Output: i8.q1, i8.q2, i4.f1, t1.f1
+ -> Nested Loop
+ Output: i8.q1, i8.q2, i4.f1, t1.f1
+ -> Nested Loop Left Join
+ Output: i8.q1, i8.q2, i4.f1
+ Join Filter: (i8.q1 = i4.f1)
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ Filter: (i8.q2 = 456)
+ -> Seq Scan on public.int4_tbl i4
+ Output: i4.f1
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ Filter: (t1.f1 = 'doh!'::text)
+(19 rows)
+
+select * from
+ text_tbl t1
+ inner join int8_tbl i8
+ on i8.q2 = 456
+ right join text_tbl t2
+ on t1.f1 = 'doh!'
+ left join int4_tbl i4
+ on i8.q1 = i4.f1;
+ f1 | q1 | q2 | f1 | f1
+------+-----+-----+-------------------+----
+ doh! | 123 | 456 | doh! |
+ doh! | 123 | 456 | hi de ho neighbor |
+(2 rows)
+
+--
+-- test for appropriate join order in the presence of lateral references
+--
+explain (verbose, costs off)
+select * from
+ text_tbl t1
+ left join int8_tbl i8
+ on i8.q2 = 123,
+ lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
+where t1.f1 = ss.f1;
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
+ Join Filter: (t1.f1 = t2.f1)
+ -> Nested Loop Left Join
+ Output: t1.f1, i8.q1, i8.q2
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ -> Materialize
+ Output: i8.q1, i8.q2
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ Filter: (i8.q2 = 123)
+ -> Memoize
+ Output: (i8.q1), t2.f1
+ Cache Key: i8.q1
+ Cache Mode: binary
+ -> Limit
+ Output: (i8.q1), t2.f1
+ -> Seq Scan on public.text_tbl t2
+ Output: i8.q1, t2.f1
+(20 rows)
+
+select * from
+ text_tbl t1
+ left join int8_tbl i8
+ on i8.q2 = 123,
+ lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
+where t1.f1 = ss.f1;
+ f1 | q1 | q2 | q1 | f1
+------+------------------+-----+------------------+------
+ doh! | 4567890123456789 | 123 | 4567890123456789 | doh!
+(1 row)
+
+explain (verbose, costs off)
+select * from
+ text_tbl t1
+ left join int8_tbl i8
+ on i8.q2 = 123,
+ lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
+ lateral (select ss1.* from text_tbl t3 limit 1) as ss2
+where t1.f1 = ss2.f1;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Nested Loop
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
+ Join Filter: (t1.f1 = (t2.f1))
+ -> Nested Loop
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
+ -> Nested Loop Left Join
+ Output: t1.f1, i8.q1, i8.q2
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ -> Materialize
+ Output: i8.q1, i8.q2
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ Filter: (i8.q2 = 123)
+ -> Memoize
+ Output: (i8.q1), t2.f1
+ Cache Key: i8.q1
+ Cache Mode: binary
+ -> Limit
+ Output: (i8.q1), t2.f1
+ -> Seq Scan on public.text_tbl t2
+ Output: i8.q1, t2.f1
+ -> Memoize
+ Output: ((i8.q1)), (t2.f1)
+ Cache Key: (i8.q1), t2.f1
+ Cache Mode: binary
+ -> Limit
+ Output: ((i8.q1)), (t2.f1)
+ -> Seq Scan on public.text_tbl t3
+ Output: (i8.q1), t2.f1
+(30 rows)
+
+select * from
+ text_tbl t1
+ left join int8_tbl i8
+ on i8.q2 = 123,
+ lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
+ lateral (select ss1.* from text_tbl t3 limit 1) as ss2
+where t1.f1 = ss2.f1;
+ f1 | q1 | q2 | q1 | f1 | q1 | f1
+------+------------------+-----+------------------+------+------------------+------
+ doh! | 4567890123456789 | 123 | 4567890123456789 | doh! | 4567890123456789 | doh!
+(1 row)
+
+explain (verbose, costs off)
+select 1 from
+ text_tbl as tt1
+ inner join text_tbl as tt2 on (tt1.f1 = 'foo')
+ left join text_tbl as tt3 on (tt3.f1 = 'foo')
+ left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
+ lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
+where tt1.f1 = ss1.c0;
+ QUERY PLAN
+----------------------------------------------------------
+ Nested Loop
+ Output: 1
+ -> Nested Loop Left Join
+ Output: tt1.f1, tt4.f1
+ -> Nested Loop
+ Output: tt1.f1
+ -> Seq Scan on public.text_tbl tt1
+ Output: tt1.f1
+ Filter: (tt1.f1 = 'foo'::text)
+ -> Seq Scan on public.text_tbl tt2
+ Output: tt2.f1
+ -> Materialize
+ Output: tt4.f1
+ -> Nested Loop Left Join
+ Output: tt4.f1
+ Join Filter: (tt3.f1 = tt4.f1)
+ -> Seq Scan on public.text_tbl tt3
+ Output: tt3.f1
+ Filter: (tt3.f1 = 'foo'::text)
+ -> Seq Scan on public.text_tbl tt4
+ Output: tt4.f1
+ Filter: (tt4.f1 = 'foo'::text)
+ -> Memoize
+ Output: ss1.c0
+ Cache Key: tt4.f1
+ Cache Mode: binary
+ -> Subquery Scan on ss1
+ Output: ss1.c0
+ Filter: (ss1.c0 = 'foo'::text)
+ -> Limit
+ Output: (tt4.f1)
+ -> Seq Scan on public.text_tbl tt5
+ Output: tt4.f1
+(33 rows)
+
+select 1 from
+ text_tbl as tt1
+ inner join text_tbl as tt2 on (tt1.f1 = 'foo')
+ left join text_tbl as tt3 on (tt3.f1 = 'foo')
+ left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
+ lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
+where tt1.f1 = ss1.c0;
+ ?column?
+----------
+(0 rows)
+
+--
+-- check a case in which a PlaceHolderVar forces join order
+--
+explain (verbose, costs off)
+select ss2.* from
+ int4_tbl i41
+ left join int8_tbl i8
+ join (select i42.f1 as c1, i43.f1 as c2, 42 as c3
+ from int4_tbl i42, int4_tbl i43) ss1
+ on i8.q1 = ss1.c2
+ on i41.f1 = ss1.c1,
+ lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
+where ss1.c2 = 0;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Nested Loop
+ Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
+ -> Hash Join
+ Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42
+ Hash Cond: (i41.f1 = i42.f1)
+ -> Nested Loop
+ Output: i8.q1, i8.q2, i43.f1, i41.f1
+ -> Nested Loop
+ Output: i8.q1, i8.q2, i43.f1
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ Filter: (i8.q1 = 0)
+ -> Seq Scan on public.int4_tbl i43
+ Output: i43.f1
+ Filter: (i43.f1 = 0)
+ -> Seq Scan on public.int4_tbl i41
+ Output: i41.f1
+ -> Hash
+ Output: i42.f1
+ -> Seq Scan on public.int4_tbl i42
+ Output: i42.f1
+ -> Limit
+ Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
+ -> Seq Scan on public.text_tbl
+ Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42)
+(25 rows)
+
+select ss2.* from
+ int4_tbl i41
+ left join int8_tbl i8
+ join (select i42.f1 as c1, i43.f1 as c2, 42 as c3
+ from int4_tbl i42, int4_tbl i43) ss1
+ on i8.q1 = ss1.c2
+ on i41.f1 = ss1.c1,
+ lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
+where ss1.c2 = 0;
+ f1 | q1 | q2 | c1 | c2 | c3
+----+----+----+----+----+----
+(0 rows)
+
+--
+-- test successful handling of full join underneath left join (bug #14105)
+--
+explain (costs off)
+select * from
+ (select 1 as id) as xx
+ left join
+ (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
+ on (xx.id = coalesce(yy.id));
+ QUERY PLAN
+---------------------------------------
+ Nested Loop Left Join
+ -> Result
+ -> Hash Full Join
+ Hash Cond: (a1.unique1 = (1))
+ Filter: (1 = COALESCE((1)))
+ -> Seq Scan on tenk1 a1
+ -> Hash
+ -> Result
+(8 rows)
+
+select * from
+ (select 1 as id) as xx
+ left join
+ (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
+ on (xx.id = coalesce(yy.id));
+ id | unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | id
+----+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+----
+ 1 | 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx | 1
+(1 row)
+
+--
+-- test ability to push constants through outer join clauses
+--
+explain (costs off)
+ select * from int4_tbl a left join tenk1 b on f1 = unique2 where f1 = 0;
+ QUERY PLAN
+-------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: (a.f1 = b.unique2)
+ -> Seq Scan on int4_tbl a
+ Filter: (f1 = 0)
+ -> Index Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 = 0)
+(6 rows)
+
+explain (costs off)
+ select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42;
+ QUERY PLAN
+-------------------------------------------------
+ Merge Full Join
+ Merge Cond: (a.unique2 = b.unique2)
+ -> Index Scan using tenk1_unique2 on tenk1 a
+ Index Cond: (unique2 = 42)
+ -> Index Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 = 42)
+(6 rows)
+
+--
+-- test that quals attached to an outer join have correct semantics,
+-- specifically that they don't re-use expressions computed below the join;
+-- we force a mergejoin so that coalesce(b.q1, 1) appears as a join input
+--
+set enable_hashjoin to off;
+set enable_nestloop to off;
+explain (verbose, costs off)
+ select a.q2, b.q1
+ from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1)
+ where coalesce(b.q1, 1) > 0;
+ QUERY PLAN
+---------------------------------------------------------
+ Merge Left Join
+ Output: a.q2, b.q1
+ Merge Cond: (a.q2 = (COALESCE(b.q1, '1'::bigint)))
+ Filter: (COALESCE(b.q1, '1'::bigint) > 0)
+ -> Sort
+ Output: a.q2
+ Sort Key: a.q2
+ -> Seq Scan on public.int8_tbl a
+ Output: a.q2
+ -> Sort
+ Output: b.q1, (COALESCE(b.q1, '1'::bigint))
+ Sort Key: (COALESCE(b.q1, '1'::bigint))
+ -> Seq Scan on public.int8_tbl b
+ Output: b.q1, COALESCE(b.q1, '1'::bigint)
+(14 rows)
+
+select a.q2, b.q1
+ from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1)
+ where coalesce(b.q1, 1) > 0;
+ q2 | q1
+-------------------+------------------
+ -4567890123456789 |
+ 123 | 123
+ 123 | 123
+ 456 |
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+(10 rows)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+--
+-- test join removal
+--
+begin;
+CREATE TEMP TABLE a (id int PRIMARY KEY, b_id int);
+CREATE TEMP TABLE b (id int PRIMARY KEY, c_id int);
+CREATE TEMP TABLE c (id int PRIMARY KEY);
+CREATE TEMP TABLE d (a int, b int);
+INSERT INTO a VALUES (0, 0), (1, NULL);
+INSERT INTO b VALUES (0, 0), (1, NULL);
+INSERT INTO c VALUES (0), (1);
+INSERT INTO d VALUES (1,3), (2,2), (3,1);
+-- all three cases should be optimizable into a simple seqscan
+explain (costs off) SELECT a.* FROM a LEFT JOIN b ON a.b_id = b.id;
+ QUERY PLAN
+---------------
+ Seq Scan on a
+(1 row)
+
+explain (costs off) SELECT b.* FROM b LEFT JOIN c ON b.c_id = c.id;
+ QUERY PLAN
+---------------
+ Seq Scan on b
+(1 row)
+
+explain (costs off)
+ SELECT a.* FROM a LEFT JOIN (b left join c on b.c_id = c.id)
+ ON (a.b_id = b.id);
+ QUERY PLAN
+---------------
+ Seq Scan on a
+(1 row)
+
+-- check optimization of outer join within another special join
+explain (costs off)
+select id from a where id in (
+ select b.id from b left join c on b.id = c.id
+);
+ QUERY PLAN
+----------------------------
+ Hash Join
+ Hash Cond: (a.id = b.id)
+ -> Seq Scan on a
+ -> Hash
+ -> Seq Scan on b
+(5 rows)
+
+-- check that join removal works for a left join when joining a subquery
+-- that is guaranteed to be unique by its GROUP BY clause
+explain (costs off)
+select d.* from d left join (select * from b group by b.id, b.c_id) s
+ on d.a = s.id and d.b = s.c_id;
+ QUERY PLAN
+---------------
+ Seq Scan on d
+(1 row)
+
+-- similarly, but keying off a DISTINCT clause
+explain (costs off)
+select d.* from d left join (select distinct * from b) s
+ on d.a = s.id and d.b = s.c_id;
+ QUERY PLAN
+---------------
+ Seq Scan on d
+(1 row)
+
+-- join removal is not possible when the GROUP BY contains a column that is
+-- not in the join condition. (Note: as of 9.6, we notice that b.id is a
+-- primary key and so drop b.c_id from the GROUP BY of the resulting plan;
+-- but this happens too late for join removal in the outer plan level.)
+explain (costs off)
+select d.* from d left join (select * from b group by b.id, b.c_id) s
+ on d.a = s.id;
+ QUERY PLAN
+------------------------------------------
+ Merge Right Join
+ Merge Cond: (b.id = d.a)
+ -> Group
+ Group Key: b.id
+ -> Index Scan using b_pkey on b
+ -> Sort
+ Sort Key: d.a
+ -> Seq Scan on d
+(8 rows)
+
+-- similarly, but keying off a DISTINCT clause
+explain (costs off)
+select d.* from d left join (select distinct * from b) s
+ on d.a = s.id;
+ QUERY PLAN
+--------------------------------------
+ Merge Right Join
+ Merge Cond: (b.id = d.a)
+ -> Unique
+ -> Sort
+ Sort Key: b.id, b.c_id
+ -> Seq Scan on b
+ -> Sort
+ Sort Key: d.a
+ -> Seq Scan on d
+(9 rows)
+
+-- check join removal works when uniqueness of the join condition is enforced
+-- by a UNION
+explain (costs off)
+select d.* from d left join (select id from a union select id from b) s
+ on d.a = s.id;
+ QUERY PLAN
+---------------
+ Seq Scan on d
+(1 row)
+
+-- check join removal with a cross-type comparison operator
+explain (costs off)
+select i8.* from int8_tbl i8 left join (select f1 from int4_tbl group by f1) i4
+ on i8.q1 = i4.f1;
+ QUERY PLAN
+-------------------------
+ Seq Scan on int8_tbl i8
+(1 row)
+
+-- check join removal with lateral references
+explain (costs off)
+select 1 from (select a.id FROM a left join b on a.b_id = b.id) q,
+ lateral generate_series(1, q.id) gs(i) where q.id = gs.i;
+ QUERY PLAN
+-------------------------------------------
+ Nested Loop
+ -> Seq Scan on a
+ -> Function Scan on generate_series gs
+ Filter: (a.id = i)
+(4 rows)
+
+rollback;
+create temp table parent (k int primary key, pd int);
+create temp table child (k int unique, cd int);
+insert into parent values (1, 10), (2, 20), (3, 30);
+insert into child values (1, 100), (4, 400);
+-- this case is optimizable
+select p.* from parent p left join child c on (p.k = c.k);
+ k | pd
+---+----
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+explain (costs off)
+ select p.* from parent p left join child c on (p.k = c.k);
+ QUERY PLAN
+----------------------
+ Seq Scan on parent p
+(1 row)
+
+-- this case is not
+select p.*, linked from parent p
+ left join (select c.*, true as linked from child c) as ss
+ on (p.k = ss.k);
+ k | pd | linked
+---+----+--------
+ 1 | 10 | t
+ 2 | 20 |
+ 3 | 30 |
+(3 rows)
+
+explain (costs off)
+ select p.*, linked from parent p
+ left join (select c.*, true as linked from child c) as ss
+ on (p.k = ss.k);
+ QUERY PLAN
+---------------------------------
+ Hash Left Join
+ Hash Cond: (p.k = c.k)
+ -> Seq Scan on parent p
+ -> Hash
+ -> Seq Scan on child c
+(5 rows)
+
+-- check for a 9.0rc1 bug: join removal breaks pseudoconstant qual handling
+select p.* from
+ parent p left join child c on (p.k = c.k)
+ where p.k = 1 and p.k = 2;
+ k | pd
+---+----
+(0 rows)
+
+explain (costs off)
+select p.* from
+ parent p left join child c on (p.k = c.k)
+ where p.k = 1 and p.k = 2;
+ QUERY PLAN
+------------------------------------------------
+ Result
+ One-Time Filter: false
+ -> Index Scan using parent_pkey on parent p
+ Index Cond: (k = 1)
+(4 rows)
+
+select p.* from
+ (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k
+ where p.k = 1 and p.k = 2;
+ k | pd
+---+----
+(0 rows)
+
+explain (costs off)
+select p.* from
+ (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k
+ where p.k = 1 and p.k = 2;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- bug 5255: this is not optimizable by join removal
+begin;
+CREATE TEMP TABLE a (id int PRIMARY KEY);
+CREATE TEMP TABLE b (id int PRIMARY KEY, a_id int);
+INSERT INTO a VALUES (0), (1);
+INSERT INTO b VALUES (0, 0), (1, NULL);
+SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0);
+ id | a_id | id
+----+------+----
+ 1 | |
+(1 row)
+
+SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0);
+ id | a_id
+----+------
+ 1 |
+(1 row)
+
+rollback;
+-- another join removal bug: this is not optimizable, either
+begin;
+create temp table innertab (id int8 primary key, dat1 int8);
+insert into innertab values(123, 42);
+SELECT * FROM
+ (SELECT 1 AS x) ss1
+ LEFT JOIN
+ (SELECT q1, q2, COALESCE(dat1, q1) AS y
+ FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss2
+ ON true;
+ x | q1 | q2 | y
+---+------------------+-------------------+------------------
+ 1 | 123 | 456 | 123
+ 1 | 123 | 4567890123456789 | 123
+ 1 | 4567890123456789 | 123 | 42
+ 1 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 1 | 4567890123456789 | -4567890123456789 | 4567890123456789
+(5 rows)
+
+rollback;
+-- another join removal bug: we must clean up correctly when removing a PHV
+begin;
+create temp table uniquetbl (f1 text unique);
+explain (costs off)
+select t1.* from
+ uniquetbl as t1
+ left join (select *, '***'::text as d1 from uniquetbl) t2
+ on t1.f1 = t2.f1
+ left join uniquetbl t3
+ on t2.d1 = t3.f1;
+ QUERY PLAN
+--------------------------
+ Seq Scan on uniquetbl t1
+(1 row)
+
+explain (costs off)
+select t0.*
+from
+ text_tbl t0
+ left join
+ (select case t1.ten when 0 then 'doh!'::text else null::text end as case1,
+ t1.stringu2
+ from tenk1 t1
+ join int4_tbl i4 ON i4.f1 = t1.unique2
+ left join uniquetbl u1 ON u1.f1 = t1.string4) ss
+ on t0.f1 = ss.case1
+where ss.stringu2 !~* ss.case1;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END = t0.f1)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl i4
+ -> Index Scan using tenk1_unique2 on tenk1 t1
+ Index Cond: (unique2 = i4.f1)
+ Filter: (stringu2 !~* CASE ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END)
+ -> Materialize
+ -> Seq Scan on text_tbl t0
+(9 rows)
+
+select t0.*
+from
+ text_tbl t0
+ left join
+ (select case t1.ten when 0 then 'doh!'::text else null::text end as case1,
+ t1.stringu2
+ from tenk1 t1
+ join int4_tbl i4 ON i4.f1 = t1.unique2
+ left join uniquetbl u1 ON u1.f1 = t1.string4) ss
+ on t0.f1 = ss.case1
+where ss.stringu2 !~* ss.case1;
+ f1
+------
+ doh!
+(1 row)
+
+rollback;
+-- test case to expose miscomputation of required relid set for a PHV
+explain (verbose, costs off)
+select i8.*, ss.v, t.unique2
+ from int8_tbl i8
+ left join int4_tbl i4 on i4.f1 = 1
+ left join lateral (select i4.f1 + 1 as v) as ss on true
+ left join tenk1 t on t.unique2 = ss.v
+where q2 = 456;
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop Left Join
+ Output: i8.q1, i8.q2, ((i4.f1 + 1)), t.unique2
+ -> Nested Loop Left Join
+ Output: i8.q1, i8.q2, (i4.f1 + 1)
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ Filter: (i8.q2 = 456)
+ -> Seq Scan on public.int4_tbl i4
+ Output: i4.f1
+ Filter: (i4.f1 = 1)
+ -> Index Only Scan using tenk1_unique2 on public.tenk1 t
+ Output: t.unique2
+ Index Cond: (t.unique2 = ((i4.f1 + 1)))
+(13 rows)
+
+select i8.*, ss.v, t.unique2
+ from int8_tbl i8
+ left join int4_tbl i4 on i4.f1 = 1
+ left join lateral (select i4.f1 + 1 as v) as ss on true
+ left join tenk1 t on t.unique2 = ss.v
+where q2 = 456;
+ q1 | q2 | v | unique2
+-----+-----+---+---------
+ 123 | 456 | |
+(1 row)
+
+-- and check a related issue where we miscompute required relids for
+-- a PHV that's been translated to a child rel
+create temp table parttbl (a integer primary key) partition by range (a);
+create temp table parttbl1 partition of parttbl for values from (1) to (100);
+insert into parttbl values (11), (12);
+explain (costs off)
+select * from
+ (select *, 12 as phv from parttbl) as ss
+ right join int4_tbl on true
+where ss.a = ss.phv and f1 = 0;
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on int4_tbl
+ Filter: (f1 = 0)
+ -> Seq Scan on parttbl1 parttbl
+ Filter: (a = 12)
+(5 rows)
+
+select * from
+ (select *, 12 as phv from parttbl) as ss
+ right join int4_tbl on true
+where ss.a = ss.phv and f1 = 0;
+ a | phv | f1
+----+-----+----
+ 12 | 12 | 0
+(1 row)
+
+-- bug #8444: we've historically allowed duplicate aliases within aliased JOINs
+select * from
+ int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = f1; -- error
+ERROR: column reference "f1" is ambiguous
+LINE 2: ..._tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = f1;
+ ^
+select * from
+ int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1; -- error
+ERROR: invalid reference to FROM-clause entry for table "y"
+LINE 2: ...bl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1;
+ ^
+HINT: There is an entry for table "y", but it cannot be referenced from this part of the query.
+select * from
+ int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on q1 = f1; -- ok
+ q1 | q2 | f1 | ff
+----+----+----+----
+(0 rows)
+
+--
+-- Test hints given on incorrect column references are useful
+--
+select t1.uunique1 from
+ tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t1" suggestion
+ERROR: column t1.uunique1 does not exist
+LINE 1: select t1.uunique1 from
+ ^
+HINT: Perhaps you meant to reference the column "t1.unique1".
+select t2.uunique1 from
+ tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t2" suggestion
+ERROR: column t2.uunique1 does not exist
+LINE 1: select t2.uunique1 from
+ ^
+HINT: Perhaps you meant to reference the column "t2.unique1".
+select uunique1 from
+ tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, suggest both at once
+ERROR: column "uunique1" does not exist
+LINE 1: select uunique1 from
+ ^
+HINT: Perhaps you meant to reference the column "t1.unique1" or the column "t2.unique1".
+--
+-- Take care to reference the correct RTE
+--
+select atts.relid::regclass, s.* from pg_stats s join
+ pg_attribute a on s.attname = a.attname and s.tablename =
+ a.attrelid::regclass::text join (select unnest(indkey) attnum,
+ indexrelid from pg_index i) atts on atts.attnum = a.attnum where
+ schemaname != 'pg_catalog';
+ERROR: column atts.relid does not exist
+LINE 1: select atts.relid::regclass, s.* from pg_stats s join
+ ^
+--
+-- Test LATERAL
+--
+select unique2, x.*
+from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
+ unique2 | f1
+---------+----
+ 9998 | 0
+(1 row)
+
+explain (costs off)
+ select unique2, x.*
+ from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
+ QUERY PLAN
+-------------------------------------------------
+ Nested Loop
+ -> Seq Scan on int4_tbl b
+ -> Index Scan using tenk1_unique1 on tenk1 a
+ Index Cond: (unique1 = b.f1)
+(4 rows)
+
+select unique2, x.*
+from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss;
+ unique2 | f1
+---------+----
+ 9998 | 0
+(1 row)
+
+explain (costs off)
+ select unique2, x.*
+ from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss;
+ QUERY PLAN
+-----------------------------------------------
+ Nested Loop
+ -> Seq Scan on int4_tbl x
+ -> Index Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = x.f1)
+(4 rows)
+
+explain (costs off)
+ select unique2, x.*
+ from int4_tbl x cross join lateral (select unique2 from tenk1 where f1 = unique1) ss;
+ QUERY PLAN
+-----------------------------------------------
+ Nested Loop
+ -> Seq Scan on int4_tbl x
+ -> Index Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = x.f1)
+(4 rows)
+
+select unique2, x.*
+from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true;
+ unique2 | f1
+---------+-------------
+ 9998 | 0
+ | 123456
+ | -123456
+ | 2147483647
+ | -2147483647
+(5 rows)
+
+explain (costs off)
+ select unique2, x.*
+ from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true;
+ QUERY PLAN
+-----------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on int4_tbl x
+ -> Index Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = x.f1)
+(4 rows)
+
+-- check scoping of lateral versus parent references
+-- the first of these should return int8_tbl.q2, the second int8_tbl.q1
+select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
+ q1 | q2 | r
+------------------+-------------------+-------------------
+ 123 | 456 | 456
+ 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | -4567890123456789
+(5 rows)
+
+select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl;
+ q1 | q2 | r
+------------------+-------------------+------------------
+ 123 | 456 | 123
+ 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789
+(5 rows)
+
+-- lateral with function in FROM
+select count(*) from tenk1 a, lateral generate_series(1,two) g;
+ count
+-------
+ 5000
+(1 row)
+
+explain (costs off)
+ select count(*) from tenk1 a, lateral generate_series(1,two) g;
+ QUERY PLAN
+------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Seq Scan on tenk1 a
+ -> Memoize
+ Cache Key: a.two
+ Cache Mode: binary
+ -> Function Scan on generate_series g
+(7 rows)
+
+explain (costs off)
+ select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
+ QUERY PLAN
+------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Seq Scan on tenk1 a
+ -> Memoize
+ Cache Key: a.two
+ Cache Mode: binary
+ -> Function Scan on generate_series g
+(7 rows)
+
+-- don't need the explicit LATERAL keyword for functions
+explain (costs off)
+ select count(*) from tenk1 a, generate_series(1,two) g;
+ QUERY PLAN
+------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Seq Scan on tenk1 a
+ -> Memoize
+ Cache Key: a.two
+ Cache Mode: binary
+ -> Function Scan on generate_series g
+(7 rows)
+
+-- lateral with UNION ALL subselect
+explain (costs off)
+ select * from generate_series(100,200) g,
+ lateral (select * from int8_tbl a where g = q1 union all
+ select * from int8_tbl b where g = q2) ss;
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Function Scan on generate_series g
+ -> Append
+ -> Seq Scan on int8_tbl a
+ Filter: (g.g = q1)
+ -> Seq Scan on int8_tbl b
+ Filter: (g.g = q2)
+(7 rows)
+
+select * from generate_series(100,200) g,
+ lateral (select * from int8_tbl a where g = q1 union all
+ select * from int8_tbl b where g = q2) ss;
+ g | q1 | q2
+-----+------------------+------------------
+ 123 | 123 | 456
+ 123 | 123 | 4567890123456789
+ 123 | 4567890123456789 | 123
+(3 rows)
+
+-- lateral with VALUES
+explain (costs off)
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ QUERY PLAN
+------------------------------------------------------------
+ Aggregate
+ -> Merge Join
+ Merge Cond: (a.unique1 = b.unique2)
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+(5 rows)
+
+select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ count
+-------
+ 10000
+(1 row)
+
+-- lateral with VALUES, no flattening possible
+explain (costs off)
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ -> Values Scan on "*VALUES*"
+ -> Memoize
+ Cache Key: "*VALUES*".column1
+ Cache Mode: logical
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 = "*VALUES*".column1)
+(10 rows)
+
+select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
+ count
+-------
+ 10000
+(1 row)
+
+-- lateral injecting a strange outer join condition
+explain (costs off)
+ select * from int8_tbl a,
+ int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+ on x.q2 = ss.z
+ order by a.q1, a.q2, x.q1, x.q2, ss.z;
+ QUERY PLAN
+------------------------------------------------
+ Sort
+ Sort Key: a.q1, a.q2, x.q1, x.q2, (a.q1)
+ -> Nested Loop
+ -> Seq Scan on int8_tbl a
+ -> Hash Right Join
+ Hash Cond: ((a.q1) = x.q2)
+ -> Seq Scan on int4_tbl y
+ -> Hash
+ -> Seq Scan on int8_tbl x
+(9 rows)
+
+select * from int8_tbl a,
+ int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+ on x.q2 = ss.z
+ order by a.q1, a.q2, x.q1, x.q2, ss.z;
+ q1 | q2 | q1 | q2 | z
+------------------+-------------------+------------------+-------------------+------------------
+ 123 | 456 | 123 | 456 |
+ 123 | 456 | 123 | 4567890123456789 |
+ 123 | 456 | 4567890123456789 | -4567890123456789 |
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 4567890123456789 |
+ 123 | 4567890123456789 | 123 | 456 |
+ 123 | 4567890123456789 | 123 | 4567890123456789 |
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
+ 4567890123456789 | -4567890123456789 | 123 | 456 |
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 456 |
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | 123 | 4567890123456789 | 123 |
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 456 |
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+(57 rows)
+
+-- lateral reference to a join alias variable
+select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
+ lateral (select x) ss2(y);
+ x | f1 | y
+---+----+---
+ 0 | 0 | 0
+(1 row)
+
+select * from (select f1 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
+ lateral (values(x)) ss2(y);
+ x | f1 | y
+-------------+-------------+-------------
+ 0 | 0 | 0
+ 123456 | 123456 | 123456
+ -123456 | -123456 | -123456
+ 2147483647 | 2147483647 | 2147483647
+ -2147483647 | -2147483647 | -2147483647
+(5 rows)
+
+select * from ((select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1) j,
+ lateral (select x) ss2(y);
+ x | f1 | y
+---+----+---
+ 0 | 0 | 0
+(1 row)
+
+-- lateral references requiring pullup
+select * from (values(1)) x(lb),
+ lateral generate_series(lb,4) x4;
+ lb | x4
+----+----
+ 1 | 1
+ 1 | 2
+ 1 | 3
+ 1 | 4
+(4 rows)
+
+select * from (select f1/1000000000 from int4_tbl) x(lb),
+ lateral generate_series(lb,4) x4;
+ lb | x4
+----+----
+ 0 | 0
+ 0 | 1
+ 0 | 2
+ 0 | 3
+ 0 | 4
+ 0 | 0
+ 0 | 1
+ 0 | 2
+ 0 | 3
+ 0 | 4
+ 0 | 0
+ 0 | 1
+ 0 | 2
+ 0 | 3
+ 0 | 4
+ 2 | 2
+ 2 | 3
+ 2 | 4
+ -2 | -2
+ -2 | -1
+ -2 | 0
+ -2 | 1
+ -2 | 2
+ -2 | 3
+ -2 | 4
+(25 rows)
+
+select * from (values(1)) x(lb),
+ lateral (values(lb)) y(lbcopy);
+ lb | lbcopy
+----+--------
+ 1 | 1
+(1 row)
+
+select * from (values(1)) x(lb),
+ lateral (select lb from int4_tbl) y(lbcopy);
+ lb | lbcopy
+----+--------
+ 1 | 1
+ 1 | 1
+ 1 | 1
+ 1 | 1
+ 1 | 1
+(5 rows)
+
+select * from
+ int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+ lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2);
+ q1 | q2 | q1 | q2 | xq1 | yq1 | yq2
+------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
+ 123 | 456 | | | 123 | |
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789 | | | 4567890123456789 | |
+(10 rows)
+
+select * from
+ int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+ lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
+ q1 | q2 | q1 | q2 | xq1 | yq1 | yq2
+------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
+ 123 | 456 | | | 123 | |
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789 | | | 4567890123456789 | |
+(10 rows)
+
+select x.* from
+ int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
+ lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 123 | 4567890123456789
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(10 rows)
+
+select v.* from
+ (int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1)
+ left join int4_tbl z on z.f1 = x.q2,
+ lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
+ vx | vy
+-------------------+-------------------
+ 123 |
+ 456 |
+ 123 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 123
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 123 | 456
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 |
+ -4567890123456789 |
+(20 rows)
+
+select v.* from
+ (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
+ left join int4_tbl z on z.f1 = x.q2,
+ lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
+ vx | vy
+-------------------+-------------------
+ 4567890123456789 | 123
+ 123 | 456
+ 4567890123456789 | 123
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ 123 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ 123 |
+ 456 |
+ 4567890123456789 |
+ -4567890123456789 |
+(20 rows)
+
+select v.* from
+ (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
+ left join int4_tbl z on z.f1 = x.q2,
+ lateral (select x.q1,y.q1 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy);
+ vx | vy
+-------------------+-------------------
+ 4567890123456789 | 123
+ 123 | 456
+ 4567890123456789 | 123
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ 123 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ 123 |
+ 456 |
+ 4567890123456789 |
+ -4567890123456789 |
+(20 rows)
+
+explain (verbose, costs off)
+select * from
+ int8_tbl a left join
+ lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;
+ QUERY PLAN
+------------------------------------------
+ Nested Loop Left Join
+ Output: a.q1, a.q2, b.q1, b.q2, (a.q2)
+ -> Seq Scan on public.int8_tbl a
+ Output: a.q1, a.q2
+ -> Seq Scan on public.int8_tbl b
+ Output: b.q1, b.q2, a.q2
+ Filter: (a.q2 = b.q1)
+(7 rows)
+
+select * from
+ int8_tbl a left join
+ lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;
+ q1 | q2 | q1 | q2 | x
+------------------+-------------------+------------------+-------------------+------------------
+ 123 | 456 | | |
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 456 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | | |
+(10 rows)
+
+explain (verbose, costs off)
+select * from
+ int8_tbl a left join
+ lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Nested Loop Left Join
+ Output: a.q1, a.q2, b.q1, b.q2, (COALESCE(a.q2, '42'::bigint))
+ -> Seq Scan on public.int8_tbl a
+ Output: a.q1, a.q2
+ -> Seq Scan on public.int8_tbl b
+ Output: b.q1, b.q2, COALESCE(a.q2, '42'::bigint)
+ Filter: (a.q2 = b.q1)
+(7 rows)
+
+select * from
+ int8_tbl a left join
+ lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
+ q1 | q2 | q1 | q2 | x
+------------------+-------------------+------------------+-------------------+------------------
+ 123 | 456 | | |
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 456 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | | |
+(10 rows)
+
+-- lateral can result in join conditions appearing below their
+-- real semantic level
+explain (verbose, costs off)
+select * from int4_tbl i left join
+ lateral (select * from int2_tbl j where i.f1 = j.f1) k on true;
+ QUERY PLAN
+-------------------------------------------
+ Hash Left Join
+ Output: i.f1, j.f1
+ Hash Cond: (i.f1 = j.f1)
+ -> Seq Scan on public.int4_tbl i
+ Output: i.f1
+ -> Hash
+ Output: j.f1
+ -> Seq Scan on public.int2_tbl j
+ Output: j.f1
+(9 rows)
+
+select * from int4_tbl i left join
+ lateral (select * from int2_tbl j where i.f1 = j.f1) k on true;
+ f1 | f1
+-------------+----
+ 0 | 0
+ 123456 |
+ -123456 |
+ 2147483647 |
+ -2147483647 |
+(5 rows)
+
+explain (verbose, costs off)
+select * from int4_tbl i left join
+ lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
+ QUERY PLAN
+-------------------------------------
+ Nested Loop Left Join
+ Output: i.f1, (COALESCE(i.*))
+ -> Seq Scan on public.int4_tbl i
+ Output: i.f1, i.*
+ -> Seq Scan on public.int2_tbl j
+ Output: j.f1, COALESCE(i.*)
+ Filter: (i.f1 = j.f1)
+(7 rows)
+
+select * from int4_tbl i left join
+ lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
+ f1 | coalesce
+-------------+----------
+ 0 | (0)
+ 123456 |
+ -123456 |
+ 2147483647 |
+ -2147483647 |
+(5 rows)
+
+explain (verbose, costs off)
+select * from int4_tbl a,
+ lateral (
+ select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2)
+ ) ss;
+ QUERY PLAN
+-------------------------------------------------
+ Nested Loop
+ Output: a.f1, b.f1, c.q1, c.q2
+ -> Seq Scan on public.int4_tbl a
+ Output: a.f1
+ -> Hash Left Join
+ Output: b.f1, c.q1, c.q2
+ Hash Cond: (b.f1 = c.q1)
+ -> Seq Scan on public.int4_tbl b
+ Output: b.f1
+ -> Hash
+ Output: c.q1, c.q2
+ -> Seq Scan on public.int8_tbl c
+ Output: c.q1, c.q2
+ Filter: (a.f1 = c.q2)
+(14 rows)
+
+select * from int4_tbl a,
+ lateral (
+ select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2)
+ ) ss;
+ f1 | f1 | q1 | q2
+-------------+-------------+----+----
+ 0 | 0 | |
+ 0 | 123456 | |
+ 0 | -123456 | |
+ 0 | 2147483647 | |
+ 0 | -2147483647 | |
+ 123456 | 0 | |
+ 123456 | 123456 | |
+ 123456 | -123456 | |
+ 123456 | 2147483647 | |
+ 123456 | -2147483647 | |
+ -123456 | 0 | |
+ -123456 | 123456 | |
+ -123456 | -123456 | |
+ -123456 | 2147483647 | |
+ -123456 | -2147483647 | |
+ 2147483647 | 0 | |
+ 2147483647 | 123456 | |
+ 2147483647 | -123456 | |
+ 2147483647 | 2147483647 | |
+ 2147483647 | -2147483647 | |
+ -2147483647 | 0 | |
+ -2147483647 | 123456 | |
+ -2147483647 | -123456 | |
+ -2147483647 | 2147483647 | |
+ -2147483647 | -2147483647 | |
+(25 rows)
+
+-- lateral reference in a PlaceHolderVar evaluated at join level
+explain (verbose, costs off)
+select * from
+ int8_tbl a left join lateral
+ (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
+ int8_tbl b cross join int8_tbl c) ss
+ on a.q2 = ss.bq1;
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop Left Join
+ Output: a.q1, a.q2, b.q1, c.q1, (LEAST(a.q1, b.q1, c.q1))
+ -> Seq Scan on public.int8_tbl a
+ Output: a.q1, a.q2
+ -> Nested Loop
+ Output: b.q1, c.q1, LEAST(a.q1, b.q1, c.q1)
+ -> Seq Scan on public.int8_tbl b
+ Output: b.q1, b.q2
+ Filter: (a.q2 = b.q1)
+ -> Seq Scan on public.int8_tbl c
+ Output: c.q1, c.q2
+(11 rows)
+
+select * from
+ int8_tbl a left join lateral
+ (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
+ int8_tbl b cross join int8_tbl c) ss
+ on a.q2 = ss.bq1;
+ q1 | q2 | bq1 | cq1 | least
+------------------+-------------------+------------------+------------------+------------------
+ 123 | 456 | | |
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 123 | 123
+ 4567890123456789 | 123 | 123 | 123 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 123 | 123
+ 4567890123456789 | 123 | 123 | 123 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | | |
+(42 rows)
+
+-- case requiring nested PlaceHolderVars
+explain (verbose, costs off)
+select * from
+ int8_tbl c left join (
+ int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1
+ on a.q2 = ss1.q1
+ cross join
+ lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
+ ) on c.q2 = ss2.q1,
+ lateral (select ss2.y offset 0) ss3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)), ((COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)))
+ -> Hash Right Join
+ Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
+ Hash Cond: (d.q1 = c.q2)
+ -> Nested Loop
+ Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
+ -> Hash Left Join
+ Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint))
+ Hash Cond: (a.q2 = b.q1)
+ -> Seq Scan on public.int8_tbl a
+ Output: a.q1, a.q2
+ -> Hash
+ Output: b.q1, (COALESCE(b.q2, '42'::bigint))
+ -> Seq Scan on public.int8_tbl b
+ Output: b.q1, COALESCE(b.q2, '42'::bigint)
+ -> Seq Scan on public.int8_tbl d
+ Output: d.q1, COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)
+ -> Hash
+ Output: c.q1, c.q2
+ -> Seq Scan on public.int8_tbl c
+ Output: c.q1, c.q2
+ -> Result
+ Output: (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))
+(24 rows)
+
+-- case that breaks the old ph_may_need optimization
+explain (verbose, costs off)
+select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
+ int8_tbl c left join (
+ int8_tbl a left join
+ (select q1, coalesce(q2,f1) as x from int8_tbl b, int4_tbl b2
+ where q1 < f1) ss1
+ on a.q2 = ss1.q1
+ cross join
+ lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
+ ) on c.q2 = ss2.q1,
+ lateral (select * from int4_tbl i where ss2.y > f1) ss3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1
+ Join Filter: ((COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) > i.f1)
+ -> Hash Right Join
+ Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2))
+ Hash Cond: (d.q1 = c.q2)
+ -> Nested Loop
+ Output: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2))
+ -> Hash Right Join
+ Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint))
+ Hash Cond: (b.q1 = a.q2)
+ -> Nested Loop
+ Output: b.q1, COALESCE(b.q2, (b2.f1)::bigint)
+ Join Filter: (b.q1 < b2.f1)
+ -> Seq Scan on public.int8_tbl b
+ Output: b.q1, b.q2
+ -> Materialize
+ Output: b2.f1
+ -> Seq Scan on public.int4_tbl b2
+ Output: b2.f1
+ -> Hash
+ Output: a.q1, a.q2
+ -> Seq Scan on public.int8_tbl a
+ Output: a.q1, a.q2
+ -> Seq Scan on public.int8_tbl d
+ Output: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)
+ -> Hash
+ Output: c.q1, c.q2
+ -> Seq Scan on public.int8_tbl c
+ Output: c.q1, c.q2
+ -> Materialize
+ Output: i.f1
+ -> Seq Scan on public.int4_tbl i
+ Output: i.f1
+(34 rows)
+
+-- check processing of postponed quals (bug #9041)
+explain (verbose, costs off)
+select * from
+ (select 1 as x offset 0) x cross join (select 2 as y offset 0) y
+ left join lateral (
+ select * from (select 3 as z offset 0) z where z.z = x.x
+ ) zz on zz.z = y.y;
+ QUERY PLAN
+----------------------------------------------
+ Nested Loop Left Join
+ Output: (1), (2), (3)
+ Join Filter: (((3) = (1)) AND ((3) = (2)))
+ -> Nested Loop
+ Output: (1), (2)
+ -> Result
+ Output: 1
+ -> Result
+ Output: 2
+ -> Result
+ Output: 3
+(11 rows)
+
+-- check dummy rels with lateral references (bug #15694)
+explain (verbose, costs off)
+select * from int8_tbl i8 left join lateral
+ (select *, i8.q2 from int4_tbl where false) ss on true;
+ QUERY PLAN
+--------------------------------------
+ Nested Loop Left Join
+ Output: i8.q1, i8.q2, f1, (i8.q2)
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Result
+ Output: f1, i8.q2
+ One-Time Filter: false
+(7 rows)
+
+explain (verbose, costs off)
+select * from int8_tbl i8 left join lateral
+ (select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true;
+ QUERY PLAN
+-----------------------------------------
+ Nested Loop Left Join
+ Output: i8.q1, i8.q2, f1, f1, (i8.q2)
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Result
+ Output: f1, f1, i8.q2
+ One-Time Filter: false
+(7 rows)
+
+-- check handling of nested appendrels inside LATERAL
+select * from
+ ((select 2 as v) union all (select 3 as v)) as q1
+ cross join lateral
+ ((select * from
+ ((select 4 as v) union all (select 5 as v)) as q3)
+ union all
+ (select q1.v)
+ ) as q2;
+ v | v
+---+---
+ 2 | 4
+ 2 | 5
+ 2 | 2
+ 3 | 4
+ 3 | 5
+ 3 | 3
+(6 rows)
+
+-- check the number of columns specified
+SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d);
+ERROR: join expression "ss" has 3 columns available but 4 columns specified
+-- check we don't try to do a unique-ified semijoin with LATERAL
+explain (verbose, costs off)
+select * from
+ (values (0,9998), (1,1000)) v(id,x),
+ lateral (select f1 from int4_tbl
+ where f1 = any (select unique1 from tenk1
+ where unique2 = v.x offset 0)) ss;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Nested Loop
+ Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, "*VALUES*".column2
+ -> Nested Loop Semi Join
+ Output: int4_tbl.f1
+ Join Filter: (int4_tbl.f1 = tenk1.unique1)
+ -> Seq Scan on public.int4_tbl
+ Output: int4_tbl.f1
+ -> Materialize
+ Output: tenk1.unique1
+ -> Index Scan using tenk1_unique2 on public.tenk1
+ Output: tenk1.unique1
+ Index Cond: (tenk1.unique2 = "*VALUES*".column2)
+(14 rows)
+
+select * from
+ (values (0,9998), (1,1000)) v(id,x),
+ lateral (select f1 from int4_tbl
+ where f1 = any (select unique1 from tenk1
+ where unique2 = v.x offset 0)) ss;
+ id | x | f1
+----+------+----
+ 0 | 9998 | 0
+(1 row)
+
+-- check proper extParam/allParam handling (this isn't exactly a LATERAL issue,
+-- but we can make the test case much more compact with LATERAL)
+explain (verbose, costs off)
+select * from (values (0), (1)) v(id),
+lateral (select * from int8_tbl t1,
+ lateral (select * from
+ (select * from int8_tbl t2
+ where q1 = any (select q2 from int8_tbl t3
+ where q2 = (select greatest(t1.q1,t2.q2))
+ and (select v.id=0)) offset 0) ss2) ss
+ where t1.q1 = ss.q2) ss0;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Nested Loop
+ Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
+ -> Seq Scan on public.int8_tbl t1
+ Output: t1.q1, t1.q2
+ -> Nested Loop
+ Output: "*VALUES*".column1, ss2.q1, ss2.q2
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1
+ -> Subquery Scan on ss2
+ Output: ss2.q1, ss2.q2
+ Filter: (t1.q1 = ss2.q2)
+ -> Seq Scan on public.int8_tbl t2
+ Output: t2.q1, t2.q2
+ Filter: (SubPlan 3)
+ SubPlan 3
+ -> Result
+ Output: t3.q2
+ One-Time Filter: $4
+ InitPlan 1 (returns $2)
+ -> Result
+ Output: GREATEST($0, t2.q2)
+ InitPlan 2 (returns $4)
+ -> Result
+ Output: ($3 = 0)
+ -> Seq Scan on public.int8_tbl t3
+ Output: t3.q1, t3.q2
+ Filter: (t3.q2 = $2)
+(27 rows)
+
+select * from (values (0), (1)) v(id),
+lateral (select * from int8_tbl t1,
+ lateral (select * from
+ (select * from int8_tbl t2
+ where q1 = any (select q2 from int8_tbl t3
+ where q2 = (select greatest(t1.q1,t2.q2))
+ and (select v.id=0)) offset 0) ss2) ss
+ where t1.q1 = ss.q2) ss0;
+ id | q1 | q2 | q1 | q2
+----+------------------+-------------------+------------------+------------------
+ 0 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 0 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 0 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789
+(3 rows)
+
+-- test some error cases where LATERAL should have been used but wasn't
+select f1,g from int4_tbl a, (select f1 as g) ss;
+ERROR: column "f1" does not exist
+LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
+ ^
+HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+select f1,g from int4_tbl a, (select a.f1 as g) ss;
+ERROR: invalid reference to FROM-clause entry for table "a"
+LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
+ ^
+HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
+select f1,g from int4_tbl a cross join (select f1 as g) ss;
+ERROR: column "f1" does not exist
+LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
+ ^
+HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
+ERROR: invalid reference to FROM-clause entry for table "a"
+LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
+ ^
+HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
+-- SQL:2008 says the left table is in scope but illegal to access here
+select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
+ERROR: invalid reference to FROM-clause entry for table "a"
+LINE 1: ... int4_tbl a right join lateral generate_series(0, a.f1) g on...
+ ^
+DETAIL: The combining JOIN type must be INNER or LEFT for a LATERAL reference.
+select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
+ERROR: invalid reference to FROM-clause entry for table "a"
+LINE 1: ...m int4_tbl a full join lateral generate_series(0, a.f1) g on...
+ ^
+DETAIL: The combining JOIN type must be INNER or LEFT for a LATERAL reference.
+-- check we complain about ambiguous table references
+select * from
+ int8_tbl x cross join (int4_tbl x cross join lateral (select x.f1) ss);
+ERROR: table reference "x" is ambiguous
+LINE 2: ...cross join (int4_tbl x cross join lateral (select x.f1) ss);
+ ^
+-- LATERAL can be used to put an aggregate into the FROM clause of its query
+select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
+ERROR: aggregate functions are not allowed in FROM clause of their own query level
+LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
+ ^
+-- check behavior of LATERAL in UPDATE/DELETE
+create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
+-- error, can't do this:
+update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
+ERROR: column "x1" does not exist
+LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
+ ^
+HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
+ERROR: invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
+ ^
+HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+-- can't do it even with LATERAL:
+update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
+ERROR: invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...= f1 from lateral (select * from int4_tbl where f1 = x1) ss;
+ ^
+HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+-- we might in future allow something like this, but for now it's an error:
+update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
+ERROR: table name "xx1" specified more than once
+-- also errors:
+delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
+ERROR: column "x1" does not exist
+LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
+ ^
+HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
+ERROR: invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
+ ^
+HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
+ERROR: invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
+ ^
+HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+--
+-- test LATERAL reference propagation down a multi-level inheritance hierarchy
+-- produced for a multi-level partitioned table hierarchy.
+--
+create table join_pt1 (a int, b int, c varchar) partition by range(a);
+create table join_pt1p1 partition of join_pt1 for values from (0) to (100) partition by range(b);
+create table join_pt1p2 partition of join_pt1 for values from (100) to (200);
+create table join_pt1p1p1 partition of join_pt1p1 for values from (0) to (100);
+insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y');
+create table join_ut1 (a int, b int, c varchar);
+insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z');
+explain (verbose, costs off)
+select t1.b, ss.phv from join_ut1 t1 left join lateral
+ (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
+ from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
+ on t1.a = ss.t2a order by t1.a;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Sort
+ Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
+ Sort Key: t1.a
+ -> Nested Loop Left Join
+ Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
+ -> Seq Scan on public.join_ut1 t1
+ Output: t1.a, t1.b, t1.c
+ -> Hash Join
+ Output: t2.a, LEAST(t1.a, t2.a, t3.a)
+ Hash Cond: (t3.b = t2.a)
+ -> Seq Scan on public.join_ut1 t3
+ Output: t3.a, t3.b, t3.c
+ -> Hash
+ Output: t2.a
+ -> Append
+ -> Seq Scan on public.join_pt1p1p1 t2_1
+ Output: t2_1.a
+ Filter: (t1.a = t2_1.a)
+ -> Seq Scan on public.join_pt1p2 t2_2
+ Output: t2_2.a
+ Filter: (t1.a = t2_2.a)
+(21 rows)
+
+select t1.b, ss.phv from join_ut1 t1 left join lateral
+ (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
+ from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
+ on t1.a = ss.t2a order by t1.a;
+ b | phv
+-----+-----
+ 2 |
+ 101 | 101
+(2 rows)
+
+drop table join_pt1;
+drop table join_ut1;
+--
+-- test estimation behavior with multi-column foreign key and constant qual
+--
+begin;
+create table fkest (x integer, x10 integer, x10b integer, x100 integer);
+insert into fkest select x, x/10, x/10, x/100 from generate_series(1,1000) x;
+create unique index on fkest(x, x10, x100);
+analyze fkest;
+explain (costs off)
+select * from fkest f1
+ join fkest f2 on (f1.x = f2.x and f1.x10 = f2.x10b and f1.x100 = f2.x100)
+ join fkest f3 on f1.x = f3.x
+ where f1.x100 = 2;
+ QUERY PLAN
+-----------------------------------------------------------
+ Nested Loop
+ -> Hash Join
+ Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10))
+ -> Seq Scan on fkest f2
+ Filter: (x100 = 2)
+ -> Hash
+ -> Seq Scan on fkest f1
+ Filter: (x100 = 2)
+ -> Index Scan using fkest_x_x10_x100_idx on fkest f3
+ Index Cond: (x = f1.x)
+(10 rows)
+
+alter table fkest add constraint fk
+ foreign key (x, x10b, x100) references fkest (x, x10, x100);
+explain (costs off)
+select * from fkest f1
+ join fkest f2 on (f1.x = f2.x and f1.x10 = f2.x10b and f1.x100 = f2.x100)
+ join fkest f3 on f1.x = f3.x
+ where f1.x100 = 2;
+ QUERY PLAN
+-----------------------------------------------------
+ Hash Join
+ Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10))
+ -> Hash Join
+ Hash Cond: (f3.x = f2.x)
+ -> Seq Scan on fkest f3
+ -> Hash
+ -> Seq Scan on fkest f2
+ Filter: (x100 = 2)
+ -> Hash
+ -> Seq Scan on fkest f1
+ Filter: (x100 = 2)
+(11 rows)
+
+rollback;
+--
+-- test that foreign key join estimation performs sanely for outer joins
+--
+begin;
+create table fkest (a int, b int, c int unique, primary key(a,b));
+create table fkest1 (a int, b int, primary key(a,b));
+insert into fkest select x/10, x%10, x from generate_series(1,1000) x;
+insert into fkest1 select x/10, x%10 from generate_series(1,1000) x;
+alter table fkest1
+ add constraint fkest1_a_b_fkey foreign key (a,b) references fkest;
+analyze fkest;
+analyze fkest1;
+explain (costs off)
+select *
+from fkest f
+ left join fkest1 f1 on f.a = f1.a and f.b = f1.b
+ left join fkest1 f2 on f.a = f2.a and f.b = f2.b
+ left join fkest1 f3 on f.a = f3.a and f.b = f3.b
+where f.c = 1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Nested Loop Left Join
+ -> Nested Loop Left Join
+ -> Index Scan using fkest_c_key on fkest f
+ Index Cond: (c = 1)
+ -> Index Only Scan using fkest1_pkey on fkest1 f1
+ Index Cond: ((a = f.a) AND (b = f.b))
+ -> Index Only Scan using fkest1_pkey on fkest1 f2
+ Index Cond: ((a = f.a) AND (b = f.b))
+ -> Index Only Scan using fkest1_pkey on fkest1 f3
+ Index Cond: ((a = f.a) AND (b = f.b))
+(11 rows)
+
+rollback;
+--
+-- test planner's ability to mark joins as unique
+--
+create table j1 (id int primary key);
+create table j2 (id int primary key);
+create table j3 (id int);
+insert into j1 values(1),(2),(3);
+insert into j2 values(1),(2),(3);
+insert into j3 values(1),(1);
+analyze j1;
+analyze j2;
+analyze j3;
+-- ensure join is properly marked as unique
+explain (verbose, costs off)
+select * from j1 inner join j2 on j1.id = j2.id;
+ QUERY PLAN
+-----------------------------------
+ Hash Join
+ Output: j1.id, j2.id
+ Inner Unique: true
+ Hash Cond: (j1.id = j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Hash
+ Output: j2.id
+ -> Seq Scan on public.j2
+ Output: j2.id
+(10 rows)
+
+-- ensure join is not unique when not an equi-join
+explain (verbose, costs off)
+select * from j1 inner join j2 on j1.id > j2.id;
+ QUERY PLAN
+-----------------------------------
+ Nested Loop
+ Output: j1.id, j2.id
+ Join Filter: (j1.id > j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Materialize
+ Output: j2.id
+ -> Seq Scan on public.j2
+ Output: j2.id
+(9 rows)
+
+-- ensure non-unique rel is not chosen as inner
+explain (verbose, costs off)
+select * from j1 inner join j3 on j1.id = j3.id;
+ QUERY PLAN
+-----------------------------------
+ Hash Join
+ Output: j1.id, j3.id
+ Inner Unique: true
+ Hash Cond: (j3.id = j1.id)
+ -> Seq Scan on public.j3
+ Output: j3.id
+ -> Hash
+ Output: j1.id
+ -> Seq Scan on public.j1
+ Output: j1.id
+(10 rows)
+
+-- ensure left join is marked as unique
+explain (verbose, costs off)
+select * from j1 left join j2 on j1.id = j2.id;
+ QUERY PLAN
+-----------------------------------
+ Hash Left Join
+ Output: j1.id, j2.id
+ Inner Unique: true
+ Hash Cond: (j1.id = j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Hash
+ Output: j2.id
+ -> Seq Scan on public.j2
+ Output: j2.id
+(10 rows)
+
+-- ensure right join is marked as unique
+explain (verbose, costs off)
+select * from j1 right join j2 on j1.id = j2.id;
+ QUERY PLAN
+-----------------------------------
+ Hash Left Join
+ Output: j1.id, j2.id
+ Inner Unique: true
+ Hash Cond: (j2.id = j1.id)
+ -> Seq Scan on public.j2
+ Output: j2.id
+ -> Hash
+ Output: j1.id
+ -> Seq Scan on public.j1
+ Output: j1.id
+(10 rows)
+
+-- ensure full join is marked as unique
+explain (verbose, costs off)
+select * from j1 full join j2 on j1.id = j2.id;
+ QUERY PLAN
+-----------------------------------
+ Hash Full Join
+ Output: j1.id, j2.id
+ Inner Unique: true
+ Hash Cond: (j1.id = j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Hash
+ Output: j2.id
+ -> Seq Scan on public.j2
+ Output: j2.id
+(10 rows)
+
+-- a clauseless (cross) join can't be unique
+explain (verbose, costs off)
+select * from j1 cross join j2;
+ QUERY PLAN
+-----------------------------------
+ Nested Loop
+ Output: j1.id, j2.id
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Materialize
+ Output: j2.id
+ -> Seq Scan on public.j2
+ Output: j2.id
+(8 rows)
+
+-- ensure a natural join is marked as unique
+explain (verbose, costs off)
+select * from j1 natural join j2;
+ QUERY PLAN
+-----------------------------------
+ Hash Join
+ Output: j1.id
+ Inner Unique: true
+ Hash Cond: (j1.id = j2.id)
+ -> Seq Scan on public.j1
+ Output: j1.id
+ -> Hash
+ Output: j2.id
+ -> Seq Scan on public.j2
+ Output: j2.id
+(10 rows)
+
+-- ensure a distinct clause allows the inner to become unique
+explain (verbose, costs off)
+select * from j1
+inner join (select distinct id from j3) j3 on j1.id = j3.id;
+ QUERY PLAN
+-----------------------------------------
+ Nested Loop
+ Output: j1.id, j3.id
+ Inner Unique: true
+ Join Filter: (j1.id = j3.id)
+ -> Unique
+ Output: j3.id
+ -> Sort
+ Output: j3.id
+ Sort Key: j3.id
+ -> Seq Scan on public.j3
+ Output: j3.id
+ -> Seq Scan on public.j1
+ Output: j1.id
+(13 rows)
+
+-- ensure group by clause allows the inner to become unique
+explain (verbose, costs off)
+select * from j1
+inner join (select id from j3 group by id) j3 on j1.id = j3.id;
+ QUERY PLAN
+-----------------------------------------
+ Nested Loop
+ Output: j1.id, j3.id
+ Inner Unique: true
+ Join Filter: (j1.id = j3.id)
+ -> Group
+ Output: j3.id
+ Group Key: j3.id
+ -> Sort
+ Output: j3.id
+ Sort Key: j3.id
+ -> Seq Scan on public.j3
+ Output: j3.id
+ -> Seq Scan on public.j1
+ Output: j1.id
+(14 rows)
+
+drop table j1;
+drop table j2;
+drop table j3;
+-- test more complex permutations of unique joins
+create table j1 (id1 int, id2 int, primary key(id1,id2));
+create table j2 (id1 int, id2 int, primary key(id1,id2));
+create table j3 (id1 int, id2 int, primary key(id1,id2));
+insert into j1 values(1,1),(1,2);
+insert into j2 values(1,1);
+insert into j3 values(1,1);
+analyze j1;
+analyze j2;
+analyze j3;
+-- ensure there's no unique join when not all columns which are part of the
+-- unique index are seen in the join clause
+explain (verbose, costs off)
+select * from j1
+inner join j2 on j1.id1 = j2.id1;
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ Output: j1.id1, j1.id2, j2.id1, j2.id2
+ Join Filter: (j1.id1 = j2.id1)
+ -> Seq Scan on public.j2
+ Output: j2.id1, j2.id2
+ -> Seq Scan on public.j1
+ Output: j1.id1, j1.id2
+(7 rows)
+
+-- ensure proper unique detection with multiple join quals
+explain (verbose, costs off)
+select * from j1
+inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2;
+ QUERY PLAN
+----------------------------------------------------------
+ Nested Loop
+ Output: j1.id1, j1.id2, j2.id1, j2.id2
+ Inner Unique: true
+ Join Filter: ((j1.id1 = j2.id1) AND (j1.id2 = j2.id2))
+ -> Seq Scan on public.j2
+ Output: j2.id1, j2.id2
+ -> Seq Scan on public.j1
+ Output: j1.id1, j1.id2
+(8 rows)
+
+-- ensure we don't detect the join to be unique when quals are not part of the
+-- join condition
+explain (verbose, costs off)
+select * from j1
+inner join j2 on j1.id1 = j2.id1 where j1.id2 = 1;
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ Output: j1.id1, j1.id2, j2.id1, j2.id2
+ Join Filter: (j1.id1 = j2.id1)
+ -> Seq Scan on public.j1
+ Output: j1.id1, j1.id2
+ Filter: (j1.id2 = 1)
+ -> Seq Scan on public.j2
+ Output: j2.id1, j2.id2
+(8 rows)
+
+-- as above, but for left joins.
+explain (verbose, costs off)
+select * from j1
+left join j2 on j1.id1 = j2.id1 where j1.id2 = 1;
+ QUERY PLAN
+------------------------------------------
+ Nested Loop Left Join
+ Output: j1.id1, j1.id2, j2.id1, j2.id2
+ Join Filter: (j1.id1 = j2.id1)
+ -> Seq Scan on public.j1
+ Output: j1.id1, j1.id2
+ Filter: (j1.id2 = 1)
+ -> Seq Scan on public.j2
+ Output: j2.id1, j2.id2
+(8 rows)
+
+-- validate logic in merge joins which skips mark and restore.
+-- it should only do this if all quals which were used to detect the unique
+-- are present as join quals, and not plain quals.
+set enable_nestloop to 0;
+set enable_hashjoin to 0;
+set enable_sort to 0;
+-- create indexes that will be preferred over the PKs to perform the join
+create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1;
+create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1;
+-- need an additional row in j2, if we want j2_id1_idx to be preferred
+insert into j2 values(1,2);
+analyze j2;
+explain (costs off) select * from j1
+inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
+where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
+ QUERY PLAN
+-----------------------------------------
+ Merge Join
+ Merge Cond: (j1.id1 = j2.id1)
+ Join Filter: (j1.id2 = j2.id2)
+ -> Index Scan using j1_id1_idx on j1
+ -> Index Scan using j2_id1_idx on j2
+(5 rows)
+
+select * from j1
+inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
+where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
+ id1 | id2 | id1 | id2
+-----+-----+-----+-----
+ 1 | 1 | 1 | 1
+ 1 | 2 | 1 | 2
+(2 rows)
+
+-- Exercise array keys mark/restore B-Tree code
+explain (costs off) select * from j1
+inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
+where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]);
+ QUERY PLAN
+----------------------------------------------------
+ Merge Join
+ Merge Cond: (j1.id1 = j2.id1)
+ Join Filter: (j1.id2 = j2.id2)
+ -> Index Scan using j1_id1_idx on j1
+ -> Index Scan using j2_id1_idx on j2
+ Index Cond: (id1 = ANY ('{1}'::integer[]))
+(6 rows)
+
+select * from j1
+inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
+where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]);
+ id1 | id2 | id1 | id2
+-----+-----+-----+-----
+ 1 | 1 | 1 | 1
+ 1 | 2 | 1 | 2
+(2 rows)
+
+-- Exercise array keys "find extreme element" B-Tree code
+explain (costs off) select * from j1
+inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
+where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 >= any (array[1,5]);
+ QUERY PLAN
+-------------------------------------------------------
+ Merge Join
+ Merge Cond: (j1.id1 = j2.id1)
+ Join Filter: (j1.id2 = j2.id2)
+ -> Index Scan using j1_id1_idx on j1
+ -> Index Only Scan using j2_pkey on j2
+ Index Cond: (id1 >= ANY ('{1,5}'::integer[]))
+ Filter: ((id1 % 1000) = 1)
+(7 rows)
+
+select * from j1
+inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
+where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 >= any (array[1,5]);
+ id1 | id2 | id1 | id2
+-----+-----+-----+-----
+ 1 | 1 | 1 | 1
+ 1 | 2 | 1 | 2
+(2 rows)
+
+reset enable_nestloop;
+reset enable_hashjoin;
+reset enable_sort;
+drop table j1;
+drop table j2;
+drop table j3;
+-- check that semijoin inner is not seen as unique for a portion of the outerrel
+explain (verbose, costs off)
+select t1.unique1, t2.hundred
+from onek t1, tenk1 t2
+where exists (select 1 from tenk1 t3
+ where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred)
+ and t1.unique1 < 1;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Nested Loop
+ Output: t1.unique1, t2.hundred
+ -> Hash Join
+ Output: t1.unique1, t3.tenthous
+ Hash Cond: (t3.thousand = t1.unique1)
+ -> HashAggregate
+ Output: t3.thousand, t3.tenthous
+ Group Key: t3.thousand, t3.tenthous
+ -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3
+ Output: t3.thousand, t3.tenthous
+ -> Hash
+ Output: t1.unique1
+ -> Index Only Scan using onek_unique1 on public.onek t1
+ Output: t1.unique1
+ Index Cond: (t1.unique1 < 1)
+ -> Index Only Scan using tenk1_hundred on public.tenk1 t2
+ Output: t2.hundred
+ Index Cond: (t2.hundred = t3.tenthous)
+(18 rows)
+
+-- ... unless it actually is unique
+create table j3 as select unique1, tenthous from onek;
+vacuum analyze j3;
+create unique index on j3(unique1, tenthous);
+explain (verbose, costs off)
+select t1.unique1, t2.hundred
+from onek t1, tenk1 t2
+where exists (select 1 from j3
+ where j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred)
+ and t1.unique1 < 1;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Nested Loop
+ Output: t1.unique1, t2.hundred
+ -> Nested Loop
+ Output: t1.unique1, j3.tenthous
+ -> Index Only Scan using onek_unique1 on public.onek t1
+ Output: t1.unique1
+ Index Cond: (t1.unique1 < 1)
+ -> Index Only Scan using j3_unique1_tenthous_idx on public.j3
+ Output: j3.unique1, j3.tenthous
+ Index Cond: (j3.unique1 = t1.unique1)
+ -> Index Only Scan using tenk1_hundred on public.tenk1 t2
+ Output: t2.hundred
+ Index Cond: (t2.hundred = j3.tenthous)
+(13 rows)
+
+drop table j3;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-27 04:30:41 +0000