Query Results Are Inconsistent in Oracle, Teradata, and MySQL Compatibility Modes

Symptom

For a service scenario, two cluster environments run the same SQL statement on the same data volume, but get different results.

1. The syntax used can be simplified as follows:

   1 2 3 4

   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:

   1 2 3 4 5

   demo_db1=> SELECT count(*) FROM test a, test b WHERE a.a = b.a; count ------- 0 (1 row)

   Result 2:

   1 2 3 4 5

   demo_db2=> SELECT count(*) FROM test a, test b WHERE a.a = b.a; count ------- 1 (1 row)

Possible Causes

GaussDB(DWS) supports the Oracle, Teradata, and MySQL database compatibility modes.

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

You can query the PG_DATABASE system catalog to check the compatibility mode of the database.

1

SELECT datname, datcompatibility FROM pg_database;

Handling Procedure

The compatibility mode of the database is specified by the DBCOMPATIBILITY parameter when you create a database.

• DBCOMPATIBILITY [ = ] compatibilty_type

  Specifies the compatible database type.

• Value range: ORA, TD, and MySQL, indicating Oracle, Teradata, and MySQL databases, respectively.

  If this parameter is not specified during database creation, the default value ORA is used.

To solve the problems caused by database compatibility, you need to change the compatibility modes of the two databases to be the same. GaussDB(DWS) does not support changing the compatibility mode of an existing database using the ALTER statement. You can specify the compatibility mode only by creating a database.

1
2

CREATE DATABASE td_db DBCOMPATIBILITY ='TD';
CREATE DATABASE

The syntax behaviors of Oracle, Teradata, and MySQL vary according to the compatibility mode of GaussDB(DWS). For details, see section "Syntax Compatibility Differences Between Oracle, Teradata, and MySQL" in the Developer Guide. Syntax Compatibility Differences Among Oracle, Teradata, and MySQL.