ORCA displays a message, "GPDB Expression type: Query Parameter not supported in DXL," when the query contains DATE_PART() function.

[pobbatihari]

Bug Report

ORCA displays a below message, even though it generates plans when the query contains DATE_PART(field, source) and the source is explicitly cast to date. INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL

Greenplum version or build

7X and 6X

Expected behavior

select DATE_PART('year', '20230331'::date);
 date_part
-----------
      2023
(1 row)

explain verbose select DATE_PART('year', '20230331'::date);
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=1 width=1)
   Output: '2023'::double precision
 Optimizer: Pivotal Optimizer (GPORCA)
(3 rows)

Actual behavior

7X: 
set optimizer_trace_fallback to on;
select DATE_PART('year', '20230331'::date);
INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  GPDB Expression type: Query Parameter not supported in DXL
 date_part
-----------
      2023
(1 row)

explain verbose select DATE_PART('year', '20230331'::date);
INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  GPDB Expression type: Query Parameter not supported in DXL
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=1 width=1)
   Output: '2023'::double precision
 Optimizer: Pivotal Optimizer (GPORCA)
(3 rows)

set optimizer to off;
SET
explain verbose select DATE_PART('year', '20230331'::date);
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=8)
   Output: '2023'::double precision
 Optimizer: Postgres query optimizer
 Settings: optimizer = 'off'
(4 rows)

select DATE_PART('year', '20230331'::date);
 date_part
-----------
      2023
(1 row)

[kainwen]

Seems this is fixed by https://github.com/greenplum-db/gpdb/commit/e747b2e1c2a67fd84a8213ccd5c261869c77d772 would you please help confirm? @chrishajas Thanks!

[chrishajas]

Yes, this was fixed with https://github.com/greenplum-db/gpdb/commit/e747b2e1c2a67fd84a8213ccd5c261869c77d772 in 7X and will be fixed with https://github.com/greenplum-db/gpdb/pull/17310 in 6X.