| -- |
| -- Tests for joins between replicated tables |
| -- |
| create schema rpt_joins; |
| set search_path to rpt_joins, public; |
| -- |
| -- Test JOIN clauses, bellow tests are copy from tests for partitioned table |
| -- |
| CREATE TABLE J1_TBL ( |
| i integer, |
| j integer, |
| t text |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE TABLE J2_TBL ( |
| i integer, |
| k integer |
| ) distributed replicated; |
| INSERT INTO J1_TBL VALUES (1, 4, 'one'); |
| INSERT INTO J1_TBL VALUES (2, 3, 'two'); |
| INSERT INTO J1_TBL VALUES (3, 2, 'three'); |
| INSERT INTO J1_TBL VALUES (4, 1, 'four'); |
| INSERT INTO J1_TBL VALUES (5, 0, 'five'); |
| INSERT INTO J1_TBL VALUES (6, 6, 'six'); |
| INSERT INTO J1_TBL VALUES (7, 7, 'seven'); |
| INSERT INTO J1_TBL VALUES (8, 8, 'eight'); |
| INSERT INTO J1_TBL VALUES (0, NULL, 'zero'); |
| INSERT INTO J1_TBL VALUES (NULL, NULL, 'null'); |
| INSERT INTO J1_TBL VALUES (NULL, 0, 'zero'); |
| INSERT INTO J2_TBL VALUES (1, -1); |
| INSERT INTO J2_TBL VALUES (2, 2); |
| INSERT INTO J2_TBL VALUES (3, -3); |
| INSERT INTO J2_TBL VALUES (2, 4); |
| INSERT INTO J2_TBL VALUES (5, -5); |
| INSERT INTO J2_TBL VALUES (5, -5); |
| INSERT INTO J2_TBL VALUES (0, NULL); |
| INSERT INTO J2_TBL VALUES (NULL, NULL); |
| INSERT INTO J2_TBL VALUES (NULL, 0); |
| -- |
| -- CROSS JOIN |
| -- Qualifications are not allowed on cross joins, |
| -- which degenerate into a standard unqualified inner join. |
| -- |
| SELECT '' AS "xxx", * |
| FROM J1_TBL CROSS JOIN J2_TBL; |
| xxx | i | j | t | i | k |
| -----+---+---+-------+---+---- |
| | 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 |
| | 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 |
| | 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 |
| | 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 |
| | | | null | 1 | -1 |
| | | | null | 2 | 2 |
| | | | null | 3 | -3 |
| | | | null | 2 | 4 |
| | | | null | 5 | -5 |
| | | | null | 5 | -5 |
| | | | null | 0 | |
| | | | null | | |
| | | | null | | 0 |
| | | 0 | zero | 1 | -1 |
| | | 0 | zero | 2 | 2 |
| | | 0 | zero | 3 | -3 |
| | | 0 | zero | 2 | 4 |
| | | 0 | zero | 5 | -5 |
| | | 0 | zero | 5 | -5 |
| | | 0 | zero | 0 | |
| | | 0 | zero | | |
| | | 0 | zero | | 0 |
| (99 rows) |
| |
| -- ambiguous column |
| SELECT '' AS "xxx", i, k, t |
| FROM J1_TBL CROSS JOIN J2_TBL; |
| ERROR: column reference "i" is ambiguous |
| LINE 1: SELECT '' AS "xxx", i, k, t |
| ^ |
| -- resolve previous ambiguity by specifying the table name |
| SELECT '' AS "xxx", t1.i, k, t |
| FROM J1_TBL t1 CROSS JOIN J2_TBL t2; |
| xxx | i | k | t |
| -----+---+----+------- |
| | 1 | -1 | one |
| | 1 | 2 | one |
| | 1 | -3 | one |
| | 1 | 4 | one |
| | 1 | -5 | one |
| | 1 | -5 | one |
| | 1 | | one |
| | 1 | | one |
| | 1 | 0 | one |
| | 2 | -1 | two |
| | 2 | 2 | two |
| | 2 | -3 | two |
| | 2 | 4 | two |
| | 2 | -5 | two |
| | 2 | -5 | two |
| | 2 | | two |
| | 2 | | two |
| | 2 | 0 | two |
| | 0 | -1 | zero |
| | 0 | 2 | zero |
| | 0 | -3 | zero |
| | 0 | 4 | zero |
| | 0 | -5 | zero |
| | 0 | -5 | zero |
| | 0 | | zero |
| | 0 | | zero |
| | 0 | 0 | zero |
| | 3 | -1 | three |
| | 3 | 2 | three |
| | 3 | -3 | three |
| | 3 | 4 | three |
| | 3 | -5 | three |
| | 3 | -5 | three |
| | 3 | | three |
| | 3 | | three |
| | 3 | 0 | three |
| | 4 | -1 | four |
| | 4 | 2 | four |
| | 4 | -3 | four |
| | 4 | 4 | four |
| | 4 | -5 | four |
| | 4 | -5 | four |
| | 4 | | four |
| | 4 | | four |
| | 4 | 0 | four |
| | 5 | -1 | five |
| | 5 | 2 | five |
| | 5 | -3 | five |
| | 5 | 4 | five |
| | 5 | -5 | five |
| | 5 | -5 | five |
| | 5 | | five |
| | 5 | | five |
| | 5 | 0 | five |
| | 6 | -1 | six |
| | 6 | 2 | six |
| | 6 | -3 | six |
| | 6 | 4 | six |
| | 6 | -5 | six |
| | 6 | -5 | six |
| | 6 | | six |
| | 6 | | six |
| | 6 | 0 | six |
| | 7 | -1 | seven |
| | 7 | 2 | seven |
| | 7 | -3 | seven |
| | 7 | 4 | seven |
| | 7 | -5 | seven |
| | 7 | -5 | seven |
| | 7 | | seven |
| | 7 | | seven |
| | 7 | 0 | seven |
| | | -1 | null |
| | | 2 | null |
| | | -3 | null |
| | | 4 | null |
| | | -5 | null |
| | | -5 | null |
| | | | null |
| | | | null |
| | | 0 | null |
| | | -1 | zero |
| | | 2 | zero |
| | | -3 | zero |
| | | 4 | zero |
| | | -5 | zero |
| | | -5 | zero |
| | | | zero |
| | | | zero |
| | | 0 | zero |
| | 8 | -1 | eight |
| | 8 | 2 | eight |
| | 8 | -3 | eight |
| | 8 | 4 | eight |
| | 8 | -5 | eight |
| | 8 | -5 | eight |
| | 8 | | eight |
| | 8 | | eight |
| | 8 | 0 | eight |
| (99 rows) |
| |
| SELECT '' AS "xxx", ii, tt, kk |
| FROM (J1_TBL CROSS JOIN J2_TBL) |
| AS tx (ii, jj, tt, ii2, kk); |
| xxx | ii | tt | kk |
| -----+----+-------+---- |
| | 1 | one | -1 |
| | 1 | one | 2 |
| | 1 | one | -3 |
| | 1 | one | 4 |
| | 1 | one | -5 |
| | 1 | one | -5 |
| | 1 | one | |
| | 1 | one | |
| | 1 | one | 0 |
| | 2 | two | -1 |
| | 2 | two | 2 |
| | 2 | two | -3 |
| | 2 | two | 4 |
| | 2 | two | -5 |
| | 2 | two | -5 |
| | 2 | two | |
| | 2 | two | |
| | 2 | two | 0 |
| | 0 | zero | -1 |
| | 0 | zero | 2 |
| | 0 | zero | -3 |
| | 0 | zero | 4 |
| | 0 | zero | -5 |
| | 0 | zero | -5 |
| | 0 | zero | |
| | 0 | zero | |
| | 0 | zero | 0 |
| | 8 | eight | -1 |
| | 8 | eight | 2 |
| | 8 | eight | -3 |
| | 8 | eight | 4 |
| | 8 | eight | -5 |
| | 8 | eight | -5 |
| | 8 | eight | |
| | 8 | eight | |
| | 8 | eight | 0 |
| | 3 | three | -1 |
| | 3 | three | 2 |
| | 3 | three | -3 |
| | 3 | three | 4 |
| | 3 | three | -5 |
| | 3 | three | -5 |
| | 3 | three | |
| | 3 | three | |
| | 3 | three | 0 |
| | 4 | four | -1 |
| | 4 | four | 2 |
| | 4 | four | -3 |
| | 4 | four | 4 |
| | 4 | four | -5 |
| | 4 | four | -5 |
| | 4 | four | |
| | 4 | four | |
| | 4 | four | 0 |
| | 5 | five | -1 |
| | 5 | five | 2 |
| | 5 | five | -3 |
| | 5 | five | 4 |
| | 5 | five | -5 |
| | 5 | five | -5 |
| | 5 | five | |
| | 5 | five | |
| | 5 | five | 0 |
| | 6 | six | -1 |
| | 6 | six | 2 |
| | 6 | six | -3 |
| | 6 | six | 4 |
| | 6 | six | -5 |
| | 6 | six | -5 |
| | 6 | six | |
| | 6 | six | |
| | 6 | six | 0 |
| | 7 | seven | -1 |
| | 7 | seven | 2 |
| | 7 | seven | -3 |
| | 7 | seven | 4 |
| | 7 | seven | -5 |
| | 7 | seven | -5 |
| | 7 | seven | |
| | 7 | seven | |
| | 7 | seven | 0 |
| | | null | -1 |
| | | null | 2 |
| | | null | -3 |
| | | null | 4 |
| | | null | -5 |
| | | null | -5 |
| | | null | |
| | | null | |
| | | null | 0 |
| | | zero | -1 |
| | | zero | 2 |
| | | zero | -3 |
| | | zero | 4 |
| | | zero | -5 |
| | | zero | -5 |
| | | zero | |
| | | zero | |
| | | zero | 0 |
| (99 rows) |
| |
| SELECT '' AS "xxx", tx.ii, tx.jj, tx.kk |
| FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e)) |
| AS tx (ii, jj, tt, ii2, kk); |
| xxx | ii | jj | kk |
| -----+----+----+---- |
| | 1 | 4 | -1 |
| | 1 | 4 | 2 |
| | 1 | 4 | -3 |
| | 1 | 4 | 4 |
| | 1 | 4 | -5 |
| | 1 | 4 | -5 |
| | 1 | 4 | |
| | 1 | 4 | |
| | 1 | 4 | 0 |
| | 2 | 3 | -1 |
| | 2 | 3 | 2 |
| | 2 | 3 | -3 |
| | 2 | 3 | 4 |
| | 2 | 3 | -5 |
| | 2 | 3 | -5 |
| | 2 | 3 | |
| | 2 | 3 | |
| | 2 | 3 | 0 |
| | 0 | | -1 |
| | 0 | | 2 |
| | 0 | | -3 |
| | 0 | | 4 |
| | 0 | | -5 |
| | 0 | | -5 |
| | 0 | | |
| | 0 | | |
| | 0 | | 0 |
| | 3 | 2 | -1 |
| | 3 | 2 | 2 |
| | 3 | 2 | -3 |
| | 3 | 2 | 4 |
| | 3 | 2 | -5 |
| | 3 | 2 | -5 |
| | 3 | 2 | |
| | 3 | 2 | |
| | 3 | 2 | 0 |
| | 4 | 1 | -1 |
| | 4 | 1 | 2 |
| | 4 | 1 | -3 |
| | 4 | 1 | 4 |
| | 4 | 1 | -5 |
| | 4 | 1 | -5 |
| | 4 | 1 | |
| | 4 | 1 | |
| | 4 | 1 | 0 |
| | 5 | 0 | -1 |
| | 5 | 0 | 2 |
| | 5 | 0 | -3 |
| | 5 | 0 | 4 |
| | 5 | 0 | -5 |
| | 5 | 0 | -5 |
| | 5 | 0 | |
| | 5 | 0 | |
| | 5 | 0 | 0 |
| | 6 | 6 | -1 |
| | 6 | 6 | 2 |
| | 6 | 6 | -3 |
| | 6 | 6 | 4 |
| | 6 | 6 | -5 |
| | 6 | 6 | -5 |
| | 6 | 6 | |
| | 6 | 6 | |
| | 6 | 6 | 0 |
| | 7 | 7 | -1 |
| | 7 | 7 | 2 |
| | 7 | 7 | -3 |
| | 7 | 7 | 4 |
| | 7 | 7 | -5 |
| | 7 | 7 | -5 |
| | 7 | 7 | |
| | 7 | 7 | |
| | 7 | 7 | 0 |
| | | | -1 |
| | | | 2 |
| | | | -3 |
| | | | 4 |
| | | | -5 |
| | | | -5 |
| | | | |
| | | | |
| | | | 0 |
| | | 0 | -1 |
| | | 0 | 2 |
| | | 0 | -3 |
| | | 0 | 4 |
| | | 0 | -5 |
| | | 0 | -5 |
| | | 0 | |
| | | 0 | |
| | | 0 | 0 |
| | 8 | 8 | -1 |
| | 8 | 8 | 2 |
| | 8 | 8 | -3 |
| | 8 | 8 | 4 |
| | 8 | 8 | -5 |
| | 8 | 8 | -5 |
| | 8 | 8 | |
| | 8 | 8 | |
| | 8 | 8 | 0 |
| (99 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b; |
| xxx | i | j | t | i | k | i | k |
| -----+---+---+-------+---+----+---+---- |
| | 8 | 8 | eight | 1 | -1 | 1 | -1 |
| | 8 | 8 | eight | 1 | -1 | 2 | 2 |
| | 8 | 8 | eight | 1 | -1 | 3 | -3 |
| | 8 | 8 | eight | 1 | -1 | 2 | 4 |
| | 8 | 8 | eight | 1 | -1 | 5 | -5 |
| | 8 | 8 | eight | 1 | -1 | 5 | -5 |
| | 8 | 8 | eight | 1 | -1 | 0 | |
| | 8 | 8 | eight | 1 | -1 | | |
| | 8 | 8 | eight | 1 | -1 | | 0 |
| | 8 | 8 | eight | 2 | 2 | 1 | -1 |
| | 8 | 8 | eight | 2 | 2 | 2 | 2 |
| | 8 | 8 | eight | 2 | 2 | 3 | -3 |
| | 8 | 8 | eight | 2 | 2 | 2 | 4 |
| | 8 | 8 | eight | 2 | 2 | 5 | -5 |
| | 8 | 8 | eight | 2 | 2 | 5 | -5 |
| | 8 | 8 | eight | 2 | 2 | 0 | |
| | 8 | 8 | eight | 2 | 2 | | |
| | 8 | 8 | eight | 2 | 2 | | 0 |
| | 8 | 8 | eight | 3 | -3 | 1 | -1 |
| | 8 | 8 | eight | 3 | -3 | 2 | 2 |
| | 8 | 8 | eight | 3 | -3 | 3 | -3 |
| | 8 | 8 | eight | 3 | -3 | 2 | 4 |
| | 8 | 8 | eight | 3 | -3 | 5 | -5 |
| | 8 | 8 | eight | 3 | -3 | 5 | -5 |
| | 8 | 8 | eight | 3 | -3 | 0 | |
| | 8 | 8 | eight | 3 | -3 | | |
| | 8 | 8 | eight | 3 | -3 | | 0 |
| | 8 | 8 | eight | 2 | 4 | 1 | -1 |
| | 8 | 8 | eight | 2 | 4 | 2 | 2 |
| | 8 | 8 | eight | 2 | 4 | 3 | -3 |
| | 8 | 8 | eight | 2 | 4 | 2 | 4 |
| | 8 | 8 | eight | 2 | 4 | 5 | -5 |
| | 8 | 8 | eight | 2 | 4 | 5 | -5 |
| | 8 | 8 | eight | 2 | 4 | 0 | |
| | 8 | 8 | eight | 2 | 4 | | |
| | 8 | 8 | eight | 2 | 4 | | 0 |
| | 8 | 8 | eight | 5 | -5 | 1 | -1 |
| | 8 | 8 | eight | 5 | -5 | 2 | 2 |
| | 8 | 8 | eight | 5 | -5 | 3 | -3 |
| | 8 | 8 | eight | 5 | -5 | 2 | 4 |
| | 8 | 8 | eight | 5 | -5 | 5 | -5 |
| | 8 | 8 | eight | 5 | -5 | 5 | -5 |
| | 8 | 8 | eight | 5 | -5 | 0 | |
| | 8 | 8 | eight | 5 | -5 | | |
| | 8 | 8 | eight | 5 | -5 | | 0 |
| | 8 | 8 | eight | 5 | -5 | 1 | -1 |
| | 8 | 8 | eight | 5 | -5 | 2 | 2 |
| | 8 | 8 | eight | 5 | -5 | 3 | -3 |
| | 8 | 8 | eight | 5 | -5 | 2 | 4 |
| | 8 | 8 | eight | 5 | -5 | 5 | -5 |
| | 8 | 8 | eight | 5 | -5 | 5 | -5 |
| | 8 | 8 | eight | 5 | -5 | 0 | |
| | 8 | 8 | eight | 5 | -5 | | |
| | 8 | 8 | eight | 5 | -5 | | 0 |
| | 8 | 8 | eight | 0 | | 1 | -1 |
| | 8 | 8 | eight | 0 | | 2 | 2 |
| | 8 | 8 | eight | 0 | | 3 | -3 |
| | 8 | 8 | eight | 0 | | 2 | 4 |
| | 8 | 8 | eight | 0 | | 5 | -5 |
| | 8 | 8 | eight | 0 | | 5 | -5 |
| | 8 | 8 | eight | 0 | | 0 | |
| | 8 | 8 | eight | 0 | | | |
| | 8 | 8 | eight | 0 | | | 0 |
| | 8 | 8 | eight | | | 1 | -1 |
| | 8 | 8 | eight | | | 2 | 2 |
| | 8 | 8 | eight | | | 3 | -3 |
| | 8 | 8 | eight | | | 2 | 4 |
| | 8 | 8 | eight | | | 5 | -5 |
| | 8 | 8 | eight | | | 5 | -5 |
| | 8 | 8 | eight | | | 0 | |
| | 8 | 8 | eight | | | | |
| | 8 | 8 | eight | | | | 0 |
| | 8 | 8 | eight | | 0 | 1 | -1 |
| | 8 | 8 | eight | | 0 | 2 | 2 |
| | 8 | 8 | eight | | 0 | 3 | -3 |
| | 8 | 8 | eight | | 0 | 2 | 4 |
| | 8 | 8 | eight | | 0 | 5 | -5 |
| | 8 | 8 | eight | | 0 | 5 | -5 |
| | 8 | 8 | eight | | 0 | 0 | |
| | 8 | 8 | eight | | 0 | | |
| | 8 | 8 | eight | | 0 | | 0 |
| | 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 |
| | 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 |
| | 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 |
| | 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 |
| | 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 |
| | 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 |
| | 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 |
| | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | | | 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 |
| | 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 |
| | 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 |
| | 0 | | zero | | 0 | 1 | -1 |
| | 0 | | zero | | 0 | 2 | 2 |
| | 0 | | zero | | 0 | 3 | -3 |
| | 0 | | zero | | 0 | 2 | 4 |
| | 0 | | zero | | 0 | 5 | -5 |
| | 0 | | zero | | 0 | 5 | -5 |
| | 0 | | zero | | 0 | 0 | |
| | 0 | | zero | | 0 | | |
| | 0 | | zero | | 0 | | 0 |
| (891 rows) |
| |
| -- |
| -- |
| -- Inner joins (equi-joins) |
| -- |
| -- |
| -- |
| -- Inner joins (equi-joins) with USING clause |
| -- The USING syntax changes the shape of the resulting table |
| -- by including a column in the USING clause only once in the result. |
| -- |
| -- Inner equi-join on specified column |
| SELECT '' AS "xxx", * |
| FROM J1_TBL INNER JOIN J2_TBL USING (i); |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 3 | 2 | three | -3 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 4 |
| | 2 | 3 | two | 2 |
| | 0 | | zero | |
| (7 rows) |
| |
| -- Same as above, slightly different syntax |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL USING (i); |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 4 |
| | 2 | 3 | two | 2 |
| | 0 | | zero | |
| | 3 | 2 | three | -3 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| (7 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) |
| ORDER BY a, d; |
| xxx | a | b | c | d |
| -----+---+---+-------+---- |
| | 0 | | zero | |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 2 |
| | 2 | 3 | two | 4 |
| | 3 | 2 | three | -3 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| (7 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) |
| ORDER BY b, t1.a; |
| xxx | b | a | c | a |
| -----+---+---+-------+--- |
| | 0 | 5 | five | |
| | 0 | | zero | |
| | 2 | 3 | three | 2 |
| | 4 | 1 | one | 2 |
| (4 rows) |
| |
| -- |
| -- NATURAL JOIN |
| -- Inner equi-join on all columns with the same name |
| -- |
| SELECT '' AS "xxx", * |
| FROM J1_TBL NATURAL JOIN J2_TBL; |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 4 |
| | 2 | 3 | two | 2 |
| | 0 | | zero | |
| | 3 | 2 | three | -3 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| (7 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d); |
| xxx | a | b | c | d |
| -----+---+---+-------+---- |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 4 |
| | 2 | 3 | two | 2 |
| | 0 | | zero | |
| | 3 | 2 | three | -3 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| (7 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a); |
| xxx | a | b | c | d |
| -----+---+---+------+--- |
| | 2 | 3 | two | 2 |
| | 0 | | zero | |
| | 4 | 1 | four | 2 |
| (3 rows) |
| |
| -- mismatch number of columns |
| -- currently, Postgres will fill in with underlying names |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a); |
| xxx | a | b | t | k |
| -----+---+---+-------+---- |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 4 |
| | 2 | 3 | two | 2 |
| | 0 | | zero | |
| | 3 | 2 | three | -3 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| (7 rows) |
| |
| -- |
| -- Inner joins (equi-joins) |
| -- |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i); |
| xxx | i | j | t | i | k |
| -----+---+---+-------+---+---- |
| | 1 | 4 | one | 1 | -1 |
| | 2 | 3 | two | 2 | 4 |
| | 2 | 3 | two | 2 | 2 |
| | 0 | | zero | 0 | |
| | 3 | 2 | three | 3 | -3 |
| | 5 | 0 | five | 5 | -5 |
| | 5 | 0 | five | 5 | -5 |
| (7 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k); |
| xxx | i | j | t | i | k |
| -----+---+---+------+---+--- |
| | 2 | 3 | two | 2 | 2 |
| | 0 | | zero | | 0 |
| | 4 | 1 | four | 2 | 4 |
| (3 rows) |
| |
| -- |
| -- Non-equi-joins |
| -- |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k); |
| xxx | i | j | t | i | k |
| -----+---+---+-------+---+--- |
| | 1 | 4 | one | 2 | 2 |
| | 1 | 4 | one | 2 | 4 |
| | 2 | 3 | two | 2 | 2 |
| | 2 | 3 | two | 2 | 4 |
| | 0 | | zero | 2 | 2 |
| | 0 | | zero | 2 | 4 |
| | 0 | | zero | | 0 |
| | 3 | 2 | three | 2 | 4 |
| | 4 | 1 | four | 2 | 4 |
| (9 rows) |
| |
| -- |
| -- Outer joins |
| -- Note that OUTER is a noise word |
| -- |
| SELECT '' AS "xxx", * |
| FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) |
| ORDER BY i, k, t; |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 0 | | zero | |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 2 |
| | 2 | 3 | two | 4 |
| | 3 | 2 | three | -3 |
| | 4 | 1 | four | |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| | 6 | 6 | six | |
| | 7 | 7 | seven | |
| | 8 | 8 | eight | |
| | | | null | |
| | | 0 | zero | |
| (13 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL LEFT JOIN J2_TBL USING (i) |
| ORDER BY i, k, t; |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 0 | | zero | |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 2 |
| | 2 | 3 | two | 4 |
| | 3 | 2 | three | -3 |
| | 4 | 1 | four | |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| | 6 | 6 | six | |
| | 7 | 7 | seven | |
| | 8 | 8 | eight | |
| | | | null | |
| | | 0 | zero | |
| (13 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 2 |
| | 3 | 2 | three | -3 |
| | 2 | 3 | two | 4 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| | 0 | | zero | |
| | | | | |
| | | | | 0 |
| (9 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL RIGHT JOIN J2_TBL USING (i); |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 2 |
| | 3 | 2 | three | -3 |
| | 2 | 3 | two | 4 |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| | 0 | | zero | |
| | | | | |
| | | | | 0 |
| (9 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) |
| ORDER BY i, k, t; |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 0 | | zero | |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 2 |
| | 2 | 3 | two | 4 |
| | 3 | 2 | three | -3 |
| | 4 | 1 | four | |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| | 6 | 6 | six | |
| | 7 | 7 | seven | |
| | 8 | 8 | eight | |
| | | | | 0 |
| | | | null | |
| | | 0 | zero | |
| | | | | |
| (15 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL FULL JOIN J2_TBL USING (i) |
| ORDER BY i, k, t; |
| xxx | i | j | t | k |
| -----+---+---+-------+---- |
| | 0 | | zero | |
| | 1 | 4 | one | -1 |
| | 2 | 3 | two | 2 |
| | 2 | 3 | two | 4 |
| | 3 | 2 | three | -3 |
| | 4 | 1 | four | |
| | 5 | 0 | five | -5 |
| | 5 | 0 | five | -5 |
| | 6 | 6 | six | |
| | 7 | 7 | seven | |
| | 8 | 8 | eight | |
| | | | | 0 |
| | | | null | |
| | | 0 | zero | |
| | | | | |
| (15 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); |
| xxx | i | j | t | k |
| -----+---+---+---+--- |
| (0 rows) |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); |
| xxx | i | j | t | k |
| -----+---+---+-----+---- |
| | 1 | 4 | one | -1 |
| (1 row) |
| |
| -- |
| -- Multiway full join |
| -- |
| CREATE TABLE t1 (name TEXT, n INTEGER); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE TABLE t2 (name TEXT, n INTEGER) distributed replicated; |
| CREATE TABLE t3 (name TEXT, n INTEGER); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO t1 VALUES ( 'bb', 11 ); |
| INSERT INTO t2 VALUES ( 'bb', 12 ); |
| INSERT INTO t2 VALUES ( 'cc', 22 ); |
| INSERT INTO t2 VALUES ( 'ee', 42 ); |
| INSERT INTO t3 VALUES ( 'bb', 13 ); |
| INSERT INTO t3 VALUES ( 'cc', 23 ); |
| INSERT INTO t3 VALUES ( 'dd', 33 ); |
| SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); |
| name | n | n | n |
| ------+----+----+---- |
| bb | 11 | 12 | 13 |
| dd | | | 33 |
| cc | | 22 | 23 |
| ee | | 42 | |
| (4 rows) |
| |
| -- |
| -- Test interactions of join syntax and subqueries |
| -- |
| -- Basic cases (we expect planner to pull up the subquery here) |
| SELECT * FROM |
| (SELECT * FROM t2) as s2 |
| INNER JOIN |
| (SELECT * FROM t3) s3 |
| USING (name); |
| name | n | n |
| ------+----+---- |
| bb | 12 | 13 |
| cc | 22 | 23 |
| (2 rows) |
| |
| SELECT * FROM |
| (SELECT * FROM t2) as s2 |
| LEFT JOIN |
| (SELECT * FROM t3) s3 |
| USING (name); |
| name | n | n |
| ------+----+---- |
| bb | 12 | 13 |
| cc | 22 | 23 |
| ee | 42 | |
| (3 rows) |
| |
| SELECT * FROM |
| (SELECT * FROM t2) as s2 |
| FULL JOIN |
| (SELECT * FROM t3) s3 |
| USING (name); |
| name | n | n |
| ------+----+---- |
| bb | 12 | 13 |
| cc | 22 | 23 |
| dd | | 33 |
| ee | 42 | |
| (4 rows) |
| |
| -- Cases with non-nullable expressions in subquery results; |
| -- make sure these go to null as expected |
| SELECT * FROM |
| (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 |
| NATURAL INNER JOIN |
| (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; |
| name | s2_n | s2_2 | s3_n | s3_2 |
| ------+------+------+------+------ |
| bb | 12 | 2 | 13 | 3 |
| cc | 22 | 2 | 23 | 3 |
| (2 rows) |
| |
| SELECT * FROM |
| (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 |
| NATURAL LEFT JOIN |
| (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; |
| name | s2_n | s2_2 | s3_n | s3_2 |
| ------+------+------+------+------ |
| bb | 12 | 2 | 13 | 3 |
| cc | 22 | 2 | 23 | 3 |
| ee | 42 | 2 | | |
| (3 rows) |
| |
| SELECT * FROM |
| (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 |
| NATURAL FULL JOIN |
| (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; |
| name | s2_n | s2_2 | s3_n | s3_2 |
| ------+------+------+------+------ |
| bb | 12 | 2 | 13 | 3 |
| cc | 22 | 2 | 23 | 3 |
| dd | | | 33 | 3 |
| ee | 42 | 2 | | |
| (4 rows) |
| |
| SELECT * FROM |
| (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 |
| NATURAL INNER JOIN |
| (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 |
| NATURAL INNER JOIN |
| (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; |
| name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 |
| ------+------+------+------+------+------+------ |
| bb | 11 | 1 | 12 | 2 | 13 | 3 |
| (1 row) |
| |
| SELECT * FROM |
| (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 |
| NATURAL FULL JOIN |
| (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 |
| NATURAL FULL JOIN |
| (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; |
| name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 |
| ------+------+------+------+------+------+------ |
| bb | 11 | 1 | 12 | 2 | 13 | 3 |
| dd | | | | | 33 | 3 |
| cc | | | 22 | 2 | 23 | 3 |
| ee | | | 42 | 2 | | |
| (4 rows) |
| |
| SELECT * FROM |
| (SELECT name, n as s1_n FROM t1) as s1 |
| NATURAL FULL JOIN |
| (SELECT * FROM |
| (SELECT name, n as s2_n FROM t2) as s2 |
| NATURAL FULL JOIN |
| (SELECT name, n as s3_n FROM t3) as s3 |
| ) ss2; |
| name | s1_n | s2_n | s3_n |
| ------+------+------+------ |
| cc | | 22 | 23 |
| ee | | 42 | |
| bb | 11 | 12 | 13 |
| dd | | | 33 |
| (4 rows) |
| |
| SELECT * FROM |
| (SELECT name, n as s1_n FROM t1) as s1 |
| NATURAL FULL JOIN |
| (SELECT * FROM |
| (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 |
| NATURAL FULL JOIN |
| (SELECT name, n as s3_n FROM t3) as s3 |
| ) ss2; |
| name | s1_n | s2_n | s2_2 | s3_n |
| ------+------+------+------+------ |
| bb | 11 | 12 | 2 | 13 |
| dd | | | | 33 |
| cc | | 22 | 2 | 23 |
| ee | | 42 | 2 | |
| (4 rows) |
| |
| -- Test for propagation of nullability constraints into sub-joins |
| create temp table x (x1 int, x2 int) distributed replicated; |
| insert into x values (1,11); |
| insert into x values (2,22); |
| insert into x values (3,null); |
| insert into x values (4,44); |
| insert into x values (5,null); |
| create temp table y (y1 int, y2 int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'y1' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into y values (1,111); |
| insert into y values (2,222); |
| insert into y values (3,333); |
| insert into y values (4,null); |
| select * from x; |
| x1 | x2 |
| ----+---- |
| 1 | 11 |
| 2 | 22 |
| 3 | |
| 4 | 44 |
| 5 | |
| (5 rows) |
| |
| select * from y; |
| y1 | y2 |
| ----+----- |
| 3 | 333 |
| 4 | |
| 1 | 111 |
| 2 | 222 |
| (4 rows) |
| |
| select * from x left join y on (x1 = y1 and x2 is not null); |
| x1 | x2 | y1 | y2 |
| ----+----+----+----- |
| 1 | 11 | 1 | 111 |
| 2 | 22 | 2 | 222 |
| 3 | | | |
| 4 | 44 | 4 | |
| 5 | | | |
| (5 rows) |
| |
| select * from x left join y on (x1 = y1 and y2 is not null); |
| x1 | x2 | y1 | y2 |
| ----+----+----+----- |
| 1 | 11 | 1 | 111 |
| 2 | 22 | 2 | 222 |
| 3 | | 3 | 333 |
| 4 | 44 | | |
| 5 | | | |
| (5 rows) |
| |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1); |
| x1 | x2 | y1 | y2 | xx1 | xx2 |
| ----+----+----+-----+-----+----- |
| 1 | 11 | 1 | 111 | 1 | 11 |
| 2 | 22 | 2 | 222 | 2 | 22 |
| 3 | | 3 | 333 | 3 | |
| 4 | 44 | 4 | | 4 | 44 |
| 5 | | | | 5 | |
| (5 rows) |
| |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1 and x2 is not null); |
| x1 | x2 | y1 | y2 | xx1 | xx2 |
| ----+----+----+-----+-----+----- |
| 1 | 11 | 1 | 111 | 1 | 11 |
| 2 | 22 | 2 | 222 | 2 | 22 |
| 3 | | 3 | 333 | | |
| 4 | 44 | 4 | | 4 | 44 |
| 5 | | | | | |
| (5 rows) |
| |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1 and y2 is not null); |
| x1 | x2 | y1 | y2 | xx1 | xx2 |
| ----+----+----+-----+-----+----- |
| 1 | 11 | 1 | 111 | 1 | 11 |
| 2 | 22 | 2 | 222 | 2 | 22 |
| 3 | | 3 | 333 | 3 | |
| 4 | 44 | 4 | | | |
| 5 | | | | | |
| (5 rows) |
| |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1 and xx2 is not null); |
| x1 | x2 | y1 | y2 | xx1 | xx2 |
| ----+----+----+-----+-----+----- |
| 1 | 11 | 1 | 111 | 1 | 11 |
| 2 | 22 | 2 | 222 | 2 | 22 |
| 3 | | 3 | 333 | | |
| 4 | 44 | 4 | | 4 | 44 |
| 5 | | | | | |
| (5 rows) |
| |
| -- these should NOT give the same answers as above |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1) where (x2 is not null); |
| x1 | x2 | y1 | y2 | xx1 | xx2 |
| ----+----+----+-----+-----+----- |
| 1 | 11 | 1 | 111 | 1 | 11 |
| 2 | 22 | 2 | 222 | 2 | 22 |
| 4 | 44 | 4 | | 4 | 44 |
| (3 rows) |
| |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1) where (y2 is not null); |
| x1 | x2 | y1 | y2 | xx1 | xx2 |
| ----+----+----+-----+-----+----- |
| 1 | 11 | 1 | 111 | 1 | 11 |
| 2 | 22 | 2 | 222 | 2 | 22 |
| 3 | | 3 | 333 | 3 | |
| (3 rows) |
| |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1) where (xx2 is not null); |
| x1 | x2 | y1 | y2 | xx1 | xx2 |
| ----+----+----+-----+-----+----- |
| 1 | 11 | 1 | 111 | 1 | 11 |
| 2 | 22 | 2 | 222 | 2 | 22 |
| 4 | 44 | 4 | | 4 | 44 |
| (3 rows) |
| |
| -- |
| -- regression test: check for bug with propagation of implied equality |
| -- to outside an IN |
| -- |
| create table foo (unique1 int, unique2 int) distributed replicated; |
| insert into foo values (1, 2), (2, 42); |
| select count(*) from foo a where unique1 in |
| (select unique1 from foo b join foo c using (unique1) |
| where b.unique2 = 42); |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| drop table if exists foo; |
| -- Both DELETE and UPDATE allow the specification of additional tables |
| -- to "join" against to determine which rows should be modified. |
| CREATE TEMP TABLE t1 (a int, b int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE TEMP TABLE t2 (a int, b int) distributed replicated; |
| CREATE TEMP TABLE t3 (x int, y int) distributed replicated; |
| CREATE TEMP TABLE t4 (x int, y int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO t1 VALUES (5, 10); |
| INSERT INTO t1 VALUES (15, 20); |
| INSERT INTO t1 VALUES (100, 100); |
| INSERT INTO t1 VALUES (200, 1000); |
| INSERT INTO t2 VALUES (200, 2000); |
| INSERT INTO t3 VALUES (5, 20); |
| INSERT INTO t3 VALUES (6, 7); |
| INSERT INTO t3 VALUES (7, 8); |
| INSERT INTO t3 VALUES (500, 100); |
| INSERT INTO t4 SELECT * FROM t3; |
| DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a; |
| SELECT * FROM t3; |
| x | y |
| -----+----- |
| 6 | 7 |
| 7 | 8 |
| 500 | 100 |
| (3 rows) |
| |
| DELETE FROM t4 USING t1 JOIN t2 USING (a) WHERE t4.x > t1.a; |
| SELECT * FROM t4; |
| x | y |
| ---+---- |
| 5 | 20 |
| 6 | 7 |
| 7 | 8 |
| (3 rows) |
| |
| DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y; |
| SELECT * FROM t3; |
| x | y |
| ---+--- |
| (0 rows) |
| |
| -- |
| -- regression test for 8.1 merge right join bug |
| -- |
| CREATE TEMP TABLE tt1 ( tt1_id int4, joincol int4 ) distributed replicated; |
| INSERT INTO tt1 VALUES (1, 11); |
| INSERT INTO tt1 VALUES (2, NULL); |
| CREATE TEMP TABLE tt2 ( tt2_id int4, joincol int4 ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'tt2_id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO tt2 VALUES (21, 11); |
| INSERT INTO tt2 VALUES (22, 11); |
| set enable_hashjoin to off; |
| set enable_nestloop to off; |
| -- these should give the same results |
| select tt1.*, tt2.* from tt1 left join tt2 on tt1.joincol = tt2.joincol; |
| tt1_id | joincol | tt2_id | joincol |
| --------+---------+--------+--------- |
| 1 | 11 | 21 | 11 |
| 1 | 11 | 22 | 11 |
| 2 | | | |
| (3 rows) |
| |
| select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol; |
| tt1_id | joincol | tt2_id | joincol |
| --------+---------+--------+--------- |
| 1 | 11 | 21 | 11 |
| 1 | 11 | 22 | 11 |
| 2 | | | |
| (3 rows) |
| |
| reset enable_hashjoin; |
| reset enable_nestloop; |
| -- |
| -- regression test for 8.2 bug with improper re-ordering of left joins |
| -- |
| create temp table tt3(f1 int, f2 text) distributed replicated; |
| insert into tt3 select x, repeat('xyzzy', 100) from generate_series(1,10000) x; |
| create index tt3i on tt3(f1); |
| analyze tt3; |
| create temp table tt4(f1 int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into tt4 values (0),(1),(9999); |
| analyze tt4; |
| SELECT a.f1 |
| FROM tt4 a |
| LEFT JOIN ( |
| SELECT b.f1 |
| FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1) |
| WHERE c.f1 IS NULL |
| ) AS d ON (a.f1 = d.f1) |
| WHERE d.f1 IS NULL; |
| f1 |
| ------ |
| 0 |
| 1 |
| 9999 |
| (3 rows) |
| |
| -- |
| -- regression test for problems of the sort depicted in bug #3494 |
| -- |
| create temp table tt5(f1 int, f2 int) distributed replicated; |
| create temp table tt6(f1 int, f2 int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into tt5 values(1, 10); |
| insert into tt5 values(1, 11); |
| insert into tt6 values(1, 9); |
| insert into tt6 values(1, 2); |
| insert into tt6 values(2, 9); |
| select * from tt5,tt6 where tt5.f1 = tt6.f1 and tt5.f1 = tt5.f2 - tt6.f2; |
| f1 | f2 | f1 | f2 |
| ----+----+----+---- |
| 1 | 10 | 1 | 9 |
| (1 row) |
| |
| -- |
| -- regression test for problems of the sort depicted in bug #3588 |
| -- |
| create temp table xx (pkxx int) distributed replicated; |
| create temp table yy (pkyy int, pkxx int) distributed replicated; |
| insert into xx values (1); |
| insert into xx values (2); |
| insert into xx values (3); |
| insert into yy values (101, 1); |
| insert into yy values (201, 2); |
| insert into yy values (301, NULL); |
| select yy.pkyy as yy_pkyy, yy.pkxx as yy_pkxx, yya.pkyy as yya_pkyy, |
| xxa.pkxx as xxa_pkxx, xxb.pkxx as xxb_pkxx |
| from yy |
| left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy |
| left join xx xxa on yya.pkxx = xxa.pkxx |
| left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx; |
| yy_pkyy | yy_pkxx | yya_pkyy | xxa_pkxx | xxb_pkxx |
| ---------+---------+----------+----------+---------- |
| 101 | 1 | 101 | 1 | 1 |
| 201 | 2 | | | 1 |
| 301 | | | | 1 |
| (3 rows) |
| |
| -- |
| -- regression test for improper pushing of constants across outer-join clauses |
| -- (as seen in early 8.2.x releases) |
| -- |
| create temp table zt1 (f1 int primary key); |
| create temp table zt2 (f2 int primary key); |
| create temp table zt3 (f3 int primary key) distributed replicated; |
| insert into zt1 values(53); |
| insert into zt2 values(53); |
| select * from |
| zt2 left join zt3 on (f2 = f3) |
| left join zt1 on (f3 = f1) |
| where f2 = 53; |
| f2 | f3 | f1 |
| ----+----+---- |
| 53 | | |
| (1 row) |
| |
| create temp view zv1 as select *,'dummy'::text AS junk from zt1; |
| select * from |
| zt2 left join zt3 on (f2 = f3) |
| left join zv1 on (f3 = f1) |
| where f2 = 53; |
| f2 | f3 | f1 | junk |
| ----+----+----+------ |
| 53 | | | |
| (1 row) |
| |
| -- |
| -- regression test for nest loop join of rpt and entry |
| -- |
| create temp table t_5628 (c1 int, c2 int) distributed replicated; |
| insert into t_5628 values (1,1), (2,2); |
| set enable_indexscan to off; |
| set enable_bitmapscan to off; |
| explain (costs off) select max(c1) from pg_class left join t_5628 on true; |
| QUERY PLAN |
| ------------------------------------------------------------ |
| Aggregate |
| -> Nested Loop Left Join |
| -> Seq Scan on pg_class |
| -> Materialize |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_5628 |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| select max(c1) from pg_class left join t_5628 on true; |
| max |
| ----- |
| 2 |
| (1 row) |
| |
| -- |
| -- Writeable CTE on replicated table join with other tables. |
| -- See issue https://github.com/greenplum-db/gpdb/issues/15860 |
| -- |
| select gp_debug_set_create_table_default_numsegments(2); |
| gp_debug_set_create_table_default_numsegments |
| ----------------------------------------------- |
| 2 |
| (1 row) |
| |
| create table rpt_issue_15860_2_segments(c1 int, c2 int) distributed replicated; |
| create table hash_issue_15860_2_segments(c1 int, c2 int) distributed by (c1); |
| select gp_debug_reset_create_table_default_numsegments(); |
| gp_debug_reset_create_table_default_numsegments |
| ------------------------------------------------- |
| |
| (1 row) |
| |
| create table rpt_issue_15860 (c1 int, c2 int) distributed replicated; |
| create table rpt2_issue_15860 (c1 int, c2 int) distributed replicated; |
| create table hash_issue_15860(c1 int, c2 int) distributed by (c1); |
| create table strewn_issue_15860(c1 int, c2 int) distributed randomly; |
| insert into rpt2_issue_15860 values (1, 2), (2, 3); |
| insert into rpt_issue_15860_2_segments values (1, 2), (2, 3); |
| analyze rpt_issue_15860; |
| analyze rpt2_issue_15860; |
| analyze hash_issue_15860; |
| analyze strewn_issue_15860; |
| analyze rpt_issue_15860_2_segments; |
| -- Replicated join SegmentGeneral. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join rpt2_issue_15860 using(c1); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Explicit Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (rpt2_issue_15860.c1 = rpt_issue_15860.c1) |
| -> Seq Scan on rpt2_issue_15860 |
| -> Hash |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- Replicated join SegmentGeneral, Replicated is not ok to replicate. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 left join rpt2_issue_15860 using(c1); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Right Join |
| Hash Cond: (rpt2_issue_15860.c1 = rpt_issue_15860.c1) |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on rpt2_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| -- Replicated join SegmentGeneral, SegmentGeneral is not ok to replicate. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 right join rpt2_issue_15860 using(c1); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Left Join |
| Hash Cond: (rpt2_issue_15860.c1 = cte1.c1) |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on rpt2_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Subquery Scan on cte1 |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| -- Replicated join SegmentGeneral, both are not ok to replicate. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 full join rpt2_issue_15860 using(c1); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Full Join |
| Hash Cond: (rpt2_issue_15860.c1 = rpt_issue_15860.c1) |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on rpt2_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| -- Replicated join SegmentGeneral, num segments are not matched. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join rpt_issue_15860_2_segments using(c1); |
| QUERY PLAN |
| ------------------------------------------------------------------- |
| Hash Join |
| Hash Cond: (rpt_issue_15860_2_segments.c1 = rpt_issue_15860.c1) |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on rpt_issue_15860_2_segments |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| -- Replicated join General. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join generate_series(1, 5) i on i= cte1.c1 ; |
| QUERY PLAN |
| --------------------------------------------------- |
| Explicit Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (i.i = rpt_issue_15860.c1) |
| -> Function Scan on generate_series i |
| -> Hash |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- Replicated join General, Replicated is not ok to replicate. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 left join generate_series(1, 5) i on i= cte1.c1 ; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Right Join |
| Hash Cond: (i.i = rpt_issue_15860.c1) |
| -> Function Scan on generate_series i |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice1; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- Replicated join General, General is not ok to replicate. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 right join generate_series(1, 5) i on i= cte1.c1 ; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Left Join |
| Hash Cond: (i.i = rpt_issue_15860.c1) |
| -> Function Scan on generate_series i |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice1; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- Replicated join General, both are not not ok to replicate. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 full join generate_series(1, 5) i on i= cte1.c1 ; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Full Join |
| Hash Cond: (i.i = rpt_issue_15860.c1) |
| -> Function Scan on generate_series i |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice1; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- Replicate join SingleQE. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join (select count(*) as c from hash_issue_15860) a on a.c = cte1.c1; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Join |
| Hash Cond: ((count(*)) = rpt_issue_15860.c1) |
| -> Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 left join (select count(*) as c from hash_issue_15860) a on a.c = cte1.c1; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Right Join |
| Hash Cond: ((count(*)) = rpt_issue_15860.c1) |
| -> Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 right join (select count(*) as c from hash_issue_15860) a on a.c = cte1.c1; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Left Join |
| Hash Cond: ((count(*)) = rpt_issue_15860.c1) |
| -> Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 full join (select count(*) as c from hash_issue_15860) a on a.c = cte1.c1; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Full Join |
| Hash Cond: ((count(*)) = rpt_issue_15860.c1) |
| -> Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| -- When external_fts is on |
| -- Seq scan on gp_segment_configuration would be replaced by |
| -- Function Scan on gp_get_segment_configuration |
| -- Inconsistence between CIs will cause such cases to fail. |
| -- Use other catalog to test Entry. |
| -- Replicate join Entry. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join pg_class c on c.oid = cte1.c1; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Join |
| Hash Cond: (c.oid = (rpt_issue_15860.c1)::oid) |
| -> Seq Scan on pg_class c |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice1; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- |
| -- Begin of Replicated join Partitioned. |
| -- |
| -- Replicated join Hashed. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join hash_issue_15860 using(c1); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (hash_issue_15860.c1 = rpt_issue_15860.c1) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 right join hash_issue_15860 using(c1); |
| QUERY PLAN |
| ---------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Left Join |
| Hash Cond: (hash_issue_15860.c1 = cte1.c1) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Subquery Scan on cte1 |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| -- Replicated join Hashed, Replicated is not ok to replicate |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 left join hash_issue_15860 using(c1); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Right Join |
| Hash Cond: (hash_issue_15860.c1 = rpt_issue_15860.c1) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 full join hash_issue_15860 using(c1); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Hash Full Join |
| Hash Cond: (hash_issue_15860.c1 = rpt_issue_15860.c1) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on hash_issue_15860 |
| -> Hash |
| -> Explicit Gather Motion 3:1 (slice2; segments: 3) |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| -- Replicated join Hashed, num segments are not match. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join hash_issue_15860_2_segments using(c1); |
| QUERY PLAN |
| -------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (hash_issue_15860_2_segments.c1 = rpt_issue_15860.c1) |
| -> Redistribute Motion 2:3 (slice2; segments: 2) |
| Hash Key: hash_issue_15860_2_segments.c1 |
| -> Seq Scan on hash_issue_15860_2_segments |
| -> Hash |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| -- Replicated join Strewn = Strewn. |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join strewn_issue_15860 using(c1); |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (strewn_issue_15860.c1 = rpt_issue_15860.c1) |
| -> Seq Scan on strewn_issue_15860 |
| -> Hash |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- Replicated join HashedOJ = HashedOJ |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select * from cte1 join (select * from hash_issue_15860 a full join hash_issue_15860 b using(c1)) c using(c1); |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: ((COALESCE(a.c1, b.c1)) = rpt_issue_15860.c1) |
| -> Hash Full Join |
| Hash Cond: (a.c1 = b.c1) |
| -> Seq Scan on hash_issue_15860 a |
| -> Hash |
| -> Seq Scan on hash_issue_15860 b |
| -> Hash |
| -> Insert on rpt_issue_15860 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (12 rows) |
| |
| -- |
| -- End of Replicated join Partitioned. |
| -- |
| -- CBDB_FIXME: How to derive a plan? |
| -- Replicates join OuterQuery |
| explain(costs off) with cte1 as (insert into rpt_issue_15860 values (1, 2) returning *) select ( select foo.c1 from (select * from strewn_issue_15860) foo join cte1 using(c2) where foo.c1 = hash_issue_15860.c1) from hash_issue_15860; |
| ERROR: could not devise a query plan for the given query (pathnode.c:281) |
| -- Test case for Github Issue 17460 |
| create table t_17460 (a int ) distributed replicated; |
| create table foo_17460 (a int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into t_17460 values(1); |
| insert into foo_17460 select i from generate_series(1, 10000) i; |
| analyze t_17460; |
| analyze foo_17460; |
| -- update|delete on replicated table should not use unique rowid skill |
| -- to plan semi join. |
| explain (costs off) delete from t_17460 where a in (select a from foo_17460); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Delete on t_17460 |
| -> Hash Semi Join |
| Hash Cond: (t_17460.a = foo_17460.a) |
| -> Seq Scan on t_17460 |
| -> Hash |
| -> Broadcast Motion 3:3 (slice1; segments: 3) |
| -> Seq Scan on foo_17460 |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| delete from t_17460 where a in (select a from foo_17460); |
| drop schema rpt_joins cascade; |
| NOTICE: drop cascades to 13 other objects |
| DETAIL: drop cascades to table j1_tbl |
| drop cascades to table j2_tbl |
| drop cascades to table rpt_joins.t1 |
| drop cascades to table rpt_joins.t2 |
| drop cascades to table rpt_joins.t3 |
| drop cascades to table rpt_issue_15860_2_segments |
| drop cascades to table hash_issue_15860_2_segments |
| drop cascades to table rpt_issue_15860 |
| drop cascades to table rpt2_issue_15860 |
| drop cascades to table hash_issue_15860 |
| drop cascades to table strewn_issue_15860 |
| drop cascades to table t_17460 |
| drop cascades to table foo_17460 |
