Help Center/ GaussDB(DWS)/ Developer Guide/ Developer Guide (9.1.0.x)/ Syntax Compatibility Differences Among Oracle, Teradata, and MySQL

Updated on 2025-07-22 GMT+08:00

View PDF

Syntax Compatibility Differences Among Oracle, Teradata, and MySQL

GaussDB(DWS) supports Oracle, Teradata, and MySQL compatibility types, each matching their respective syntax. Syntax behavior changes based on the selected type.

The database compatibility type can be specified during database creation (using the DBCOMPATIBILITY parameter). The following is an example of the syntax. For details, see the CREATE DATABASE syntax.

    
         CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'TD'; --Create a Teradata-compatible database.
CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA'; --Create an Oracle-compatible database.
CREATE DATABASE mysql_compatible_db DBCOMPATIBILITY 'MYSQL'; --Create a MySQL-compatible database.

Query results:

    
         SELECT datname,datcompatibility FROM PG_DATABASE WHERE datname LIKE '%compatible_db';
       datname       | datcompatibility 
---------------------+------------------
 td_compatible_db    | TD
 ora_compatible_db   | ORA
 mysql_compatible_db | MYSQL
(3 rows)

Running SQL Statements

The following explains how to run SQL statements in a Teradata-compatible database. To see how Oracle and MySQL compatibility types behave differently, switch to either ora_compatible_db or mysql_compatible_db. Run these SQL statements, replacing table names with ora_table or mysql_table, to observe the variations described in the preceding table.

     
          CREATE TABLE td_table(a INT,b VARCHAR(5),c date);
INSERT INTO td_table VALUES(1,null,CURRENT_DATE);  
INSERT INTO td_table VALUES(2,'',CURRENT_DATE);

Distinctions Between Empty Strings, NULL Values, and Date Displays

In both Teradata and MySQL compatibility types, empty strings and NULL values are distinct. However, in the Oracle compatibility type, they are considered the same. Additionally, dates convert to timestamps, displaying the year, month, day, hour, minute, and second.

      
           SELECT a, b, b IS NULL AS null, c FROM td_table;

Click to enlarge

      
           SELECT CURRENT_DATE;

      
           SELECT a, b, b IS NULL AS null, c FROM ora_table;

Click to enlarge

      
           SELECT CURRENT_DATE;

      
           SELECT a, b, b IS NULL AS null, c FROM mysql_table;

      
           SELECT CURRENT_DATE;

Processing Empty Strings

Different from the Oracle database, which processes an empty string as a NULL value, Teradata database converts an empty string to 0 by default. Therefore, when an empty string is queried, value 0 is found.

Similarly, in the Teradata compatibility type, the empty string is converted to 0 of the corresponding numeric type by default. In addition, '-', '+', and ' ' are converted to 0 by default in the Teradata compatibility type, but an error is reported for a decimal point string.

      
           SELECT b::int FROM td_table WHERE b = '';

      
           SELECT b::int FROM ora_table WHERE b = '';

      
           SELECT b::int FROM mysql_table WHERE b = '';

Automatic Truncation of Overlong Characters

In the Teradata compatibility type, if td_compatible_truncation is set to on, a long character string will be automatically truncated. If later INSERT statements (not involving foreign tables) insert long strings to columns of char- and varchar-typed columns in the target table, the system will truncate the long strings to ensure no strings exceed the maximum length defined in the target table.
In the Oracle and MySQL compatibility types, an error is reported when an overlong string is inserted.

      
           SHOW td_compatible_truncation;
SET td_compatible_truncation = ON;
INSERT INTO td_table VALUES(3,'12345678',CURRENT_DATE);
SELECT * FROM td_table WHERE a = 3;

The td_compatible_truncation parameter is invalid. If an overlong character is inserted, an error is reported.

      
           SHOW td_compatible_truncation;
SET td_compatible_truncation = ON;
INSERT INTO ora_table VALUES(3,'12345678',CURRENT_DATE);

Click to enlarge

The td_compatible_truncation parameter is invalid. If an overlong character is inserted, an error is reported.

      
           SHOW td_compatible_truncation;
