| -- |
| -- Tests for joins between replicated tables |
| -- |
| -- start_ignore |
| create extension if not exists gp_debug_numsegments; |
| -- end_ignore |
| 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 |
| ); |
| |
| 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; |
| |
| -- ambiguous column |
| SELECT '' AS "xxx", i, k, t |
| FROM J1_TBL CROSS JOIN J2_TBL; |
| |
| -- resolve previous ambiguity by specifying the table name |
| SELECT '' AS "xxx", t1.i, k, t |
| FROM J1_TBL t1 CROSS JOIN J2_TBL t2; |
| |
| SELECT '' AS "xxx", ii, tt, kk |
| FROM (J1_TBL CROSS JOIN J2_TBL) |
| AS tx (ii, jj, tt, ii2, kk); |
| |
| 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); |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b; |
| |
| |
| -- |
| -- |
| -- 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); |
| |
| -- Same as above, slightly different syntax |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL USING (i); |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) |
| ORDER BY a, d; |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) |
| ORDER BY b, t1.a; |
| |
| |
| -- |
| -- NATURAL JOIN |
| -- Inner equi-join on all columns with the same name |
| -- |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL NATURAL JOIN J2_TBL; |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d); |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a); |
| |
| -- 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); |
| |
| |
| -- |
| -- Inner joins (equi-joins) |
| -- |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i); |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k); |
| |
| |
| -- |
| -- Non-equi-joins |
| -- |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k); |
| |
| |
| -- |
| -- 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; |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL LEFT JOIN J2_TBL USING (i) |
| ORDER BY i, k, t; |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL RIGHT JOIN J2_TBL USING (i); |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) |
| ORDER BY i, k, t; |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL FULL JOIN J2_TBL USING (i) |
| ORDER BY i, k, t; |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); |
| |
| SELECT '' AS "xxx", * |
| FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); |
| |
| -- |
| -- Multiway full join |
| -- |
| |
| CREATE TABLE t1 (name TEXT, n INTEGER); |
| CREATE TABLE t2 (name TEXT, n INTEGER) distributed replicated; |
| CREATE TABLE t3 (name TEXT, n INTEGER); |
| |
| INSERT INTO t1 VALUES ( 'bb', 11 ); |
| INSERT INTO t2 VALUES ( 'bb', 12 ); |
| INSERT INTO t2 VALUES ( 'cc', 22 ); |
| INSERT INTO t2 VALUES ( 'ee', 42 ); |
| INSERT INTO t3 VALUES ( 'bb', 13 ); |
| INSERT INTO t3 VALUES ( 'cc', 23 ); |
| INSERT INTO t3 VALUES ( 'dd', 33 ); |
| |
| SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); |
| |
| -- |
| -- 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); |
| |
| SELECT * FROM |
| (SELECT * FROM t2) as s2 |
| LEFT JOIN |
| (SELECT * FROM t3) s3 |
| USING (name); |
| |
| SELECT * FROM |
| (SELECT * FROM t2) as s2 |
| FULL JOIN |
| (SELECT * FROM t3) s3 |
| USING (name); |
| |
| -- 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| -- 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); |
| 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; |
| select * from y; |
| |
| select * from x left join y on (x1 = y1 and x2 is not null); |
| select * from x left join y on (x1 = y1 and y2 is not null); |
| |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1); |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1 and x2 is not null); |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1 and y2 is not null); |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1 and xx2 is not null); |
| -- 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); |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1) where (y2 is not null); |
| select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) |
| on (x1 = xx1) where (xx2 is not null); |
| |
| -- |
| -- 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); |
| |
| 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); |
| 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); |
| |
| 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; |
| DELETE FROM t4 USING t1 JOIN t2 USING (a) WHERE t4.x > t1.a; |
| SELECT * FROM t4; |
| DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y; |
| SELECT * FROM t3; |
| |
| -- |
| -- 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 ); |
| 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; |
| |
| select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol; |
| |
| 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); |
| 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; |
| |
| -- |
| -- 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); |
| |
| 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; |
| |
| -- |
| -- 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; |
| |
| -- |
| -- 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; |
| |
| 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; |
| |
| -- |
| -- 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; |
| select max(c1) from pg_class left join t_5628 on true; |
| |
| |
| -- |
| -- 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); |
| 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(); |
| 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); |
| -- 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); |
| -- 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); |
| -- 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); |
| -- 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); |
| |
| -- 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 ; |
| -- 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 ; |
| -- 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 ; |
| -- 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 ; |
| |
| -- 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; |
| 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; |
| 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; |
| 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; |
| |
| -- 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; |
| |
| -- |
| -- 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); |
| 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); |
| -- 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); |
| 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); |
| -- 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); |
| -- 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); |
| -- 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); |
| -- |
| -- 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; |
| -- Test case for Github Issue 17460 |
| create table t_17460 (a int ) distributed replicated; |
| create table foo_17460 (a int); |
| 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); |
| delete from t_17460 where a in (select a from foo_17460); |
| |
| drop schema rpt_joins cascade; |
