Syntax Compatibility Differences Among Oracle, Teradata, and MySQL

In GaussDB(DWS), DBCOMPATIBILITY can be set to TD, Oracle, or MySQL to be compatible with Teradata, Oracle, or MySQL syntax, respectively. Syntax behavior varies with the three modes.

The database compatibility model can be specified using the DBCOMPATIBILITY parameter when creating a database. For details, refer to the CREATE DATABASE syntax.

   
        CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';

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
Empty string	Only null is available.	An empty string is distinguished from null.	An empty string is distinguished from null.
Conversion of an empty string to a number	null	0	0
Automatic truncation of overlong characters	Not supported	Supported (set GUC parameter td_compatible_truncation to ON)	Not supported
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.
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 and month values.	Stores year and month values.
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 exclusive OR 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 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.