-- -- JOIN -- Test JOIN clauses -- --Testcase 360: CREATE EXTENSION sqlite_fdw; --Testcase 361: CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test_core.db'); --Testcase 362: CREATE FOREIGN TABLE J1_TBL ( i integer, j integer, t text ) SERVER sqlite_svr; --Testcase 363: CREATE FOREIGN TABLE J2_TBL ( i integer, k integer ) SERVER sqlite_svr; --Testcase 364: 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; --Testcase 365: 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; --Testcase 366: CREATE FOREIGN TABLE INT4_TBL(f1 int4 OPTIONS (key 'true')) SERVER sqlite_svr; --Testcase 367: CREATE FOREIGN TABLE FLOAT8_TBL(f1 float8 OPTIONS (key 'true')) SERVER sqlite_svr; --Testcase 368: CREATE FOREIGN TABLE INT8_TBL( q1 int8 OPTIONS (key 'true'), q2 int8 OPTIONS (key 'true') ) SERVER sqlite_svr; --Testcase 369: CREATE FOREIGN TABLE INT2_TBL(f1 int2 OPTIONS (key 'true')) SERVER sqlite_svr; --Testcase 1: INSERT INTO J1_TBL VALUES (1, 4, 'one'); --Testcase 2: INSERT INTO J1_TBL VALUES (2, 3, 'two'); --Testcase 3: INSERT INTO J1_TBL VALUES (3, 2, 'three'); --Testcase 4: INSERT INTO J1_TBL VALUES (4, 1, 'four'); --Testcase 5: INSERT INTO J1_TBL VALUES (5, 0, 'five'); --Testcase 6: INSERT INTO J1_TBL VALUES (6, 6, 'six'); --Testcase 7: INSERT INTO J1_TBL VALUES (7, 7, 'seven'); --Testcase 8: INSERT INTO J1_TBL VALUES (8, 8, 'eight'); --Testcase 9: INSERT INTO J1_TBL VALUES (0, NULL, 'zero'); --Testcase 10: INSERT INTO J1_TBL VALUES (NULL, NULL, 'null'); --Testcase 11: INSERT INTO J1_TBL VALUES (NULL, 0, 'zero'); --Testcase 12: INSERT INTO J2_TBL VALUES (1, -1); --Testcase 13: INSERT INTO J2_TBL VALUES (2, 2); --Testcase 14: INSERT INTO J2_TBL VALUES (3, -3); --Testcase 15: INSERT INTO J2_TBL VALUES (2, 4); --Testcase 16: INSERT INTO J2_TBL VALUES (5, -5); --Testcase 17: INSERT INTO J2_TBL VALUES (5, -5); --Testcase 18: INSERT INTO J2_TBL VALUES (0, NULL); --Testcase 19: INSERT INTO J2_TBL VALUES (NULL, NULL); --Testcase 20: INSERT INTO J2_TBL VALUES (NULL, 0); -- useful in some tests below --Testcase 370: create temp table onerow(); --Testcase 371: insert into onerow default values; analyze onerow; -- -- CORRELATION NAMES -- Make sure that table/column aliases are supported -- before diving into more complex join syntax. -- --Testcase 21: 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) --Testcase 22: 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) --Testcase 23: 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) --Testcase 24: 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) --Testcase 25: 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 | 2 | 3 | two | 1 | -1 | 3 | 2 | three | 1 | -1 | 4 | 1 | four | 1 | -1 | 5 | 0 | five | 1 | -1 | 6 | 6 | six | 1 | -1 | 7 | 7 | seven | 1 | -1 | 8 | 8 | eight | 1 | -1 | 0 | | zero | 1 | -1 | | | null | 1 | -1 | | 0 | zero | 1 | -1 | 1 | 4 | one | 2 | 2 | 2 | 3 | two | 2 | 2 | 3 | 2 | three | 2 | 2 | 4 | 1 | four | 2 | 2 | 5 | 0 | five | 2 | 2 | 6 | 6 | six | 2 | 2 | 7 | 7 | seven | 2 | 2 | 8 | 8 | eight | 2 | 2 | 0 | | zero | 2 | 2 | | | null | 2 | 2 | | 0 | zero | 2 | 2 | 1 | 4 | one | 3 | -3 | 2 | 3 | two | 3 | -3 | 3 | 2 | three | 3 | -3 | 4 | 1 | four | 3 | -3 | 5 | 0 | five | 3 | -3 | 6 | 6 | six | 3 | -3 | 7 | 7 | seven | 3 | -3 | 8 | 8 | eight | 3 | -3 | 0 | | zero | 3 | -3 | | | null | 3 | -3 | | 0 | zero | 3 | -3 | 1 | 4 | one | 2 | 4 | 2 | 3 | two | 2 | 4 | 3 | 2 | three | 2 | 4 | 4 | 1 | four | 2 | 4 | 5 | 0 | five | 2 | 4 | 6 | 6 | six | 2 | 4 | 7 | 7 | seven | 2 | 4 | 8 | 8 | eight | 2 | 4 | 0 | | zero | 2 | 4 | | | null | 2 | 4 | | 0 | zero | 2 | 4 | 1 | 4 | one | 5 | -5 | 2 | 3 | two | 5 | -5 | 3 | 2 | three | 5 | -5 | 4 | 1 | four | 5 | -5 | 5 | 0 | five | 5 | -5 | 6 | 6 | six | 5 | -5 | 7 | 7 | seven | 5 | -5 | 8 | 8 | eight | 5 | -5 | 0 | | zero | 5 | -5 | | | null | 5 | -5 | | 0 | zero | 5 | -5 | 1 | 4 | one | 5 | -5 | 2 | 3 | two | 5 | -5 | 3 | 2 | three | 5 | -5 | 4 | 1 | four | 5 | -5 | 5 | 0 | five | 5 | -5 | 6 | 6 | six | 5 | -5 | 7 | 7 | seven | 5 | -5 | 8 | 8 | eight | 5 | -5 | 0 | | zero | 5 | -5 | | | null | 5 | -5 | | 0 | zero | 5 | -5 | 1 | 4 | one | 0 | | 2 | 3 | two | 0 | | 3 | 2 | three | 0 | | 4 | 1 | four | 0 | | 5 | 0 | five | 0 | | 6 | 6 | six | 0 | | 7 | 7 | seven | 0 | | 8 | 8 | eight | 0 | | 0 | | zero | 0 | | | | null | 0 | | | 0 | zero | 0 | | 1 | 4 | one | | | 2 | 3 | two | | | 3 | 2 | three | | | 4 | 1 | four | | | 5 | 0 | five | | | 6 | 6 | six | | | 7 | 7 | seven | | | 8 | 8 | eight | | | 0 | | zero | | | | | null | | | | 0 | zero | | | 1 | 4 | one | | 0 | 2 | 3 | two | | 0 | 3 | 2 | three | | 0 | 4 | 1 | four | | 0 | 5 | 0 | five | | 0 | 6 | 6 | six | | 0 | 7 | 7 | seven | | 0 | 8 | 8 | eight | | 0 | 0 | | zero | | 0 | | | null | | 0 | | 0 | zero | | 0 (99 rows) --Testcase 26: 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. -- --Testcase 27: SELECT '' AS "xxx", * FROM J1_TBL CROSS JOIN J2_TBL; xxx | i | j | t | i | k -----+---+---+-------+---+---- | 1 | 4 | one | 1 | -1 | 2 | 3 | two | 1 | -1 | 3 | 2 | three | 1 | -1 | 4 | 1 | four | 1 | -1 | 5 | 0 | five | 1 | -1 | 6 | 6 | six | 1 | -1 | 7 | 7 | seven | 1 | -1 | 8 | 8 | eight | 1 | -1 | 0 | | zero | 1 | -1 | | | null | 1 | -1 | | 0 | zero | 1 | -1 | 1 | 4 | one | 2 | 2 | 2 | 3 | two | 2 | 2 | 3 | 2 | three | 2 | 2 | 4 | 1 | four | 2 | 2 | 5 | 0 | five | 2 | 2 | 6 | 6 | six | 2 | 2 | 7 | 7 | seven | 2 | 2 | 8 | 8 | eight | 2 | 2 | 0 | | zero | 2 | 2 | | | null | 2 | 2 | | 0 | zero | 2 | 2 | 1 | 4 | one | 3 | -3 | 2 | 3 | two | 3 | -3 | 3 | 2 | three | 3 | -3 | 4 | 1 | four | 3 | -3 | 5 | 0 | five | 3 | -3 | 6 | 6 | six | 3 | -3 | 7 | 7 | seven | 3 | -3 | 8 | 8 | eight | 3 | -3 | 0 | | zero | 3 | -3 | | | null | 3 | -3 | | 0 | zero | 3 | -3 | 1 | 4 | one | 2 | 4 | 2 | 3 | two | 2 | 4 | 3 | 2 | three | 2 | 4 | 4 | 1 | four | 2 | 4 | 5 | 0 | five | 2 | 4 | 6 | 6 | six | 2 | 4 | 7 | 7 | seven | 2 | 4 | 8 | 8 | eight | 2 | 4 | 0 | | zero | 2 | 4 | | | null | 2 | 4 | | 0 | zero | 2 | 4 | 1 | 4 | one | 5 | -5 | 2 | 3 | two | 5 | -5 | 3 | 2 | three | 5 | -5 | 4 | 1 | four | 5 | -5 | 5 | 0 | five | 5 | -5 | 6 | 6 | six | 5 | -5 | 7 | 7 | seven | 5 | -5 | 8 | 8 | eight | 5 | -5 | 0 | | zero | 5 | -5 | | | null | 5 | -5 | | 0 | zero | 5 | -5 | 1 | 4 | one | 5 | -5 | 2 | 3 | two | 5 | -5 | 3 | 2 | three | 5 | -5 | 4 | 1 | four | 5 | -5 | 5 | 0 | five | 5 | -5 | 6 | 6 | six | 5 | -5 | 7 | 7 | seven | 5 | -5 | 8 | 8 | eight | 5 | -5 | 0 | | zero | 5 | -5 | | | null | 5 | -5 | | 0 | zero | 5 | -5 | 1 | 4 | one | 0 | | 2 | 3 | two | 0 | | 3 | 2 | three | 0 | | 4 | 1 | four | 0 | | 5 | 0 | five | 0 | | 6 | 6 | six | 0 | | 7 | 7 | seven | 0 | | 8 | 8 | eight | 0 | | 0 | | zero | 0 | | | | null | 0 | | | 0 | zero | 0 | | 1 | 4 | one | | | 2 | 3 | two | | | 3 | 2 | three | | | 4 | 1 | four | | | 5 | 0 | five | | | 6 | 6 | six | | | 7 | 7 | seven | | | 8 | 8 | eight | | | 0 | | zero | | | | | null | | | | 0 | zero | | | 1 | 4 | one | | 0 | 2 | 3 | two | | 0 | 3 | 2 | three | | 0 | 4 | 1 | four | | 0 | 5 | 0 | five | | 0 | 6 | 6 | six | | 0 | 7 | 7 | seven | | 0 | 8 | 8 | eight | | 0 | 0 | | zero | | 0 | | | null | | 0 | | 0 | zero | | 0 (99 rows) -- ambiguous column --Testcase 28: 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 --Testcase 29: SELECT '' AS "xxx", t1.i, k, t FROM J1_TBL t1 CROSS JOIN J2_TBL t2; xxx | i | k | t -----+---+----+------- | 1 | -1 | one | 2 | -1 | two | 3 | -1 | three | 4 | -1 | four | 5 | -1 | five | 6 | -1 | six | 7 | -1 | seven | 8 | -1 | eight | 0 | -1 | zero | | -1 | null | | -1 | zero | 1 | 2 | one | 2 | 2 | two | 3 | 2 | three | 4 | 2 | four | 5 | 2 | five | 6 | 2 | six | 7 | 2 | seven | 8 | 2 | eight | 0 | 2 | zero | | 2 | null | | 2 | zero | 1 | -3 | one | 2 | -3 | two | 3 | -3 | three | 4 | -3 | four | 5 | -3 | five | 6 | -3 | six | 7 | -3 | seven | 8 | -3 | eight | 0 | -3 | zero | | -3 | null | | -3 | zero | 1 | 4 | one | 2 | 4 | two | 3 | 4 | three | 4 | 4 | four | 5 | 4 | five | 6 | 4 | six | 7 | 4 | seven | 8 | 4 | eight | 0 | 4 | zero | | 4 | null | | 4 | zero | 1 | -5 | one | 2 | -5 | two | 3 | -5 | three | 4 | -5 | four | 5 | -5 | five | 6 | -5 | six | 7 | -5 | seven | 8 | -5 | eight | 0 | -5 | zero | | -5 | null | | -5 | zero | 1 | -5 | one | 2 | -5 | two | 3 | -5 | three | 4 | -5 | four | 5 | -5 | five | 6 | -5 | six | 7 | -5 | seven | 8 | -5 | eight | 0 | -5 | zero | | -5 | null | | -5 | zero | 1 | | one | 2 | | two | 3 | | three | 4 | | four | 5 | | five | 6 | | six | 7 | | seven | 8 | | eight | 0 | | zero | | | null | | | zero | 1 | | one | 2 | | two | 3 | | three | 4 | | four | 5 | | five | 6 | | six | 7 | | seven | 8 | | eight | 0 | | zero | | | null | | | zero | 1 | 0 | one | 2 | 0 | two | 3 | 0 | three | 4 | 0 | four | 5 | 0 | five | 6 | 0 | six | 7 | 0 | seven | 8 | 0 | eight | 0 | 0 | zero | | 0 | null | | 0 | zero (99 rows) --Testcase 30: 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 | 2 | two | -1 | 3 | three | -1 | 4 | four | -1 | 5 | five | -1 | 6 | six | -1 | 7 | seven | -1 | 8 | eight | -1 | 0 | zero | -1 | | null | -1 | | zero | -1 | 1 | one | 2 | 2 | two | 2 | 3 | three | 2 | 4 | four | 2 | 5 | five | 2 | 6 | six | 2 | 7 | seven | 2 | 8 | eight | 2 | 0 | zero | 2 | | null | 2 | | zero | 2 | 1 | one | -3 | 2 | two | -3 | 3 | three | -3 | 4 | four | -3 | 5 | five | -3 | 6 | six | -3 | 7 | seven | -3 | 8 | eight | -3 | 0 | zero | -3 | | null | -3 | | zero | -3 | 1 | one | 4 | 2 | two | 4 | 3 | three | 4 | 4 | four | 4 | 5 | five | 4 | 6 | six | 4 | 7 | seven | 4 | 8 | eight | 4 | 0 | zero | 4 | | null | 4 | | zero | 4 | 1 | one | -5 | 2 | two | -5 | 3 | three | -5 | 4 | four | -5 | 5 | five | -5 | 6 | six | -5 | 7 | seven | -5 | 8 | eight | -5 | 0 | zero | -5 | | null | -5 | | zero | -5 | 1 | one | -5 | 2 | two | -5 | 3 | three | -5 | 4 | four | -5 | 5 | five | -5 | 6 | six | -5 | 7 | seven | -5 | 8 | eight | -5 | 0 | zero | -5 | | null | -5 | | zero | -5 | 1 | one | | 2 | two | | 3 | three | | 4 | four | | 5 | five | | 6 | six | | 7 | seven | | 8 | eight | | 0 | zero | | | null | | | zero | | 1 | one | | 2 | two | | 3 | three | | 4 | four | | 5 | five | | 6 | six | | 7 | seven | | 8 | eight | | 0 | zero | | | null | | | zero | | 1 | one | 0 | 2 | two | 0 | 3 | three | 0 | 4 | four | 0 | 5 | five | 0 | 6 | six | 0 | 7 | seven | 0 | 8 | eight | 0 | 0 | zero | 0 | | null | 0 | | zero | 0 (99 rows) --Testcase 31: 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 | 2 | 3 | -1 | 3 | 2 | -1 | 4 | 1 | -1 | 5 | 0 | -1 | 6 | 6 | -1 | 7 | 7 | -1 | 8 | 8 | -1 | 0 | | -1 | | | -1 | | 0 | -1 | 1 | 4 | 2 | 2 | 3 | 2 | 3 | 2 | 2 | 4 | 1 | 2 | 5 | 0 | 2 | 6 | 6 | 2 | 7 | 7 | 2 | 8 | 8 | 2 | 0 | | 2 | | | 2 | | 0 | 2 | 1 | 4 | -3 | 2 | 3 | -3 | 3 | 2 | -3 | 4 | 1 | -3 | 5 | 0 | -3 | 6 | 6 | -3 | 7 | 7 | -3 | 8 | 8 | -3 | 0 | | -3 | | | -3 | | 0 | -3 | 1 | 4 | 4 | 2 | 3 | 4 | 3 | 2 | 4 | 4 | 1 | 4 | 5 | 0 | 4 | 6 | 6 | 4 | 7 | 7 | 4 | 8 | 8 | 4 | 0 | | 4 | | | 4 | | 0 | 4 | 1 | 4 | -5 | 2 | 3 | -5 | 3 | 2 | -5 | 4 | 1 | -5 | 5 | 0 | -5 | 6 | 6 | -5 | 7 | 7 | -5 | 8 | 8 | -5 | 0 | | -5 | | | -5 | | 0 | -5 | 1 | 4 | -5 | 2 | 3 | -5 | 3 | 2 | -5 | 4 | 1 | -5 | 5 | 0 | -5 | 6 | 6 | -5 | 7 | 7 | -5 | 8 | 8 | -5 | 0 | | -5 | | | -5 | | 0 | -5 | 1 | 4 | | 2 | 3 | | 3 | 2 | | 4 | 1 | | 5 | 0 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 0 | | | | | | | 0 | | 1 | 4 | | 2 | 3 | | 3 | 2 | | 4 | 1 | | 5 | 0 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 0 | | | | | | | 0 | | 1 | 4 | 0 | 2 | 3 | 0 | 3 | 2 | 0 | 4 | 1 | 0 | 5 | 0 | 0 | 6 | 6 | 0 | 7 | 7 | 0 | 8 | 8 | 0 | 0 | | 0 | | | 0 | | 0 | 0 (99 rows) --Testcase 32: 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 | 2 | 3 | two | 1 | -1 | 1 | -1 | 2 | 3 | two | 1 | -1 | 2 | 2 | 2 | 3 | two | 1 | -1 | 3 | -3 | 2 | 3 | two | 1 | -1 | 2 | 4 | 2 | 3 | two | 1 | -1 | 5 | -5 | 2 | 3 | two | 1 | -1 | 5 | -5 | 2 | 3 | two | 1 | -1 | 0 | | 2 | 3 | two | 1 | -1 | | | 2 | 3 | two | 1 | -1 | | 0 | 3 | 2 | three | 1 | -1 | 1 | -1 | 3 | 2 | three | 1 | -1 | 2 | 2 | 3 | 2 | three | 1 | -1 | 3 | -3 | 3 | 2 | three | 1 | -1 | 2 | 4 | 3 | 2 | three | 1 | -1 | 5 | -5 | 3 | 2 | three | 1 | -1 | 5 | -5 | 3 | 2 | three | 1 | -1 | 0 | | 3 | 2 | three | 1 | -1 | | | 3 | 2 | three | 1 | -1 | | 0 | 4 | 1 | four | 1 | -1 | 1 | -1 | 4 | 1 | four | 1 | -1 | 2 | 2 | 4 | 1 | four | 1 | -1 | 3 | -3 | 4 | 1 | four | 1 | -1 | 2 | 4 | 4 | 1 | four | 1 | -1 | 5 | -5 | 4 | 1 | four | 1 | -1 | 5 | -5 | 4 | 1 | four | 1 | -1 | 0 | | 4 | 1 | four | 1 | -1 | | | 4 | 1 | four | 1 | -1 | | 0 | 5 | 0 | five | 1 | -1 | 1 | -1 | 5 | 0 | five | 1 | -1 | 2 | 2 | 5 | 0 | five | 1 | -1 | 3 | -3 | 5 | 0 | five | 1 | -1 | 2 | 4 | 5 | 0 | five | 1 | -1 | 5 | -5 | 5 | 0 | five | 1 | -1 | 5 | -5 | 5 | 0 | five | 1 | -1 | 0 | | 5 | 0 | five | 1 | -1 | | | 5 | 0 | five | 1 | -1 | | 0 | 6 | 6 | six | 1 | -1 | 1 | -1 | 6 | 6 | six | 1 | -1 | 2 | 2 | 6 | 6 | six | 1 | -1 | 3 | -3 | 6 | 6 | six | 1 | -1 | 2 | 4 | 6 | 6 | six | 1 | -1 | 5 | -5 | 6 | 6 | six | 1 | -1 | 5 | -5 | 6 | 6 | six | 1 | -1 | 0 | | 6 | 6 | six | 1 | -1 | | | 6 | 6 | six | 1 | -1 | | 0 | 7 | 7 | seven | 1 | -1 | 1 | -1 | 7 | 7 | seven | 1 | -1 | 2 | 2 | 7 | 7 | seven | 1 | -1 | 3 | -3 | 7 | 7 | seven | 1 | -1 | 2 | 4 | 7 | 7 | seven | 1 | -1 | 5 | -5 | 7 | 7 | seven | 1 | -1 | 5 | -5 | 7 | 7 | seven | 1 | -1 | 0 | | 7 | 7 | seven | 1 | -1 | | | 7 | 7 | seven | 1 | -1 | | 0 | 8 | 8 | eight | 1 | -1 | 1 | -1 | 8 | 8 | eight | 1 | -1 | 2 | 2 | 8 | 8 | eight | 1 | -1 | 3 | -3 | 8 | 8 | eight | 1 | -1 | 2 | 4 | 8 | 8 | eight | 1 | -1 | 5 | -5 | 8 | 8 | eight | 1 | -1 | 5 | -5 | 8 | 8 | eight | 1 | -1 | 0 | | 8 | 8 | eight | 1 | -1 | | | 8 | 8 | eight | 1 | -1 | | 0 | 0 | | zero | 1 | -1 | 1 | -1 | 0 | | zero | 1 | -1 | 2 | 2 | 0 | | zero | 1 | -1 | 3 | -3 | 0 | | zero | 1 | -1 | 2 | 4 | 0 | | zero | 1 | -1 | 5 | -5 | 0 | | zero | 1 | -1 | 5 | -5 | 0 | | zero | 1 | -1 | 0 | | 0 | | zero | 1 | -1 | | | 0 | | zero | 1 | -1 | | 0 | | | null | 1 | -1 | 1 | -1 | | | null | 1 | -1 | 2 | 2 | | | null | 1 | -1 | 3 | -3 | | | null | 1 | -1 | 2 | 4 | | | null | 1 | -1 | 5 | -5 | | | null | 1 | -1 | 5 | -5 | | | null | 1 | -1 | 0 | | | | null | 1 | -1 | | | | | null | 1 | -1 | | 0 | | 0 | zero | 1 | -1 | 1 | -1 | | 0 | zero | 1 | -1 | 2 | 2 | | 0 | zero | 1 | -1 | 3 | -3 | | 0 | zero | 1 | -1 | 2 | 4 | | 0 | zero | 1 | -1 | 5 | -5 | | 0 | zero | 1 | -1 | 5 | -5 | | 0 | zero | 1 | -1 | 0 | | | 0 | zero | 1 | -1 | | | | 0 | zero | 1 | -1 | | 0 | 1 | 4 | one | 2 | 2 | 1 | -1 | 1 | 4 | one | 2 | 2 | 2 | 2 | 1 | 4 | one | 2 | 2 | 3 | -3 | 1 | 4 | one | 2 | 2 | 2 | 4 | 1 | 4 | one | 2 | 2 | 5 | -5 | 1 | 4 | one | 2 | 2 | 5 | -5 | 1 | 4 | one | 2 | 2 | 0 | | 1 | 4 | one | 2 | 2 | | | 1 | 4 | one | 2 | 2 | | 0 | 2 | 3 | two | 2 | 2 | 1 | -1 | 2 | 3 | two | 2 | 2 | 2 | 2 | 2 | 3 | two | 2 | 2 | 3 | -3 | 2 | 3 | two | 2 | 2 | 2 | 4 | 2 | 3 | two | 2 | 2 | 5 | -5 | 2 | 3 | two | 2 | 2 | 5 | -5 | 2 | 3 | two | 2 | 2 | 0 | | 2 | 3 | two | 2 | 2 | | | 2 | 3 | two | 2 | 2 | | 0 | 3 | 2 | three | 2 | 2 | 1 | -1 | 3 | 2 | three | 2 | 2 | 2 | 2 | 3 | 2 | three | 2 | 2 | 3 | -3 | 3 | 2 | three | 2 | 2 | 2 | 4 | 3 | 2 | three | 2 | 2 | 5 | -5 | 3 | 2 | three | 2 | 2 | 5 | -5 | 3 | 2 | three | 2 | 2 | 0 | | 3 | 2 | three | 2 | 2 | | | 3 | 2 | three | 2 | 2 | | 0 | 4 | 1 | four | 2 | 2 | 1 | -1 | 4 | 1 | four | 2 | 2 | 2 | 2 | 4 | 1 | four | 2 | 2 | 3 | -3 | 4 | 1 | four | 2 | 2 | 2 | 4 | 4 | 1 | four | 2 | 2 | 5 | -5 | 4 | 1 | four | 2 | 2 | 5 | -5 | 4 | 1 | four | 2 | 2 | 0 | | 4 | 1 | four | 2 | 2 | | | 4 | 1 | four | 2 | 2 | | 0 | 5 | 0 | five | 2 | 2 | 1 | -1 | 5 | 0 | five | 2 | 2 | 2 | 2 | 5 | 0 | five | 2 | 2 | 3 | -3 | 5 | 0 | five | 2 | 2 | 2 | 4 | 5 | 0 | five | 2 | 2 | 5 | -5 | 5 | 0 | five | 2 | 2 | 5 | -5 | 5 | 0 | five | 2 | 2 | 0 | | 5 | 0 | five | 2 | 2 | | | 5 | 0 | five | 2 | 2 | | 0 | 6 | 6 | six | 2 | 2 | 1 | -1 | 6 | 6 | six | 2 | 2 | 2 | 2 | 6 | 6 | six | 2 | 2 | 3 | -3 | 6 | 6 | six | 2 | 2 | 2 | 4 | 6 | 6 | six | 2 | 2 | 5 | -5 | 6 | 6 | six | 2 | 2 | 5 | -5 | 6 | 6 | six | 2 | 2 | 0 | | 6 | 6 | six | 2 | 2 | | | 6 | 6 | six | 2 | 2 | | 0 | 7 | 7 | seven | 2 | 2 | 1 | -1 | 7 | 7 | seven | 2 | 2 | 2 | 2 | 7 | 7 | seven | 2 | 2 | 3 | -3 | 7 | 7 | seven | 2 | 2 | 2 | 4 | 7 | 7 | seven | 2 | 2 | 5 | -5 | 7 | 7 | seven | 2 | 2 | 5 | -5 | 7 | 7 | seven | 2 | 2 | 0 | | 7 | 7 | seven | 2 | 2 | | | 7 | 7 | seven | 2 | 2 | | 0 | 8 | 8 | eight | 2 | 2 | 1 | -1 | 8 | 8 | eight | 2 | 2 | 2 | 2 | 8 | 8 | eight | 2 | 2 | 3 | -3 | 8 | 8 | eight | 2 | 2 | 2 | 4 | 8 | 8 | eight | 2 | 2 | 5 | -5 | 8 | 8 | eight | 2 | 2 | 5 | -5 | 8 | 8 | eight | 2 | 2 | 0 | | 8 | 8 | eight | 2 | 2 | | | 8 | 8 | eight | 2 | 2 | | 0 | 0 | | zero | 2 | 2 | 1 | -1 | 0 | | zero | 2 | 2 | 2 | 2 | 0 | | zero | 2 | 2 | 3 | -3 | 0 | | zero | 2 | 2 | 2 | 4 | 0 | | zero | 2 | 2 | 5 | -5 | 0 | | zero | 2 | 2 | 5 | -5 | 0 | | zero | 2 | 2 | 0 | | 0 | | zero | 2 | 2 | | | 0 | | zero | 2 | 2 | | 0 | | | null | 2 | 2 | 1 | -1 | | | null | 2 | 2 | 2 | 2 | | | null | 2 | 2 | 3 | -3 | | | null | 2 | 2 | 2 | 4 | | | null | 2 | 2 | 5 | -5 | | | null | 2 | 2 | 5 | -5 | | | null | 2 | 2 | 0 | | | | null | 2 | 2 | | | | | null | 2 | 2 | | 0 | | 0 | zero | 2 | 2 | 1 | -1 | | 0 | zero | 2 | 2 | 2 | 2 | | 0 | zero | 2 | 2 | 3 | -3 | | 0 | zero | 2 | 2 | 2 | 4 | | 0 | zero | 2 | 2 | 5 | -5 | | 0 | zero | 2 | 2 | 5 | -5 | | 0 | zero | 2 | 2 | 0 | | | 0 | zero | 2 | 2 | | | | 0 | zero | 2 | 2 | | 0 | 1 | 4 | one | 3 | -3 | 1 | -1 | 1 | 4 | one | 3 | -3 | 2 | 2 | 1 | 4 | one | 3 | -3 | 3 | -3 | 1 | 4 | one | 3 | -3 | 2 | 4 | 1 | 4 | one | 3 | -3 | 5 | -5 | 1 | 4 | one | 3 | -3 | 5 | -5 | 1 | 4 | one | 3 | -3 | 0 | | 1 | 4 | one | 3 | -3 | | | 1 | 4 | one | 3 | -3 | | 0 | 2 | 3 | two | 3 | -3 | 1 | -1 | 2 | 3 | two | 3 | -3 | 2 | 2 | 2 | 3 | two | 3 | -3 | 3 | -3 | 2 | 3 | two | 3 | -3 | 2 | 4 | 2 | 3 | two | 3 | -3 | 5 | -5 | 2 | 3 | two | 3 | -3 | 5 | -5 | 2 | 3 | two | 3 | -3 | 0 | | 2 | 3 | two | 3 | -3 | | | 2 | 3 | two | 3 | -3 | | 0 | 3 | 2 | three | 3 | -3 | 1 | -1 | 3 | 2 | three | 3 | -3 | 2 | 2 | 3 | 2 | three | 3 | -3 | 3 | -3 | 3 | 2 | three | 3 | -3 | 2 | 4 | 3 | 2 | three | 3 | -3 | 5 | -5 | 3 | 2 | three | 3 | -3 | 5 | -5 | 3 | 2 | three | 3 | -3 | 0 | | 3 | 2 | three | 3 | -3 | | | 3 | 2 | three | 3 | -3 | | 0 | 4 | 1 | four | 3 | -3 | 1 | -1 | 4 | 1 | four | 3 | -3 | 2 | 2 | 4 | 1 | four | 3 | -3 | 3 | -3 | 4 | 1 | four | 3 | -3 | 2 | 4 | 4 | 1 | four | 3 | -3 | 5 | -5 | 4 | 1 | four | 3 | -3 | 5 | -5 | 4 | 1 | four | 3 | -3 | 0 | | 4 | 1 | four | 3 | -3 | | | 4 | 1 | four | 3 | -3 | | 0 | 5 | 0 | five | 3 | -3 | 1 | -1 | 5 | 0 | five | 3 | -3 | 2 | 2 | 5 | 0 | five | 3 | -3 | 3 | -3 | 5 | 0 | five | 3 | -3 | 2 | 4 | 5 | 0 | five | 3 | -3 | 5 | -5 | 5 | 0 | five | 3 | -3 | 5 | -5 | 5 | 0 | five | 3 | -3 | 0 | | 5 | 0 | five | 3 | -3 | | | 5 | 0 | five | 3 | -3 | | 0 | 6 | 6 | six | 3 | -3 | 1 | -1 | 6 | 6 | six | 3 | -3 | 2 | 2 | 6 | 6 | six | 3 | -3 | 3 | -3 | 6 | 6 | six | 3 | -3 | 2 | 4 | 6 | 6 | six | 3 | -3 | 5 | -5 | 6 | 6 | six | 3 | -3 | 5 | -5 | 6 | 6 | six | 3 | -3 | 0 | | 6 | 6 | six | 3 | -3 | | | 6 | 6 | six | 3 | -3 | | 0 | 7 | 7 | seven | 3 | -3 | 1 | -1 | 7 | 7 | seven | 3 | -3 | 2 | 2 | 7 | 7 | seven | 3 | -3 | 3 | -3 | 7 | 7 | seven | 3 | -3 | 2 | 4 | 7 | 7 | seven | 3 | -3 | 5 | -5 | 7 | 7 | seven | 3 | -3 | 5 | -5 | 7 | 7 | seven | 3 | -3 | 0 | | 7 | 7 | seven | 3 | -3 | | | 7 | 7 | seven | 3 | -3 | | 0 | 8 | 8 | eight | 3 | -3 | 1 | -1 | 8 | 8 | eight | 3 | -3 | 2 | 2 | 8 | 8 | eight | 3 | -3 | 3 | -3 | 8 | 8 | eight | 3 | -3 | 2 | 4 | 8 | 8 | eight | 3 | -3 | 5 | -5 | 8 | 8 | eight | 3 | -3 | 5 | -5 | 8 | 8 | eight | 3 | -3 | 0 | | 8 | 8 | eight | 3 | -3 | | | 8 | 8 | eight | 3 | -3 | | 0 | 0 | | zero | 3 | -3 | 1 | -1 | 0 | | zero | 3 | -3 | 2 | 2 | 0 | | zero | 3 | -3 | 3 | -3 | 0 | | zero | 3 | -3 | 2 | 4 | 0 | | zero | 3 | -3 | 5 | -5 | 0 | | zero | 3 | -3 | 5 | -5 | 0 | | zero | 3 | -3 | 0 | | 0 | | zero | 3 | -3 | | | 0 | | zero | 3 | -3 | | 0 | | | null | 3 | -3 | 1 | -1 | | | null | 3 | -3 | 2 | 2 | | | null | 3 | -3 | 3 | -3 | | | null | 3 | -3 | 2 | 4 | | | null | 3 | -3 | 5 | -5 | | | null | 3 | -3 | 5 | -5 | | | null | 3 | -3 | 0 | | | | null | 3 | -3 | | | | | null | 3 | -3 | | 0 | | 0 | zero | 3 | -3 | 1 | -1 | | 0 | zero | 3 | -3 | 2 | 2 | | 0 | zero | 3 | -3 | 3 | -3 | | 0 | zero | 3 | -3 | 2 | 4 | | 0 | zero | 3 | -3 | 5 | -5 | | 0 | zero | 3 | -3 | 5 | -5 | | 0 | zero | 3 | -3 | 0 | | | 0 | zero | 3 | -3 | | | | 0 | zero | 3 | -3 | | 0 | 1 | 4 | one | 2 | 4 | 1 | -1 | 1 | 4 | one | 2 | 4 | 2 | 2 | 1 | 4 | one | 2 | 4 | 3 | -3 | 1 | 4 | one | 2 | 4 | 2 | 4 | 1 | 4 | one | 2 | 4 | 5 | -5 | 1 | 4 | one | 2 | 4 | 5 | -5 | 1 | 4 | one | 2 | 4 | 0 | | 1 | 4 | one | 2 | 4 | | | 1 | 4 | one | 2 | 4 | | 0 | 2 | 3 | two | 2 | 4 | 1 | -1 | 2 | 3 | two | 2 | 4 | 2 | 2 | 2 | 3 | two | 2 | 4 | 3 | -3 | 2 | 3 | two | 2 | 4 | 2 | 4 | 2 | 3 | two | 2 | 4 | 5 | -5 | 2 | 3 | two | 2 | 4 | 5 | -5 | 2 | 3 | two | 2 | 4 | 0 | | 2 | 3 | two | 2 | 4 | | | 2 | 3 | two | 2 | 4 | | 0 | 3 | 2 | three | 2 | 4 | 1 | -1 | 3 | 2 | three | 2 | 4 | 2 | 2 | 3 | 2 | three | 2 | 4 | 3 | -3 | 3 | 2 | three | 2 | 4 | 2 | 4 | 3 | 2 | three | 2 | 4 | 5 | -5 | 3 | 2 | three | 2 | 4 | 5 | -5 | 3 | 2 | three | 2 | 4 | 0 | | 3 | 2 | three | 2 | 4 | | | 3 | 2 | three | 2 | 4 | | 0 | 4 | 1 | four | 2 | 4 | 1 | -1 | 4 | 1 | four | 2 | 4 | 2 | 2 | 4 | 1 | four | 2 | 4 | 3 | -3 | 4 | 1 | four | 2 | 4 | 2 | 4 | 4 | 1 | four | 2 | 4 | 5 | -5 | 4 | 1 | four | 2 | 4 | 5 | -5 | 4 | 1 | four | 2 | 4 | 0 | | 4 | 1 | four | 2 | 4 | | | 4 | 1 | four | 2 | 4 | | 0 | 5 | 0 | five | 2 | 4 | 1 | -1 | 5 | 0 | five | 2 | 4 | 2 | 2 | 5 | 0 | five | 2 | 4 | 3 | -3 | 5 | 0 | five | 2 | 4 | 2 | 4 | 5 | 0 | five | 2 | 4 | 5 | -5 | 5 | 0 | five | 2 | 4 | 5 | -5 | 5 | 0 | five | 2 | 4 | 0 | | 5 | 0 | five | 2 | 4 | | | 5 | 0 | five | 2 | 4 | | 0 | 6 | 6 | six | 2 | 4 | 1 | -1 | 6 | 6 | six | 2 | 4 | 2 | 2 | 6 | 6 | six | 2 | 4 | 3 | -3 | 6 | 6 | six | 2 | 4 | 2 | 4 | 6 | 6 | six | 2 | 4 | 5 | -5 | 6 | 6 | six | 2 | 4 | 5 | -5 | 6 | 6 | six | 2 | 4 | 0 | | 6 | 6 | six | 2 | 4 | | | 6 | 6 | six | 2 | 4 | | 0 | 7 | 7 | seven | 2 | 4 | 1 | -1 | 7 | 7 | seven | 2 | 4 | 2 | 2 | 7 | 7 | seven | 2 | 4 | 3 | -3 | 7 | 7 | seven | 2 | 4 | 2 | 4 | 7 | 7 | seven | 2 | 4 | 5 | -5 | 7 | 7 | seven | 2 | 4 | 5 | -5 | 7 | 7 | seven | 2 | 4 | 0 | | 7 | 7 | seven | 2 | 4 | | | 7 | 7 | seven | 2 | 4 | | 0 | 8 | 8 | eight | 2 | 4 | 1 | -1 | 8 | 8 | eight | 2 | 4 | 2 | 2 | 8 | 8 | eight | 2 | 4 | 3 | -3 | 8 | 8 | eight | 2 | 4 | 2 | 4 | 8 | 8 | eight | 2 | 4 | 5 | -5 | 8 | 8 | eight | 2 | 4 | 5 | -5 | 8 | 8 | eight | 2 | 4 | 0 | | 8 | 8 | eight | 2 | 4 | | | 8 | 8 | eight | 2 | 4 | | 0 | 0 | | zero | 2 | 4 | 1 | -1 | 0 | | zero | 2 | 4 | 2 | 2 | 0 | | zero | 2 | 4 | 3 | -3 | 0 | | zero | 2 | 4 | 2 | 4 | 0 | | zero | 2 | 4 | 5 | -5 | 0 | | zero | 2 | 4 | 5 | -5 | 0 | | zero | 2 | 4 | 0 | | 0 | | zero | 2 | 4 | | | 0 | | zero | 2 | 4 | | 0 | | | null | 2 | 4 | 1 | -1 | | | null | 2 | 4 | 2 | 2 | | | null | 2 | 4 | 3 | -3 | | | null | 2 | 4 | 2 | 4 | | | null | 2 | 4 | 5 | -5 | | | null | 2 | 4 | 5 | -5 | | | null | 2 | 4 | 0 | | | | null | 2 | 4 | | | | | null | 2 | 4 | | 0 | | 0 | zero | 2 | 4 | 1 | -1 | | 0 | zero | 2 | 4 | 2 | 2 | | 0 | zero | 2 | 4 | 3 | -3 | | 0 | zero | 2 | 4 | 2 | 4 | | 0 | zero | 2 | 4 | 5 | -5 | | 0 | zero | 2 | 4 | 5 | -5 | | 0 | zero | 2 | 4 | 0 | | | 0 | zero | 2 | 4 | | | | 0 | zero | 2 | 4 | | 0 | 1 | 4 | one | 5 | -5 | 1 | -1 | 1 | 4 | one | 5 | -5 | 2 | 2 | 1 | 4 | one | 5 | -5 | 3 | -3 | 1 | 4 | one | 5 | -5 | 2 | 4 | 1 | 4 | one | 5 | -5 | 5 | -5 | 1 | 4 | one | 5 | -5 | 5 | -5 | 1 | 4 | one | 5 | -5 | 0 | | 1 | 4 | one | 5 | -5 | | | 1 | 4 | one | 5 | -5 | | 0 | 2 | 3 | two | 5 | -5 | 1 | -1 | 2 | 3 | two | 5 | -5 | 2 | 2 | 2 | 3 | two | 5 | -5 | 3 | -3 | 2 | 3 | two | 5 | -5 | 2 | 4 | 2 | 3 | two | 5 | -5 | 5 | -5 | 2 | 3 | two | 5 | -5 | 5 | -5 | 2 | 3 | two | 5 | -5 | 0 | | 2 | 3 | two | 5 | -5 | | | 2 | 3 | two | 5 | -5 | | 0 | 3 | 2 | three | 5 | -5 | 1 | -1 | 3 | 2 | three | 5 | -5 | 2 | 2 | 3 | 2 | three | 5 | -5 | 3 | -3 | 3 | 2 | three | 5 | -5 | 2 | 4 | 3 | 2 | three | 5 | -5 | 5 | -5 | 3 | 2 | three | 5 | -5 | 5 | -5 | 3 | 2 | three | 5 | -5 | 0 | | 3 | 2 | three | 5 | -5 | | | 3 | 2 | three | 5 | -5 | | 0 | 4 | 1 | four | 5 | -5 | 1 | -1 | 4 | 1 | four | 5 | -5 | 2 | 2 | 4 | 1 | four | 5 | -5 | 3 | -3 | 4 | 1 | four | 5 | -5 | 2 | 4 | 4 | 1 | four | 5 | -5 | 5 | -5 | 4 | 1 | four | 5 | -5 | 5 | -5 | 4 | 1 | four | 5 | -5 | 0 | | 4 | 1 | four | 5 | -5 | | | 4 | 1 | four | 5 | -5 | | 0 | 5 | 0 | five | 5 | -5 | 1 | -1 | 5 | 0 | five | 5 | -5 | 2 | 2 | 5 | 0 | five | 5 | -5 | 3 | -3 | 5 | 0 | five | 5 | -5 | 2 | 4 | 5 | 0 | five | 5 | -5 | 5 | -5 | 5 | 0 | five | 5 | -5 | 5 | -5 | 5 | 0 | five | 5 | -5 | 0 | | 5 | 0 | five | 5 | -5 | | | 5 | 0 | five | 5 | -5 | | 0 | 6 | 6 | six | 5 | -5 | 1 | -1 | 6 | 6 | six | 5 | -5 | 2 | 2 | 6 | 6 | six | 5 | -5 | 3 | -3 | 6 | 6 | six | 5 | -5 | 2 | 4 | 6 | 6 | six | 5 | -5 | 5 | -5 | 6 | 6 | six | 5 | -5 | 5 | -5 | 6 | 6 | six | 5 | -5 | 0 | | 6 | 6 | six | 5 | -5 | | | 6 | 6 | six | 5 | -5 | | 0 | 7 | 7 | seven | 5 | -5 | 1 | -1 | 7 | 7 | seven | 5 | -5 | 2 | 2 | 7 | 7 | seven | 5 | -5 | 3 | -3 | 7 | 7 | seven | 5 | -5 | 2 | 4 | 7 | 7 | seven | 5 | -5 | 5 | -5 | 7 | 7 | seven | 5 | -5 | 5 | -5 | 7 | 7 | seven | 5 | -5 | 0 | | 7 | 7 | seven | 5 | -5 | | | 7 | 7 | seven | 5 | -5 | | 0 | 8 | 8 | eight | 5 | -5 | 1 | -1 | 8 | 8 | eight | 5 | -5 | 2 | 2 | 8 | 8 | eight | 5 | -5 | 3 | -3 | 8 | 8 | eight | 5 | -5 | 2 | 4 | 8 | 8 | eight | 5 | -5 | 5 | -5 | 8 | 8 | eight | 5 | -5 | 5 | -5 | 8 | 8 | eight | 5 | -5 | 0 | | 8 | 8 | eight | 5 | -5 | | | 8 | 8 | eight | 5 | -5 | | 0 | 0 | | zero | 5 | -5 | 1 | -1 | 0 | | zero | 5 | -5 | 2 | 2 | 0 | | zero | 5 | -5 | 3 | -3 | 0 | | zero | 5 | -5 | 2 | 4 | 0 | | zero | 5 | -5 | 5 | -5 | 0 | | zero | 5 | -5 | 5 | -5 | 0 | | zero | 5 | -5 | 0 | | 0 | | zero | 5 | -5 | | | 0 | | zero | 5 | -5 | | 0 | | | null | 5 | -5 | 1 | -1 | | | null | 5 | -5 | 2 | 2 | | | null | 5 | -5 | 3 | -3 | | | null | 5 | -5 | 2 | 4 | | | null | 5 | -5 | 5 | -5 | | | null | 5 | -5 | 5 | -5 | | | null | 5 | -5 | 0 | | | | null | 5 | -5 | | | | | null | 5 | -5 | | 0 | | 0 | zero | 5 | -5 | 1 | -1 | | 0 | zero | 5 | -5 | 2 | 2 | | 0 | zero | 5 | -5 | 3 | -3 | | 0 | zero | 5 | -5 | 2 | 4 | | 0 | zero | 5 | -5 | 5 | -5 | | 0 | zero | 5 | -5 | 5 | -5 | | 0 | zero | 5 | -5 | 0 | | | 0 | zero | 5 | -5 | | | | 0 | zero | 5 | -5 | | 0 | 1 | 4 | one | 5 | -5 | 1 | -1 | 1 | 4 | one | 5 | -5 | 2 | 2 | 1 | 4 | one | 5 | -5 | 3 | -3 | 1 | 4 | one | 5 | -5 | 2 | 4 | 1 | 4 | one | 5 | -5 | 5 | -5 | 1 | 4 | one | 5 | -5 | 5 | -5 | 1 | 4 | one | 5 | -5 | 0 | | 1 | 4 | one | 5 | -5 | | | 1 | 4 | one | 5 | -5 | | 0 | 2 | 3 | two | 5 | -5 | 1 | -1 | 2 | 3 | two | 5 | -5 | 2 | 2 | 2 | 3 | two | 5 | -5 | 3 | -3 | 2 | 3 | two | 5 | -5 | 2 | 4 | 2 | 3 | two | 5 | -5 | 5 | -5 | 2 | 3 | two | 5 | -5 | 5 | -5 | 2 | 3 | two | 5 | -5 | 0 | | 2 | 3 | two | 5 | -5 | | | 2 | 3 | two | 5 | -5 | | 0 | 3 | 2 | three | 5 | -5 | 1 | -1 | 3 | 2 | three | 5 | -5 | 2 | 2 | 3 | 2 | three | 5 | -5 | 3 | -3 | 3 | 2 | three | 5 | -5 | 2 | 4 | 3 | 2 | three | 5 | -5 | 5 | -5 | 3 | 2 | three | 5 | -5 | 5 | -5 | 3 | 2 | three | 5 | -5 | 0 | | 3 | 2 | three | 5 | -5 | | | 3 | 2 | three | 5 | -5 | | 0 | 4 | 1 | four | 5 | -5 | 1 | -1 | 4 | 1 | four | 5 | -5 | 2 | 2 | 4 | 1 | four | 5 | -5 | 3 | -3 | 4 | 1 | four | 5 | -5 | 2 | 4 | 4 | 1 | four | 5 | -5 | 5 | -5 | 4 | 1 | four | 5 | -5 | 5 | -5 | 4 | 1 | four | 5 | -5 | 0 | | 4 | 1 | four | 5 | -5 | | | 4 | 1 | four | 5 | -5 | | 0 | 5 | 0 | five | 5 | -5 | 1 | -1 | 5 | 0 | five | 5 | -5 | 2 | 2 | 5 | 0 | five | 5 | -5 | 3 | -3 | 5 | 0 | five | 5 | -5 | 2 | 4 | 5 | 0 | five | 5 | -5 | 5 | -5 | 5 | 0 | five | 5 | -5 | 5 | -5 | 5 | 0 | five | 5 | -5 | 0 | | 5 | 0 | five | 5 | -5 | | | 5 | 0 | five | 5 | -5 | | 0 | 6 | 6 | six | 5 | -5 | 1 | -1 | 6 | 6 | six | 5 | -5 | 2 | 2 | 6 | 6 | six | 5 | -5 | 3 | -3 | 6 | 6 | six | 5 | -5 | 2 | 4 | 6 | 6 | six | 5 | -5 | 5 | -5 | 6 | 6 | six | 5 | -5 | 5 | -5 | 6 | 6 | six | 5 | -5 | 0 | | 6 | 6 | six | 5 | -5 | | | 6 | 6 | six | 5 | -5 | | 0 | 7 | 7 | seven | 5 | -5 | 1 | -1 | 7 | 7 | seven | 5 | -5 | 2 | 2 | 7 | 7 | seven | 5 | -5 | 3 | -3 | 7 | 7 | seven | 5 | -5 | 2 | 4 | 7 | 7 | seven | 5 | -5 | 5 | -5 | 7 | 7 | seven | 5 | -5 | 5 | -5 | 7 | 7 | seven | 5 | -5 | 0 | | 7 | 7 | seven | 5 | -5 | | | 7 | 7 | seven | 5 | -5 | | 0 | 8 | 8 | eight | 5 | -5 | 1 | -1 | 8 | 8 | eight | 5 | -5 | 2 | 2 | 8 | 8 | eight | 5 | -5 | 3 | -3 | 8 | 8 | eight | 5 | -5 | 2 | 4 | 8 | 8 | eight | 5 | -5 | 5 | -5 | 8 | 8 | eight | 5 | -5 | 5 | -5 | 8 | 8 | eight | 5 | -5 | 0 | | 8 | 8 | eight | 5 | -5 | | | 8 | 8 | eight | 5 | -5 | | 0 | 0 | | zero | 5 | -5 | 1 | -1 | 0 | | zero | 5 | -5 | 2 | 2 | 0 | | zero | 5 | -5 | 3 | -3 | 0 | | zero | 5 | -5 | 2 | 4 | 0 | | zero | 5 | -5 | 5 | -5 | 0 | | zero | 5 | -5 | 5 | -5 | 0 | | zero | 5 | -5 | 0 | | 0 | | zero | 5 | -5 | | | 0 | | zero | 5 | -5 | | 0 | | | null | 5 | -5 | 1 | -1 | | | null | 5 | -5 | 2 | 2 | | | null | 5 | -5 | 3 | -3 | | | null | 5 | -5 | 2 | 4 | | | null | 5 | -5 | 5 | -5 | | | null | 5 | -5 | 5 | -5 | | | null | 5 | -5 | 0 | | | | null | 5 | -5 | | | | | null | 5 | -5 | | 0 | | 0 | zero | 5 | -5 | 1 | -1 | | 0 | zero | 5 | -5 | 2 | 2 | | 0 | zero | 5 | -5 | 3 | -3 | | 0 | zero | 5 | -5 | 2 | 4 | | 0 | zero | 5 | -5 | 5 | -5 | | 0 | zero | 5 | -5 | 5 | -5 | | 0 | zero | 5 | -5 | 0 | | | 0 | zero | 5 | -5 | | | | 0 | zero | 5 | -5 | | 0 | 1 | 4 | one | 0 | | 1 | -1 | 1 | 4 | one | 0 | | 2 | 2 | 1 | 4 | one | 0 | | 3 | -3 | 1 | 4 | one | 0 | | 2 | 4 | 1 | 4 | one | 0 | | 5 | -5 | 1 | 4 | one | 0 | | 5 | -5 | 1 | 4 | one | 0 | | 0 | | 1 | 4 | one | 0 | | | | 1 | 4 | one | 0 | | | 0 | 2 | 3 | two | 0 | | 1 | -1 | 2 | 3 | two | 0 | | 2 | 2 | 2 | 3 | two | 0 | | 3 | -3 | 2 | 3 | two | 0 | | 2 | 4 | 2 | 3 | two | 0 | | 5 | -5 | 2 | 3 | two | 0 | | 5 | -5 | 2 | 3 | two | 0 | | 0 | | 2 | 3 | two | 0 | | | | 2 | 3 | two | 0 | | | 0 | 3 | 2 | three | 0 | | 1 | -1 | 3 | 2 | three | 0 | | 2 | 2 | 3 | 2 | three | 0 | | 3 | -3 | 3 | 2 | three | 0 | | 2 | 4 | 3 | 2 | three | 0 | | 5 | -5 | 3 | 2 | three | 0 | | 5 | -5 | 3 | 2 | three | 0 | | 0 | | 3 | 2 | three | 0 | | | | 3 | 2 | three | 0 | | | 0 | 4 | 1 | four | 0 | | 1 | -1 | 4 | 1 | four | 0 | | 2 | 2 | 4 | 1 | four | 0 | | 3 | -3 | 4 | 1 | four | 0 | | 2 | 4 | 4 | 1 | four | 0 | | 5 | -5 | 4 | 1 | four | 0 | | 5 | -5 | 4 | 1 | four | 0 | | 0 | | 4 | 1 | four | 0 | | | | 4 | 1 | four | 0 | | | 0 | 5 | 0 | five | 0 | | 1 | -1 | 5 | 0 | five | 0 | | 2 | 2 | 5 | 0 | five | 0 | | 3 | -3 | 5 | 0 | five | 0 | | 2 | 4 | 5 | 0 | five | 0 | | 5 | -5 | 5 | 0 | five | 0 | | 5 | -5 | 5 | 0 | five | 0 | | 0 | | 5 | 0 | five | 0 | | | | 5 | 0 | five | 0 | | | 0 | 6 | 6 | six | 0 | | 1 | -1 | 6 | 6 | six | 0 | | 2 | 2 | 6 | 6 | six | 0 | | 3 | -3 | 6 | 6 | six | 0 | | 2 | 4 | 6 | 6 | six | 0 | | 5 | -5 | 6 | 6 | six | 0 | | 5 | -5 | 6 | 6 | six | 0 | | 0 | | 6 | 6 | six | 0 | | | | 6 | 6 | six | 0 | | | 0 | 7 | 7 | seven | 0 | | 1 | -1 | 7 | 7 | seven | 0 | | 2 | 2 | 7 | 7 | seven | 0 | | 3 | -3 | 7 | 7 | seven | 0 | | 2 | 4 | 7 | 7 | seven | 0 | | 5 | -5 | 7 | 7 | seven | 0 | | 5 | -5 | 7 | 7 | seven | 0 | | 0 | | 7 | 7 | seven | 0 | | | | 7 | 7 | seven | 0 | | | 0 | 8 | 8 | eight | 0 | | 1 | -1 | 8 | 8 | eight | 0 | | 2 | 2 | 8 | 8 | eight | 0 | | 3 | -3 | 8 | 8 | eight | 0 | | 2 | 4 | 8 | 8 | eight | 0 | | 5 | -5 | 8 | 8 | eight | 0 | | 5 | -5 | 8 | 8 | eight | 0 | | 0 | | 8 | 8 | eight | 0 | | | | 8 | 8 | eight | 0 | | | 0 | 0 | | zero | 0 | | 1 | -1 | 0 | | zero | 0 | | 2 | 2 | 0 | | zero | 0 | | 3 | -3 | 0 | | zero | 0 | | 2 | 4 | 0 | | zero | 0 | | 5 | -5 | 0 | | zero | 0 | | 5 | -5 | 0 | | zero | 0 | | 0 | | 0 | | zero | 0 | | | | 0 | | zero | 0 | | | 0 | | | null | 0 | | 1 | -1 | | | null | 0 | | 2 | 2 | | | null | 0 | | 3 | -3 | | | null | 0 | | 2 | 4 | | | null | 0 | | 5 | -5 | | | null | 0 | | 5 | -5 | | | null | 0 | | 0 | | | | null | 0 | | | | | | null | 0 | | | 0 | | 0 | zero | 0 | | 1 | -1 | | 0 | zero | 0 | | 2 | 2 | | 0 | zero | 0 | | 3 | -3 | | 0 | zero | 0 | | 2 | 4 | | 0 | zero | 0 | | 5 | -5 | | 0 | zero | 0 | | 5 | -5 | | 0 | zero | 0 | | 0 | | | 0 | zero | 0 | | | | | 0 | zero | 0 | | | 0 | 1 | 4 | one | | | 1 | -1 | 1 | 4 | one | | | 2 | 2 | 1 | 4 | one | | | 3 | -3 | 1 | 4 | one | | | 2 | 4 | 1 | 4 | one | | | 5 | -5 | 1 | 4 | one | | | 5 | -5 | 1 | 4 | one | | | 0 | | 1 | 4 | one | | | | | 1 | 4 | one | | | | 0 | 2 | 3 | two | | | 1 | -1 | 2 | 3 | two | | | 2 | 2 | 2 | 3 | two | | | 3 | -3 | 2 | 3 | two | | | 2 | 4 | 2 | 3 | two | | | 5 | -5 | 2 | 3 | two | | | 5 | -5 | 2 | 3 | two | | | 0 | | 2 | 3 | two | | | | | 2 | 3 | two | | | | 0 | 3 | 2 | three | | | 1 | -1 | 3 | 2 | three | | | 2 | 2 | 3 | 2 | three | | | 3 | -3 | 3 | 2 | three | | | 2 | 4 | 3 | 2 | three | | | 5 | -5 | 3 | 2 | three | | | 5 | -5 | 3 | 2 | three | | | 0 | | 3 | 2 | three | | | | | 3 | 2 | three | | | | 0 | 4 | 1 | four | | | 1 | -1 | 4 | 1 | four | | | 2 | 2 | 4 | 1 | four | | | 3 | -3 | 4 | 1 | four | | | 2 | 4 | 4 | 1 | four | | | 5 | -5 | 4 | 1 | four | | | 5 | -5 | 4 | 1 | four | | | 0 | | 4 | 1 | four | | | | | 4 | 1 | four | | | | 0 | 5 | 0 | five | | | 1 | -1 | 5 | 0 | five | | | 2 | 2 | 5 | 0 | five | | | 3 | -3 | 5 | 0 | five | | | 2 | 4 | 5 | 0 | five | | | 5 | -5 | 5 | 0 | five | | | 5 | -5 | 5 | 0 | five | | | 0 | | 5 | 0 | five | | | | | 5 | 0 | five | | | | 0 | 6 | 6 | six | | | 1 | -1 | 6 | 6 | six | | | 2 | 2 | 6 | 6 | six | | | 3 | -3 | 6 | 6 | six | | | 2 | 4 | 6 | 6 | six | | | 5 | -5 | 6 | 6 | six | | | 5 | -5 | 6 | 6 | six | | | 0 | | 6 | 6 | six | | | | | 6 | 6 | six | | | | 0 | 7 | 7 | seven | | | 1 | -1 | 7 | 7 | seven | | | 2 | 2 | 7 | 7 | seven | | | 3 | -3 | 7 | 7 | seven | | | 2 | 4 | 7 | 7 | seven | | | 5 | -5 | 7 | 7 | seven | | | 5 | -5 | 7 | 7 | seven | | | 0 | | 7 | 7 | seven | | | | | 7 | 7 | seven | | | | 0 | 8 | 8 | eight | | | 1 | -1 | 8 | 8 | eight | | | 2 | 2 | 8 | 8 | eight | | | 3 | -3 | 8 | 8 | eight | | | 2 | 4 | 8 | 8 | eight | | | 5 | -5 | 8 | 8 | eight | | | 5 | -5 | 8 | 8 | eight | | | 0 | | 8 | 8 | eight | | | | | 8 | 8 | eight | | | | 0 | 0 | | zero | | | 1 | -1 | 0 | | zero | | | 2 | 2 | 0 | | zero | | | 3 | -3 | 0 | | zero | | | 2 | 4 | 0 | | zero | | | 5 | -5 | 0 | | zero | | | 5 | -5 | 0 | | zero | | | 0 | | 0 | | zero | | | | | 0 | | zero | | | | 0 | | | null | | | 1 | -1 | | | null | | | 2 | 2 | | | null | | | 3 | -3 | | | null | | | 2 | 4 | | | null | | | 5 | -5 | | | null | | | 5 | -5 | | | null | | | 0 | | | | null | | | | | | | null | | | | 0 | | 0 | zero | | | 1 | -1 | | 0 | zero | | | 2 | 2 | | 0 | zero | | | 3 | -3 | | 0 | zero | | | 2 | 4 | | 0 | zero | | | 5 | -5 | | 0 | zero | | | 5 | -5 | | 0 | zero | | | 0 | | | 0 | zero | | | | | | 0 | zero | | | | 0 | 1 | 4 | one | | 0 | 1 | -1 | 1 | 4 | one | | 0 | 2 | 2 | 1 | 4 | one | | 0 | 3 | -3 | 1 | 4 | one | | 0 | 2 | 4 | 1 | 4 | one | | 0 | 5 | -5 | 1 | 4 | one | | 0 | 5 | -5 | 1 | 4 | one | | 0 | 0 | | 1 | 4 | one | | 0 | | | 1 | 4 | one | | 0 | | 0 | 2 | 3 | two | | 0 | 1 | -1 | 2 | 3 | two | | 0 | 2 | 2 | 2 | 3 | two | | 0 | 3 | -3 | 2 | 3 | two | | 0 | 2 | 4 | 2 | 3 | two | | 0 | 5 | -5 | 2 | 3 | two | | 0 | 5 | -5 | 2 | 3 | two | | 0 | 0 | | 2 | 3 | two | | 0 | | | 2 | 3 | two | | 0 | | 0 | 3 | 2 | three | | 0 | 1 | -1 | 3 | 2 | three | | 0 | 2 | 2 | 3 | 2 | three | | 0 | 3 | -3 | 3 | 2 | three | | 0 | 2 | 4 | 3 | 2 | three | | 0 | 5 | -5 | 3 | 2 | three | | 0 | 5 | -5 | 3 | 2 | three | | 0 | 0 | | 3 | 2 | three | | 0 | | | 3 | 2 | three | | 0 | | 0 | 4 | 1 | four | | 0 | 1 | -1 | 4 | 1 | four | | 0 | 2 | 2 | 4 | 1 | four | | 0 | 3 | -3 | 4 | 1 | four | | 0 | 2 | 4 | 4 | 1 | four | | 0 | 5 | -5 | 4 | 1 | four | | 0 | 5 | -5 | 4 | 1 | four | | 0 | 0 | | 4 | 1 | four | | 0 | | | 4 | 1 | four | | 0 | | 0 | 5 | 0 | five | | 0 | 1 | -1 | 5 | 0 | five | | 0 | 2 | 2 | 5 | 0 | five | | 0 | 3 | -3 | 5 | 0 | five | | 0 | 2 | 4 | 5 | 0 | five | | 0 | 5 | -5 | 5 | 0 | five | | 0 | 5 | -5 | 5 | 0 | five | | 0 | 0 | | 5 | 0 | five | | 0 | | | 5 | 0 | five | | 0 | | 0 | 6 | 6 | six | | 0 | 1 | -1 | 6 | 6 | six | | 0 | 2 | 2 | 6 | 6 | six | | 0 | 3 | -3 | 6 | 6 | six | | 0 | 2 | 4 | 6 | 6 | six | | 0 | 5 | -5 | 6 | 6 | six | | 0 | 5 | -5 | 6 | 6 | six | | 0 | 0 | | 6 | 6 | six | | 0 | | | 6 | 6 | six | | 0 | | 0 | 7 | 7 | seven | | 0 | 1 | -1 | 7 | 7 | seven | | 0 | 2 | 2 | 7 | 7 | seven | | 0 | 3 | -3 | 7 | 7 | seven | | 0 | 2 | 4 | 7 | 7 | seven | | 0 | 5 | -5 | 7 | 7 | seven | | 0 | 5 | -5 | 7 | 7 | seven | | 0 | 0 | | 7 | 7 | seven | | 0 | | | 7 | 7 | seven | | 0 | | 0 | 8 | 8 | eight | | 0 | 1 | -1 | 8 | 8 | eight | | 0 | 2 | 2 | 8 | 8 | eight | | 0 | 3 | -3 | 8 | 8 | eight | | 0 | 2 | 4 | 8 | 8 | eight | | 0 | 5 | -5 | 8 | 8 | eight | | 0 | 5 | -5 | 8 | 8 | eight | | 0 | 0 | | 8 | 8 | eight | | 0 | | | 8 | 8 | eight | | 0 | | 0 | 0 | | zero | | 0 | 1 | -1 | 0 | | zero | | 0 | 2 | 2 | 0 | | zero | | 0 | 3 | -3 | 0 | | zero | | 0 | 2 | 4 | 0 | | zero | | 0 | 5 | -5 | 0 | | zero | | 0 | 5 | -5 | 0 | | zero | | 0 | 0 | | 0 | | zero | | 0 | | | 0 | | zero | | 0 | | 0 | | | null | | 0 | 1 | -1 | | | null | | 0 | 2 | 2 | | | null | | 0 | 3 | -3 | | | null | | 0 | 2 | 4 | | | null | | 0 | 5 | -5 | | | null | | 0 | 5 | -5 | | | null | | 0 | 0 | | | | null | | 0 | | | | | null | | 0 | | 0 | | 0 | zero | | 0 | 1 | -1 | | 0 | zero | | 0 | 2 | 2 | | 0 | zero | | 0 | 3 | -3 | | 0 | zero | | 0 | 2 | 4 | | 0 | zero | | 0 | 5 | -5 | | 0 | zero | | 0 | 5 | -5 | | 0 | zero | | 0 | 0 | | | 0 | zero | | 0 | | | | 0 | zero | | 0 | | 0 (891 rows) -- -- -- Inner joins (equi-joins) -- -- -- -- Inner joins (equi-joins) with USING clause -- The USING syntax changes the shape of the resulting table -- by including a column in the USING clause only once in the result. -- -- Inner equi-join on specified column --Testcase 33: 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 --Testcase 34: 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) --Testcase 35: 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) --Testcase 36: 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 -- --Testcase 37: 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) --Testcase 38: 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) --Testcase 39: 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 --Testcase 40: 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) -- --Testcase 41: 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) --Testcase 42: 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 -- --Testcase 43: 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 -- --Testcase 44: 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) --Testcase 45: 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) --Testcase 46: 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) --Testcase 47: 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) --Testcase 48: 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) --Testcase 49: 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) --Testcase 50: SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); xxx | i | j | t | k -----+---+---+---+--- (0 rows) --Testcase 51: 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 <> -- --Testcase 52: explain (costs off) select * from int4_tbl i4, tenk1 a where exists(select * from tenk1 b where a.twothousand = b.twothousand and a.fivethous <> b.fivethous) and i4.f1 = a.tenthous; QUERY PLAN ---------------------------------------------- Hash Semi Join Hash Cond: (a.twothousand = b.twothousand) Join Filter: (a.fivethous <> b.fivethous) -> Foreign Scan -> Hash -> Foreign Scan on tenk1 b (6 rows) -- -- More complicated constructs -- -- -- Multiway full join -- --Testcase 372: CREATE FOREIGN TABLE t11 (name TEXT, n INTEGER) SERVER sqlite_svr; --Testcase 373: CREATE FOREIGN TABLE t21 (name TEXT, n INTEGER) SERVER sqlite_svr; --Testcase 374: CREATE FOREIGN TABLE t31 (name TEXT, n INTEGER) SERVER sqlite_svr; --Testcase 53: INSERT INTO t11 VALUES ( 'bb', 11 ); --Testcase 54: INSERT INTO t21 VALUES ( 'bb', 12 ); --Testcase 55: INSERT INTO t21 VALUES ( 'cc', 22 ); --Testcase 56: INSERT INTO t21 VALUES ( 'ee', 42 ); --Testcase 57: INSERT INTO t31 VALUES ( 'bb', 13 ); --Testcase 58: INSERT INTO t31 VALUES ( 'cc', 23 ); --Testcase 59: INSERT INTO t31 VALUES ( 'dd', 33 ); --Testcase 60: 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) --Testcase 61: 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) --Testcase 62: 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) --Testcase 63: 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 --Testcase 64: 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) --Testcase 65: 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) --Testcase 66: 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) --Testcase 67: 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) --Testcase 68: 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) --Testcase 69: 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) --Testcase 70: 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) -- Constants as join keys can also be problematic --Testcase 375: SELECT * FROM (SELECT name, n as s1_n FROM t11) as s1 FULL JOIN (SELECT name, 2 as s2_n FROM t21) as s2 ON (s1_n = s2_n); ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions -- Test for propagation of nullability constraints into sub-joins --Testcase 376: create foreign table x (x1 int, x2 int) server sqlite_svr; --Testcase 71: insert into x values (1,11); --Testcase 72: insert into x values (2,22); --Testcase 73: insert into x values (3,null); --Testcase 74: insert into x values (4,44); --Testcase 75: insert into x values (5,null); --Testcase 377: create foreign table y (y1 int, y2 int) server sqlite_svr; --Testcase 76: insert into y values (1,111); --Testcase 77: insert into y values (2,222); --Testcase 78: insert into y values (3,333); --Testcase 79: insert into y values (4,null); --Testcase 80: select * from x; x1 | x2 ----+---- 1 | 11 2 | 22 3 | 4 | 44 5 | (5 rows) --Testcase 81: select * from y; y1 | y2 ----+----- 1 | 111 2 | 222 3 | 333 4 | (4 rows) --Testcase 82: 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) --Testcase 83: 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) --Testcase 84: 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) --Testcase 85: 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) --Testcase 86: 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) --Testcase 87: 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 --Testcase 88: 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) --Testcase 89: 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) --Testcase 90: 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 -- --Testcase 91: 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 -- --Testcase 92: 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; --Testcase 93: select count(*) from tenk1 x where x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and x.unique1 = 0 and x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1); count ------- 1 (1 row) rollback; -- -- regression test: be sure we cope with proven-dummy append rels -- --Testcase 378: create table b (aa int, bb int); --Testcase 379: explain (costs off) select aa, bb, unique1, unique1 from tenk1 right join b on aa = unique1 where bb < bb and bb is null; QUERY PLAN ---------------------------------------------------- Hash Right Join Hash Cond: (tenk1.unique1 = b.aa) -> Foreign Scan on tenk1 -> Hash -> Seq Scan on b Filter: ((bb IS NULL) AND (bb < bb)) (6 rows) --Testcase 380: select aa, bb, unique1, unique1 from tenk1 right join b on aa = unique1 where bb < bb and bb is null; aa | bb | unique1 | unique1 ----+----+---------+--------- (0 rows) -- -- regression test: check handling of empty-FROM subquery underneath outer join -- --Testcase 94: 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 ----------------------------------------------- Nested Loop Left Join Join Filter: (i1.q2 = i2.q2) -> Foreign Scan on int8_tbl i1 -> Materialize -> Hash Join Hash Cond: (i2.q1 = (123)) -> Foreign Scan on int8_tbl i2 -> Hash -> Result (9 rows) --Testcase 95: 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 -- --Testcase 96: 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 -- --Testcase 97: 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) --Testcase 98: 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 -- PS: Used ORDER BY to force SQLite and PG12 always order in the same way (NULLS FIRST/LAST default value for PG and Sqlite are different) -- --Testcase 99: 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 ORDER BY j1_tbl.i, j2_tbl.k; QUERY PLAN ---------------------------------------------------------------------- Sort Sort Key: j1_tbl.i, j2_tbl.k -> Hash Full Join Hash Cond: ((j1_tbl.i = j2_tbl.i) AND (j1_tbl.i = j2_tbl.k)) -> Foreign Scan on j1_tbl -> Hash -> Foreign Scan on j2_tbl (7 rows) --Testcase 100: 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 ORDER BY j1_tbl.i, j2_tbl.k; i | j | t | i | k ---+---+-------+---+---- 0 | | zero | | 1 | 4 | one | | 2 | 3 | two | 2 | 2 3 | 2 | three | | 4 | 1 | four | | 5 | 0 | five | | 6 | 6 | six | | 7 | 7 | seven | | 8 | 8 | eight | | | | | 5 | -5 | | | 5 | -5 | | | 3 | -3 | | | 1 | -1 | | | | 0 | | | 2 | 4 | | | | | | | 0 | | 0 | zero | | | | null | | (19 rows) -- -- a different check for handling of redundant sort keys in merge joins -- --Testcase 101: explain (costs off) select count(*) from (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x left join (select * from tenk1 y order by y.unique2) y on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2; QUERY PLAN ---------------------------------------------------------------------------------- Aggregate -> Merge Left Join Merge Cond: (x.thousand = y.unique2) Join Filter: ((x.twothousand = y.hundred) AND (x.fivethous = y.unique2)) -> Foreign Scan on tenk1 x -> Materialize -> Foreign Scan on tenk1 y (7 rows) --Testcase 102: 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 -- --Testcase 381: DROP FOREIGN TABLE t11; --Testcase 382: DROP FOREIGN TABLE t21; --Testcase 383: DROP FOREIGN TABLE t31; --Testcase 384: DROP FOREIGN TABLE J1_TBL; --Testcase 385: 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. --Testcase 386: CREATE FOREIGN TABLE t12 (a int OPTIONS (key 'true'), b int) SERVER sqlite_svr; --Testcase 387: CREATE FOREIGN TABLE t22 (a int OPTIONS (key 'true'), b int) SERVER sqlite_svr; --Testcase 388: CREATE FOREIGN TABLE t32 (x int OPTIONS (key 'true'), y int) SERVER sqlite_svr; --Testcase 103: INSERT INTO t12 VALUES (5, 10); --Testcase 104: INSERT INTO t12 VALUES (15, 20); --Testcase 105: INSERT INTO t12 VALUES (100, 100); --Testcase 106: INSERT INTO t12 VALUES (200, 1000); --Testcase 107: INSERT INTO t22 VALUES (200, 2000); --Testcase 108: INSERT INTO t32 VALUES (5, 20); --Testcase 109: INSERT INTO t32 VALUES (6, 7); --Testcase 110: INSERT INTO t32 VALUES (7, 8); --Testcase 111: INSERT INTO t32 VALUES (500, 100); --Testcase 112: DELETE FROM t32 USING t12 table1 WHERE t32.x = table1.a; --Testcase 113: SELECT * FROM t32; x | y -----+----- 6 | 7 7 | 8 500 | 100 (3 rows) --Testcase 114: DELETE FROM t32 USING t12 JOIN t22 USING (a) WHERE t32.x > t12.a; --Testcase 115: SELECT * FROM t32; x | y ---+--- 6 | 7 7 | 8 (2 rows) --Testcase 116: DELETE FROM t32 USING t32 t3_other WHERE t32.x = t3_other.x AND t32.y = t3_other.y; --Testcase 117: SELECT * FROM t32; x | y ---+--- (0 rows) -- Test join against inheritance tree --Testcase 389: create temp table t2a () inherits (t22); --Testcase 118: insert into t2a values (200, 2001); --Testcase 119: 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 --Testcase 120: 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 -- --Testcase 390: CREATE FOREIGN TABLE tt1 ( tt1_id int4, joincol int4 ) SERVER sqlite_svr; --Testcase 121: INSERT INTO tt1 VALUES (1, 11); --Testcase 122: INSERT INTO tt1 VALUES (2, NULL); --Testcase 391: CREATE FOREIGN TABLE tt2 ( tt2_id int4, joincol int4 ) SERVER sqlite_svr; --Testcase 123: INSERT INTO tt2 VALUES (21, 11); --Testcase 124: INSERT INTO tt2 VALUES (22, 11); set enable_hashjoin to off; set enable_nestloop to off; -- these should give the same results --Testcase 125: 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) --Testcase 126: 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; --Testcase 127: explain (costs off) select count(*) from tenk1 a, tenk1 b where a.hundred = b.thousand and (b.fivethous % 10) < 10; QUERY PLAN -------------- Foreign Scan (1 row) --Testcase 128: 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 -- --Testcase 392: create foreign table tt3(f1 int, f2 text) server sqlite_svr; --Testcase 129: insert into tt3 select x, repeat('xyzzy', 100) from generate_series(1,10000) x; --Testcase 393: create foreign table tt4(f1 int) server sqlite_svr; --Testcase 130: insert into tt4 values (0),(1),(9999); --Testcase 131: SELECT a.f1 FROM tt4 a LEFT JOIN ( SELECT b.f1 FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1) WHERE c.f1 IS NULL ) AS d ON (a.f1 = d.f1) WHERE d.f1 IS NULL; f1 ------ 0 1 9999 (3 rows) -- -- regression test for proper handling of outer joins within antijoins -- --Testcase 394: create foreign table tt4x(c1 int, c2 int, c3 int) server sqlite_svr; --Testcase 132: 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 Right Join Merge Cond: (t5.c1 = t3.c2) -> Merge Join Merge Cond: (t4.c2 = t5.c1) -> Sort Sort Key: t4.c2 -> Foreign Scan on tt4x t4 -> Sort Sort Key: t5.c1 -> Foreign Scan on tt4x t5 -> Sort Sort Key: t3.c2 -> Merge Left Join Merge Cond: (t2.c3 = t3.c1) -> Sort Sort Key: t2.c3 -> Foreign Scan on tt4x t2 -> Sort Sort Key: t3.c1 -> Foreign Scan on tt4x t3 (24 rows) -- -- regression test for problems of the sort depicted in bug #3494 -- --Testcase 395: create foreign table tt5(f1 int, f2 int) server sqlite_svr; --Testcase 396: create foreign table tt6(f1 int, f2 int) server sqlite_svr; --Testcase 133: insert into tt5 values(1, 10); --Testcase 134: insert into tt5 values(1, 11); --Testcase 135: insert into tt6 values(1, 9); --Testcase 136: insert into tt6 values(1, 2); --Testcase 137: insert into tt6 values(2, 9); --Testcase 138: 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 -- --Testcase 397: create foreign table xx (pkxx int) server sqlite_svr; --Testcase 398: create foreign table yy (pkyy int, pkxx int) server sqlite_svr; --Testcase 139: insert into xx values (1); --Testcase 140: insert into xx values (2); --Testcase 141: insert into xx values (3); --Testcase 142: insert into yy values (101, 1); --Testcase 143: insert into yy values (201, 2); --Testcase 144: insert into yy values (301, NULL); --Testcase 145: 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) -- --Testcase 399: create foreign table zt1 (f1 int OPTIONS(key 'true')) server sqlite_svr; --Testcase 400: create foreign table zt2 (f2 int OPTIONS(key 'true')) server sqlite_svr; --Testcase 401: create foreign table zt3 (f3 int OPTIONS(key 'true')) server sqlite_svr; --Testcase 146: insert into zt1 values(53); --Testcase 147: insert into zt2 values(53); --Testcase 148: select * from zt2 left join zt3 on (f2 = f3) left join zt1 on (f3 = f1) where f2 = 53; f2 | f3 | f1 ----+----+---- 53 | | (1 row) --Testcase 402: create temp view zv1 as select *,'dummy'::text AS junk from zt1; --Testcase 149: 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) -- --Testcase 150: 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) -- --Testcase 151: 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)); --Testcase 152: execute foo(true); count ------- 10000 (1 row) --Testcase 153: 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; --Testcase 403: create foreign table a1 (i integer) server sqlite_svr; --Testcase 404: create foreign table b1 (x integer, y integer) server sqlite_svr; --Testcase 154: select * from a1 left join b1 on i = x and i = y and x = i; i | x | y ---+---+--- (0 rows) rollback; -- skip this test, sqlite fdw does not support customized type -- test handling of merge clauses using record_ops -- --begin; --create type mycomptype as (id int, v bigint); --create foreign table tidv (idv mycomptype) server sqlite_svr; --create index on tidv (idv); --explain (costs off) --select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; --set enable_mergejoin = 0; --explain (costs off) --select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; --rollback; -- -- test NULL behavior of whole-row Vars, per bug #5025 -- --Testcase 155: 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) --Testcase 156: 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) --Testcase 157: 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) --Testcase 158: 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; --Testcase 405: create foreign table a2 ( code char OPTIONS (key 'true') ) server sqlite_svr; --Testcase 406: create foreign table b2 ( a char OPTIONS (key 'true'), num integer OPTIONS (key 'true') ) server sqlite_svr; --Testcase 407: create foreign table c2 ( name char OPTIONS (key 'true'), a char ) server sqlite_svr; --Testcase 159: insert into a2 (code) values ('p'); --Testcase 160: insert into a2 (code) values ('q'); --Testcase 161: insert into b2 (a, num) values ('p', 1); --Testcase 162: insert into b2 (a, num) values ('p', 2); --Testcase 163: insert into c2 (name, a) values ('A', 'p'); --Testcase 164: insert into c2 (name, a) values ('B', 'q'); --Testcase 165: insert into c2 (name, a) values ('C', null); --Testcase 166: 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 incorrect handling of placeholders that only appear in targetlists, -- per bug #6154 -- --Testcase 408: create foreign table sub_tbl (key1 int, key3 int, key5 int, key6 int, value1 int, id int options (key 'true')) server sqlite_svr; --Testcase 409: insert into sub_tbl values (1, 1, 1, 2, 42); --Testcase 410: SELECT * FROM ( SELECT key1 from sub_tbl) sub1 LEFT JOIN ( SELECT sub3.key3, sub4.value2, COALESCE(sub4.value2, 66) as value3 FROM ( SELECT key3 from sub_tbl) sub3 LEFT JOIN ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM ( SELECT key5 from sub_tbl) sub5 LEFT JOIN ( SELECT key6, value1 from sub_tbl ) sub6 ON sub5.key5 = sub6.key6 ) sub4 ON sub4.key5 = sub3.key3 ) sub2 ON sub1.key1 = sub2.key3; key1 | key3 | value2 | value3 ------+------+--------+-------- 1 | 1 | 1 | 1 (1 row) -- test the path using join aliases, too --Testcase 411: SELECT * FROM ( SELECT key1 from sub_tbl ) sub1 LEFT JOIN ( SELECT sub3.key3, value2, COALESCE(value2, 66) as value3 FROM ( SELECT key3 from sub_tbl ) sub3 LEFT JOIN ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM ( SELECT key5 from sub_tbl ) sub5 LEFT JOIN ( SELECT key6, value1 from sub_tbl) sub6 ON sub5.key5 = sub6.key6 ) sub4 ON sub4.key5 = sub3.key3 ) sub2 ON sub1.key1 = sub2.key3; key1 | key3 | value2 | value3 ------+------+--------+-------- 1 | 1 | 1 | 1 (1 row) -- -- test case where a PlaceHolderVar is used as a nestloop parameter -- --Testcase 167: 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 -> 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 -> Sort Sort Key: (COALESCE(b.q2, '-1'::bigint)) -> Foreign Scan on int8_tbl b (15 rows) --Testcase 168: 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 -- --Testcase 412: create foreign table nt1 ( id int OPTIONS (key 'true'), a1 boolean, a2 boolean ) server sqlite_svr; --Testcase 413: create foreign table nt2 ( id int OPTIONS (key 'true'), nt1_id int, b1 boolean, b2 boolean ) server sqlite_svr; --Testcase 414: create foreign table nt3 ( id int OPTIONS (key 'true'), nt2_id int, c1 boolean ) server sqlite_svr; --Testcase 169: insert into nt1 values (1,true,true); --Testcase 170: insert into nt1 values (2,true,false); --Testcase 171: insert into nt1 values (3,false,false); --Testcase 172: insert into nt2 values (1,1,true,true); --Testcase 173: insert into nt2 values (2,2,true,false); --Testcase 174: insert into nt2 values (3,3,false,false); --Testcase 175: insert into nt3 values (1,1,true); --Testcase 176: insert into nt3 values (2,2,false); --Testcase 177: insert into nt3 values (3,3,true); --Testcase 178: 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 ---------------------------------------------------- Hash Join Hash Cond: (nt1.id = nt2.nt1_id) Join Filter: (nt2.b1 AND ((nt1.id IS NOT NULL))) -> Foreign Scan on nt1 -> Hash -> Foreign Scan (6 rows) --Testcase 179: 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 -- --Testcase 180: 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 Right Join Hash Cond: (t2.q1 = t1.q2) Filter: (1 = (SubPlan 1)) -> Foreign Scan on int8_tbl t2 -> Hash -> Foreign Scan on int8_tbl t1 SubPlan 1 -> Result One-Time Filter: ((42) IS NOT NULL) -> Foreign Scan on int8_tbl t3 (12 rows) --Testcase 181: 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 -- --Testcase 182: 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) --Testcase 183: 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 -- --Testcase 415: create foreign table q1(i int) server sqlite_svr; --Testcase 416: insert into q1 values (1); --Testcase 417: create foreign table q2(i int) server sqlite_svr; --Testcase 418: insert into q2 values (0); --Testcase 184: explain (costs off) select * from tenk1 join int4_tbl on f1 = twothousand, q1, q2 where q1.i = thousand or q2.i = thousand; QUERY PLAN ------------------------------------------------ Hash Join Hash Cond: (tenk1.twothousand = int4_tbl.f1) -> Foreign Scan -> Hash -> Foreign Scan on int4_tbl (5 rows) --Testcase 185: explain (costs off) select * from tenk1 join int4_tbl on f1 = twothousand, q1, q2 where thousand = (q1.i + q2.i); QUERY PLAN -------------------------------------------------- Merge Join Merge Cond: (tenk1.thousand = ((q1.i + q2.i))) -> Sort Sort Key: tenk1.thousand -> Foreign Scan -> Materialize -> Sort Sort Key: ((q1.i + q2.i)) -> Nested Loop -> Foreign Scan on q1 -> Materialize -> Foreign Scan on q2 (12 rows) -- -- test ability to generate a suitable plan for a star-schema query -- --Testcase 186: 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 ---------------------------------------- Hash Join Hash Cond: (tenk1.tenthous = b.q1) -> Foreign Scan -> Hash -> Foreign Scan on int8_tbl b (5 rows) -- -- test a corner case in which we shouldn't apply the star-schema optimization -- --Testcase 187: explain (costs off) select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from tenk1 t1 inner join int4_tbl i1 left join (select v1.x2, v2.y1, 11 AS d1 from (select 1,0 from onerow) v1(x1,x2) left join (select 3,1 from onerow) v2(y1,y2) on v1.x1 = v2.y2) subq1 on (i1.f1 = subq1.x2) on (t1.unique2 = subq1.d1) left join tenk1 t2 on (subq1.y1 = t2.unique1) where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; QUERY PLAN ----------------------------------------------------------------- Merge Join Merge Cond: (t2.unique1 = (3)) Join Filter: (t1.stringu1 > t2.stringu2) -> Sort Sort Key: t2.unique1 -> Foreign Scan on tenk1 t2 -> Sort Sort Key: (3) -> Hash Join Hash Cond: ((11) = t1.unique2) -> Nested Loop Left Join Join Filter: (i1.f1 = 0) -> Foreign Scan on int4_tbl i1 -> Materialize -> Nested Loop Left Join -> Seq Scan on onerow -> Seq Scan on onerow onerow_1 -> Hash -> Foreign Scan on tenk1 t1 (19 rows) --Testcase 188: select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from tenk1 t1 inner join int4_tbl i1 left join (select v1.x2, v2.y1, 11 AS d1 from (select 1,0 from onerow) v1(x1,x2) left join (select 3,1 from onerow) v2(y1,y2) on v1.x1 = v2.y2) subq1 on (i1.f1 = subq1.x2) on (t1.unique2 = subq1.d1) left join tenk1 t2 on (subq1.y1 = t2.unique1) where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; unique2 | stringu1 | unique1 | stringu2 ---------+----------+---------+---------- 11 | WFAAAA | 3 | LKIAAA (1 row) -- variant that isn't quite a star-schema case --Testcase 189: select ss1.d1 from tenk1 as t1 inner join tenk1 as t2 on t1.tenthous = t2.ten inner join int8_tbl as i8 left join int4_tbl as i4 inner join (select 64::information_schema.cardinal_number as d1 from tenk1 t3, lateral (select abs(t3.unique1) + random()) ss0(x) where t3.fivethous < 0) as ss1 on i4.f1 = ss1.d1 on i8.q1 = i4.f1 on t1.tenthous = ss1.d1 where t1.unique1 < i4.f1; d1 ---- (0 rows) -- this variant is foldable by the remove-useless-RESULT-RTEs code --Testcase 419: explain (costs off) select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from tenk1 t1 inner join int4_tbl i1 left join (select v1.x2, v2.y1, 11 AS d1 from (values(1,0)) v1(x1,x2) left join (values(3,1)) v2(y1,y2) on v1.x1 = v2.y2) subq1 on (i1.f1 = subq1.x2) on (t1.unique2 = subq1.d1) left join tenk1 t2 on (subq1.y1 = t2.unique1) where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; QUERY PLAN --------------------------------------------------------------------------- Nested Loop Join Filter: ((0) = i1.f1) -> Nested Loop Join Filter: ((t1.stringu1 > t2.stringu2) AND ((3) = t2.unique1)) -> Hash Join Hash Cond: (t1.unique2 = (11)) -> Foreign Scan on tenk1 t1 -> Hash -> Nested Loop Join Filter: ((1) = (1)) -> Result -> Result -> Foreign Scan on tenk1 t2 -> Foreign Scan on int4_tbl i1 (14 rows) --Testcase 420: select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from tenk1 t1 inner join int4_tbl i1 left join (select v1.x2, v2.y1, 11 AS d1 from (values(1,0)) v1(x1,x2) left join (values(3,1)) v2(y1,y2) on v1.x1 = v2.y2) subq1 on (i1.f1 = subq1.x2) on (t1.unique2 = subq1.d1) left join tenk1 t2 on (subq1.y1 = t2.unique1) where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; unique2 | stringu1 | unique1 | stringu2 ---------+----------+---------+---------- 11 | WFAAAA | 3 | LKIAAA (1 row) -- Here's a variant that we can't fold too aggressively, though, -- or we end up with noplace to evaluate the lateral PHV --Testcase 421: explain (verbose, costs off) select * from (select key1 as x from sub_tbl) ss1 left join (select key6 as y from sub_tbl) ss2 on (true), lateral (select ss2.y as z limit 1) ss3; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Nested Loop Output: sub_tbl.key1, sub_tbl_1.key6, (sub_tbl_1.key6) -> Nested Loop Left Join Output: sub_tbl.key1, sub_tbl_1.key6 -> Foreign Scan on public.sub_tbl Output: sub_tbl.key1, sub_tbl.key3, sub_tbl.key5, sub_tbl.key6, sub_tbl.value1, sub_tbl.id SQLite query: SELECT `key1` FROM main."sub_tbl" -> Materialize Output: sub_tbl_1.key6 -> Foreign Scan on public.sub_tbl sub_tbl_1 Output: sub_tbl_1.key6 SQLite query: SELECT `key6` FROM main."sub_tbl" -> Limit Output: (sub_tbl_1.key6) -> Result Output: sub_tbl_1.key6 (16 rows) --Testcase 422: select * from (select key1 as x from sub_tbl as x) ss1 left join (select key6 as y from sub_tbl) ss2 on (true), lateral (select ss2.y as z limit 1) ss3; x | y | z ---+---+--- 1 | 2 | 2 (1 row) -- -- test inlining of immutable functions -- --Testcase 423: create function f_immutable_int4(i integer) returns integer as $$ begin return i; end; $$ language plpgsql immutable; -- check optimization of function scan with join --Testcase 424: explain (costs off) select unique1 from tenk1, (select * from f_immutable_int4(1) x) x where x = unique1; QUERY PLAN ------------------------------------ Hash Join Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on tenk1 -> Hash -> Function Scan on x (5 rows) --Testcase 425: explain (verbose, costs off) select unique1, x.* from tenk1, (select *, random() from f_immutable_int4(1) x) x where x = unique1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join Output: tenk1.unique1, x.x, (random()) Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on public.tenk1 Output: tenk1.unique1, tenk1.unique2, tenk1.two, tenk1.four, tenk1.ten, tenk1.twenty, tenk1.hundred, tenk1.thousand, tenk1.twothousand, tenk1.fivethous, tenk1.tenthous, tenk1.odd, tenk1.even, tenk1.stringu1, tenk1.stringu2, tenk1.string4 SQLite query: SELECT `unique1` FROM main."tenk1" -> Hash Output: x.x, (random()) -> Function Scan on x Output: x.x, random() Function Call: 1 (11 rows) --Testcase 426: explain (costs off) select unique1 from tenk1, f_immutable_int4(1) x where x = unique1; QUERY PLAN ------------------------------------ Hash Join Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on tenk1 -> Hash -> Function Scan on x (5 rows) --Testcase 427: explain (costs off) select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1; QUERY PLAN ------------------------------------ Hash Join Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on tenk1 -> Hash -> Function Scan on x (5 rows) --Testcase 428: explain (costs off) select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x; QUERY PLAN ------------------------------------ Hash Join Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on tenk1 -> Hash -> Function Scan on x (5 rows) --Testcase 429: explain (costs off) select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x; QUERY PLAN ------------------------------------ Hash Left Join Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on tenk1 -> Hash -> Function Scan on x (5 rows) --Testcase 430: explain (costs off) select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x; QUERY PLAN ------------------------------------ Hash Right Join Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on tenk1 -> Hash -> Function Scan on x (5 rows) --Testcase 431: explain (costs off) select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x; QUERY PLAN ------------------------------------ Hash Full Join Hash Cond: (tenk1.unique1 = x.x) -> Foreign Scan on tenk1 -> Hash -> Function Scan on x (5 rows) -- check that pullup of a const function allows further const-folding --Testcase 432: explain (costs off) select unique1 from tenk1, f_immutable_int4(1) x where x = 42; QUERY PLAN ----------------------------- Nested Loop -> Function Scan on x Filter: (x = 42) -> Foreign Scan on tenk1 (4 rows) -- test inlining of immutable functions with PlaceHolderVars --Testcase 433: explain (costs off) select nt3.id from nt3 as nt3 left join (select nt2.*, (nt2.b1 or i4 = 42) AS b3 from nt2 as nt2 left join f_immutable_int4(0) i4 on i4 = nt2.nt1_id ) as ss2 on ss2.id = nt3.nt2_id where nt3.id = 1 and ss2.b3; QUERY PLAN ----------------------------------------- Hash Right Join Hash Cond: (nt2.id = nt3.nt2_id) Filter: ((nt2.b1 OR (i4.i4 = 42))) -> Hash Left Join Hash Cond: (nt2.nt1_id = i4.i4) -> Foreign Scan on nt2 -> Hash -> Function Scan on i4 -> Hash -> Foreign Scan on nt3 (10 rows) --Testcase 434: drop function f_immutable_int4(int); -- test inlining when function returns composite --Testcase 435: create function mki8(bigint, bigint) returns int8_tbl as $$select row($1,$2)::int8_tbl$$ language sql; --Testcase 436: create function mki4(int) returns int4_tbl as $$select row($1)::int4_tbl$$ language sql; --Testcase 437: explain (verbose, costs off) select * from mki8(1,2); QUERY PLAN ---------------------------------------------------------- Function Scan on mki8 Output: q1, q2 Function Call: ROW('1'::bigint, '2'::bigint)::int8_tbl (3 rows) --Testcase 438: select * from mki8(1,2); q1 | q2 ----+---- 1 | 2 (1 row) --Testcase 439: explain (verbose, costs off) select * from mki4(42); QUERY PLAN ------------------------------------ Function Scan on mki4 Output: f1 Function Call: ROW(42)::int4_tbl (3 rows) --Testcase 440: select * from mki4(42); f1 ---- 42 (1 row) --Testcase 441: drop function mki8(bigint, bigint); --Testcase 442: drop function mki4(int); -- -- test extraction of restriction OR clauses from join OR clause -- (we used to only do this for indexable clauses) -- --Testcase 190: 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) --Testcase 191: 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) --Testcase 192: 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 -- --Testcase 193: 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 -------------- Foreign Scan (1 row) --Testcase 194: 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 --------------------------------------------- Hash Right Join Hash Cond: (t2.hundred = t1.hundred) Join Filter: ((t1.ten + t2.ten) = t3.ten) -> Foreign Scan -> Hash -> Foreign Scan on tenk1 t1 (6 rows) --Testcase 195: 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 -------------- Foreign Scan (1 row) --Testcase 196: 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) --Testcase 197: 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 -------------- Foreign Scan (1 row) --Testcase 198: 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) --Testcase 199: 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 -> Sort Sort Key: tenk1.unique2 -> Foreign Scan on tenk1 (9 rows) --Testcase 200: 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) --Testcase 201: 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) --Testcase 202: 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 -- --Testcase 203: 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 Right Join Merge Cond: (b.unique2 = a.q1) Filter: ((COALESCE(b.thousand, 123) = a.q1) AND (a.q1 = COALESCE(b.hundred, 123))) -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b -> Sort Sort Key: a.q1 -> Foreign Scan on int8_tbl a (9 rows) --Testcase 204: 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) --Testcase 205: 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 -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b (9 rows) --Testcase 206: 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) -- --Testcase 207: 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 -> Sort Sort Key: b.thousand -> Foreign Scan on tenk1 b -> Hash -> Foreign Scan on tenk1 c (13 rows) --Testcase 208: 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 -- --Testcase 209: 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" -> 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 (23 rows) --Testcase 210: 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 -- --Testcase 443: create foreign table text_tbl(f1 text) server sqlite_svr; --Testcase 211: 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" -> 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" -> 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" (49 rows) --Testcase 212: 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) --Testcase 213: explain (verbose, costs off) select t1.* from text_tbl t1 left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 left join int8_tbl i8 left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2 on (i8.q1 = b2.q1) on (b2.d2 = b1.q2) on (t1.f1 = b1.d1) left join int4_tbl i4 on (i8.q2 = i4.f1); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Hash Left Join Output: t1.f1 Hash Cond: (i8.q2 = i4.f1) -> Nested Loop Left Join Output: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) -> 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.int4_tbl i4b2 Output: i4b2.f1 SQLite query: SELECT NULL FROM main."int4_tbl" -> Materialize Output: i8b2.q1 -> Foreign Scan on public.int8_tbl i8b2 Output: i8b2.q1 SQLite query: SELECT `q1` FROM main."int8_tbl" -> Hash Output: i4.f1 -> Foreign Scan on public.int4_tbl i4 Output: i4.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" (54 rows) --Testcase 214: 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) --Testcase 215: 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" -> 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" (62 rows) --Testcase 216: 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) --Testcase 217: 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 Output: i8.q1, i8.q2, i4.f1 SQLite query: SELECT r2.`q1`, r2.`q2`, r6.`f1` FROM (main."int8_tbl" r2 LEFT JOIN main."int4_tbl" r6 ON (((r2.`q1` = r6.`f1`)))) WHERE ((r2.`q2` = 456)) -> Materialize Output: t1.f1 -> Foreign Scan on public.text_tbl t1 Output: t1.f1 SQLite query: SELECT `f1` FROM main."text_tbl" WHERE ((`f1` = 'doh!')) (17 rows) --Testcase 218: 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 -- --Testcase 219: 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) -> Foreign Scan Output: t1.f1, i8.q1, i8.q2 SQLite query: SELECT r1.`f1`, r2.`q1`, r2.`q2` FROM (main."text_tbl" r1 LEFT JOIN main."int8_tbl" r2 ON (((r2.`q2` = 123)))) -> Foreign Scan on public.text_tbl t2 Output: i8.q1, t2.f1 SQLite query: SELECT `f1` FROM main."text_tbl" LIMIT 1 (9 rows) --Testcase 220: 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) --Testcase 221: 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 -> Foreign Scan Output: t1.f1, i8.q1, i8.q2 SQLite query: SELECT r1.`f1`, r2.`q1`, r2.`q2` FROM (main."text_tbl" r1 LEFT JOIN main."int8_tbl" r2 ON (((r2.`q2` = 123)))) -> Foreign Scan on public.text_tbl t2 Output: i8.q1, t2.f1 SQLite query: SELECT `f1` FROM main."text_tbl" LIMIT 1 -> Foreign Scan on public.text_tbl t3 Output: (i8.q1), t2.f1 SQLite query: SELECT NULL FROM main."text_tbl" LIMIT 1 (14 rows) --Testcase 222: 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) --Testcase 223: explain (verbose, costs off) select 1 from text_tbl as tt1 inner join text_tbl as tt2 on (tt1.f1 = 'foo') left join text_tbl as tt3 on (tt3.f1 = 'foo') left join text_tbl as tt4 on (tt3.f1 = tt4.f1), lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 where tt1.f1 = ss1.c0; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: 1 -> Nested Loop Left Join Output: tt1.f1, tt4.f1 -> Foreign Scan Output: tt1.f1 SQLite query: SELECT r1.`f1` FROM (main."text_tbl" r1 INNER JOIN main."text_tbl" r2 ON (((r1.`f1` = 'foo')))) -> Hash Left Join Output: tt4.f1 Hash Cond: (tt3.f1 = tt4.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) -> Foreign Scan on public.text_tbl tt5 Output: tt4.f1 SQLite query: SELECT NULL FROM main."text_tbl" LIMIT 1 (24 rows) --Testcase 224: 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 -- --Testcase 225: 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)) -> Nested Loop Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42 -> Nested Loop Output: i41.f1, i42.f1, i8.q1, i8.q2 -> Merge Join Output: i41.f1, i42.f1 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" -> Sort Output: i42.f1 Sort Key: i42.f1 -> Foreign Scan on public.int4_tbl i42 Output: i42.f1 SQLite query: SELECT `f1` FROM main."int4_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 ((`q1` = 0)) -> 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)) -> 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" LIMIT 1 (34 rows) --Testcase 226: 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) -- --Testcase 227: 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) --Testcase 228: 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 -- --Testcase 229: explain (costs off) select * from int4_tbl a left join tenk1 b on f1 = unique2 where f1 = 0; QUERY PLAN ------------------------------------- Hash Left Join Hash Cond: (a.f1 = b.unique2) -> Foreign Scan on int4_tbl a -> Hash -> Foreign Scan on tenk1 b (5 rows) --Testcase 230: explain (costs off) select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42; QUERY PLAN --------------------------------------- Merge Full Join Merge Cond: (a.unique2 = b.unique2) -> Sort Sort Key: a.unique2 -> Foreign Scan on tenk1 a -> Sort Sort Key: b.unique2 -> Foreign Scan on tenk1 b (8 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; --Testcase 231: 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" -> 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" (16 rows) --Testcase 232: 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; --Testcase 444: CREATE FOREIGN TABLE a3 (id int OPTIONS (key 'true'), b_id int) SERVER sqlite_svr; --Testcase 445: CREATE FOREIGN TABLE b3 (id int OPTIONS (key 'true'), c_id int) SERVER sqlite_svr; --Testcase 446: CREATE FOREIGN TABLE c3 (id int OPTIONS (key 'true')) SERVER sqlite_svr; --Testcase 447: CREATE FOREIGN TABLE d3 (a int, b int) SERVER sqlite_svr; --Testcase 233: INSERT INTO a3 VALUES (0, 0), (1, NULL); --Testcase 234: INSERT INTO b3 VALUES (0, 0), (1, NULL); --Testcase 235: INSERT INTO c3 VALUES (0), (1); --Testcase 236: INSERT INTO d3 VALUES (1,3), (2,2), (3,1); -- all three cases should be optimizable into a3 simple seqscan --Testcase 237: 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 -> Sort Sort Key: b3.id -> Foreign Scan on b3 (8 rows) --Testcase 238: 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 -> Sort Sort Key: c3.id -> Foreign Scan on c3 (8 rows) --Testcase 239: 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 Right Join Merge Cond: (c3.id = b3.c_id) -> Sort Sort Key: c3.id -> Foreign Scan on c3 -> Sort Sort Key: b3.c_id -> Merge Left Join Merge Cond: (a3.b_id = b3.id) -> Sort Sort Key: a3.b_id -> Foreign Scan on a3 -> Sort Sort Key: b3.id -> Foreign Scan on b3 (15 rows) -- check optimization of outer join within another special join --Testcase 240: 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 -> Sort Sort Key: c3.id -> Foreign Scan on c3 (14 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 --Testcase 241: 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 --Testcase 242: 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.) --Testcase 243: 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 --Testcase 244: explain (costs off) select d3.* from d3 left join (select distinct * from b3) 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 -> Unique -> Foreign Scan on b3 (7 rows) -- check join removal works when uniqueness of the join condition is enforced -- by a UNION --Testcase 245: 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 --Testcase 246: 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 --Testcase 247: 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 -> Sort Sort Key: a3.b_id -> Nested Loop -> Foreign Scan on a3 -> Function Scan on generate_series gs Filter: (a3.id = i) (11 rows) rollback; --Testcase 448: create foreign table parent (k int options (key 'true'), pd int) server sqlite_svr; --Testcase 449: create foreign table child (k int options (key 'true'), cd int) server sqlite_svr; --Testcase 248: insert into parent values (1, 10), (2, 20), (3, 30); --Testcase 249: insert into child values (1, 100), (4, 400); -- this case is optimizable --Testcase 250: select p.* from parent p left join child c on (p.k = c.k); k | pd ---+---- 1 | 10 2 | 20 3 | 30 (3 rows) --Testcase 251: 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 -> Sort Sort Key: c.k -> Foreign Scan on child c (8 rows) -- this case is not --Testcase 252: 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) --Testcase 253: 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 -> Sort Sort Key: c.k -> Foreign Scan on child c (8 rows) -- check for a 9.0rc1 bug: join removal breaks pseudoconstant qual handling --Testcase 254: 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) --Testcase 255: 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) --Testcase 256: 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) --Testcase 257: 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; --Testcase 450: CREATE FOREIGN TABLE a4 (id int OPTIONS (key 'true')) SERVER sqlite_svr; --Testcase 451: CREATE FOREIGN TABLE b4 (id int OPTIONS (key 'true'), a_id int) SERVER sqlite_svr; --Testcase 258: INSERT INTO a4 VALUES (0), (1); --Testcase 259: INSERT INTO b4 VALUES (0, 0), (1, NULL); --Testcase 260: 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) --Testcase 261: 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; --Testcase 452: create foreign table innertab (id int8 options (key 'true'), dat1 int8) server sqlite_svr; --Testcase 262: insert into innertab values(123, 42); --Testcase 263: 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; --Testcase 453: create foreign table uniquetbl (f1 text) server sqlite_svr; --Testcase 264: 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 -> Sort Sort Key: ('***'::text) -> Hash Left Join Hash Cond: (t1.f1 = uniquetbl.f1) -> Foreign Scan on uniquetbl t1 -> Hash -> Foreign Scan on uniquetbl (12 rows) --Testcase 265: 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 -> Sort Sort Key: t1.unique2 -> Merge Right Join Merge Cond: (u1.f1 = ((t1.string4)::text)) -> Sort Sort Key: u1.f1 -> Foreign Scan on uniquetbl u1 -> Sort Sort Key: ((t1.string4)::text) -> Merge Join Merge Cond: ((CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END) = t0.f1) -> Sort Sort Key: (CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END) -> Foreign Scan on tenk1 t1 Filter: (stringu2 !~* CASE ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END) -> Sort Sort Key: t0.f1 -> Foreign Scan on text_tbl t0 (23 rows) --Testcase 266: 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 --Testcase 267: 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; ^ --Testcase 268: 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. --Testcase 269: 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 -- --Testcase 270: 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". --Testcase 271: 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". --Testcase 272: select uunique1 from tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, suggest both at once ERROR: column "uunique1" does not exist LINE 1: select uunique1 from ^ HINT: Perhaps you meant to reference the column "t1.unique1" or the column "t2.unique1". -- -- Take care to reference the correct RTE -- --Testcase 454: select atts.relid::regclass, s.* from pg_stats s join pg_attribute a on s.attname = a.attname and s.tablename = a.attrelid::regclass::text join (select unnest(indkey) attnum, indexrelid from pg_index i) atts on atts.attnum = a.attnum where schemaname != 'pg_catalog'; ERROR: column atts.relid does not exist LINE 1: select atts.relid::regclass, s.* from pg_stats s join ^ -- -- Test LATERAL -- --Testcase 273: select unique2, x.* from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; unique2 | f1 ---------+---- 9998 | 0 (1 row) --Testcase 274: 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 -> Sort Sort Key: b.f1 -> Foreign Scan on int4_tbl b (8 rows) --Testcase 275: select unique2, x.* from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; unique2 | f1 ---------+---- 9998 | 0 (1 row) --Testcase 276: 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: (tenk1.unique1 = x.f1) -> Sort Sort Key: tenk1.unique1 -> Foreign Scan on tenk1 -> Sort Sort Key: x.f1 -> Foreign Scan on int4_tbl x (8 rows) --Testcase 277: 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: (tenk1.unique1 = x.f1) -> Sort Sort Key: tenk1.unique1 -> Foreign Scan on tenk1 -> Sort Sort Key: x.f1 -> Foreign Scan on int4_tbl x (8 rows) --Testcase 278: 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) --Testcase 279: 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 Right Join Merge Cond: (tenk1.unique1 = x.f1) -> Sort Sort Key: tenk1.unique1 -> Foreign Scan on tenk1 -> Sort Sort Key: x.f1 -> Foreign Scan on int4_tbl x (8 rows) -- check scoping of lateral versus parent references -- the first of these should return int8_tbl.q2, the second int8_tbl.q1 --Testcase 280: select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl; q1 | q2 | r ------------------+-------------------+------------------- 123 | 456 | 456 123 | 4567890123456789 | 4567890123456789 4567890123456789 | 123 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 | -4567890123456789 (5 rows) --Testcase 281: select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl; q1 | q2 | r ------------------+-------------------+------------------ 123 | 456 | 123 123 | 4567890123456789 | 123 4567890123456789 | 123 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 | 4567890123456789 (5 rows) -- lateral with function in FROM --Testcase 282: select count(*) from tenk1 a, lateral generate_series(1,two) g; count ------- 5000 (1 row) --Testcase 283: 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) --Testcase 284: 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 --Testcase 285: 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 --Testcase 286: 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) --Testcase 287: 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 --Testcase 288: explain (costs off) select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; QUERY PLAN -------------- Foreign Scan (1 row) --Testcase 289: 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 --Testcase 290: 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 -> Sort Sort Key: "*VALUES*".column1 -> Nested Loop -> Foreign Scan on tenk1 a -> Values Scan on "*VALUES*" (11 rows) --Testcase 291: 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 --Testcase 292: 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 -> Sort Sort Key: (a.q1) -> Foreign Scan on int4_tbl y (12 rows) --Testcase 293: 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 --Testcase 294: 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) --Testcase 295: 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) --Testcase 296: 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 --Testcase 297: select * from (values(1)) x(lb), lateral generate_series(lb,4) x4; lb | x4 ----+---- 1 | 1 1 | 2 1 | 3 1 | 4 (4 rows) --Testcase 298: 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) --Testcase 299: select * from (values(1)) x(lb), lateral (values(lb)) y(lbcopy); lb | lbcopy ----+-------- 1 | 1 (1 row) --Testcase 300: 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) --Testcase 301: 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 | 123 | 123 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 (10 rows) --Testcase 302: 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 | 123 | 123 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 (10 rows) --Testcase 303: 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) --Testcase 304: 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 | 123 123 | 4567890123456789 4567890123456789 | 4567890123456789 123 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (20 rows) --Testcase 305: 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 | 123 123 | 4567890123456789 4567890123456789 | 4567890123456789 123 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (20 rows) --Testcase 307: select v.* from (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1) left join int4_tbl z on z.f1 = x.q2, lateral (select x.q1,y.q1 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy); vx | vy -------------------+------------------- 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 123 | 456 4567890123456789 | 123 123 | 4567890123456789 123 | 456 | 123 | 4567890123456789 4567890123456789 | 123 123 | 4567890123456789 4567890123456789 | 4567890123456789 123 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (20 rows) -- Error when using sub-query with multi instances of table, this issue is fixed on PostgreSQL-12 --Testcase 455: explain (verbose, costs off) select * from int8_tbl a left join lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; QUERY PLAN --------------------------------------------------------------------------------- Nested Loop Left Join Output: a.q1, a.q2, b.q1, b.q2, (a.q2) -> Foreign Scan on public.int8_tbl a Output: a.q1, a.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Foreign Scan on public.int8_tbl b Output: b.q1, b.q2, a.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" WHERE ((? = `q1`)) (8 rows) --Testcase 456: select * from int8_tbl a left join lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; q1 | q2 | q1 | q2 | x ------------------+-------------------+------------------+-------------------+------------------ 123 | 456 | | | 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 123 | 123 | 456 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 | | | (10 rows) --Testcase 457: explain (verbose, costs off) select * from int8_tbl a left join lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; QUERY PLAN --------------------------------------------------------------------------------- Nested Loop Left Join Output: a.q1, a.q2, b.q1, b.q2, (COALESCE(a.q2, '42'::bigint)) -> Foreign Scan on public.int8_tbl a Output: a.q1, a.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Foreign Scan on public.int8_tbl b Output: b.q1, b.q2, COALESCE(a.q2, '42'::bigint) SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" WHERE ((? = `q1`)) (8 rows) --Testcase 458: select * from int8_tbl a left join lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; q1 | q2 | q1 | q2 | x ------------------+-------------------+------------------+-------------------+------------------ 123 | 456 | | | 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 123 | 123 | 456 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 | | | (10 rows) -- lateral can result in join conditions appearing below their -- real semantic level --Testcase 308: 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" -> 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" (15 rows) --Testcase 309: 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) --Testcase 310: explain (verbose, costs off) select * from int4_tbl i left join lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; QUERY PLAN --------------------------------------------------------------------------- Nested Loop Left Join Output: i.f1, (COALESCE(i.*)) -> Foreign Scan on public.int4_tbl i Output: i.f1, i.* SQLite query: SELECT `f1` FROM main."int4_tbl" -> Foreign Scan on public.int2_tbl j Output: j.f1, COALESCE(i.*) SQLite query: SELECT `f1` FROM main."int2_tbl" WHERE ((? = `f1`)) (8 rows) --Testcase 311: select * from int4_tbl i left join lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; f1 | coalesce -------------+---------- 0 | (0) 123456 | -123456 | 2147483647 | -2147483647 | (5 rows) --Testcase 312: 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" -> Foreign Scan Output: b.f1, c.q1, c.q2 SQLite query: SELECT r1.`f1`, r2.`q1`, r2.`q2` FROM (main."int4_tbl" r1 LEFT JOIN main."int8_tbl" r2 ON (((r1.`f1` = r2.`q1`)) AND ((? = r2.`q2`)))) (8 rows) --Testcase 313: select * from int4_tbl a, lateral ( select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) ) ss; f1 | f1 | q1 | q2 -------------+-------------+----+---- 0 | 0 | | 0 | 123456 | | 0 | -123456 | | 0 | 2147483647 | | 0 | -2147483647 | | 123456 | 0 | | 123456 | 123456 | | 123456 | -123456 | | 123456 | 2147483647 | | 123456 | -2147483647 | | -123456 | 0 | | -123456 | 123456 | | -123456 | -123456 | | -123456 | 2147483647 | | -123456 | -2147483647 | | 2147483647 | 0 | | 2147483647 | 123456 | | 2147483647 | -123456 | | 2147483647 | 2147483647 | | 2147483647 | -2147483647 | | -2147483647 | 0 | | -2147483647 | 123456 | | -2147483647 | -123456 | | -2147483647 | 2147483647 | | -2147483647 | -2147483647 | | (25 rows) -- lateral reference in a PlaceHolderVar evaluated at join level -- Error when using sub-query with multi instances of table, this issue is fixed on PostgreSQL-12 --Testcase 459: explain (verbose, costs off) select * from int8_tbl a left join lateral (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from int8_tbl b cross join int8_tbl c) ss on a.q2 = ss.bq1; QUERY PLAN --------------------------------------------------------------------------------- Nested Loop Left Join Output: a.q1, a.q2, b.q1, c.q1, (LEAST(a.q1, b.q1, c.q1)) -> Foreign Scan on public.int8_tbl a Output: a.q1, a.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Nested Loop Output: b.q1, c.q1, LEAST(a.q1, b.q1, c.q1) -> Foreign Scan on public.int8_tbl b Output: b.q1, b.q2 SQLite query: SELECT `q1` FROM main."int8_tbl" WHERE ((? = `q1`)) -> Materialize Output: c.q1 -> Foreign Scan on public.int8_tbl c Output: c.q1 SQLite query: SELECT `q1` FROM main."int8_tbl" (15 rows) --Testcase 460: select * from int8_tbl a left join lateral (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from int8_tbl b cross join int8_tbl c) ss on a.q2 = ss.bq1; q1 | q2 | bq1 | cq1 | least ------------------+-------------------+------------------+------------------+------------------ 123 | 456 | | | 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 123 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 4567890123456789 | 123 | 123 | 123 | 123 4567890123456789 | 123 | 123 | 123 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 123 | 123 | 123 | 123 4567890123456789 | 123 | 123 | 123 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 123 | 123 | 4567890123456789 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 123 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 | | | (42 rows) -- case requiring nested PlaceHolderVars --Testcase 461: explain (verbose, costs off) select * from int8_tbl c left join ( int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1 on a.q2 = ss1.q1 cross join lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select ss2.y offset 0) ss3; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)), ((COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))) -> Hash Right Join Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) -> Nested Loop Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) -> Merge Left Join Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)) Merge Cond: (a.q2 = b.q1) -> Sort Output: a.q1, a.q2 Sort Key: a.q2 -> Foreign Scan on public.int8_tbl a Output: a.q1, a.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Sort Output: b.q1, (COALESCE(b.q2, '42'::bigint)) Sort Key: b.q1 -> Foreign Scan on public.int8_tbl b Output: b.q1, COALESCE(b.q2, '42'::bigint) SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Foreign Scan on public.int8_tbl d Output: d.q1, COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2) SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Hash Output: c.q1, c.q2 -> Foreign Scan on public.int8_tbl c Output: c.q1, c.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Result Output: (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) (32 rows) -- case that breaks the old ph_may_need optimization --Testcase 462: explain (verbose, costs off) select c.*,a.*,ss1.q1,ss2.q1,ss3.* from int8_tbl c left join ( int8_tbl a left join (select q1, coalesce(q2,f1) as x from int8_tbl b, int4_tbl b2 where q1 < f1) ss1 on a.q2 = ss1.q1 cross join lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select * from int4_tbl i where ss2.y > f1) ss3; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1 Join Filter: ((COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) > i.f1) -> Hash Right Join Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) -> Nested Loop Output: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) -> Merge Left Join Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) Merge Cond: (a.q2 = b.q1) -> Sort Output: a.q1, a.q2 Sort Key: a.q2 -> Foreign Scan on public.int8_tbl a Output: a.q1, a.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Materialize Output: b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) -> Sort Output: b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) Sort Key: b.q1 -> Foreign Scan Output: b.q1, COALESCE(b.q2, (b2.f1)::bigint) SQLite query: SELECT r9.`q1`, r9.`q2`, r10.`f1` FROM (main."int8_tbl" r9 INNER JOIN main."int4_tbl" r10 ON (((r9.`q1` < r10.`f1`)))) -> Foreign Scan on public.int8_tbl d Output: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2) SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Hash Output: c.q1, c.q2 -> Foreign Scan on public.int8_tbl c Output: c.q1, c.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Materialize Output: i.f1 -> Foreign Scan on public.int4_tbl i Output: i.f1 SQLite query: SELECT `f1` FROM main."int4_tbl" (38 rows) -- check processing of postponed quals (bug #9041) --Testcase 463: delete from sub_tbl; --Testcase 464: insert into sub_tbl values (1, 2, 3, 4, 5); --Testcase 465: explain (verbose, costs off) select * from (select key1 as x from sub_tbl offset 0) x cross join (select key3 as y from sub_tbl offset 0) y left join lateral ( select * from (select key5 as z from sub_tbl offset 0) z where z.z = x.x ) zz on zz.z = y.y; QUERY PLAN --------------------------------------------------------------------------------------- Merge Left Join Output: sub_tbl.key1, sub_tbl_1.key3, sub_tbl_2.key5 Merge Cond: ((sub_tbl.key1 = sub_tbl_2.key5) AND (sub_tbl_1.key3 = sub_tbl_2.key5)) -> Sort Output: sub_tbl.key1, sub_tbl_1.key3 Sort Key: sub_tbl.key1, sub_tbl_1.key3 -> Nested Loop Output: sub_tbl.key1, sub_tbl_1.key3 -> Foreign Scan on public.sub_tbl Output: sub_tbl.key1 SQLite query: SELECT `key1` FROM main."sub_tbl" -> Materialize Output: sub_tbl_1.key3 -> Foreign Scan on public.sub_tbl sub_tbl_1 Output: sub_tbl_1.key3 SQLite query: SELECT `key3` FROM main."sub_tbl" -> Sort Output: sub_tbl_2.key5 Sort Key: sub_tbl_2.key5 -> Foreign Scan on public.sub_tbl sub_tbl_2 Output: sub_tbl_2.key5 SQLite query: SELECT `key5` FROM main."sub_tbl" (22 rows) -- check dummy rels with lateral references (bug #15694) --Testcase 466: explain (verbose, costs off) select * from int8_tbl i8 left join lateral (select *, i8.q2 from int4_tbl where false) ss on true; QUERY PLAN -------------------------------------------------------------- Nested Loop Left Join Output: i8.q1, i8.q2, f1, (i8.q2) -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Result Output: f1, i8.q2 One-Time Filter: false (8 rows) --Testcase 467: explain (verbose, costs off) select * from int8_tbl i8 left join lateral (select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true; QUERY PLAN -------------------------------------------------------------- Nested Loop Left Join Output: i8.q1, i8.q2, f1, f1, (i8.q2) -> Foreign Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 SQLite query: SELECT `q1`, `q2` FROM main."int8_tbl" -> Result Output: f1, f1, i8.q2 One-Time Filter: false (8 rows) -- check handling of nested appendrels inside LATERAL --Testcase 468: select * from ((select key3 as v from sub_tbl) union all (select key5 as v from sub_tbl)) as q1 cross join lateral ((select * from ((select key6 as v from sub_tbl) union all (select value1 as v from sub_tbl)) as q3) union all (select q1.v) ) as q2; v | v ---+--- 2 | 4 2 | 5 2 | 2 3 | 4 3 | 5 3 | 3 (6 rows) -- check we don't try to do a unique-ified semijoin with LATERAL --Testcase 314: 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 Semi Join Output: int4_tbl.f1 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 -> Foreign Scan on public.tenk1 Output: tenk1.unique1 SQLite query: SELECT `unique1` FROM main."tenk1" WHERE ((`unique2` = ?)) (15 rows) --Testcase 315: 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) --Testcase 316: 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) --Testcase 317: select * from (values (0), (1)) v(id), lateral (select * from int8_tbl t1, lateral (select * from (select * from int8_tbl t2 where q1 = any (select q2 from int8_tbl t3 where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; id | q1 | q2 | q1 | q2 ----+------------------+-------------------+------------------+------------------ 0 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 0 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 0 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 (3 rows) -- test some error cases where LATERAL should have been used but wasn't --Testcase 318: 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. --Testcase 319: 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. --Testcase 320: 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. --Testcase 321: 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 --Testcase 322: 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. --Testcase 323: 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 --Testcase 324: 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 --Testcase 325: 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 --Testcase 469: create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl; -- error, can't do this: --Testcase 326: 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. --Testcase 327: 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: --Testcase 328: 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: --Testcase 329: 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: --Testcase 330: 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. --Testcase 331: 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. --Testcase 332: 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. -- Skip this test, sqlite fdw does not support to create partition table -- test LATERAL reference propagation down a multi-level inheritance hierarchy -- produced for a multi-level partitioned table hierarchy. -- --create table join_pt1 (a int, b int, c varchar) partition by range(a); --create table join_pt1p1 partition of join_pt1 for values from (0) to (100) partition by range(b); --create table join_pt1p2 partition of join_pt1 for values from (100) to (200); --create table join_pt1p1p1 partition of join_pt1p1 for values from (0) to (100); --insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y'); --create table join_ut1 (a int, b int, c varchar); --insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z'); --explain (verbose, costs off) --select t1.b, ss.phv from join_ut1 t1 left join lateral -- (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv -- from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss -- on t1.a = ss.t2a order by t1.a; --select t1.b, ss.phv from join_ut1 t1 left join lateral -- (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv -- from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss -- on t1.a = ss.t2a order by t1.a; --drop table join_pt1; --drop table join_ut1; -- -- test that foreign key join estimation performs sanely for outer joins -- begin; --Testcase 470: create foreign table fkest (a int options (key 'true'), b int options (key 'true'), c int) server sqlite_svr; --Testcase 471: create foreign table fkest1 (a int options (key 'true'), b int options (key 'true')) server sqlite_svr; --Testcase 333: insert into fkest select x/10, x%10, x from generate_series(1,1000) x; --Testcase 334: insert into fkest1 select x/10, x%10 from generate_series(1,1000) x; --alter table fkest1 -- add constraint fkest1_a_b_fkey foreign key (a,b) references fkest; --analyze fkest; --analyze fkest1; --Testcase 335: 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 -------------- Foreign Scan (1 row) rollback; -- -- test planner's ability to mark joins as unique -- --Testcase 472: create foreign table j11 (id int options (key 'true')) server sqlite_svr; --Testcase 473: create foreign table j21 (id int options (key 'true')) server sqlite_svr; --Testcase 474: create foreign table j31 (id int) server sqlite_svr; --Testcase 336: insert into j11 values(1),(2),(3); --Testcase 337: insert into j21 values(1),(2),(3); --Testcase 338: insert into j31 values(1),(1); -- ensure join is properly marked as unique --Testcase 339: 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" -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (15 rows) -- ensure join is not unique when not an equi-join --Testcase 340: explain (verbose, costs off) select * from j11 inner join j21 on j11.id > j21.id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: j11.id, j21.id SQLite query: SELECT r1.`id`, r2.`id` FROM (main."j11" r1 INNER JOIN main."j21" r2 ON (((r1.`id` > r2.`id`)))) (3 rows) -- ensure non-unique rel is not chosen as inner --Testcase 341: 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" -> Sort Output: j31.id Sort Key: j31.id -> Foreign Scan on public.j31 Output: j31.id SQLite query: SELECT `id` FROM main."j31" (15 rows) -- ensure left join is marked as unique --Testcase 342: 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" -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (15 rows) -- ensure right join is marked as unique --Testcase 343: 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" -> Sort Output: j11.id Sort Key: j11.id -> Foreign Scan on public.j11 Output: j11.id SQLite query: SELECT `id` FROM main."j11" (15 rows) -- ensure full join is marked as unique --Testcase 344: 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" -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (15 rows) -- a clauseless (cross) join can't be unique --Testcase 345: 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 --Testcase 346: 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" -> Sort Output: j21.id Sort Key: j21.id -> Foreign Scan on public.j21 Output: j21.id SQLite query: SELECT `id` FROM main."j21" (15 rows) -- ensure a distinct clause allows the inner to become unique --Testcase 347: 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 -> Unique Output: j31.id -> Foreign Scan on public.j31 Output: j31.id SQLite query: SELECT `id` FROM main."j31" ORDER BY `id` ASC NULLS LAST (14 rows) -- ensure group by clause allows the inner to become unique --Testcase 348: 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) --drop table j1; --drop table j2; --drop table j3; -- test more complex permutations of unique joins --Testcase 475: create foreign table j12 (id1 int options (key 'true'), id2 int options (key 'true')) server sqlite_svr; --Testcase 476: create foreign table j22 (id1 int options (key 'true'), id2 int options (key 'true')) server sqlite_svr; --Testcase 477: create foreign table j32 (id1 int options (key 'true'), id2 int options (key 'true')) server sqlite_svr; --Testcase 349: insert into j12 values(1,1),(1,2); --Testcase 350: insert into j22 values(1,1); --Testcase 351: insert into j32 values(1,1); --analyze j1; --analyze j2; --analyze j3; -- ensure there's no unique join when not all columns which are part of the -- unique index are seen in the join clause --Testcase 352: 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" -> 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" (15 rows) -- ensure proper unique detection with multiple join quals --Testcase 353: 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" -> 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" (15 rows) -- ensure we don't detect the join to be unique when quals are not part of the -- join condition --Testcase 354: explain (verbose, costs off) select * from j12 inner join j22 on j12.id1 = j22.id1 where j12.id2 = 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: j12.id1, j12.id2, j22.id1, j22.id2 SQLite query: SELECT r1.`id1`, r1.`id2`, r2.`id1`, r2.`id2` FROM (main."j12" r1 INNER JOIN main."j22" r2 ON (((r1.`id1` = r2.`id1`)) AND ((r1.`id2` = 1)))) (3 rows) -- as above, but for left joins. --Testcase 355: explain (verbose, costs off) select * from j12 left join j22 on j12.id1 = j22.id1 where j12.id2 = 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: j12.id1, j12.id2, j22.id1, j22.id2 SQLite query: SELECT r1.`id1`, r1.`id2`, r2.`id1`, r2.`id2` FROM (main."j12" r1 LEFT JOIN main."j22" r2 ON (((r1.`id1` = r2.`id1`)))) WHERE ((r1.`id2` = 1)) (3 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; -- skip, cannot create index on foreign table -- create indexes that will be preferred over the PKs to perform the join --create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1; --create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1; -- need an additional row in j2, if we want j2_id1_idx to be preferred --Testcase 478: insert into j22 values(1,2); --analyze j2; --Testcase 356: 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 -------------- Foreign Scan (1 row) --Testcase 357: 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) -- Exercise array keys mark/restore B-Tree code --Testcase 479: explain (costs off) select * from j12 inner join j22 on j12.id1 = j22.id1 and j12.id2 = j22.id2 where j12.id1 % 1000 = 1 and j22.id1 % 1000 = 1 and j22.id1 = any (array[1]); QUERY PLAN -------------- Foreign Scan (1 row) --Testcase 480: select * from j12 inner join j22 on j12.id1 = j22.id1 and j12.id2 = j22.id2 where j12.id1 % 1000 = 1 and j22.id1 % 1000 = 1 and j22.id1 = any (array[1]); id1 | id2 | id1 | id2 -----+-----+-----+----- 1 | 1 | 1 | 1 1 | 2 | 1 | 2 (2 rows) -- Exercise array keys "find extreme element" B-Tree code --Testcase 481: explain (costs off) select * from j12 inner join j22 on j12.id1 = j22.id1 and j12.id2 = j22.id2 where j12.id1 % 1000 = 1 and j22.id1 % 1000 = 1 and j22.id1 >= any (array[1,5]); QUERY PLAN -------------- Foreign Scan (1 row) --Testcase 482: select * from j12 inner join j22 on j12.id1 = j22.id1 and j12.id2 = j22.id2 where j12.id1 % 1000 = 1 and j22.id1 % 1000 = 1 and j22.id1 >= any (array[1,5]); id1 | id2 | id1 | id2 -----+-----+-----+----- 1 | 1 | 1 | 1 1 | 2 | 1 | 2 (2 rows) reset enable_nestloop; reset enable_hashjoin; reset enable_sort; --drop table j1; --drop table j2; --drop table j3; -- check that semijoin inner is not seen as unique for a portion of the outerrel --Testcase 483: 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; --Testcase 358: 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join Output: t1.unique1, t2.hundred Hash Cond: (t2.hundred = t3.tenthous) -> Foreign Scan on public.tenk1 t2 Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4 SQLite query: SELECT `hundred` FROM main."tenk1" -> Hash Output: t1.unique1, 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" (22 rows) -- ... unless it actually is unique --Testcase 484: create table j3 as select unique1, tenthous from onek; vacuum analyze j3; --Testcase 485: create unique index on j3(unique1, tenthous); --Testcase 359: 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.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4 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) --Testcase 486: 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 --Testcase 487: DROP SERVER sqlite_svr; --Testcase 488: DROP EXTENSION sqlite_fdw CASCADE;