SET td_compatible_truncation = ON;
INSERT INTO mysql_table VALUES(3,'12345678',CURRENT_DATE);

Click to enlarge

Converting VARCHAR + INT Calculation to NUMERIC + NUMERIC Calculation

In the Teradata compatibility type, the VARCHAR + INT calculation is converted to the NUMERIC + NUMERIC calculation.
In the Oracle and MySQL compatibility types, BIGINT + INT calculation is used.

      
           EXPLAIN VERBOSE SELECT b + a FROM td_table WHERE a = 3;

Click to enlarge

      
           INSERT INTO ora_table VALUES(3,'12345',CURRENT_DATE);
EXPLAIN VERBOSE SELECT b + a FROM ora_table WHERE a = 3;

Click to enlarge

      
           INSERT INTO mysql_table VALUES(3,'12345',CURRENT_DATE);
EXPLAIN VERBOSE SELECT b + a FROM mysql_table WHERE a = 3;

Click to enlarge

Concatenating a Null Value

Teradata compatibility type: The strict_text_concat_td option is added for GUC parameter behavior_compat_options. Concatenating the date, time, number, string, and null will return null.
Oracle compatibility type: Concatenating a non-null object with NULL values returns a non-null object. For example, 'abc'||null returns 'abc'.
MySQL compatibility type: Concatenating null types returns null, matching MySQL's behavior. For example, 'abc'||null returns null.

      
           SET behavior_compat_options = 'strict_text_concat_td';
SELECT '2024-02-07 12:12:12'::TIMESTAMP || NULL;
SELECT '12:12:12'::TIME || NULL;
SELECT '12'::TINYINT || NULL;
SELECT 'abc'::CHAR(10) || NULL;

      
           SELECT 'abc'::CHAR(10) || NULL;

      
           SELECT 'abc'::CHAR(10) || NULL;

Concatenating the char(n) Type

In the Teradata compatibility type, the bpchar_text_without_rtrim option is added to GUC parameter behavior_compat_options. When the char(n) type is concatenated, spaces are reserved and padded to the specified length n.
In the Oracle and MySQL compatibility types, spaces are not reserved.

If a has three spaces and converts to char(10), it fills up to 10 characters since its length is under 10. This uses 10 bytes of storage, with each byte holding 8 bits, totaling 80 bits.

      
           SET behavior_compat_options = 'bpchar_text_without_rtrim';
SELECT bit_length('a   '::char(10));

In the Oracle and MySQL compatibility types, no extra space is reserved. The letter a uses just one character and takes up one byte of storage. As a result, the output is 8.

      
           SELECT bit_length('a   '::char(10));

Using the CONCAT Function

In the Teradata and Oracle compatibility types, concat(str1,str2) returns all non-null character strings. In the MySQL compatibility type, it returns null if an input parameter contains null.

      
           SELECT concat(null, 'World!');

      
           SELECT concat(null, 'World!');

Negative Value Processing in the left and right Functions

In Teradata and Oracle compatibility types, the left function removes the last |n| characters, while the right function removes the first |n| characters. In the MySQL compatibility type, an empty string is returned.

      
           SELECT left('abcde', -2);

      
           SELECT right('abcde', -2);

      
           SELECT left('abcde', -2);

      
           SELECT right('abcde', -2);

Empty String Processing in the lpad and rpad Functions

In the Teradata compatibility type, when fill is empty and the input string's length is shorter than length, both lpad and rpad functions output the original string unchanged. If the value of length is negative, an empty string is returned.
In the Oracle compatibility type, these functions also return null.
In the MySQL compatibility type, if fill is an empty string and the string length is less than the specified length, an empty string is returned. If length is a negative number, null is returned.

      
           SELECT lpad('a',7,'');

      
           SELECT lpad('a',-7,'');

      
           SELECT rpad('a',7,'');

      
           SELECT rpad('a',-7,'');

In the Oracle compatibility type, the lpad function returns null. The rpad function handles empty strings similarly; no further details are provided here.

      
           SELECT lpad('a',7,'');

      
           SELECT lpad('a',-7,'');

      
           SELECT lpad('a',7,'');

      
           SELECT lpad('a',-7,'');

