query with date_part in subquery will cause ORCA fallback to planner

Products

• VMware Tanzu Greenplum

Issue/Introduction

In Greenplum we have a function date_part that can capture the element we needed from a date, for example:

iadpprod=# .SELECT date_part('year'::text, 'now'::text::date) ;
 date_part
-----------
      2024

However, if we have a query that contains a subquery running with date_part(), it might not be able to run with ORCA and will fall back to the planner, for example:

set optimizer_trace_fallback = 1;
Call

INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  GPDB Expression type: Query Parameter not supported in DXL
CONTEXT:  SQL function "date_part" during startup
 date_part
-----------
      2024

It has been confirmed that this is a known issue of Greenplum ORCA and will be fixed in the future. 

Environment

Product Version: 6.25

Resolution

Workaround:

The workaround is to rewrite the query, cast the 2nd Parameter from date to other formats like timestamp. 

set optimizer_trace_fallback = 1;
EXPLAIN SELECT * FROM ( SELECT date_part('year'::text, 'now'::text::timestamp) ) sub ;
                   QUERY PLAN
------------------------------------------------
 Result  (cost=0.00..0.00 rows=1 width=8)
   ->  Result  (cost=0.00..0.00 rows=1 width=1)
 Optimizer: Pivotal Optimizer (GPORCA)
(3 rows)

The reason behind this is that the date_part function can accept different inputs and each kind of function refers to a different code. Some are SQL defined functions while others are compiled source code.
Calling the functions which are defined as SQL will triggered the fall back to Planner. 
Calling the functions which are defained as C functions will all Pivotal Optimizer (GPORCA) to create a plan.
To check which functions are C functions (highlighed below), list the funotions with "\df+ date_part":

iadpprod=# \df+ date_part
                                                                                                                                    List of functions
   Schema   |   Name    |... |        Argument data types        |...|                               Source code                                |......
------------+-----------+...-+-----------------------------------+...+--------------------------------------------------------------------------+......
 pg_catalog | date_part |... | text, abstime                     |...| select pg_catalog.date_part($1, cast($2 as timestamp with time zone))    |......
 pg_catalog | date_part |... | text, date                        |...| select pg_catalog.date_part($1, cast($2 as timestamp without time zone)) |......
 pg_catalog | date_part |... | text, interval                    |...| interval_part                                                            |......
 pg_catalog | date_part |... | text, reltime                     |...| select pg_catalog.date_part($1, cast($2 as pg_catalog.interval))         |......
 pg_catalog | date_part |... | text, timestamp without time zone |...| timestamp_part                                                           |......
 pg_catalog | date_part |... | text, timestamp with time zone    |...| timestamptz_part                                                         |......
 pg_catalog | date_part |... | text, time without time zone      |...| time_part                                                                |......
 pg_catalog | date_part |... | text, time with time zone         |...| timetz_part                                                              |......