src/test/regress/expected/sublink_optimizer.out - cloudberry - Git at Google

 -- start_ignore
 DROP TABLE IF EXISTS d_xpect_setup;
 NOTICE:  table "d_xpect_setup" does not exist, skipping
 DROP VIEW IF EXISTS v_xpect_triangle_de;
 NOTICE:  view "v_xpect_triangle_de" does not exist, skipping
 -- end_ignore
 CREATE TABLE d_xpect_setup (
     key character varying(20) NOT NULL,
     country character varying(5) NOT NULL,
     key_value character varying(50),
     key_desc character varying(200)
 ) DISTRIBUTED BY (country ,key);
 CREATE VIEW v_xpect_triangle_de AS
     SELECT x.rep_year, y.age, ((x.rep_year - y.age) - t."offset") AS yob, t.triangle FROM (SELECT s.a AS rep_year FROM (SELECT generate_series.generate_series FROM generate_series((SELECT (substr((d_xpect_setup.key_value)::text, 1, 4))::integer AS valid_from FROM d_xpect_setup WHERE (((d_xpect_setup.key)::text = 'data_valid_from'::text) AND ((d_xpect_setup.country)::text = 'DE'::text))), (SELECT (to_char(((SELECT CASE d_xpect_setup.key_value WHEN IS NOT DISTINCT FROM 'NULL'::text THEN ('now'::text)::date ELSE to_date((d_xpect_setup.key_value)::text, 'YYYYMM'::text) END AS to_date FROM d_xpect_setup WHERE (((d_xpect_setup.key)::text = 'launch_date'::text) AND ((d_xpect_setup.country)::text = 'DE'::text))) - (((d_xpect_setup.key_value)::integer)::double precision * '1 mon'::interval)), 'yyyy'::text))::integer AS valid_to FROM d_xpect_setup WHERE (((d_xpect_setup.key)::text = 'data_valid_to'::text) AND ((d_xpect_setup.country)::text = 'DE'::text)))) generate_series(generate_series)) s(a)) x, (SELECT s.a AS age FROM (SELECT generate_series.generate_series FROM generate_series(0, 120) generate_series(generate_series)) s(a)) y, (SELECT 1 AS "offset", 'HT' AS triangle UNION SELECT 0 AS "offset", 'LT') t ORDER BY x.rep_year DESC, y.age DESC;
 SELECT * FROM v_xpect_triangle_de , ( SELECT lpad(s.a ::text, 2, '0'::text) AS all_months FROM generate_series(1, 12) s(a)) b WHERE (v_xpect_triangle_de.rep_year::text || b.all_months)::text>=  ( SELECT d_xpect_setup.key_value AS valid_from FROM d_xpect_setup WHERE d_xpect_setup.key::text = 'data_valid_from'::text AND d_xpect_setup.country::text = 'NL'::text);
  rep_year | age | yob | triangle | all_months
 ----------+-----+-----+----------+------------
 (0 rows)

 --
 -- This bizarre looking query is reduced from a customer's query that used
 -- to cause an assertion failure or crash. The interesting property is that
 -- there are two references to cte_a in the query, inside cte_b, but after
 -- the planner has expanded both references to cte_b, there are now four
 -- references to cte_a, in the half-built plan tree.
 --
 WITH cte_a (col1, col2)
 AS
 (
   VALUES (10, 123), (20, 234)
 )
 ,
 cte_b AS
 (
   SELECT (SELECT col1 FROM cte_a WHERE cte_a.col1 = lp.col1) as match1,
 	 (SELECT col1 FROM cte_a WHERE cte_a.col1 = lp.col2) as match2
   FROM (SELECT 10 as col1, 20 as col2) as lp
 )
 SELECT *
 FROM cte_b as first, cte_b as second;
  match1 | match2 | match1 | match2
 --------+--------+--------+--------
      10 |     20 |     10 |     20
 (1 row)

 --
 -- Test GROUP BY IN exists subquery
 -- More details can be found in https://github.com/greenplum-db/gpdb/issues/11849
 --
 create table group_by_sublink(a int);
 NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 group_by_sublink select i from generate_series(1, 5) i;
 explain (costs off)
 select a from group_by_sublink where exists (select avg(a) from group_by_sublink group by a);
                                       QUERY PLAN
 ---------------------------------------------------------------------------------------
  Result
    Filter: (SubPlan 1)
    ->  Gather Motion 3:1  (slice1; segments: 3)
          ->  Seq Scan on group_by_sublink
    SubPlan 1
      ->  Limit
            ->  Materialize
                  ->  Gather Motion 3:1  (slice2; segments: 3)
                        ->  GroupAggregate
                              Group Key: group_by_sublink_1.a
                              ->  Sort
                                    Sort Key: group_by_sublink_1.a
                                    ->  Seq Scan on group_by_sublink group_by_sublink_1
  Optimizer: Pivotal Optimizer (GPORCA)
 (14 rows)

 select count(*) from group_by_sublink where exists (select avg(a) from group_by_sublink group by a);
  count
 -------
      5
 (1 row)

 -- Below queries will not be affected, WINDOW/DISTINCT/DISTINCT ON/ORDER BY clause will be
 -- throwed,
 explain (costs off)
 select a from group_by_sublink where exists (select a from group_by_sublink order by a desc);
                                QUERY PLAN
 ------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Nested Loop Semi Join
          Join Filter: true
          ->  Seq Scan on group_by_sublink group_by_sublink_1
          ->  Materialize
                ->  Broadcast Motion 1:3  (slice2)
                      ->  Limit
                            ->  Gather Motion 3:1  (slice3; segments: 3)
                                  ->  Seq Scan on group_by_sublink
  Optimizer: Pivotal Optimizer (GPORCA)
 (10 rows)

 select count(*) from group_by_sublink where exists (select a from group_by_sublink order by a desc);
  count
 -------
      5
 (1 row)

 explain (costs off)
 select a from group_by_sublink where exists (select distinct a from group_by_sublink);
                                QUERY PLAN
 ------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Nested Loop Semi Join
          Join Filter: true
          ->  Seq Scan on group_by_sublink group_by_sublink_1
          ->  Materialize
                ->  Broadcast Motion 1:3  (slice2)
                      ->  Limit
                            ->  Gather Motion 3:1  (slice3; segments: 3)
                                  ->  Seq Scan on group_by_sublink
  Optimizer: Pivotal Optimizer (GPORCA)
 (10 rows)

 select count(*) from group_by_sublink where exists (select distinct a from group_by_sublink);
  count
 -------
      5
 (1 row)

 explain (costs off)
 select a from group_by_sublink where exists (select distinct on (a) a from group_by_sublink);
                                QUERY PLAN
 ------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Nested Loop Semi Join
          Join Filter: true
          ->  Seq Scan on group_by_sublink group_by_sublink_1
          ->  Materialize
                ->  Broadcast Motion 1:3  (slice2)
                      ->  Limit
                            ->  Gather Motion 3:1  (slice3; segments: 3)
                                  ->  Seq Scan on group_by_sublink
  Optimizer: Pivotal Optimizer (GPORCA)
 (10 rows)

 select count(*) from group_by_sublink where exists (select distinct on (a) a from group_by_sublink);
  count
 -------
      5
 (1 row)

 explain (costs off)
 select a from group_by_sublink where exists (select sum(a) over (order by a) from group_by_sublink );
                                    QUERY PLAN
 ---------------------------------------------------------------------------------
  Result
    Filter: (SubPlan 1)
    ->  Gather Motion 3:1  (slice1; segments: 3)
          ->  Seq Scan on group_by_sublink
    SubPlan 1
      ->  Limit
            ->  WindowAgg
                  Order By: group_by_sublink_1.a
                  ->  Sort
                        Sort Key: group_by_sublink_1.a
                        ->  Gather Motion 3:1  (slice2; segments: 3)
                              ->  Seq Scan on group_by_sublink group_by_sublink_1
  Optimizer: Pivotal Optimizer (GPORCA)
 (13 rows)

 select count(*) from group_by_sublink where exists (select sum(a) over (order by a) from group_by_sublink );
  count
 -------
      5
 (1 row)
	-- start_ignore
	DROP TABLE IF EXISTS d_xpect_setup;
	NOTICE: table "d_xpect_setup" does not exist, skipping
	DROP VIEW IF EXISTS v_xpect_triangle_de;
	NOTICE: view "v_xpect_triangle_de" does not exist, skipping
	-- end_ignore
	CREATE TABLE d_xpect_setup (
	key character varying(20) NOT NULL,
	country character varying(5) NOT NULL,
	key_value character varying(50),
	key_desc character varying(200)
	) DISTRIBUTED BY (country ,key);
	CREATE VIEW v_xpect_triangle_de AS
	SELECT x.rep_year, y.age, ((x.rep_year - y.age) - t."offset") AS yob, t.triangle FROM (SELECT s.a AS rep_year FROM (SELECT generate_series.generate_series FROM generate_series((SELECT (substr((d_xpect_setup.key_value)::text, 1, 4))::integer AS valid_from FROM d_xpect_setup WHERE (((d_xpect_setup.key)::text = 'data_valid_from'::text) AND ((d_xpect_setup.country)::text = 'DE'::text))), (SELECT (to_char(((SELECT CASE d_xpect_setup.key_value WHEN IS NOT DISTINCT FROM 'NULL'::text THEN ('now'::text)::date ELSE to_date((d_xpect_setup.key_value)::text, 'YYYYMM'::text) END AS to_date FROM d_xpect_setup WHERE (((d_xpect_setup.key)::text = 'launch_date'::text) AND ((d_xpect_setup.country)::text = 'DE'::text))) - (((d_xpect_setup.key_value)::integer)::double precision * '1 mon'::interval)), 'yyyy'::text))::integer AS valid_to FROM d_xpect_setup WHERE (((d_xpect_setup.key)::text = 'data_valid_to'::text) AND ((d_xpect_setup.country)::text = 'DE'::text)))) generate_series(generate_series)) s(a)) x, (SELECT s.a AS age FROM (SELECT generate_series.generate_series FROM generate_series(0, 120) generate_series(generate_series)) s(a)) y, (SELECT 1 AS "offset", 'HT' AS triangle UNION SELECT 0 AS "offset", 'LT') t ORDER BY x.rep_year DESC, y.age DESC;
	SELECT * FROM v_xpect_triangle_de , ( SELECT lpad(s.a ::text, 2, '0'::text) AS all_months FROM generate_series(1, 12) s(a)) b WHERE (v_xpect_triangle_de.rep_year::text \|\| b.all_months)::text>= ( SELECT d_xpect_setup.key_value AS valid_from FROM d_xpect_setup WHERE d_xpect_setup.key::text = 'data_valid_from'::text AND d_xpect_setup.country::text = 'NL'::text);
	rep_year \| age \| yob \| triangle \| all_months
	----------+-----+-----+----------+------------
	(0 rows)

	--
	-- This bizarre looking query is reduced from a customer's query that used
	-- to cause an assertion failure or crash. The interesting property is that
	-- there are two references to cte_a in the query, inside cte_b, but after
	-- the planner has expanded both references to cte_b, there are now four
	-- references to cte_a, in the half-built plan tree.
	--
	WITH cte_a (col1, col2)
	AS
	(
	VALUES (10, 123), (20, 234)
	)
	,
	cte_b AS
	(
	SELECT (SELECT col1 FROM cte_a WHERE cte_a.col1 = lp.col1) as match1,
	(SELECT col1 FROM cte_a WHERE cte_a.col1 = lp.col2) as match2
	FROM (SELECT 10 as col1, 20 as col2) as lp
	)
	SELECT *
	FROM cte_b as first, cte_b as second;
	match1 \| match2 \| match1 \| match2
	--------+--------+--------+--------
	10 \| 20 \| 10 \| 20
	(1 row)

	--
	-- Test GROUP BY IN exists subquery
	-- More details can be found in https://github.com/greenplum-db/gpdb/issues/11849
	--
	create table group_by_sublink(a int);
	NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 group_by_sublink select i from generate_series(1, 5) i;
	explain (costs off)
	select a from group_by_sublink where exists (select avg(a) from group_by_sublink group by a);
	QUERY PLAN
	---------------------------------------------------------------------------------------
	Result
	Filter: (SubPlan 1)
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Seq Scan on group_by_sublink
	SubPlan 1
	-> Limit
	-> Materialize
	-> Gather Motion 3:1 (slice2; segments: 3)
	-> GroupAggregate
	Group Key: group_by_sublink_1.a
	-> Sort
	Sort Key: group_by_sublink_1.a
	-> Seq Scan on group_by_sublink group_by_sublink_1
	Optimizer: Pivotal Optimizer (GPORCA)
	(14 rows)

	select count(*) from group_by_sublink where exists (select avg(a) from group_by_sublink group by a);
	count
	-------
	5
	(1 row)

	-- Below queries will not be affected, WINDOW/DISTINCT/DISTINCT ON/ORDER BY clause will be
	-- throwed,
	explain (costs off)
	select a from group_by_sublink where exists (select a from group_by_sublink order by a desc);
	QUERY PLAN
	------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Nested Loop Semi Join
	Join Filter: true
	-> Seq Scan on group_by_sublink group_by_sublink_1
	-> Materialize
	-> Broadcast Motion 1:3 (slice2)
	-> Limit
	-> Gather Motion 3:1 (slice3; segments: 3)
	-> Seq Scan on group_by_sublink
	Optimizer: Pivotal Optimizer (GPORCA)
	(10 rows)

	select count(*) from group_by_sublink where exists (select a from group_by_sublink order by a desc);
	count
	-------
	5
	(1 row)

	explain (costs off)
	select a from group_by_sublink where exists (select distinct a from group_by_sublink);
	QUERY PLAN
	------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Nested Loop Semi Join
	Join Filter: true
	-> Seq Scan on group_by_sublink group_by_sublink_1
	-> Materialize
	-> Broadcast Motion 1:3 (slice2)
	-> Limit
	-> Gather Motion 3:1 (slice3; segments: 3)
	-> Seq Scan on group_by_sublink
	Optimizer: Pivotal Optimizer (GPORCA)
	(10 rows)

	select count(*) from group_by_sublink where exists (select distinct a from group_by_sublink);
	count
	-------
	5
	(1 row)

	explain (costs off)
	select a from group_by_sublink where exists (select distinct on (a) a from group_by_sublink);
	QUERY PLAN
	------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Nested Loop Semi Join
	Join Filter: true
	-> Seq Scan on group_by_sublink group_by_sublink_1
	-> Materialize
	-> Broadcast Motion 1:3 (slice2)
	-> Limit
	-> Gather Motion 3:1 (slice3; segments: 3)
	-> Seq Scan on group_by_sublink
	Optimizer: Pivotal Optimizer (GPORCA)
	(10 rows)

	select count(*) from group_by_sublink where exists (select distinct on (a) a from group_by_sublink);
	count
	-------
	5
	(1 row)

	explain (costs off)
	select a from group_by_sublink where exists (select sum(a) over (order by a) from group_by_sublink );
	QUERY PLAN
	---------------------------------------------------------------------------------
	Result
	Filter: (SubPlan 1)
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Seq Scan on group_by_sublink
	SubPlan 1
	-> Limit
	-> WindowAgg
	Order By: group_by_sublink_1.a
	-> Sort
	Sort Key: group_by_sublink_1.a
	-> Gather Motion 3:1 (slice2; segments: 3)
	-> Seq Scan on group_by_sublink group_by_sublink_1
	Optimizer: Pivotal Optimizer (GPORCA)
	(13 rows)

	select count(*) from group_by_sublink where exists (select sum(a) over (order by a) from group_by_sublink );
	count
	-------
	5
	(1 row)