Using the SUBSTR Function

In the Teradata and Oracle compatibility types, the substr(str, s[, n]) function gives the first n characters if s is 0. In the MySQL compatibility type, it returns an empty string.

      
           SELECT SUBSTR('Hello, World!', 0, 5);

      
           SELECT SUBSTR('Hello, World!', 0, 5);

Using the SUBSTRING Function

The substring(str [from s] [for n]) function works differently based on its inputs in the Teradata-compatible and Oracle-compatible modes:

If s is set to 0, the first n – 1 characters are returned.
If s is less than 0, the first s + n – 1 characters are returned.
If n is less than 0, an error is reported.

In the MySQL compatibility type:

If s is 0, an empty string is returned.
If s is less than 0, characters starting from the last |s| character are truncated.
If n is less than 0, an empty string is returned.

Example 1: If s is 0 and n is 5, the first four characters of Hello, World! are returned.

      
           SELECT SUBSTRING('Hello, World!' FROM 0 FOR 5);

Example 2: If s is -1 and n is 4, the first two characters of "Hello, World!" are returned.

      
           SELECT SUBSTRING('Hello, World!' FROM -1 FOR 4);

Example 3: If n is -1, an error is reported.

      
           SELECT SUBSTRING('Hello, World!' FROM -1 FOR -1);

Example 1: An empty string is returned when s is 0 and n is 5.

      
           SELECT SUBSTRING('Hello, World!' FROM 0 FOR 5);

Example 2: If s is -1 and n is 4, the last character is returned.

      
           SELECT SUBSTRING('Hello, World!' FROM -1 FOR 4);

Example 3: If n is -1, an empty string is returned.

      
           SELECT SUBSTRING('Hello, World!' FROM -1 FOR -1);

Using the BTRIM function

In the Teradata and Oracle compatibility types, btrim(string[,characters]) removes the longest string that contains only the characters (a space by default) in the characters from a specified position of the string.
In the MySQL compatibility type, it removes the string that is equivalent to characters (a space by default) from a specified position of the string.

      
           SELECT BTRIM('xxHello Worldxx', 'xxz');

      
           SELECT BTRIM('xxHello Worldxx', 'xxz');

Using the Log Function

In the Teradata and Oracle compatibility types, log(x) calculates the base-10 logarithm. For instance, log(100) equals log(10,100).
In the MySQL compatibility type, it calculates the natural logarithm using base e (e ≈ 2.71828). For example, log(100) roughly matches log(2.71828,100).

      
           SELECT log(100);

      
           SELECT log(100);

Using the mod(x,0) Function

In the Teradata and Oracle compatibility types, x is returned if the divisor of mod(x) is 0. However, in the MySQL compatibility type, this results in an error.

      
           SELECT mod(3,0);

      
           SELECT mod(3,0);

Using the TO_CHAR Function

In the Teradata compatibility type, the input parameter's maximum value must be a timestamp, not a date. The function returns a date formatted as YYYY/MM/DD when GUC parameter behavior_compat_options is set to convert_empty_str_to_null_td.
In the Oracle compatibility type, the input parameter's maximum value must also be a timestamp, not a date. The returned value is a timestamp.
In the MySQL compatibility type, the maximum value of the input parameter can be a timestamp or date. The return type is date.

      
           SELECT TO_CHAR(DATE '294276-12-31');

      
           SELECT TO_CHAR(DATE '5874897-12-31');

Click to enlarge

      
           SELECT TO_CHAR(DATE '294276-12-31');

      
           SELECT TO_CHAR(DATE '5874897-12-31');

      
           SELECT TO_CHAR(DATE '294276-12-31');

      
           SELECT TO_CHAR(DATE '5874897-12-31');

to_date, to_timestamp, and to_number Processing of Empty Strings

In the Teradata compatibility type, if GUC parameter behavior_compat_options is set to convert_empty_str_to_null_td, these functions return null.
In the Oracle compatibility type, these functions also return null.
In the MySQL compatibility type, to_date and to_timestamp returns null. If the parameter passed to to_number is an empty string, 0 is returned.

      
           SET behavior_compat_options = 'convert_empty_str_to_null_td'; --This is only valid for the Teradata-compatible databases.
