Orafce Extension Missing Compatibility Functions

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Suite

Issue/Introduction

The orafce extension provides Oracle Compatibility SQL functions and behavior in Greenplum database. Part of this extension's functionality is to automatically convert data type values in a query to match the data type of the first value referenced.

For example, a query combining the incompatible data types Date + Bigint normally returns:

select (date_value) + (bigint_value) FROM public.test_table;
ERROR:  operator does not exist: date + bigint
LINE 1: select (date_value) + (bigint_value)
                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

But with the orafce extension correctly installed will change to the below:

select (date_value) + (bigint_value) FROM public.test_table;
   ?????
---------------------
 2019-11-30 00:00:00
(1 row)

An Explain Analyze Verbose plan of this query shows the new data type casting due to the orafce extension:

((("timestamp"((date_value)::timestamp without time zone, 0))::timestamp without time zone + ('1 day'::interval * (bigint_value)::double precision)))"

However, only creating the orafce extension will not replicate the data type casting behavior above, and the query will still return with the error - ERROR: operator does not exist.

Environment

Product Version: 6.25

Resolution

Some Oracle Compatibility Functions reside in the oracle schema. To access them, the search path for the database must be set to include the oracle schema name. To see the current search_path value, the command show search_path; can be run.

show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

Adding the oracle schema to the current search path can allow the the orafce extension compatibility functions to behave as expected:

ALTER DATABASE <db_name> SET <search_path> = "$user", public, oracle;
 
\q
psql
 
show search_path;
       search_path       
-------------------------
 "$user", public, oracle
(1 row)

Now when the query is run the data types will behave in the same manner as identified in the explain analyze verbose plan.

explain analyze verbose select (date_value) + (bigint_value) FROM public.test_table;
---
((("timestamp"((date_value)::timestamp without time zone, 0))::timestamp without time zone + ('1 day'::interval * (bigint_value)::double precision)))"