-- -- JOIN -- Test JOIN clauses -- CREATE EXTENSION sqlite_fdw; CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test_core.db'); CREATE FOREIGN TABLE J1_TBL ( i integer, j integer, t text ) SERVER sqlite_svr; CREATE FOREIGN TABLE J2_TBL ( i integer, k integer ) SERVER sqlite_svr; CREATE FOREIGN TABLE tenk1 ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name ) SERVER sqlite_svr; ALTER TABLE tenk1 SET WITH OIDS; CREATE FOREIGN TABLE tenk2 ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name ) SERVER sqlite_svr; CREATE FOREIGN TABLE INT4_TBL(f1 int4 OPTIONS (key 'true')) SERVER sqlite_svr; CREATE FOREIGN TABLE FLOAT8_TBL(f1 float8 OPTIONS (key 'true')) SERVER sqlite_svr; CREATE FOREIGN TABLE INT8_TBL( q1 int8 OPTIONS (key 'true'), q2 int8 OPTIONS (key 'true') ) SERVER sqlite_svr; CREATE FOREIGN TABLE INT2_TBL(f1 int2 OPTIONS (key 'true')) SERVER sqlite_svr; 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); -- -- CORRELATION NAMES -- Make sure that table/column aliases are supported -- before diving into more complex join syntax. -- SELECT '' AS "xxx", * FROM J1_TBL AS tx; xxx | 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 '' AS "xxx", * FROM J1_TBL tx; xxx | 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 '' AS "xxx", * FROM J1_TBL AS t1 (a, b, c); xxx | 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 '' AS "xxx", * FROM J1_TBL t1 (a, b, c); xxx | 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 '' AS "xxx", * FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e); xxx | a | b | c | d | e -----+---+---+-------+---+---- | 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 (99 rows) SELECT '' AS "xxx", t1.a, t2.e FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) WHERE t1.a = t2.d; xxx | 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 '' AS "xxx", * FROM J1_TBL CROSS JOIN J2_TBL; xxx | i | j | t | i | k -----+---+---+-------+---+---- | 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 (99 rows) -- ambiguous column SELECT '' AS "xxx", i, k, t FROM J1_TBL CROSS JOIN J2_TBL; ERROR: column reference "i" is ambiguous LINE 1: SELECT '' AS "xxx", i, k, t ^ -- resolve previous ambiguity by specifying the table name SELECT '' AS "xxx", t1.i, k, t FROM J1_TBL t1 CROSS JOIN J2_TBL t2; xxx | i | k | t -----+---+----+------- | 1 | -1 | one | 1 | 2 | one | 1 | -3 | one | 1 | 4 | one | 1 | -5 | one | 1 | -5 | one | 1 | | one | 1 | | one | 1 | 0 | one | 2 | -1 | two | 2 | 2 | two | 2 | -3 | two | 2 | 4 | two | 2 | -5 | two | 2 | -5 | two | 2 | | two | 2 | | two | 2 | 0 | two | 3 | -1 | three | 3 | 2 | three | 3 | -3 | three | 3 | 4 | three | 3 | -5 | three | 3 | -5 | three | 3 | | three | 3 | | three | 3 | 0 | three | 4 | -1 | four | 4 | 2 | four | 4 | -3 | four | 4 | 4 | four | 4 | -5 | four | 4 | -5 | four | 4 | | four | 4 | | four | 4 | 0 | four | 5 | -1 | five | 5 | 2 | five | 5 | -3 | five | 5 | 4 | five | 5 | -5 | five | 5 | -5 | five | 5 | | five | 5 | | five | 5 | 0 | five | 6 | -1 | six | 6 | 2 | six | 6 | -3 | six | 6 | 4 | six | 6 | -5 | six | 6 | -5 | six | 6 | | six | 6 | | six | 6 | 0 | six | 7 | -1 | seven | 7 | 2 | seven | 7 | -3 | seven | 7 | 4 | seven | 7 | -5 | seven | 7 | -5 | seven | 7 | | seven | 7 | | seven | 7 | 0 | seven | 8 | -1 | eight | 8 | 2 | eight | 8 | -3 | eight | 8 | 4 | eight | 8 | -5 | eight | 8 | -5 | eight | 8 | | eight | 8 | | eight | 8 | 0 | eight | 0 | -1 | zero | 0 | 2 | zero | 0 | -3 | zero | 0 | 4 | zero | 0 | -5 | zero | 0 | -5 | zero | 0 | | zero | 0 | | zero | 0 | 0 | zero | | -1 | null | | 2 | null | | -3 | null | | 4 | null | | -5 | null | | -5 | null | | | null | | | null | | 0 | null | | -1 | zero | | 2 | zero | | -3 | zero | | 4 | zero | | -5 | zero | | -5 | zero | | | zero | | | zero | | 0 | zero (99 rows) SELECT '' AS "xxx", ii, tt, kk FROM (J1_TBL CROSS JOIN J2_TBL) AS tx (ii, jj, tt, ii2, kk); xxx | ii | tt | kk -----+----+-------+---- | 1 | one | -1 | 1 | one | 2 | 1 | one | -3 | 1 | one | 4 | 1 | one | -5 | 1 | one | -5 | 1 | one | | 1 | one | | 1 | one | 0 | 2 | two | -1 | 2 | two | 2 | 2 | two | -3 | 2 | two | 4 | 2 | two | -5 | 2 | two | -5 | 2 | two | | 2 | two | | 2 | two | 0 | 3 | three | -1 | 3 | three | 2 | 3 | three | -3 | 3 | three | 4 | 3 | three | -5 | 3 | three | -5 | 3 | three | | 3 | three | | 3 | three | 0 | 4 | four | -1 | 4 | four | 2 | 4 | four | -3 | 4 | four | 4 | 4 | four | -5 | 4 | four | -5 | 4 | four | | 4 | four | | 4 | four | 0 | 5 | five | -1 | 5 | five | 2 | 5 | five | -3 | 5 | five | 4 | 5 | five | -5 | 5 | five | -5 | 5 | five | | 5 | five | | 5 | five | 0 | 6 | six | -1 | 6 | six | 2 | 6 | six | -3 | 6 | six | 4 | 6 | six | -5 | 6 | six | -5 | 6 | six | | 6 | six | | 6 | six | 0 | 7 | seven | -1 | 7 | seven | 2 | 7 | seven | -3 | 7 | seven | 4 | 7 | seven | -5 | 7 | seven | -5 | 7 | seven | | 7 | seven | | 7 | seven | 0 | 8 | eight | -1 | 8 | eight | 2 | 8 | eight | -3 | 8 | eight | 4 | 8 | eight | -5 | 8 | eight | -5 | 8 | eight | | 8 | eight | | 8 | eight | 0 | 0 | zero | -1 | 0 | zero | 2 | 0 | zero | -3 | 0 | zero | 4 | 0 | zero | -5 | 0 | zero | -5 | 0 | zero | | 0 | zero | | 0 | zero | 0 | | null | -1 | | null | 2 | | null | -3 | | null | 4 | | null | -5 | | null | -5 | | null | | | null | | | null | 0 | | zero | -1 | | zero | 2 | | zero | -3 | | zero | 4 | | zero | -5 | | zero | -5 | | zero | | | zero | | | zero | 0 (99 rows) SELECT '' AS "xxx", 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); xxx | ii | jj | kk -----+----+----+---- | 1 | 4 | -1 | 1 | 4 | 2 | 1 | 4 | -3 | 1 | 4 | 4 | 1 | 4 | -5 | 1 | 4 | -5 | 1 | 4 | | 1 | 4 | | 1 | 4 | 0 | 2 | 3 | -1 | 2 | 3 | 2 | 2 | 3 | -3 | 2 | 3 | 4 | 2 | 3 | -5 | 2 | 3 | -5 | 2 | 3 | | 2 | 3 | | 2 | 3 | 0 | 3 | 2 | -1 | 3 | 2 | 2 | 3 | 2 | -3 | 3 | 2 | 4 | 3 | 2 | -5 | 3 | 2 | -5 | 3 | 2 | | 3 | 2 | | 3 | 2 | 0 | 4 | 1 | -1 | 4 | 1 | 2 | 4 | 1 | -3 | 4 | 1 | 4 | 4 | 1 | -5 | 4 | 1 | -5 | 4 | 1 | | 4 | 1 | | 4 | 1 | 0 | 5 | 0 | -1 | 5 | 0 | 2 | 5 | 0 | -3 | 5 | 0 | 4 | 5 | 0 | -5 | 5 | 0 | -5 | 5 | 0 | | 5 | 0 | | 5 | 0 | 0 | 6 | 6 | -1 | 6 | 6 | 2 | 6 | 6 | -3 | 6 | 6 | 4 | 6 | 6 | -5 | 6 | 6 | -5 | 6 | 6 | | 6 | 6 | | 6 | 6 | 0 | 7 | 7 | -1 | 7 | 7 | 2 | 7 | 7 | -3 | 7 | 7 | 4 | 7 | 7 | -5 | 7 | 7 | -5 | 7 | 7 | | 7 | 7 | | 7 | 7 | 0 | 8 | 8 | -1 | 8 | 8 | 2 | 8 | 8 | -3 | 8 | 8 | 4 | 8 | 8 | -5 | 8 | 8 | -5 | 8 | 8 | | 8 | 8 | | 8 | 8 | 0 | 0 | | -1 | 0 | | 2 | 0 | | -3 | 0 | | 4 | 0 | | -5 | 0 | | -5 | 0 | | | 0 | | | 0 | | 0 | | | -1 | | | 2 | | | -3 | | | 4 | | | -5 | | | -5 | | | | | | | | | 0 | | 0 | -1 | | 0 | 2 | | 0 | -3 | | 0 | 4 | | 0 | -5 | | 0 | -5 | | 0 | | | 0 | | | 0 | 0 (99 rows) SELECT '' AS "xxx", * FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b; xxx | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | | | 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 | | | 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 | | | 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 | | | 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 | | | 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 | | | 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 | | | null | | | 1 | -1 | | | null | | | 2 | 2 | | | null | | | 3 | -3 | | | null | | | 2 | 4 | | | null | | | 5 | -5 | | | null | | | 5 | -5 | | | null | | | 0 | | | | null | | | | | | | null | | | | 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 | 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 | | 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 | | 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 | | 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 | | 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 | | 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 | | 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 | | 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 | | 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 '' AS "xxx", * FROM J1_TBL INNER JOIN J2_TBL USING (i); xxx | 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 '' AS "xxx", * FROM J1_TBL JOIN J2_TBL USING (i); xxx | 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 '' AS "xxx", * FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) ORDER BY a, d; xxx | 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 '' AS "xxx", * FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) ORDER BY b, t1.a; xxx | b | a | c | a -----+---+---+-------+--- | 0 | 5 | five | | 0 | | zero | | 2 | 3 | three | 2 | 4 | 1 | one | 2 (4 rows) -- -- NATURAL JOIN -- Inner equi-join on all columns with the same name -- SELECT '' AS "xxx", * FROM J1_TBL NATURAL JOIN J2_TBL; xxx | 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 '' AS "xxx", * FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d); xxx | 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 '' AS "xxx", * FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a); xxx | 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 '' AS "xxx", * FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a); xxx | 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 '' AS "xxx", * FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i); xxx | 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 '' AS "xxx", * FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k); xxx | i | j | t | i | k -----+---+---+------+---+--- | 0 | | zero | | 0 | 2 | 3 | two | 2 | 2 | 4 | 1 | four | 2 | 4 (3 rows) -- -- Non-equi-joins -- SELECT '' AS "xxx", * FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k); xxx | i | j | t | i | k -----+---+---+-------+---+--- | 1 | 4 | one | 2 | 2 | 1 | 4 | one | 2 | 4 | 2 | 3 | two | 2 | 2 | 2 | 3 | two | 2 | 4 | 3 | 2 | three | 2 | 4 | 4 | 1 | four | 2 | 4 | 0 | | zero | 2 | 2 | 0 | | zero | 2 | 4 | 0 | | zero | | 0 (9 rows) -- -- Outer joins -- Note that OUTER is a noise word -- SELECT '' AS "xxx", * FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) ORDER BY i, k, t; xxx | 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 '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) ORDER BY i, k, t; xxx | 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 '' AS "xxx", * FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); xxx | 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 '' AS "xxx", * FROM J1_TBL RIGHT JOIN J2_TBL USING (i); xxx | 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 '' AS "xxx", * FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) ORDER BY i, k, t; xxx | 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 '' AS "xxx", * FROM J1_TBL FULL JOIN J2_TBL USING (i) ORDER BY i, k, t; xxx | 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 '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); xxx | i | j | t | k -----+---+---+---+--- (0 rows) SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); xxx | 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 ---------------------------------------------------------- Merge Join Merge Cond: (a.tenthous = i4.f1) -> Sort Sort Key: a.tenthous -> Hash Semi Join Hash Cond: (a.twothousand = b.twothousand) Join Filter: (a.fivethous <> b.fivethous) -> Foreign Scan on tenk1 a -> Hash -> Foreign Scan on tenk1 b -> Materialize -> Sort Sort Key: i4.f1 -> Foreign Scan on int4_tbl i4 (14 rows) -- -- More complicated constructs -- -- -- Multiway full join -- CREATE FOREIGN TABLE t11 (name TEXT, n INTEGER) SERVER sqlite_svr; CREATE FOREIGN TABLE t21 (name TEXT, n INTEGER) SERVER sqlite_svr; CREATE FOREIGN TABLE t31 (name TEXT, n INTEGER) SERVER sqlite_svr; INSERT INTO t11 VALUES ( 'bb', 11 ); INSERT INTO t21 VALUES ( 'bb', 12 ); INSERT INTO t21 VALUES ( 'cc', 22 ); INSERT INTO t21 VALUES ( 'ee', 42 ); INSERT INTO t31 VALUES ( 'bb', 13 ); INSERT INTO t31 VALUES ( 'cc', 23 ); INSERT INTO t31 VALUES ( 'dd', 33 ); SELECT * FROM t11 FULL JOIN t21 USING (name) FULL JOIN t31 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 t21) as s2 INNER JOIN (SELECT * FROM t31) s3 USING (name); name | n | n ------+----+---- bb | 12 | 13 cc | 22 | 23 (2 rows) SELECT * FROM (SELECT * FROM t21) as s2 LEFT JOIN (SELECT * FROM t31) s3 USING (name); name | n | n ------+----+---- bb | 12 | 13 cc | 22 | 23 ee | 42 | (3 rows) SELECT * FROM (SELECT * FROM t21) as s2 FULL JOIN (SELECT * FROM t31) 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 t21) as s2 NATURAL INNER JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t31) 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 t21) as s2 NATURAL LEFT JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t31) 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 t21) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t31) 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 t11) as s1 NATURAL INNER JOIN (SELECT name, n as s2_n, 2 as s2_2 FROM t21) as s2 NATURAL INNER JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t31) 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 t11) as s1 NATURAL FULL JOIN (SELECT name, n as s2_n, 2 as s2_2 FROM t21) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t31) 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 t11) as s1 NATURAL FULL JOIN (SELECT * FROM (SELECT name, n as s2_n FROM t21) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n FROM t31) 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 t11) as s1 NATURAL FULL JOIN (SELECT * FROM (SELECT name, n as s2_n, 2 as s2_2 FROM t21) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n FROM t31) 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) -- Test for propagation of nullability constraints into sub-joins create foreign table x (x1 int, x2 int) server sqlite_svr; 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 foreign table y (y1 int, y2 int) server sqlite_svr; 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: 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 -> Merge Right Join Merge Cond: (i2.q2 = i1.q2) -> Sort Sort Key: i2.q2 -> Hash Join Hash Cond: (i2.q1 = (123)) -> Foreign Scan on int8_tbl i2 -> Hash -> Result -> Materialize -> Sort Sort Key: i1.q2 -> Foreign Scan on int8_tbl i1 (15 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 t31.tenthous as x1, coalesce(t11.stringu1, t21.stringu1) as x2 from tenk1 t11 left join tenk1 t21 on t11.unique1 = t21.unique1 join tenk1 t31 on t11.unique2 = t31.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 ----------------------------------------------------------------------------- Hash Join Hash Cond: (t.thousand = (sum(i4b.f1))) Join Filter: (((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999) = t.tenthous) -> Hash Join Hash Cond: (t.thousand = ((sum(i4a.f1) + 1))) -> Foreign Scan on tenk1 t -> Hash -> Foreign Scan -> Hash -> Foreign Scan (10 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 -> Foreign Scan on j2_tbl -> Materialize -> Sort Sort Key: j1_tbl.i DESC -> Foreign Scan on j1_tbl (9 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) AND (x.twothousand = y.hundred) AND (x.fivethous = y.unique2)) -> Sort Sort Key: x.thousand, x.twothousand, x.fivethous -> Foreign Scan on tenk1 x -> Materialize -> Sort Sort Key: y.unique2, y.hundred -> Subquery Scan on y -> Sort Sort Key: y_1.unique2 -> Foreign Scan on tenk1 y_1 (13 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 FOREIGN TABLE t11; DROP FOREIGN TABLE t21; DROP FOREIGN TABLE t31; DROP FOREIGN TABLE J1_TBL; DROP FOREIGN TABLE J2_TBL; -- Both DELETE and UPDATE allow the specification of additional tables -- to "join" against to determine which rows should be modified. CREATE FOREIGN TABLE t12 (a int OPTIONS (key 'true'), b int) SERVER sqlite_svr; CREATE FOREIGN TABLE t22 (a int OPTIONS (key 'true'), b int) SERVER sqlite_svr; CREATE FOREIGN TABLE t32 (x int OPTIONS (key 'true'), y int) SERVER sqlite_svr; INSERT INTO t12 VALUES (5, 10); INSERT INTO t12 VALUES (15, 20); INSERT INTO t12 VALUES (100, 100); INSERT INTO t12 VALUES (200, 1000); INSERT INTO t22 VALUES (200, 2000); INSERT INTO t32 VALUES (5, 20); INSERT INTO t32 VALUES (6, 7); INSERT INTO t32 VALUES (7, 8); INSERT INTO t32 VALUES (500, 100); DELETE FROM t32 USING t12 table1 WHERE t32.x = table1.a; SELECT * FROM t32; x | y -----+----- 6 | 7 7 | 8 500 | 100 (3 rows) DELETE FROM t32 USING t12 JOIN t22 USING (a) WHERE t32.x > t12.a; SELECT * FROM t32; x | y ---+--- 6 | 7 7 | 8 (2 rows) DELETE FROM t32 USING t32 t3_other WHERE t32.x = t3_other.x AND t32.y = t3_other.y; SELECT * FROM t32; x | y ---+--- (0 rows) -- Test join against inheritance tree create temp table t2a () inherits (t22); insert into t2a values (200, 2001); select * from t12 left join t22 on (t12.a = t22.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 t12.x from t12 join t32 on (t12.a = t32.x); ERROR: column t12.x does not exist LINE 1: select t12.x from t12 join t32 on (t12.a = t32.x); ^ HINT: Perhaps you meant to reference the column "t32.x". -- -- regression test for 8.1 merge right join bug -- CREATE FOREIGN TABLE tt1 ( tt1_id int4, joincol int4 ) SERVER sqlite_svr; INSERT INTO tt1 VALUES (1, 11); INSERT INTO tt1 VALUES (2, NULL); CREATE FOREIGN TABLE tt2 ( tt2_id int4, joincol int4 ) SERVER sqlite_svr; 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; 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) -> Foreign Scan on tenk1 a -> Hash -> Foreign Scan on tenk1 b (6 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; -- -- regression test for 8.2 bug with improper re-ordering of left joins -- create foreign table tt3(f1 int, f2 text) server sqlite_svr; insert into tt3 select x, repeat('xyzzy', 100) from generate_series(1,10000) x; create foreign table tt4(f1 int) server sqlite_svr; insert into tt4 values (0),(1),(9999); 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 ------ 1 9999 0 (3 rows) -- -- regression test for proper handling of outer joins within antijoins -- create foreign table tt4x(c1 int, c2 int, c3 int) server sqlite_svr; 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) -> Foreign Scan on tt4x t1 -> Hash -> Merge Left Join Merge Cond: (t3.c2 = t5.c1) -> Sort Sort Key: t3.c2 -> Merge Left Join Merge Cond: (t2.c3 = t3.c1) -> Sort Sort Key: t2.c3 -> Foreign Scan on tt4x t2 -> Materialize -> Sort Sort Key: t3.c1 -> Foreign Scan on tt4x t3 -> Materialize -> Merge Join Merge Cond: (t4.c2 = t5.c1) -> Sort Sort Key: t4.c2 -> Foreign Scan on tt4x t4 -> Materialize -> Sort Sort Key: t5.c1 -> Foreign Scan on tt4x t5 (27 rows) -- -- regression test for problems of the sort depicted in bug #3494 -- create foreign table tt5(f1 int, f2 int) server sqlite_svr; create foreign table tt6(f1 int, f2 int) server sqlite_svr; 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 foreign table xx (pkxx int) server sqlite_svr; create foreign table yy (pkyy int, pkxx int) server sqlite_svr; 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 foreign table zt1 (f1 int OPTIONS(key 'true')) server sqlite_svr; create foreign table zt2 (f2 int OPTIONS(key 'true')) server sqlite_svr; create foreign table zt3 (f3 int OPTIONS(key 'true')) server sqlite_svr; 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 foreign table a1 (i integer) server sqlite_svr; create foreign table b1 (x integer, y integer) server sqlite_svr; select * from a1 left join b1 on i = x and i = y and x = i; i | x | y ---+---+--- (0 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 foreign table a2 ( code char OPTIONS (key 'true') ) server sqlite_svr; create foreign table b2 ( a char OPTIONS (key 'true'), num integer OPTIONS (key 'true') ) server sqlite_svr; create foreign table c2 ( name char OPTIONS (key 'true'), a char ) server sqlite_svr; insert into a2 (code) values ('p'); insert into a2 (code) values ('q'); insert into b2 (a, num) values ('p', 1); insert into b2 (a, num) values ('p', 2); insert into c2 (name, a) values ('A', 'p'); insert into c2 (name, a) values ('B', 'q'); insert into c2 (name, a) values ('C', null); select c2.name, ss.code, ss.b_cnt, ss.const from c2 left join (select a2.code, coalesce(b_grp.cnt, 0) as b_cnt, -1 as const from a2 left join (select count(1) as cnt, b2.a from b2 group by b2.a) as b_grp on a2.code = b_grp.a ) as ss on (c2.a = ss.code) order by c2.name; name | code | b_cnt | const ------+------+-------+------- A | p | 2 | -1 B | q | 0 | -1 C | | | (3 rows) rollback; -- -- 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 ------------------------------------------------------------------------------------------------------- Merge Join Merge Cond: (c.unique2 = (COALESCE((COALESCE(a.q1, '0'::bigint)), (COALESCE(b.q2, '-1'::bigint))))) -> Sort Sort Key: c.unique2 -> Foreign Scan on tenk1 c -> Materialize -> Sort Sort Key: (COALESCE((COALESCE(a.q1, '0'::bigint)), (COALESCE(b.q2, '-1'::bigint)))) -> Merge Full Join Merge Cond: ((COALESCE(a.q1, '0'::bigint)) = (COALESCE(b.q2, '-1'::bigint))) -> Sort Sort Key: (COALESCE(a.q1, '0'::bigint)) -> Foreign Scan on int8_tbl a -> Materialize -> Sort Sort Key: (COALESCE(b.q2, '-1'::bigint)) -> Foreign Scan on int8_tbl b (17 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 foreign table nt1 ( id int OPTIONS (key 'true'), a1 boolean, a2 boolean ) server sqlite_svr; create foreign table nt2 ( id int OPTIONS (key 'true'), nt1_id int, b1 boolean, b2 boolean ) server sqlite_svr; create foreign table nt3 ( id int OPTIONS (key 'true'), nt2_id int, c1 boolean ) server sqlite_svr; 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 ---------------------------------------------------------------------- Merge Join Merge Cond: (nt3.nt2_id = nt2.id) -> Sort Sort Key: nt3.nt2_id -> Foreign Scan on nt3 -> Materialize -> Sort Sort Key: nt2.id -> Merge Join Merge Cond: (nt2.nt1_id = nt1.id) Join Filter: (nt2.b1 AND ((nt1.id IS NOT NULL))) -> Sort Sort Key: nt2.nt1_id -> Foreign Scan on nt2 -> Materialize -> Sort Sort Key: nt1.id -> Foreign Scan on nt1 (18 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)) -> Foreign Scan on int8_tbl t1 -> Hash -> Foreign Scan on int8_tbl t2 SubPlan 1 -> Limit -> Result One-Time Filter: ((42) IS NOT NULL) -> Foreign 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) -- -- 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 -- explain (costs off) select * from tenk1 join int4_tbl on f1 = twothousand, int4(sin(1)) q1, int4(sin(0)) q2 where q1 = thousand or q2 = thousand; QUERY PLAN ----------------------------------------------------------------------------------- Merge Join Merge Cond: (tenk1.twothousand = int4_tbl.f1) -> Sort Sort Key: tenk1.twothousand -> Nested Loop Join Filter: ((q1.q1 = tenk1.thousand) OR (q2.q2 = tenk1.thousand)) -> Nested Loop -> Function Scan on q1 -> Function Scan on q2 -> Foreign Scan on tenk1 -> Materialize -> Sort Sort Key: int4_tbl.f1 -> Foreign Scan on int4_tbl (14 rows) explain (costs off) select * from tenk1 join int4_tbl on f1 = twothousand, int4(sin(1)) q1, int4(sin(0)) q2 where thousand = (q1 + q2); QUERY PLAN ------------------------------------------------------------- Merge Join Merge Cond: (tenk1.twothousand = int4_tbl.f1) -> Sort Sort Key: tenk1.twothousand -> Hash Join Hash Cond: (tenk1.thousand = (q1.q1 + q2.q2)) -> Foreign Scan on tenk1 -> Hash -> Nested Loop -> Function Scan on q1 -> Function Scan on q2 -> Materialize -> Sort Sort Key: int4_tbl.f1 -> Foreign Scan on int4_tbl (15 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 ---------------------------------------------------------------- Merge Join Merge Cond: (b.q1 = tenk1.tenthous) -> Sort Sort Key: b.q1 -> Foreign Scan on int8_tbl b -> Materialize -> Sort Sort Key: tenk1.tenthous -> Merge Join Merge Cond: (tenk1.thousand = a.q1) -> Sort Sort Key: tenk1.thousand -> Foreign Scan on tenk1 -> Materialize -> Sort Sort Key: a.q1 -> Foreign Scan on int8_tbl a (17 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 (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 ---------------------------------------------------------------------- Merge Join Merge Cond: (i1.f1 = (0)) -> Sort Sort Key: i1.f1 -> Foreign Scan on int4_tbl i1 -> Materialize -> Sort Sort Key: (0) -> Merge Join Merge Cond: ((3) = t2.unique1) Join Filter: (t1.stringu1 > t2.stringu2) -> Sort Sort Key: (3) -> Hash Join Hash Cond: (t1.unique2 = (11)) -> Foreign Scan on tenk1 t1 -> Hash -> Nested Loop Join Filter: ((1) = (1)) -> Result -> Result -> Materialize -> Sort Sort Key: t2.unique1 -> Foreign Scan on tenk1 t2 (25 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) -- 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) -- -- 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))) -> Foreign Scan on tenk1 a -> Materialize -> Foreign Scan on tenk1 b (5 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))) -> Foreign Scan on tenk1 a -> Materialize -> Foreign Scan on tenk1 b (5 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))) -> Foreign Scan on tenk1 a -> Materialize -> Foreign Scan on tenk1 b (5 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 ----------------------------------------------------------------- Merge Left Join Merge Cond: ((t1.hundred = t2.hundred) AND (t1.ten = t3.ten)) -> Sort Sort Key: t1.hundred, t1.ten -> Foreign Scan on tenk1 t1 -> Materialize -> Sort Sort Key: t2.hundred, t3.ten -> Merge Join Merge Cond: (t2.thousand = t3.unique2) -> Sort Sort Key: t2.thousand -> Foreign Scan on tenk1 t2 -> Materialize -> Sort Sort Key: t3.unique2 -> Foreign Scan on tenk1 t3 (17 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 -------------------------------------------------------------- Merge Left Join Merge Cond: (t1.hundred = t2.hundred) Join Filter: ((t1.ten + t2.ten) = t3.ten) -> Sort Sort Key: t1.hundred -> Foreign Scan on tenk1 t1 -> Materialize -> Sort Sort Key: t2.hundred -> Merge Join Merge Cond: (t2.thousand = t3.unique2) -> Sort Sort Key: t2.thousand -> Foreign Scan on tenk1 t2 -> Materialize -> Sort Sort Key: t3.unique2 -> Foreign Scan on tenk1 t3 (18 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 -> Merge Join Merge Cond: (int4_tbl.f1 = b.thousand) -> Sort Sort Key: int4_tbl.f1 -> Foreign Scan on int4_tbl -> Materialize -> Sort Sort Key: b.thousand -> Merge Left Join Merge Cond: (a.thousand = c.thousand) Join Filter: (a.unique2 = b.unique1) -> Sort Sort Key: a.thousand -> Merge Join Merge Cond: (a.unique1 = b.unique2) -> Sort Sort Key: a.unique1 -> Foreign Scan on tenk1 a -> Materialize -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b -> Materialize -> Sort Sort Key: c.thousand -> Foreign Scan on tenk1 c (27 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 -> Merge Left Join Merge Cond: (i2.f1 = b.tenthous) -> Sort Sort Key: i2.f1 -> Foreign Scan on int4_tbl i2 -> Materialize -> Sort Sort Key: b.tenthous -> Merge Join Merge Cond: (i1.f1 = b.thousand) -> Sort Sort Key: i1.f1 -> Foreign Scan on int4_tbl i1 -> Materialize -> Sort Sort Key: b.thousand -> Merge Left Join Merge Cond: (a.thousand = c.thousand) Join Filter: (b.unique1 = 42) -> Sort Sort Key: a.thousand -> Merge Join Merge Cond: (a.unique1 = b.unique2) -> Sort Sort Key: a.unique1 -> Foreign Scan on tenk1 a -> Materialize -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b -> Materialize -> Sort Sort Key: c.thousand -> Foreign Scan on tenk1 c (36 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 -------------------------------------------------------------------------- Merge Left Join Merge Cond: (int8_tbl.q2 = tenk1.unique2) Filter: ((COALESCE(tenk1.unique1, '-1'::integer) + int8_tbl.q1) = 122) -> Sort Sort Key: int8_tbl.q2 -> Foreign Scan on int8_tbl -> Materialize -> Sort Sort Key: tenk1.unique2 -> Foreign Scan on tenk1 (10 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 -------------------------------------------------------------------------------------- Merge Left Join Merge Cond: (a.q1 = b.unique2) Filter: ((COALESCE(b.thousand, 123) = a.q1) AND (a.q1 = COALESCE(b.hundred, 123))) -> Sort Sort Key: a.q1 -> Foreign Scan on int8_tbl a -> Materialize -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b (10 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 -------------------------------------------------------------------- Merge Left Join Merge Cond: (a.f1 = b.unique2) Filter: (CASE WHEN (b.unique2 IS NULL) THEN a.f1 ELSE 0 END = 0) -> Sort Sort Key: a.f1 -> Foreign Scan on int4_tbl a -> Materialize -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b (10 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 ------------------------------------------------------------------- Hash Left Join Hash Cond: (COALESCE(b.twothousand, a.twothousand) = c.unique2) -> Merge Left Join Merge Cond: (a.unique1 = b.thousand) Filter: (COALESCE(b.twothousand, a.twothousand) = 44) -> Sort Sort Key: a.unique1 -> Foreign Scan on tenk1 a -> Materialize -> Sort Sort Key: b.thousand -> Foreign Scan on tenk1 b -> Hash -> Foreign Scan on tenk1 c (14 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 ---------------------------------------------------------------------------- Merge Right Join Output: "*VALUES*".column1, i1.f1, (666) Merge Cond: (i1.f1 = "*VALUES*".column1) -> Merge Left Join Output: i1.f1, 666 Merge Cond: (i1.f1 = i2.unique2) -> Sort Output: i1.f1 Sort Key: i1.f1 -> Foreign Scan on public.int4_tbl i1 Output: i1.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: i2.unique2 -> Sort Output: i2.unique2 Sort Key: i2.unique2 -> Foreign Scan on public.tenk1 i2 Output: i2.unique2 SQLite query: SELECT `unique2` FROM main."tenk1" -> Sort Output: "*VALUES*".column1 Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" Output: "*VALUES*".column1 (25 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 -- create foreign table text_tbl(f1 text) server sqlite_svr; 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 -------------------------------------------------------------------------------------------------------------------- Merge Right Join Output: t1.f1 Merge Cond: (i4.f1 = i8.q2) -> Sort Output: i4.f1 Sort Key: i4.f1 -> Foreign Scan on public.int4_tbl i4 Output: i4.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: t1.f1, i8.q2 -> Sort Output: t1.f1, i8.q2 Sort Key: i8.q2 -> Nested Loop Left Join Output: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) -> Foreign Scan on public.text_tbl t1 Output: t1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" -> Materialize Output: i8.q2 -> Merge Left Join Output: i8.q2 Merge Cond: (i8b1.q2 = (NULL::integer)) -> Sort Output: i8b1.q2 Sort Key: i8b1.q2 -> Foreign Scan on public.int8_tbl i8b1 Output: i8b1.q2 SQLite query: SELECT `q2` FROM main."int8_tbl" -> Materialize Output: i8.q2, (NULL::integer) -> Sort Output: i8.q2, (NULL::integer) Sort Key: (NULL::integer) -> Merge Left Join Output: i8.q2, (NULL::integer) Merge Cond: (i8.q1 = i8b2.q1) -> Sort Output: i8.q1, i8.q2 Sort Key: i8.q1 -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Materialize Output: i8b2.q1, (NULL::integer) -> Sort Output: i8b2.q1, (NULL::integer) Sort Key: i8b2.q1 -> Foreign Scan on public.int8_tbl i8b2 Output: i8b2.q1, NULL::integer SQLite query: SELECT `q1` FROM main."int8_tbl" (53 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 -------------------------------------------------------------------------------------------------------------------------------- Merge Right Join Output: t1.f1 Merge Cond: (i4.f1 = i8.q2) -> Sort Output: i4.f1 Sort Key: i4.f1 -> Foreign Scan on public.int4_tbl i4 Output: i4.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: t1.f1, i8.q2 -> Sort Output: t1.f1, i8.q2 Sort Key: i8.q2 -> Nested Loop Left Join Output: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) -> Foreign Scan on public.text_tbl t1 Output: t1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" -> Materialize Output: i8.q2 -> Merge Left Join Output: i8.q2 Merge Cond: (i8b1.q2 = (NULL::integer)) -> Sort Output: i8b1.q2 Sort Key: i8b1.q2 -> Foreign Scan on public.int8_tbl i8b1 Output: i8b1.q2 SQLite query: SELECT `q2` FROM main."int8_tbl" -> Materialize Output: i8.q2, (NULL::integer) -> Sort Output: i8.q2, (NULL::integer) Sort Key: (NULL::integer) -> Merge Left Join Output: i8.q2, (NULL::integer) Merge Cond: (i8.q1 = i8b2.q1) -> Sort Output: i8.q1, i8.q2 Sort Key: i8.q1 -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Materialize Output: i8b2.q1, (NULL::integer) -> Sort Output: i8b2.q1, (NULL::integer) Sort Key: i8b2.q1 -> Nested Loop Output: i8b2.q1, NULL::integer -> Foreign Scan on public.int8_tbl i8b2 Output: i8b2.q1, i8b2.q2 SQLite query: SELECT `q1` FROM main."int8_tbl" -> Materialize -> Foreign Scan on public.int4_tbl i4b2 SQLite query: SELECT NULL FROM main."int4_tbl" (58 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 -------------------------------------------------------------------------------------------------------------------------------- Merge Right Join Output: t1.f1 Merge Cond: (i4.f1 = i8.q2) -> Sort Output: i4.f1 Sort Key: i4.f1 -> Foreign Scan on public.int4_tbl i4 Output: i4.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: t1.f1, i8.q2 -> Sort Output: t1.f1, i8.q2 Sort Key: i8.q2 -> Nested Loop Left Join Output: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) -> Foreign Scan on public.text_tbl t1 Output: t1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" -> Materialize Output: i8.q2 -> Merge Left Join Output: i8.q2 Merge Cond: (i8b1.q2 = (NULL::integer)) -> Sort Output: i8b1.q2 Sort Key: i8b1.q2 -> Foreign Scan on public.int8_tbl i8b1 Output: i8b1.q2 SQLite query: SELECT `q2` FROM main."int8_tbl" -> Materialize Output: i8.q2, (NULL::integer) -> Sort Output: i8.q2, (NULL::integer) Sort Key: (NULL::integer) -> Merge Left Join Output: i8.q2, (NULL::integer) Merge Cond: (i8.q1 = i8b2.q1) -> Sort Output: i8.q1, i8.q2 Sort Key: i8.q1 -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Materialize Output: i8b2.q1, (NULL::integer) -> Merge Join Output: i8b2.q1, NULL::integer Merge Cond: (i8b2.q1 = i4b2.f1) -> Sort Output: i8b2.q1 Sort Key: i8b2.q1 -> Foreign Scan on public.int8_tbl i8b2 Output: i8b2.q1 SQLite query: SELECT `q1` FROM main."int8_tbl" -> Materialize Output: i4b2.f1 -> Sort Output: i4b2.f1 Sort Key: i4b2.f1 -> Foreign Scan on public.int4_tbl i4b2 Output: i4b2.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" (64 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 -> Foreign Scan on public.text_tbl t2 Output: t2.f1 SQLite query: SELECT `f1` FROM main."text_tbl" -> Materialize Output: i8.q1, i8.q2, i4.f1, t1.f1 -> Nested Loop Output: i8.q1, i8.q2, i4.f1, t1.f1 -> Foreign Scan on public.text_tbl t1 Output: t1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" WHERE ((`f1` = 'doh!')) -> Materialize Output: i8.q1, i8.q2, i4.f1 -> Merge Left Join Output: i8.q1, i8.q2, i4.f1 Merge Cond: (i8.q1 = i4.f1) -> Sort Output: i8.q1, i8.q2 Sort Key: i8.q1 -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" WHERE ((`q2` = 456)) -> Materialize Output: i4.f1 -> Sort Output: i4.f1 Sort Key: i4.f1 -> Foreign Scan on public.int4_tbl i4 Output: i4.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" (31 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 -> Foreign Scan on public.text_tbl t1 Output: t1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" -> Materialize Output: i8.q1, i8.q2 -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" WHERE ((`q2` = 123)) -> Limit Output: (i8.q1), t2.f1 -> Foreign Scan on public.text_tbl t2 Output: i8.q1, t2.f1 SQLite query: SELECT `f1` FROM main."text_tbl" (18 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 -> Foreign Scan on public.text_tbl t1 Output: t1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" -> Materialize Output: i8.q1, i8.q2 -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" WHERE ((`q2` = 123)) -> Limit Output: (i8.q1), t2.f1 -> Foreign Scan on public.text_tbl t2 Output: i8.q1, t2.f1 SQLite query: SELECT `f1` FROM main."text_tbl" -> Limit Output: ((i8.q1)), (t2.f1) -> Foreign Scan on public.text_tbl t3 Output: (i8.q1), t2.f1 SQLite query: SELECT NULL FROM main."text_tbl" (25 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 -> Hash Left Join Output: tt1.f1, tt4.f1 Hash Cond: (tt3.f1 = tt4.f1) -> Nested Loop Left Join Output: tt1.f1, tt3.f1 -> Nested Loop Output: tt1.f1 -> Foreign Scan on public.text_tbl tt1 Output: tt1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" WHERE ((`f1` = 'foo')) -> Materialize -> Foreign Scan on public.text_tbl tt2 SQLite query: SELECT NULL FROM main."text_tbl" -> Materialize Output: tt3.f1 -> Foreign Scan on public.text_tbl tt3 Output: tt3.f1 SQLite query: SELECT `f1` FROM main."text_tbl" WHERE ((`f1` = 'foo')) -> Hash Output: tt4.f1 -> Foreign Scan on public.text_tbl tt4 Output: tt4.f1 SQLite query: SELECT `f1` FROM main."text_tbl" WHERE ((`f1` = 'foo')) -> Subquery Scan on ss1 Output: ss1.c0 Filter: (ss1.c0 = 'foo'::text) -> Limit Output: (tt4.f1) -> Foreign Scan on public.text_tbl tt5 Output: tt4.f1 SQLite query: SELECT NULL FROM main."text_tbl" (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)) -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" WHERE ((`q1` = 0)) -> Nested Loop Output: i43.f1, (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42)) -> Merge Join Output: i41.f1, i42.f1, i43.f1, (42) Merge Cond: (i41.f1 = i42.f1) -> Sort Output: i41.f1 Sort Key: i41.f1 -> Foreign Scan on public.int4_tbl i41 Output: i41.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: i42.f1, i43.f1, (42) -> Sort Output: i42.f1, i43.f1, (42) Sort Key: i42.f1 -> Nested Loop Output: i42.f1, i43.f1, 42 -> Foreign Scan on public.int4_tbl i42 Output: i42.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: i43.f1 -> Foreign Scan on public.int4_tbl i43 Output: i43.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" WHERE ((`f1` = 0)) -> Limit Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42)) -> Foreign Scan on public.text_tbl Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42) SQLite query: SELECT NULL FROM main."text_tbl" (36 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 Join Filter: ((1) = COALESCE((1))) -> Result -> Hash Full Join Hash Cond: (a1.unique1 = (1)) -> Foreign 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 ---------------------------------------- Hash Right Join Hash Cond: (b.unique2 = a.f1) -> Foreign Scan on tenk1 b -> Hash -> Foreign Scan on int4_tbl a (5 rows) explain (costs off) select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42; QUERY PLAN -------------------------------------- Hash Full Join Hash Cond: (a.unique2 = b.unique2) -> Foreign Scan on tenk1 a -> Hash -> Foreign Scan on tenk1 b (5 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 -> Foreign Scan on public.int8_tbl a Output: a.q2 SQLite query: SELECT `q2` FROM main."int8_tbl" -> Materialize Output: b.q1, (COALESCE(b.q1, '1'::bigint)) -> Sort Output: b.q1, (COALESCE(b.q1, '1'::bigint)) Sort Key: (COALESCE(b.q1, '1'::bigint)) -> Foreign Scan on public.int8_tbl b Output: b.q1, COALESCE(b.q1, '1'::bigint) SQLite query: SELECT `q1` FROM main."int8_tbl" (18 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 FOREIGN TABLE a3 (id int OPTIONS (key 'true'), b_id int) SERVER sqlite_svr; CREATE FOREIGN TABLE b3 (id int OPTIONS (key 'true'), c_id int) SERVER sqlite_svr; CREATE FOREIGN TABLE c3 (id int OPTIONS (key 'true')) SERVER sqlite_svr; CREATE FOREIGN TABLE d3 (a int, b int) SERVER sqlite_svr; INSERT INTO a3 VALUES (0, 0), (1, NULL); INSERT INTO b3 VALUES (0, 0), (1, NULL); INSERT INTO c3 VALUES (0), (1); INSERT INTO d3 VALUES (1,3), (2,2), (3,1); -- all three cases should be optimizable into a3 simple seqscan explain (costs off) SELECT a3.* FROM a3 LEFT JOIN b3 ON a3.b_id = b3.id; QUERY PLAN -------------------------------------- Merge Left Join Merge Cond: (a3.b_id = b3.id) -> Sort Sort Key: a3.b_id -> Foreign Scan on a3 -> Materialize -> Sort Sort Key: b3.id -> Foreign Scan on b3 (9 rows) explain (costs off) SELECT b3.* FROM b3 LEFT JOIN c3 ON b3.c_id = c3.id; QUERY PLAN -------------------------------------- Merge Left Join Merge Cond: (b3.c_id = c3.id) -> Sort Sort Key: b3.c_id -> Foreign Scan on b3 -> Materialize -> Sort Sort Key: c3.id -> Foreign Scan on c3 (9 rows) explain (costs off) SELECT a3.* FROM a3 LEFT JOIN (b3 left join c3 on b3.c_id = c3.id) ON (a3.b_id = b3.id); QUERY PLAN -------------------------------------------------------- Merge Left Join Merge Cond: (a3.b_id = b3.id) -> Sort Sort Key: a3.b_id -> Foreign Scan on a3 -> Materialize -> Sort Sort Key: b3.id -> Merge Left Join Merge Cond: (b3.c_id = c3.id) -> Sort Sort Key: b3.c_id -> Foreign Scan on b3 -> Materialize -> Sort Sort Key: c3.id -> Foreign Scan on c3 (17 rows) -- check optimization of outer join within another special join explain (costs off) select id from a3 where id in ( select b3.id from b3 left join c3 on b3.id = c3.id ); QUERY PLAN -------------------------------------------------------- Hash Join Hash Cond: (a3.id = b3.id) -> Foreign Scan on a3 -> Hash -> HashAggregate Group Key: b3.id -> Merge Left Join Merge Cond: (b3.id = c3.id) -> Sort Sort Key: b3.id -> Foreign Scan on b3 -> Materialize -> Sort Sort Key: c3.id -> Foreign Scan on c3 (15 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 d3.* from d3 left join (select * from b3 group by b3.id, b3.c_id) s on d3.a = s.id and d3.b = s.c_id; QUERY PLAN -------------------- Foreign Scan on d3 (1 row) -- similarly, but keying off a DISTINCT clause explain (costs off) select d3.* from d3 left join (select distinct * from b3) s on d3.a = s.id and d3.b = s.c_id; QUERY PLAN -------------------- Foreign Scan on d3 (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 b3.id is a -- primary key and so drop b3.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 d3.* from d3 left join (select * from b3 group by b3.id, b3.c_id) s on d3.a = s.id; QUERY PLAN -------------------------------- Hash Left Join Hash Cond: (d3.a = s.id) -> Foreign Scan on d3 -> Hash -> Subquery Scan on s -> Foreign Scan (6 rows) -- similarly, but keying off a DISTINCT clause explain (costs off) select d3.* from d3 left join (select distinct * from b3) s on d3.a = s.id; QUERY PLAN ----------------------------------------------- Merge Right Join Merge Cond: (s.id = d3.a) -> Sort Sort Key: s.id -> Subquery Scan on s -> HashAggregate Group Key: b3.id, b3.c_id -> Foreign Scan on b3 -> Materialize -> Sort Sort Key: d3.a -> Foreign Scan on d3 (12 rows) -- check join removal works when uniqueness of the join condition is enforced -- by a UNION explain (costs off) select d3.* from d3 left join (select id from a3 union select id from b3) s on d3.a = s.id; QUERY PLAN -------------------- Foreign Scan on d3 (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 ----------------------------- Foreign Scan on int8_tbl i8 (1 row) -- check join removal with lateral references explain (costs off) select 1 from (select a3.id FROM a3 left join b3 on a3.b_id = b3.id) q, lateral generate_series(1, q.id) gs(i) where q.id = gs.i; QUERY PLAN ------------------------------------------------------------- Merge Right Join Merge Cond: (b3.id = a3.b_id) -> Sort Sort Key: b3.id -> Foreign Scan on b3 -> Materialize -> Sort Sort Key: a3.b_id -> Nested Loop -> Foreign Scan on a3 -> Function Scan on generate_series gs Filter: (a3.id = i) (12 rows) rollback; create foreign table parent (k int options (key 'true'), pd int) server sqlite_svr; create foreign table child (k int options (key 'true'), cd int) server sqlite_svr; 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 ------------------------------------------- Merge Left Join Merge Cond: (p.k = c.k) -> Sort Sort Key: p.k -> Foreign Scan on parent p -> Materialize -> Sort Sort Key: c.k -> Foreign Scan on child c (9 rows) -- 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 ------------------------------------------- Merge Left Join Merge Cond: (p.k = c.k) -> Sort Sort Key: p.k -> Foreign Scan on parent p -> Materialize -> Sort Sort Key: c.k -> Foreign Scan on child c (9 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 (2 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 FOREIGN TABLE a4 (id int OPTIONS (key 'true')) SERVER sqlite_svr; CREATE FOREIGN TABLE b4 (id int OPTIONS (key 'true'), a_id int) SERVER sqlite_svr; INSERT INTO a4 VALUES (0), (1); INSERT INTO b4 VALUES (0, 0), (1, NULL); SELECT * FROM b4 LEFT JOIN a4 ON (b4.a_id = a4.id) WHERE (a4.id IS NULL OR a4.id > 0); id | a_id | id ----+------+---- 1 | | (1 row) SELECT b4.* FROM b4 LEFT JOIN a4 ON (b4.a_id = a4.id) WHERE (a4.id IS NULL OR a4.id > 0); id | a_id ----+------ 1 | (1 row) rollback; -- another join removal bug: this is not optimizable, either begin; create foreign table innertab (id int8 options (key 'true'), dat1 int8) server sqlite_svr; 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 | 4567890123456789 | -4567890123456789 | 4567890123456789 1 | 4567890123456789 | 123 | 42 1 | 123 | 456 | 123 1 | 123 | 4567890123456789 | 123 1 | 4567890123456789 | 4567890123456789 | 4567890123456789 (5 rows) rollback; -- another join removal bug: we must clean up correctly when removing a PHV begin; create foreign table uniquetbl (f1 text) server sqlite_svr; 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 --------------------------------------------------------------- Merge Right Join Merge Cond: (t3.f1 = ('***'::text)) -> Sort Sort Key: t3.f1 -> Foreign Scan on uniquetbl t3 -> Materialize -> Sort Sort Key: ('***'::text) -> Merge Left Join Merge Cond: (t1.f1 = uniquetbl.f1) -> Sort Sort Key: t1.f1 -> Foreign Scan on uniquetbl t1 -> Materialize -> Sort Sort Key: uniquetbl.f1 -> Foreign Scan on uniquetbl (17 rows) 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 -------------------------------------------------------------------------------------------------------------------------------- Merge Join Merge Cond: (i4.f1 = t1.unique2) -> Sort Sort Key: i4.f1 -> Foreign Scan on int4_tbl i4 -> Materialize -> Sort Sort Key: t1.unique2 -> Merge Join Merge Cond: (t0.f1 = (CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END)) -> Sort Sort Key: t0.f1 -> Foreign Scan on text_tbl t0 -> Materialize -> Sort Sort Key: (CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END) -> Merge Left Join Merge Cond: (((t1.string4)::text) = u1.f1) -> Sort Sort Key: ((t1.string4)::text) -> Foreign Scan on tenk1 t1 Filter: (stringu2 !~* CASE ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END) -> Materialize -> Sort Sort Key: u1.f1 -> Foreign Scan on uniquetbl u1 (26 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; -- 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". -- -- 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 ---------------------------------------------- Merge Join Merge Cond: (a.unique1 = b.f1) -> Sort Sort Key: a.unique1 -> Foreign Scan on tenk1 a -> Materialize -> Sort Sort Key: b.f1 -> Foreign Scan on int4_tbl b (9 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 ----------------------------------------- Merge Join Merge Cond: (x.f1 = tenk1.unique1) -> Sort Sort Key: x.f1 -> Foreign Scan on int4_tbl x -> Materialize -> Sort Sort Key: tenk1.unique1 -> Foreign Scan on tenk1 (9 rows) explain (costs off) select unique2, x.* from int4_tbl x cross join lateral (select unique2 from tenk1 where f1 = unique1) ss; QUERY PLAN ----------------------------------------- Merge Join Merge Cond: (x.f1 = tenk1.unique1) -> Sort Sort Key: x.f1 -> Foreign Scan on int4_tbl x -> Materialize -> Sort Sort Key: tenk1.unique1 -> Foreign Scan on tenk1 (9 rows) select unique2, x.* from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; unique2 | f1 ---------+------------- | -2147483647 | -123456 9998 | 0 | 123456 | 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 ----------------------------------------- Merge Left Join Merge Cond: (x.f1 = tenk1.unique1) -> Sort Sort Key: x.f1 -> Foreign Scan on int4_tbl x -> Materialize -> Sort Sort Key: tenk1.unique1 -> Foreign Scan on tenk1 (9 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 | -4567890123456789 | -4567890123456789 4567890123456789 | 123 | 123 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 | -4567890123456789 | 4567890123456789 4567890123456789 | 123 | 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 -> Foreign Scan on tenk1 a -> Function Scan on generate_series g (4 rows) explain (costs off) select count(*) from tenk1 a cross join lateral generate_series(1,two) g; QUERY PLAN ------------------------------------------------ Aggregate -> Nested Loop -> Foreign Scan on tenk1 a -> Function Scan on generate_series g (4 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 -> Foreign Scan on tenk1 a -> Function Scan on generate_series g (4 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 -> Foreign Scan on int8_tbl a -> Foreign Scan on int8_tbl b (5 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) -> Sort Sort Key: a.unique1 -> Foreign Scan on tenk1 a -> Materialize -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b (10 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 -> Merge Join Merge Cond: (b.unique2 = "*VALUES*".column1) -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b -> Materialize -> Sort Sort Key: "*VALUES*".column1 -> Nested Loop -> Foreign Scan on tenk1 a -> Values Scan on "*VALUES*" (12 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 -> Foreign Scan on int8_tbl a -> Merge Left Join Merge Cond: (x.q2 = (a.q1)) -> Sort Sort Key: x.q2 -> Foreign Scan on int8_tbl x -> Materialize -> Sort Sort Key: (a.q1) -> Foreign Scan on int4_tbl y (13 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 -------------+-------------+------------- -2147483647 | -2147483647 | -2147483647 -123456 | -123456 | -123456 0 | 0 | 0 123456 | 123456 | 123456 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 ------------------+-------------------+------------------+-------------------+------------------+------------------+------------------- 4567890123456789 | -4567890123456789 | | | 4567890123456789 | | 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 123 | 456 | | | 123 | | 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 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 ------------------+-------------------+------------------+-------------------+------------------+------------------+------------------- 4567890123456789 | -4567890123456789 | | | 4567890123456789 | | 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 123 | 456 | | | 123 | | 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 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 ------------------+------------------- 4567890123456789 | -4567890123456789 4567890123456789 | 123 4567890123456789 | 123 123 | 456 123 | 4567890123456789 123 | 4567890123456789 123 | 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 -------------------+------------------- 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 123 | 456 4567890123456789 | 123 123 | 4567890123456789 123 | 456 | 123 | 4567890123456789 4567890123456789 | -4567890123456789 123 | 4567890123456789 4567890123456789 | 123 123 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 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 | -4567890123456789 | 4567890123456789 | 123 123 | 456 4567890123456789 | 123 123 | 4567890123456789 123 | 456 | 123 | 4567890123456789 4567890123456789 | -4567890123456789 123 | 4567890123456789 4567890123456789 | 123 123 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 (20 rows) create temp table dual(); insert into dual default values; analyze dual; 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 dual union all select x.q2,y.q2 from dual) v(vx,vy); vx | vy -------------------+------------------- 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 123 | 456 4567890123456789 | 123 123 | 4567890123456789 123 | 456 | 123 | 4567890123456789 4567890123456789 | -4567890123456789 123 | 4567890123456789 4567890123456789 | 123 123 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 (20 rows) -- Error when using sub-query with multi instances of table, this issue is fixed on PostgreSQL-12 -- 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; -- select * from -- int8_tbl a left join -- lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; -- 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; -- 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; -- 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 -------------------------------------------------------------------- Merge Left Join Output: i.f1, j.f1 Merge Cond: (i.f1 = j.f1) -> Sort Output: i.f1 Sort Key: i.f1 -> Foreign Scan on public.int4_tbl i Output: i.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: j.f1 -> Sort Output: j.f1 Sort Key: j.f1 -> Foreign Scan on public.int2_tbl j Output: j.f1 SQLite query: SELECT `f1` FROM main."int2_tbl" (17 rows) select * from int4_tbl i left join lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; f1 | f1 -------------+---- -2147483647 | -123456 | 0 | 0 123456 | 2147483647 | (5 rows) explain (verbose, costs off) select * from int4_tbl i left join lateral (select coalesce(j) from int2_tbl j where i.f1 = j.f1) k on true; QUERY PLAN -------------------------------------------------------------------- Merge Left Join Output: i.f1, (COALESCE(j.*)) Merge Cond: (i.f1 = j.f1) -> Sort Output: i.f1 Sort Key: i.f1 -> Foreign Scan on public.int4_tbl i Output: i.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: j.f1, (COALESCE(j.*)) -> Sort Output: j.f1, (COALESCE(j.*)) Sort Key: j.f1 -> Foreign Scan on public.int2_tbl j Output: j.f1, COALESCE(j.*) SQLite query: SELECT `f1` FROM main."int2_tbl" (17 rows) select * from int4_tbl i left join lateral (select coalesce(j) from int2_tbl j where i.f1 = j.f1) k on true; f1 | coalesce -------------+---------- -2147483647 | -123456 | 0 | (0) 123456 | 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 -> Foreign Scan on public.int4_tbl a Output: a.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Merge Left Join Output: b.f1, c.q1, c.q2 Merge Cond: (b.f1 = c.q1) -> Sort Output: b.f1 Sort Key: b.f1 -> Foreign Scan on public.int4_tbl b Output: b.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Materialize Output: c.q1, c.q2 -> Sort Output: c.q1, c.q2 Sort Key: c.q1 -> Foreign Scan on public.int8_tbl c Output: c.q1, c.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" WHERE ((? = `q2`)) (22 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 | -2147483647 | | 0 | -123456 | | 0 | 0 | | 0 | 123456 | | 0 | 2147483647 | | 123456 | -2147483647 | | 123456 | -123456 | | 123456 | 0 | | 123456 | 123456 | | 123456 | 2147483647 | | -123456 | -2147483647 | | -123456 | -123456 | | -123456 | 0 | | -123456 | 123456 | | -123456 | 2147483647 | | 2147483647 | -2147483647 | | 2147483647 | -123456 | | 2147483647 | 0 | | 2147483647 | 123456 | | 2147483647 | 2147483647 | | -2147483647 | -2147483647 | | -2147483647 | -123456 | | -2147483647 | 0 | | -2147483647 | 123456 | | -2147483647 | 2147483647 | | (25 rows) -- lateral reference in a PlaceHolderVar evaluated at join level -- Error when using sub-query with multi instances of table, this issue is fixed on PostgreSQL-12 -- 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; -- 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; -- 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; -- 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; -- 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 -> Hash Join Output: int4_tbl.f1 Inner Unique: true Hash Cond: (int4_tbl.f1 = tenk1.unique1) -> Foreign Scan on public.int4_tbl Output: int4_tbl.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" -> Hash Output: tenk1.unique1 -> HashAggregate Output: tenk1.unique1 Group Key: tenk1.unique1 -> Foreign Scan on public.tenk1 Output: tenk1.unique1 SQLite query: SELECT `unique1` FROM main."tenk1" WHERE ((`unique2` = ?)) (19 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 -> Foreign Scan on public.int8_tbl t1 Output: t1.q1, t1.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> 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) -> Foreign Scan on public.int8_tbl t2 Output: t2.q1, t2.q2 Filter: (SubPlan 3) SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" 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) -> Foreign Scan on public.int8_tbl t3 Output: t3.q1, t3.q2 SQLite query: SELECT `q2` FROM main."int8_tbl" WHERE ((`q2` = ?)) (29 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 | -4567890123456789 | 4567890123456789 | 4567890123456789 0 | 4567890123456789 | 123 | 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 that foreign key join estimation performs sanely for outer joins -- begin; create foreign table fkest (a int options (key 'true'), b int options (key 'true'), c int) server sqlite_svr; create foreign table fkest1 (a int options (key 'true'), b int options (key 'true')) server sqlite_svr; 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; 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 ----------------------------------------------------------------- Merge Right Join Merge Cond: ((f3.a = f.a) AND (f3.b = f.b)) -> Sort Sort Key: f3.a, f3.b -> Foreign Scan on fkest1 f3 -> Materialize -> Merge Left Join Merge Cond: ((f.a = f2.a) AND (f.b = f2.b)) -> Merge Left Join Merge Cond: ((f.a = f1.a) AND (f.b = f1.b)) -> Sort Sort Key: f.a, f.b -> Foreign Scan on fkest f -> Materialize -> Sort Sort Key: f1.a, f1.b -> Foreign Scan on fkest1 f1 -> Materialize -> Sort Sort Key: f2.a, f2.b -> Foreign Scan on fkest1 f2 (21 rows) rollback; -- -- test planner's ability to mark joins as unique -- create foreign table j11 (id int options (key 'true')) server sqlite_svr; create foreign table j21 (id int options (key 'true')) server sqlite_svr; create foreign table j31 (id int) server sqlite_svr; insert into j11 values(1),(2),(3); insert into j21 values(1),(2),(3); insert into j31 values(1),(1); -- ensure join is properly marked as unique explain (verbose, costs off) select * from j11 inner join j21 on j11.id = j21.id; QUERY PLAN --------------------------------------------------------------- Merge Join Output: j11.id, j21.id Merge Cond: (j11.id = j21.id) -> Sort Output: j11.id Sort Key: j11.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Materialize Output: j21.id -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (17 rows) -- ensure join is not unique when not an equi-join explain (verbose, costs off) select * from j11 inner join j21 on j11.id > j21.id; QUERY PLAN --------------------------------------------------------- Nested Loop Output: j11.id, j21.id Join Filter: (j11.id > j21.id) -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Materialize Output: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (11 rows) -- ensure non-unique rel is not chosen as inner explain (verbose, costs off) select * from j11 inner join j31 on j11.id = j31.id; QUERY PLAN --------------------------------------------------------------- Merge Join Output: j11.id, j31.id Merge Cond: (j11.id = j31.id) -> Sort Output: j11.id Sort Key: j11.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Materialize Output: j31.id -> Sort Output: j31.id Sort Key: j31.id -> Foreign Scan on public.j31 Output: j31.id SQLite query: SELECT `id` FROM main."j31" (17 rows) -- ensure left join is marked as unique explain (verbose, costs off) select * from j11 left join j21 on j11.id = j21.id; QUERY PLAN --------------------------------------------------------------- Merge Left Join Output: j11.id, j21.id Merge Cond: (j11.id = j21.id) -> Sort Output: j11.id Sort Key: j11.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Materialize Output: j21.id -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (17 rows) -- ensure right join is marked as unique explain (verbose, costs off) select * from j11 right join j21 on j11.id = j21.id; QUERY PLAN --------------------------------------------------------------- Merge Left Join Output: j11.id, j21.id Merge Cond: (j21.id = j11.id) -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" -> Materialize Output: j11.id -> Sort Output: j11.id Sort Key: j11.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" (17 rows) -- ensure full join is marked as unique explain (verbose, costs off) select * from j11 full join j21 on j11.id = j21.id; QUERY PLAN --------------------------------------------------------------- Merge Full Join Output: j11.id, j21.id Merge Cond: (j11.id = j21.id) -> Sort Output: j11.id Sort Key: j11.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Materialize Output: j21.id -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (17 rows) -- a clauseless (cross) join can't be unique explain (verbose, costs off) select * from j11 cross join j21; QUERY PLAN --------------------------------------------------------- Nested Loop Output: j11.id, j21.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Materialize Output: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (10 rows) -- ensure a natural join is marked as unique explain (verbose, costs off) select * from j11 natural join j21; QUERY PLAN --------------------------------------------------------------- Merge Join Output: j11.id Merge Cond: (j11.id = j21.id) -> Sort Output: j11.id Sort Key: j11.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Materialize Output: j21.id -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (17 rows) -- ensure a distinct clause allows the inner to become unique explain (verbose, costs off) select * from j11 inner join (select distinct id from j31) j31 on j11.id = j31.id; QUERY PLAN --------------------------------------------------------------- Hash Join Output: j11.id, j31.id Inner Unique: true Hash Cond: (j11.id = j31.id) -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Hash Output: j31.id -> HashAggregate Output: j31.id Group Key: j31.id -> Foreign Scan on public.j31 Output: j31.id SQLite query: SELECT `id` FROM main."j31" (15 rows) -- ensure group by clause allows the inner to become unique explain (verbose, costs off) select * from j11 inner join (select id from j31 group by id) j31 on j11.id = j31.id; QUERY PLAN -------------------------------------------------------------------- Hash Join Output: j11.id, j31.id Inner Unique: true Hash Cond: (j11.id = j31.id) -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" -> Hash Output: j31.id -> Foreign Scan Output: j31.id SQLite query: SELECT `id` FROM main."j31" GROUP BY 1 (12 rows) -- test more complex permutations of unique joins create foreign table j12 (id1 int options (key 'true'), id2 int options (key 'true')) server sqlite_svr; create foreign table j22 (id1 int options (key 'true'), id2 int options (key 'true')) server sqlite_svr; create foreign table j32 (id1 int options (key 'true'), id2 int options (key 'true')) server sqlite_svr; insert into j12 values(1,1),(1,2); insert into j22 values(1,1); insert into j32 values(1,1); -- 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 j12 inner join j22 on j12.id1 = j22.id1; QUERY PLAN ----------------------------------------------------------------------- Merge Join Output: j12.id1, j12.id2, j22.id1, j22.id2 Merge Cond: (j12.id1 = j22.id1) -> Sort Output: j12.id1, j12.id2 Sort Key: j12.id1 -> Foreign Scan on public.j12 Output: j12.id1, j12.id2 SQLite query: SELECT `id1`, `id2` FROM main."j12" -> Materialize Output: j22.id1, j22.id2 -> Sort Output: j22.id1, j22.id2 Sort Key: j22.id1 -> Foreign Scan on public.j22 Output: j22.id1, j22.id2 SQLite query: SELECT `id1`, `id2` FROM main."j22" (17 rows) -- ensure proper unique detection with multiple join quals explain (verbose, costs off) select * from j12 inner join j22 on j12.id1 = j22.id1 and j12.id2 = j22.id2; QUERY PLAN ----------------------------------------------------------------------- Merge Join Output: j12.id1, j12.id2, j22.id1, j22.id2 Merge Cond: ((j12.id1 = j22.id1) AND (j12.id2 = j22.id2)) -> Sort Output: j12.id1, j12.id2 Sort Key: j12.id1, j12.id2 -> Foreign Scan on public.j12 Output: j12.id1, j12.id2 SQLite query: SELECT `id1`, `id2` FROM main."j12" -> Materialize Output: j22.id1, j22.id2 -> Sort Output: j22.id1, j22.id2 Sort Key: j22.id1, j22.id2 -> Foreign Scan on public.j22 Output: j22.id1, j22.id2 SQLite query: SELECT `id1`, `id2` FROM main."j22" (17 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 j12 inner join j22 on j12.id1 = j22.id1 where j12.id2 = 1; QUERY PLAN ------------------------------------------------------------------------------------- Merge Join Output: j12.id1, j12.id2, j22.id1, j22.id2 Merge Cond: (j12.id1 = j22.id1) -> Sort Output: j12.id1, j12.id2 Sort Key: j12.id1 -> Foreign Scan on public.j12 Output: j12.id1, j12.id2 SQLite query: SELECT `id1`, `id2` FROM main."j12" WHERE ((`id2` = 1)) -> Materialize Output: j22.id1, j22.id2 -> Sort Output: j22.id1, j22.id2 Sort Key: j22.id1 -> Foreign Scan on public.j22 Output: j22.id1, j22.id2 SQLite query: SELECT `id1`, `id2` FROM main."j22" (17 rows) -- as above, but for left joins. explain (verbose, costs off) select * from j12 left join j22 on j12.id1 = j22.id1 where j12.id2 = 1; QUERY PLAN ------------------------------------------------------------------------------------- Merge Left Join Output: j12.id1, j12.id2, j22.id1, j22.id2 Merge Cond: (j12.id1 = j22.id1) -> Sort Output: j12.id1, j12.id2 Sort Key: j12.id1 -> Foreign Scan on public.j12 Output: j12.id1, j12.id2 SQLite query: SELECT `id1`, `id2` FROM main."j12" WHERE ((`id2` = 1)) -> Materialize Output: j22.id1, j22.id2 -> Sort Output: j22.id1, j22.id2 Sort Key: j22.id1 -> Foreign Scan on public.j22 Output: j22.id1, j22.id2 SQLite query: SELECT `id1`, `id2` FROM main."j22" (17 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; explain (costs off) select * from j12 j12 inner join j12 j22 on j12.id1 = j22.id1 and j12.id2 = j22.id2 where j12.id1 % 1000 = 1 and j22.id1 % 1000 = 1; QUERY PLAN ------------------------------------------------------------- Merge Join Merge Cond: ((j12.id1 = j22.id1) AND (j12.id2 = j22.id2)) -> Sort Sort Key: j12.id1, j12.id2 -> Foreign Scan on j12 -> Materialize -> Sort Sort Key: j22.id1, j22.id2 -> Foreign Scan on j12 j22 (9 rows) select * from j12 j12 inner join j12 j22 on j12.id1 = j22.id1 and j12.id2 = j22.id2 where j12.id1 % 1000 = 1 and j22.id1 % 1000 = 1; id1 | id2 | id1 | id2 -----+-----+-----+----- 1 | 1 | 1 | 1 1 | 2 | 1 | 2 (2 rows) reset enable_nestloop; reset enable_hashjoin; reset enable_sort; -- check that semijoin inner is not seen as unique for a portion of the outerrel CREATE FOREIGN TABLE onek ( unique1 int4 OPTIONS (key 'true'), unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name ) SERVER sqlite_svr; 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join Output: t1.unique1, t2.hundred Merge Cond: (t3.tenthous = t2.hundred) -> Sort Output: t1.unique1, t3.tenthous Sort Key: t3.tenthous -> Hash Join Output: t1.unique1, t3.tenthous Hash Cond: (t1.unique1 = t3.thousand) -> Foreign Scan on public.onek t1 Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4 SQLite query: SELECT `unique1` FROM main."onek" WHERE ((`unique1` < 1)) -> Hash Output: t3.thousand, t3.tenthous -> HashAggregate Output: t3.thousand, t3.tenthous Group Key: t3.thousand, t3.tenthous -> Foreign Scan on public.tenk1 t3 Output: t3.thousand, t3.tenthous SQLite query: SELECT `thousand`, `tenthous` FROM main."tenk1" -> Materialize Output: t2.hundred -> Sort Output: t2.hundred Sort Key: t2.hundred -> Foreign Scan on public.tenk1 t2 Output: t2.hundred SQLite query: SELECT `hundred` FROM main."tenk1" (28 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join Output: t1.unique1, t2.hundred Hash Cond: (t2.hundred = j3.tenthous) -> Foreign Scan on public.tenk1 t2 Output: t2.hundred SQLite query: SELECT `hundred` FROM main."tenk1" -> Hash Output: t1.unique1, j3.tenthous -> Hash Join Output: t1.unique1, j3.tenthous Hash Cond: (t1.unique1 = j3.unique1) -> Foreign Scan on public.onek t1 Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4 SQLite query: SELECT `unique1` FROM main."onek" WHERE ((`unique1` < 1)) -> Hash Output: j3.unique1, j3.tenthous -> Seq Scan on public.j3 Output: j3.unique1, j3.tenthous (18 rows) drop table j3; DO $d$ declare l_rec record; begin for l_rec in (select foreign_table_schema, foreign_table_name from information_schema.foreign_tables) loop execute format('drop foreign table %I.%I cascade;', l_rec.foreign_table_schema, l_rec.foreign_table_name); end loop; end; $d$; NOTICE: drop cascades to table t2a NOTICE: drop cascades to view zv1 DROP SERVER sqlite_svr; DROP EXTENSION sqlite_fdw CASCADE;