SELECT TO_DATE('');
SELECT TO_TIMESTAMP('');
SELECT TO_NUMBER('');

      
           SELECT TO_DATE('');
SELECT TO_TIMESTAMP('');
SELECT TO_NUMBER('');

Click to enlarge

Using the LAST_DAY and NEXT_DAY Functions

In the Teradata and Oracle compatibility types, these functions return timestamps.
In the MySQL compatibility type, these functions return dates.

      
           SELECT last_day(to_date('2024-02-07', 'YYYY-MM-DD')) AS cal_result;

      
           SELECT next_day(TIMESTAMP '2024-02-07 00:00:00','Sunday')AS cal_result;

      
           SELECT last_day(to_date('2024-02-07', 'YYYY-MM-DD')) AS cal_result;

      
           SELECT next_day(TIMESTAMP '2024-02-07 00:00:00','Sunday')AS cal_result;

Using the ADD_MONTHS Function

In the Teradata and Oracle compatibility types, the ADD_MONTHS function returns the timestamp plus integer months.

The ADD_MONTHS function works differently based on the input parameter in the MySQL compatibility type:

If the input parameter is a date, the function returns a date.
If the input parameter is a timestamp, the function returns a timestamp.
If the input parameter is a timestamptz value, the function returns a timestamptz value.

      
           SELECT add_months('2024-02-07'::date,3);

      
           SELECT add_months('2024-02-07'::date,3);

      
           SELECT add_months('2024-02-07 00:00:00',3);

Operator ^

In the Teradata and Oracle compatibility types, it indicates the exponentiation operation.
In the MySQL compatibility type, it indicates XOR.

      
           SELECT 10^3;

      
           SELECT 10^3;

GREATEST and LEAST Expressions

In the Teradata and Oracle compatibility types, GREATEST and LEAST return the comparison results of all non-null input parameters.
In the MySQL compatibility type, it returns null if an input parameter contains null.

      
           SELECT greatest(1,2,3),least(1,2,3),greatest(1,null,3),least(1,null,3);

Click to enlarge

      
           SELECT greatest(1,2,3),least(1,2,3),greatest(1,null,3),least(1,null,3);

Click to enlarge

CASE and COALESCE Expressions

The Teradata compatibility type is compatible with Teradata's behavior and allows converting numbers to strings or vice versa. When using COALESCE with INT and VARCHAR inputs, both values become VARCHAR.
In the Oracle compatibility type, an error is reported if these expressions are used.
The MySQL compatibility type is compatible with MySQL's behavior and allows converting data types like strings into others. When using COALESCE with DATE, INT, or VARCHAR inputs, they all convert to VARCHAR.

Type resolution for CASE and COALESCE in the Teradata Compatibility Type

If all inputs are of the same type, and it is not unknown, resolve as that type.
If all inputs are of type unknown, resolve as type text.
If inputs are of string type (including unknown which is resolved as type text) and digit type, resolve as the string type. If the inputs are not of the two types, fail.
If the non-unknown inputs are all of the same type category, choose the input type which is a preferred type in that category, if there is one.
Convert all inputs to the selected type. Fail if there is not an implicit conversion from a given input to the selected type.

Example 1: Use type resolution with underspecified types in a union as the first example. Here, the unknown-type literal 'b' will be resolved to type text.

      
           SELECT text 'a' AS "text" UNION SELECT 'b';

Example 2: Use type resolution in a simple union as the second example. The literal 1.2 is of type numeric, and the integer value 1 can be cast implicitly to numeric, so that type is used.

      
           SELECT 1.2 AS "numeric" UNION SELECT 1;

Example 3: Use type resolution in a transposed union as the third example. Here, since type real cannot be implicitly cast to integer, but integer can be implicitly cast to real, the union result type is resolved as real.

      
           SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);

Example 4: In the Teradata compatibility type, if input parameters for COALESCE are of int and varchar types, resolve as type varchar. In the Oracle compatibility type, an error is reported. Show the execution plan of a statement for querying the types int and varchar of input parameters for COALESCE.

      
           EXPLAIN VERBOSE select coalesce(a, b) FROM td_table;

