Oracle and Greenplum Implementation Differences

Post date: Apr 23, 2013 5:50:54 PM

There are some differences in the implementation of these compatibility functions in the Greenplum Database from the Oracle implementation. If you use validation scripts, the output may not be exactly the same as in Oracle. Some of the differences are as follows:

1.Oracle performs a decimal round off, Greenplum Database does not. 2.00 becomes 2 in Oracle and remains 2.00 in Greenplum Database.

2.The provided Oracle Compatibility functions handle implicit type conversions differently. For example, using the decode function

decode(expression, value, return [,value, return]...

[, default])

Oracle automatically converts expression and each value to the datatype of the first value before comparing. Oracle automatically converts return to the same datatype as the first result.

The Greenplum implementation restricts return and default to be of the same data type. The expression and value can be different types if the data type of value can be converted into the data type of the expression. This is done implicitly. Otherwise, decode fails with an invalid input syntax error. For example:

SELECT decode('M',true,false);

CASE

------

(1 row)

SELECT decode(1,'M',true,false);

ERROR: Invalid input syntax for integer:"M"

LINE 1: SELECT decode(1,'M',true,false);

3. Numbers in bigint format are displayed in scientific notation in Oracle, but not in Greenplum Database. 9223372036854775 displays as 9.2234E+15 in Oracle and remains 9223372036854775 in Greenplum Database.

4. The default date and timestamp format in Oracle is different than the default format in Greenplum Database. If the following code is executed

CREATE TABLE TEST(date1 date, time1 timestamp, time2 timestamp with timezone);

INSERT INTO TEST VALUES ('2001-11-11','2001-12-13 01:51:15','2001-12-13 01:51:15 -08:00');

SELECT DECODE(date1, '2001-11-11', '2001-01-01') FROM TEST;

Greenplum Database returns the row, but Oracle does not return any rows.

Note: The correct syntax in Oracle is

SELECT DECODE(to_char(date1, 'YYYY-MM-DD'), '2001-11-11', '2001-01-01') FROM TEST

which returns the row.