Help Center> Data Warehouse Service> Troubleshooting> Cluster Use> Query Results Are Inconsistent in Oracle and Teradata Compatibility Modes
View PDF

Query Results Are Inconsistent in Oracle and Teradata Compatibility Modes

Symptom

In two sets of the cluster environments of the same data volume, the query results of the same SQL statements are different.

  1. The syntax used can be simplified as follows: 

    create table test (a text, b int);
insert into test values('', 1);
insert into test values(null, 1);
select count(*) from test a, test b where a.a = b.a;

  2. The execution results in the two environments are as follows: 

    Result 1:
select count(*) from test a, test b where a.a = b.a;
 count
-------
     0
(1 row)

Result 2:
tpcds1xcpm=# select count(*) from test a, test b where a.a = b.a;
 count
-------
     1
(1 row)

Possible Causes

Currently, two database compatibility modes are supported: TD and ORA.

Null and empty strings are equal in ORA mode, but not equal in TD mode. Therefore, the preceding scenarios may be caused by different compatibility mode settings of the databases in the two environments.

Run the following statement to check the compatibility modes:

select datname, datcompatibility from pg_database;

Handling Procedure

The problem can be solved only when the compatibility modes of the databases in the two environments are set to the same. The datcompatibility attribute of the database cannot be altered. You can specify the attribute only when you create a database.

Parent topic: Cluster Use