Click to enlarge

In the Teradata compatibility type, if input parameters for COALESCE are of int and varchar types, resolve as type varchar. In the Oracle compatibility type, an error is reported. Show the execution plan of a statement for querying the types int and varchar of input parameters for COALESCE.

      
           EXPLAIN VERBOSE select coalesce(a, b) FROM ora_table;

The MySQL compatibility type is compatible with behavior of MySQL and allows converting data between string and other types. When using COALESCE with inputs of DATE, INT, or VARCHAR types, all values are treated as VARCHAR.

Previous topic: Viewing GaussDB(DWS) System Catalogs

Next topic: GaussDB(DWS) Database Security Management

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot

Compatibility Item	Oracle	Teradata	MySQL
date data type	Converts the date data type to the timestamp data type which stores year, month, day, hour, minute, and second values.	Stores year, month, and date values.	Stores year, month, and date values.
Empty string	Only null is available.	Distinguishes empty strings from null values.	Distinguishes empty strings from null values.
Conversion of an empty string to a number	Converts to null.	Converts to 0.	Converts to 0.
Automatic truncation of overlong characters	Not supported.	Supported (set GUC parameter td_compatible_truncation to ON).	Not supported.
VARCHAR + INT calculation	Converts to BIGINT + INT calculation.	Converts to NUMERIC + NUMERIC calculation.	Converts to BIGINT + INT calculation.
null concatenation	Returns a non-null object after combining a non-null object with null. For example, 'abc'\|\|null returns 'abc'.	The strict_text_concat_td option is added to the GUC parameter behavior_compat_options to be compatible with the Teradata behavior. After the null type is concatenated, null is returned. For example, 'abc'\|\|null returns null.	Compatible with MySQL behavior. After the null type is concatenated, null is returned. For example, 'abc'\|\|null returns null.
Concatenation of the char(n) type	Removes spaces and placeholders on the right when the char(n) type is concatenated. For example, cast('a' as char(3))\|\|'b' returns 'ab'.	After the bpchar_text_without_rtrim option is added to the GUC parameter behavior_compat_options, when the char(n) type is concatenated, spaces are reserved and supplemented to the specified length n. Currently, ignoring spaces at the end of a string for comparison is not supported. If the concatenated string contains spaces at the end, the comparison is space-sensitive. For example, cast('a' as char(3))\|\|'b' returns 'a b'.	Removes spaces and placeholders on the right.
concat(str1,str2)	Returns the concatenation of all non-null strings.	Returns the concatenation of all non-null strings.	If an input parameter is null, null is returned.
left and right processing of negative values	Returns all characters except the first and last \|n\| characters.	Returns all characters except the first and last \|n\| characters.	Returns an empty string.
lpad(string text, length int [, fill text]) rpad(string text, length int [, fill text])	Fills up the string to the specified length by appending the fill characters (a space by default). If the string is already longer than length then it is truncated (on the right). If fill is an empty string or length is a negative number, null is returned.	If fill is an empty string and the string length is less than the specified length, the original string is returned. If length is a negative number, an empty string is returned.	If fill is an empty string and the string length is less than the specified length, an empty string is returned. If length is a negative number, null is returned.
substr(str, s[, n])	If s is set to 0, the first n characters are returned.	If s is set to 0, the first n characters are returned.	If s is set to 0, an empty string is returned.
substring(str, s[, n]) substring(str [from s] [for n])	If s is set to 0, the first n - 1 characters are returned. If s is < 0, the first s + n - 1 characters are returned. If n is < 0, an error is reported.	If s is set to 0, the first n - 1 characters are returned. If s is < 0, the first s + n - 1 characters are returned. If n is < 0, an error is reported.	If s is set to 0, an empty string is returned. If s is < 0, n characters starting from the last \|s\| character are truncated. If n is < 0, an empty string is returned.
trim, ltrim, rtrim, btrim(string[,characters])	Removes the longest string that contains only the characters (a space by default) in the characters from a specified position of the string.	Removes the longest string that contains only the characters (a space by default) in the characters from a specified position of the string.	Removes the string that is equivalent to characters (a space by default) from a specified position of the string.
log(x)	Returns the logarithm with 10 as the base.	Returns the logarithm with 10 as the base.	Returns the natural logarithm.
mod(x, 0)	Returns x if the divisor is 0.	Returns x if the divisor is 0.	Reports an error if the divisor is 0.
to_char(date)	The maximum value of the input parameter can only be the maximum value of the timestamp type. The maximum value of the date type is not supported. The return value is of the timestamp type.	The maximum value of the input parameter can only be the maximum value of the timestamp type. The maximum value of the date type is not supported. The return value is of the date type in YYYY/MM/DD format. (The GUC parameter convert_empty_str_to_null_td is enabled.)	Only the timestamp type and the date type support the maximum input value. The return value is of the date type.
to_date, to_timestamp, and to_number processing of empty strings	Returns null.	Returns null. (The convert_empty_str_to_null_td parameter is enabled.)	to_date and to_timestamp returns null. If the parameter passed to to_number is an empty string, 0 is returned.
Return value types of last_day and next_day	Returns values of the timestamp type.	Returns values of the timestamp type.	Returns values of the date type.
Return value type of add_months	Returns values of the timestamp type.	Returns values of the timestamp type.	If the input parameter is of the date type, the return value is of the date type. If the input parameter is of the timestamp type, the return value is of the timestamp type. If the input parameter is of the timestamptz type, the return value is of the timestamptz type.
CURRENT_TIME CURRENT_TIME(p)	Obtains the time of the current transaction. The return value is of the timetz type.	Obtains the time of the current transaction. The return value is of the timetz type.	Obtains the execution time of the current statement. The return value is of the time type.
CURRENT_TIMESTAMP CURRENT_TIMESTAMP(p)	Obtains the execution time of the current statement. The return value is of the timestamptz type.	Obtains the execution time of the current statement. The return value is of the timestamptz type.	Obtains the execution time of the current statement. The return value is of the timestamp type.
CURDATE	Not supported.	Not supported.	Obtains the execution date of the current statement. The return value is of the date type.
CURTIME(p)	Not supported.	Not supported.	Obtains the execution time of the current statement. The return value is of the time type.
LOCALTIME LOCALTIME(p)	Obtains the time of the current transaction. The return value is of the time type.	Obtains the time of the current transaction. The return value is of the time type.	Obtains the execution time of the current statement. The return value is of the timestamp type.
LOCALTIMESTAMP LOCALTIMESTAMP(p)	Obtains the time of the current transaction. The return value is of the timestamp type.	Obtains the time of the current transaction. The return value is of the timestamp type.	Obtains the execution time of the current statement. The return value is of the timestamp type.
SYSDATE SYSDATE(p)	Obtains the execution time of the current statement. The return value is of the timestamp(0) type.	Obtains the execution time of the current statement. The return value is of the timestamp(0) type.	Obtains the current system time. The return value is of the timestamp(0) type. This function cannot be pushed down. You are advised to use current_date instead.
now()	Obtains the time of the current transaction. The return value is of the timestamptz type.	Obtains the time of the current transaction. The return value is of the timestamptz type.	Obtains the statement execution time. The return value is of the timestamptz type.
Operator ^	Performs exponentiation.	Performs exponentiation.	Performs the XOR operation.
Expressions GREATEST and LEAST	Returns the comparison results of all non-null input parameters.	Returns the comparison results of all non-null input parameters.	If an input parameter is null, null is returned.
Different input parameter types of CASE, COALESCE, IF, and IFNULL expressions	Reports an error.	Is compatible with behavior of Teradata and supports type conversion between digits and strings. For example, if input parameters for COALESCE are of INT and VARCHAR types, the parameters are resolved as VARCHAR type.	Is compatible with behavior of MySQL and supports type conversion between strings and other types. For example, if input parameters for COALESCE are of DATE, INT, and VARCHAR types, the parameters are resolved as VARCHAR type.
Backquote (`)	Not supported.	Not supported.	Distinguishes MySQL reserved words from common characters.