Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ M-compatible Mode/ SQL/ DML
Updated on 2025-04-14 GMT+08:00
View PDF
Share

DML

Table 1 DML syntax compatibility

Description

Syntax

Difference

DELETE supports deleting data from multiple tables.

DELETE
  • During multi-table deletion, if a tuple to be deleted is concurrently modified by other sessions, the latest values of all tuples in the session are used for matching again. If the conditions are still met, the tuple is deleted. During this process, MySQL deletes all target tables in the same way. However, GaussDB only rematches tuples in the target tables that involve concurrent updates, which may cause data inconsistency.
  • The verification rules of target tables and range tables in the multi-table operation syntax are different from those in MySQL. After the GUC compatibility parameter m_format_dev_version is set to 's2', the verification rules become consistent with MySQL.

UPDATE supports updating data from multiple tables.

UPDATE

During multi-table update, if a tuple to be updated is concurrently modified by other sessions, the latest values of all tuples in the session are used for matching again. If the conditions are still met, the tuple is updated. During this process, MySQL updates all target tables consistently. However, GaussDB only rematches tuples of target tables that involve concurrent updates, which may cause data inconsistency.

SELECT INTO syntax

SELECT
  • In GaussDB, you can use SELECT INTO to create a table based on the query result. MySQL does not support this function.
  • In GaussDB, the SELECT INTO syntax does not support the query result that is obtained after the set operation of multiple queries is performed.

REPLACE INTO syntax

REPLACE
Difference between the initial values of the time type. For example:
  • MySQL is not affected by the strict or loose mode. You can insert time 0 into a table. 
    mysql> CREATE TABLE test(f1 TIMESTAMP NOT NULL, f2 DATETIME NOT NULL, f3 DATE NOT NULL);
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO test VALUES(f1, f2, f3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+---------------------+---------------------+------------+
| f1                  | f2                  | f3         |
+---------------------+---------------------+------------+
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 |
+---------------------+---------------------+------------+
1 row in set (0.00 sec)
  • The time 0 can be successfully inserted only when GaussDB is in loose mode. 
    gaussdb=# SET sql_mode = '';
SET
gaussdb=# CREATE TABLE test(f1 TIMESTAMP NOT NULL, f2 DATETIME NOT NULL, f3 DATE NOT NULL);
CREATE TABLE
gaussdb=# REPLACE INTO test VALUES(f1, f2, f3);
REPLACE 0 1
gaussdb=# SELECT * FROM test;
f1          |         f2          |     f3
---------------------+---------------------+------------
0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00
(1 row)

    In strict mode, the following error is reported:

    The date, time, datetime, timestamp, or year is incorrect.

Import data by using LOAD DATA.

LOAD DATA

When LOAD DATA is used to import data, GaussDB differs from MySQL in the following aspects:

  • The execution result of the LOAD DATA syntax is the same as that in MySQL strict mode. The loose mode is not adapted currently.
  • The IGNORE and LOCAL parameters are used only to ignore the conflicting rows when the imported data conflicts with the data in the table and to automatically fill default values for other columns when the number of columns in the file is less than that in the table. Other functions are not supported currently.
  • The [(col_name_or_user_var [, col_name_or_user_var]...)] parameter cannot be used to specify a column repeatedly.
  • The newline character specified by [FIELDS TERMINATED BY 'string'] cannot be the same as the separator specified by [LINES TERMINATED BY'string'].
  • If the data written to a table by running LOAD DATA cannot be converted to the data type of the table, an error is reported.
  • The LOAD DATA SET expression does not support the calculation of a specified column name.
  • LOAD DATA applies only to tables but not views.
  • The default newline character of the file in Windows is different from that in Linux. LOAD DATA cannot identify this scenario and reports an error. You are advised to check the newline character at the end of lines in the file to be imported.
  • In GaussDB, when the GUC parameter m_format_behavior_compat_options is not set, data can be imported only from the server using LOAD DATA, regardless of whether the LOCAL parameter is specified. In MySQL, if the LOCAL parameter is specified, data can be imported from the client; otherwise, it is imported from the server. After you specify the value of this GUC parameter that includes enable_load_data_remote_transmission in GaussDB, the LOCAL parameter behavior of LOAD DATA becomes consistent with that in MySQL.

LIMIT clause differences

DELETE, SELECT, and UPDATE

The LIMIT clauses of each statement in GaussDB are different from those in MySQL.

The maximum parameter value of LIMIT (of the BIG INT type) in GaussDB is 9223372036854775807. If the actual value exceeds the number, an error is reported. In MySQL, the maximum value of LIMIT (of the unsigned LONGLONG type) is 18446744073709551615. If the actual value exceeds the number, an error is reported.

You can set a small value in LIMIT, which is rounded off during execution. The value cannot be a decimal in MySQL.

Difference in using backslashes (\)

INSERT

The usage of backslashes (\) can be determined by parameters in GaussDB and MySQL, but their default usages are different.

In MySQL, the NO_BACKSLASH_ESCAPES parameter is used to determine whether backslashes (\) in character strings and identifiers are parsed as common characters or escape characters. By default, backslashes (\) are parsed as escape characters in character strings and identifiers. If SET sql_mode='NO_BACKSLASH_ESCAPES'; is used, the backslashes (\) cannot be parsed as escape characters in strings and identifiers.

In GaussDB, the standard_conforming_strings parameter is used to determine whether backslashes (\) in character strings and identifiers are parsed as common characters or escape characters. The default value is on, indicating that backslashes (\) are parsed as common text in common character string texts according to the SQL standard. If SET standard_conforming_strings=off; is used, backslashes (\) can be parsed as escape characters in character strings and identifiers.

If the inserted value is less than the number of columns, MySQL reports an error while GaussDB supplements null values.

INSERT

In GaussDB, if the column list is not specified and the inserted value is less than the number of columns, values are assigned based on the column sequence when the table is created by default. If a column has a NOT NULL constraint, an error is reported. If no NOT NULL constraint exists and a default value is specified, the default value is added to the column. If no default value is specified, null is added.

The columns sorted in ORDER BY must be included in the columns of the result set.

SELECT

In GaussDB, when used with the GROUP BY clause, the columns to be sorted in ORDER BY must be included in the columns of the result set retrieved by the SELECT statement. When used with the DISTINCT keyword, the columns to be sorted in ORDER BY must be included in the columns of the result set retrieved by the SELECT statement.

If the foreign key data type is timestamp or datetime, an error is reported for attempts to perform UPDATE or DELETE on a foreign table.

UPDATE/DELETE

If the foreign key data type is timestamp or datetime, an error is reported for attempts to perform UPDATE or DELETE on a foreign table, but such operations are allowed in MySQL.

NATURAL JOIN syntax

SELECT
  • In GaussDB, NATURAL [ [LEFT | RIGHT] OUTER] JOIN allows you not to specify LEFT | RIGHT. If LEFT | RIGHT is not specified, NATURAL OUTER JOIN is NATURAL JOIN. You can use JOIN consecutively.
  • In GaussDB, join sequence is strictly from left to right. MySQL may adjust the sequence.
  • In GaussDB and MySQL, columns involving join in the left or right table cannot be ambiguous during natural join or using. (Generally, ambiguity is caused by duplicate names of columns in the left or right temporary table.) The join sequence differs in two databases, which may lead to different behaviors.
    • Behavior in GaussDB: 
      m_regression=# CREATE TABLE t1(a int,b int);
CREATE TABLE
m_regression=# CREATE TABLE t2(a int,b int);
CREATE TABLE
m_regression=# CREATE TABLE t3(a int,b int);
CREATE TABLE
m_regression=# SELECT * FROM t1 JOIN t2;
 a | b | a | b 
---+---+---+---
(0 rows)
m_regression=# SELECT * FROM t1 JOIN t2 natural join t3; -- Failed. Duplicate contents exist in columns a and b of the temporary table obtained by t1 join t2. Therefore, there is ambiguity in nature join.
ERROR:  common column name "a" appears more than once in left table
    • Behavior in MySQL: 
      mysql> SELECT * FROM t1 JOIN t2 NATURAL JOIN t3;
Empty set (0.00 sec)
mysql> SELECT * FROM (t1 join t2) NATURAL JOIN t3;
ERROR 1052 (23000): Column 'a' in from clause is ambiguous

JOIN syntax

SELECT

Commas (,) cannot be used as a way of JOIN in GaussDB, but can be used in MySQL.

GaussDB does not support USE INDEX FOR JOIN.

The execution plans generated in the multi-table join STRAIGHT_JOIN in GaussDB may be different from those in MySQL.

Display column names by using SELECT.

SELECT
  • To ensure that the column names displayed by using the SELECT statement in GaussDB are the same as those in MySQL, you need to enable the parameter to display the column name output. 
    SET m_format_behavior_compat_options = 'select_column_name'
  • If this configuration item is not set in GaussDB:
    • SELECT System function: The output is the system function name.
    • SELECT Expression: The output is ?column?.
    • SELECT Boolean value: The output is a Boolean value.
  • If this configuration item is set in GaussDB, the column name is displayed as all functions or expressions.
    • The MySQL client ignores common comments, but the gsql and PyMySQL clients do not.
    • The MySQL server converts comments starting with /*! into executable statements. An M-compatible database does not support such comments and processes them as common comments.
    • If an expression contains two hyphens (--) that is not followed by a space, an M-compatible database cannot identify the two hyphens as a comment, whereas the MySQL server identifies it as two hyphens (--).
    • If the displayed column name string contains escape characters, the escaped characters are displayed only when m_format_behavior_compat_options is set to a value that includes enable_escape_string. Otherwise, the escape characters are displayed. For example, in an M-compatible database, "SELECT"abc\tdef";" is displayed as abc\tdef when the preceding option is disabled. 
      m_db=# SET m_format_behavior_compat_options='select_column_name,enable_escape_string';
SET
m_db=# SELECT "abc\tdef";
 abc     def 
-------------
 abc     def
(1 row)

m_db=# SET m_format_behavior_compat_options='select_column_name';
SET
m_db=# SELECT "abc\tdef";
 abc\tdef 
----------
 abc\tdef
(1 row)
    • If a column name contains more than 63 characters, the extra characters will be truncated.
    • If the last part of an expression is a comment, the last comment and the space connected to the comment are not displayed. 
      m_db=# SELECT 123        /* 456 */;
 123 
-----
 123
(1 row)
    • If an expression is a Boolean value, the command output is TRUE or FALSE regardless of the input case. 
      m_db=# SELECT true;
 TRUE 
------
 t
(1 row)
    • If an expression is null, the command output is NULL regardless of the input case. 
      m_db=# SELECT null;
 NULL 
------

(1 row)
    • If an expression contains a hyphen (-), all inputs are output as column names. 
      m_db=# SELECT (+-+1);
 (+-+1) 
--------
     -1
(1 row)

m_db=# SELECT -true;
 -true 
-------
    -1
(1 row)

m_db=# SELECT -null;
 -null 
-------

(1 row)
  • When pymysql is used to execute the SELECT statement, the prefix of the queried character string does not use ASCII characters, and the database is not encoded in UTF-8, the displayed column names are different from those in MySQL.

SELECT export file (into outfile)

SELECT ... INTO OUFILE ...

In the file exported by using the SELECT INTO OUTFILE syntax, the display precision of values of the FLOAT, DOUBLE, and REAL types in GaussDB is different from that in MySQL. The syntax does not affect the import using COPY the values after import.

Specify schema names and table names by using SELECT/UPDATE/INSERT/REPLACE.

SELECT/UPDATE/INSERT/REPLACE
  • When the SELECT statement is used to the projection column, MySQL supports the three-segment format of schema name.table alias.column name, but GaussDB does not. 
    m_db=# CREATE SCHEMA test;
CREATE SCHEMA
m_db=# CREATE TABLE test.t1(a int);
CREATE TABLE
m_db=# SELECT test.alias1.a FROM t1 alias1;
ERROR:  invalid reference to FROM-clause entry for table "alias1"
LINE 1: SELECT test.alias1.a FROM t1 alias1;
               ^
HINT:  There is an entry for table "alias1", but it cannot be referenced from this part of the query.
CONTEXT:  referenced column: a
  • The three-segment format for UPDATE/REPLACE SET is database.table.column in MySQL, and is table.column.field in GaussDB, where field indicates the attribute in the specified composite type.
  • For INSERT ... SET, MySQL supports column, table.column, and database.table.column. GaussDB supports only column and does not support table.column and database.table.column.
  • For INSERT... SET, you can reference column names and expressions that contain column names on the right of the SET clause in MySQL. GaussDB does not support this operation.
    • Behavior in GaussDB: 
      m_db=# CREATE TABLE t2 (a int default 3, b int default 5);
CREATE TABLE

m_db=# INSERT INTO t2 SET a = b + 1;
ERROR:  Column "b" does not exist.
LINE 1: INSERT INTO t2 SET a = b + 1;
                               ^
HINT:  There is a column named "b" in table "t2", but it cannot be referenced from this part of the query.

m_db=# INSERT INTO t2 SET a = b + 1, b = 0;
ERROR:  Column "b" does not exist.
LINE 1: INSERT INTO t2 SET a = b + 1, b = 0;
                               ^
HINT:  There is a column named "b" in table "t2", but it cannot be referenced from this part of the query.

m_db=# INSERT INTO t2 SET b = 0, a = b + 1;
ERROR:  Column "b" does not exist.
LINE 1: INSERT INTO t2 SET b = 0, a = b + 1;
                                      ^
HINT:  There is a column named "b" in table "t2", but it cannot be referenced from this part of the query.

m_db=# INSERT INTO t2 SET a = a + 1;
ERROR:  Column "a" does not exist.
LINE 1: INSERT INTO t2 SET a = a + 1;
                               ^
HINT:  There is a column named "a" in table "t2", but it cannot be referenced from this part of the query.

m_db=# DROP TABLE t2;
DROP TABLE
    • Behavior in MySQL: 
      mysql> CREATE TABLE t2 (a int default 3, b int default 5);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t2 SET a = b + 1;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t2;
+------+------+
| a    | b    |
+------+------+
|    6 |    5 |
+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO t2 SET a = b + 1, b = 0;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| a    | b    |
+------+------+
|    6 |    5 |
|    6 |    0 |
+------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO t2 SET b = 0, a = b + 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| a    | b    |
+------+------+
|    6 |    5 |
|    6 |    0 |
|    1 |    0 |
+------+------+
3 rows in set (0.00 sec)

mysql> INSERT INTO t2 SET a = a + 1;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t2;
+------+------+
| a    | b    |
+------+------+
|    6 |    5 |
|    6 |    0 |
|    1 |    0 |
|    4 |    5 |
+------+------+
4 rows in set (0.00 sec)

mysql> DROP TABLE t2;
Query OK, 4 rows affected (0.40 sec)

The execution sequence of UPDATE SET is different from that of MySQL.

UPDATE ... SET

In MySQL, UPDATE SET is performed in sequence. The results of UPDATE at the front affect subsequent results of UPDATE, and the same column can be set for multiple times. In GaussDB, all related data is obtained first, and then UPDATE is performed on the data at a time. The same column cannot be updated for multiple times. After the GUC compatibility parameter m_format_dev_version is set to 's2', the behavior can be the same as that in MySQL only in the single-table scenario. That is, the same column can be updated for multiple times and the updated result is referenced.

IGNORE feature

UPDATE/DELETE/INSERT

The execution process in MySQL is different from that in GaussDB. Therefore, the number and information of generated warnings may be different.

SHOW COLUMNS syntax

SHOW
  • User permission verification is different from that of MySQL.
    • In GaussDB, you need the USAGE permission on the schema of a specified table and table-level or column-level permissions on the specified table. Only information about columns with the SELECT, INSERT, UPDATE, REFERENCES, and COMMENT permissions is displayed.
    • In MySQL, you need table-level or column-level permissions on a specified table. Only information about columns with the SELECT, INSERT, UPDATE, REFERENCES, and COMMENT permissions is displayed.
  • When the LIKE and WHERE clauses involve string comparison, the fields Field, Collation, Null, Extra, and Privileges use the character set utf8mb4 and the collation utf8mb4_general_ci, and the fields Type, Key, Default, and Comment use the character set utf8mb4 and the collation utf8mb4_bin.
  • In GaussDB, you are advised not to select columns other than the returned fields in the WHERE clause. Otherwise, unexpected errors may occur. 
    -- Expected error
m_db=# SHOW FULL COLUMNS FROM t02 WHERE `b`='pri';
ERROR:  Column "b" does not exist.
LINE 1: SHOW FULL COLUMNS FROM t02 WHERE `b`='pri';
                                         ^

-- Unexpected error
m_db=# SHOW FULL COLUMNS FROM t02 WHERE `c`='pri';
ERROR:  input of anonymous composite types is not implemented
LINE 1: SHOW FULL COLUMNS FROM t02 WHERE `c`='pri';
                                             ^

SHOW CREATE DATABASE syntax

SHOW

User permission verification is different from that of MySQL.

  • In GaussDB, you need the USAGE permission on a specified schema.
  • In MySQL, you need database-level permissions (except GRANT OPTION and USAGE), table-level permissions (except GRANT OPTION), or column-level permissions.

SHOW CREATE TABLE syntax

SHOW
  • User permission verification is different from that of MySQL.
    • In GaussDB, you need the USAGE permission on the schema where a specified table is located and table-level permissions on the specified table.
    • Table-level permissions (except GRANT OPTION) of the specified table are required in MySQL.
  • The returned statements for table creation are different from those in MySQL.
    • In GaussDB, indexes are returned as CREATE INDEX statements. In MySQL, indexes are returned as CREATE TABLE statements. In GaussDB, the range of optional parameters supported by the CREATE INDEX syntax is different from that supported by the CREATE TABLE syntax. Therefore, some indexes cannot be created in CREATE TABLE statements.
    • In GaussDB, the ENGINE and ROW_FORMAT options of CREATE TABLE are adapted only for the syntax but do not take effect. Therefore, they are not displayed in the returned statements for table creation.
  • These statements are compatible with MySQL only after the compatibility parameter m_format_dev_version is set to 's2'. The compatibility parameter takes effect by changing the positions of column comments, table comments, ON COMMIT option for global temporary tables, primary key and unique constraints (where the USING INDEX TABLESPACE option is no longer displayed), and index comments.

SHOW CREATE VIEW syntax

SHOW
  • User permission verification is different from that of MySQL.
    • In GaussDB, you need the USAGE permission on the schema where a specified view is located and table-level permissions on the specified view.
    • In MySQL, you need the table-level SELECT and table-level SHOW VIEW permissions on the specified view.
  • The returned statements for view creation are different from those in MySQL. If a view is created in the format of SELECT * FROM tbl_name, * is not expanded in GaussDB but expanded in MySQL.
  • The character_set_client and collation_connection fields in the returned result are different from those in MySQL.
    • The session values of system variables character_set_client and collation_connection are displayed during view creation in MySQL
    • Related metadata is not recorded in GaussDB and NULL is displayed.

SHOW PROCESSLIST syntax

SHOW

In GaussDB, the field content and case in the query result of this command are the same as those in the information_schema.processlist view. In MySQL, the field content and case may be different.

  • In GaussDB, common users can access only their own thread information. Users with the SYSADMIN permission can access thread information of all users.
  • In MySQL, common users can access only their own thread information. Users with the PROCESS permission can access thread information of all users.

SHOW [STORAGE] ENGINES

SHOW

In GaussDB, the field content and case of the query result of this command are the same as those in the information_schema.engines view. In MySQL, they may be different from those in the view. The query results of this command are different in MySQL and GaussDB because the databases have different storage engines.

SHOW [SESSION] STATUS

SHOW

In GaussDB, the field content and case of the query result of this command are the same as those in the information_schema.session_status view. In MySQL, they may be different from those in the view. Currently, GaussDB supports only Threads_connected and Uptime.

SHOW [GLOBAL] STATUS

SHOW

In GaussDB, the field content and case of the query result of this command are the same as those in the information_schema.global_status view. In MySQL, they may be different from those in the view. Currently, GaussDB supports only Threads_connected and Uptime.

SHOW INDEX

SHOW
  • User permission verification is different from that of MySQL.
    • In GaussDB, you need the USAGE permission on a specified schema and table-level or column-level permissions on a specified table.
    • In MySQL, you need table-level (except GRANT OPTION) or column-level permission on the specified table.
  • Temporary tables in GaussDB are stored in independent temporary schemas. When using the FROM or IN db_name condition to display the index information of a specified temporary table, you must specify db_name as the schema where the temporary table is located. Otherwise, the system displays a message indicating that the temporary table does not exist. This is different from MySQL in some cases.
  • In the query result of GaussDB, the Table, Index_type, and Index_comment columns use the character set utf8mb4 and collation utf8mb4_bin. The Key_name, Column_name, Collation, Null, and Comment columns use the character set utf8mb4 and collation utf8mb4_general_ci.

SHOW SESSION VARIABLES

SHOW

In GaussDB, the field content and case of the query result are the same as those in the information_schema.session_variables view. In MySQL, they may be different from those in the view.

In GaussDB, when LIKE and WHERE are used to select fields in the query result, the sorting rule is the same as that of the corresponding fields in the information_schema.session_variables view.

SHOW GLOBAL VARIABLES

SHOW

In GaussDB, the field content and case of the query result are the same as those in the information_schema.global_variables view. In MySQL, they may be different from those in the view.

In GaussDB, when LIKE and WHERE are used to select fields in the query result, the sorting rule is the same as that of the corresponding fields in the information_schema.global_variables view.

SHOW CHARACTER SET

SHOW

In GaussDB, the field content and case of the query result are the same as those in the information_schema.character_sets view. In MySQL, they may be different from those in the view.

In GaussDB, when LIKE and WHERE are used to select fields in the query result, the sorting rule is the same as that of the corresponding fields in the information_schema.character_sets view.

SHOW COLLATION

SHOW

In GaussDB, the field content and case of the query result are the same as those in the information_schema.collations view. In MySQL, they may be different from those in the view.

In GaussDB, when LIKE and WHERE are used to select columns in the query result, the sorting rule is the same as that of the corresponding columns in the information_schema.collations view.

SHOW TABLES

SHOW
  • The LIKE behavior is different. For details, see "LIKE" in Operators.
  • The WHERE expression behavior is different. For details, see "WHERE" in GaussDB.
  • In GaussDB, permissions on tables and databases must be assigned to users separately. The database to be queried must be available to users on the SHOW SCHEMAS. Users must have permissions on both tables and databases. MySQL can be accessed as long as you have table permissions.
  • In GaussDB, the verification logic preferentially checks whether a schema exists and then checks whether the current user has the permission on the schema, which is different from that in MySQL.
  • In GaussDB, fields in the query result use the character set utf8mb4 and collation utf8mb4_bin.
  • In the LIKE clause of GaussDB, if the target database is information_schema, the pattern is converted to lowercase letters before matching. In MySQL 8.0, when the target database is information_schema, the pattern is converted to uppercase letters before matching.

SHOW TABLE STATUS

SHOW
  • In GaussDB, the syntax displays data depending on the tables view under information_schema. In MySQL, the tables view specifies tables.
  • In GaussDB, permissions on tables and databases must be assigned to users separately. The database to be queried must be available to users on the SHOW SCHEMAS. Users must have permissions on both tables and databases. MySQL can be accessed as long as you have table permissions.
  • In GaussDB, the verification logic preferentially checks whether a schema exists and then checks whether the current user has the permission on the schema, which is different from that in MySQL.
  • In GaussDB, when LIKE and WHERE are used to select fields in the query result, the sorting rule is the same as that of the corresponding fields in the information_schema.tables view.
  • In the LIKE clause of GaussDB, if the target database is information_schema, the pattern is converted to lowercase letters before matching. In MySQL 8.0, when the target database is information_schema, the pattern is converted to uppercase letters before matching.

SHOW DATABASES

SHOW

In GaussDB, fields in the query result use the character set utf8mb4 and collation utf8mb4_bin.

Support the ONLY_FULL_GROUP_BY option in SQL_MODE.

SELECT

If the non-aggregate function column in the SELECT list is inconsistent with the GROUP BY field, when all non-aggregate function columns are in the GROUP BY list or WHERE list and the column in the WHERE clause is equal to a constant, no error is reported. For the column in the WHERE clause, GaussDB supports function column expressions whose input parameter is 1, but MySQL does not support function column expressions.

In GaussDB, the column following GROUP BY must be a positive integer.

Query system parameters and user variables by using SELECT.

SELECT @variable, SELECT @@variable
  • In MySQL, user variables can be queried without adding specific variable names (that is, SELECT @). GaussDB does not support this feature.

    Behavior in MySQL:

    mysql> SELECT @;
+------------+
| @          |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
    Behavior in GaussDB: 
    m_db=# SELECT @;
ERROR:  syntax error at or near "@"
LINE 1: SELECT @;
               ^
  • When the type of the queried system variable is BOOLEAN, the output result is t or f in GaussDB and 1 or 0 in MySQL. The BOOLEAN type is actually mapped to the TINYINT type.

Subqueries

SELECT
  • In GaussDB, the subquery result cannot contain multiple columns. If the subquery result contains multiple columns, an error is reported. In MySQL, the subquery result can contain multiple columns.

    Behavior in MySQL:

    mysql> SELECT row(1,2) = (SELECT 1,2);
+-------------------------+
| row(1,2) = (SELECT 1,2) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

    Behavior in GaussDB:

    m_db=# SELECT row(1,2) = (SELECT 1,2);
ERROR:  subquery must return only one column
LINE 1: SELECT row(1,2) = (SELECT 1,2);                          ^
  • In the scenario where precision transfer is enabled, if the return type in the FROM clause of a subquery is numeric in MySQL, one of the following conditions is met:
    • The SELECT clause contains GROUP BY.
    • The SELECT clause contains HAVING.
    • The SELECT clause contains DISTINCT.
    • The SELECT clause contains LIMIT.
    • The SELECT clause does not contain FROM table.
    • The SELECT clause contains a statement that assigns a value to a user-defined variable.

    Precision truncation may occur. If this type of subquery is used as the intermediate calculation value for the next operation, the precision of GaussDB is higher than that of MySQL.

    Behavior in MySQL:

    mysql> SELECT greatest((SELECT * FROM (SELECT DISTINCT c2/1.61 FROM t_time) t4), 1.00000000000000000);
+-----------------------------------------------------------------------------------------+
| greatest((SELECT * FROM (SELECT DISTINCT c2/1.61 FROM t_time) t4), 1.00000000000000000) |
+-----------------------------------------------------------------------------------------+
|                                                                 39144.72670800000000000 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    Behavior in GaussDB:

    m_db=# SELECT greatest((SELECT * FROM (SELECT DISTINCT c2/1.61 FROM t_time) t4), 1.00000000000000000); 
        greatest         
-------------------------
 39144.72670807453416149
(1 row)

    In addition, PBE is used together with user-defined variables. If the preceding conditions are met, MySQL outputs results with the precision of 30 decimal places. Otherwise, the MySQL outputs results with the original precision, but GaussDB always outputs results with the precision of 30 decimal places. For example:

    Behavior in MySQL:

    -- The preceding conditions are met:
mysql> SET @var6=12.1234567891;
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE p1 FROM "SELECT * FROM (SELECT @var6) t";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE p1;
+-----------------------------------+
| @var6                             |
+-----------------------------------+
| 12.123456789100000000000000000000 |
+-----------------------------------+
1 row in set (0.00 sec)
-- The preceding conditions are not met:
mysql> PREPARE p1 FROM "SELECT * FROM (SELECT @var6 FROM (SELECT 1) v1) t";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE p1;
+---------------+
| @var6         |
+---------------+
| 12.1234567891 |
+---------------+
1 row in set (0.00 sec)

    Behavior in GaussDB:

    -- The preceding conditions are met:
m_db=# SET @var6=12.1234567891;
SET
m_db=# PREPARE p1 FROM "SELECT * FROM (SELECT @var6) t";
PREPARE
m_db=# EXECUTE p1;
               @var6               
-----------------------------------
 12.123456789100000000000000000000
(1 row)
-- The preceding conditions are not met:
m_db=# PREPARE p1 FROM "SELECT * FROM (SELECT @var6 FROM (SELECT 1) v1) t";
PREPARE
m_db=# EXECUTE p1;
               @var6               
-----------------------------------
 12.123456789100000000000000000000
(1 row)

SELECT followed by a row expression

SELECT

In MySQL, SELECT cannot be followed by a row expression, but in GaussDB, SELECT can be followed by a row expression.

Behavior in MySQL:

mysql> SELECT row(1,2);
ERROR 1241 (21000): Operand should contain 1 column(s)

Behavior in GaussDB:

m_db=# SELECT row(1,2);
 row(1,2) 
----------
 (1,2)
(1 row)

SELECT view query, subquery, or UNION involves the carry difference when NUMERIC is converted to TIME or DATETIME.

SELECT

In some SELECT scenarios, the results of the TIME/DATETIME type are different from those of MySQL.

Difference scenarios involving conversion from NUMERIC to TIME/DATETIME: view query, subquery, and UNION.

Differential behavior: The SELECT behavior of GaussDB is unified. When the NUMERIC type is converted to the TIME or DATETIME type, only the maximum precision bit(6) is carried. In MySQL view query, subquery, and UNION scenarios, carry is performed based on the actual precision of a result.

Behavior in MySQL:

-- In a simple query, carry is performed only on the result with the precision of 6, which is the maximum. Therefore, 11:11:00.00002 is output.
mysql> SELECT maketime(11, 11, 2.2/time '08:30:23.01');
+------------------------------------------+
| maketime(11, 11, 2.2/time '08:30:23.01') |
+------------------------------------------+
| 11:11:00.00002                           |
+------------------------------------------+
1 row in set (0.01 sec)

-- In a subquery, carry is performed based on the actual result precision. Therefore, 11:11:00.00003 is output.
mysql> SELECT * FROM (SELECT maketime(11, 11, 2.2/time '08:30:23.01')) f1;
+------------------------------------------+
| maketime(11, 11, 2.2/time '08:30:23.01') |
+------------------------------------------+
| 11:11:00.00003                           |
+------------------------------------------+
1 row in set (0.00 sec)

Behavior in GaussDB:

m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
SET

-- In a simple query, carry is performed only on the result with the precision of 6, which is the maximum. Therefore, 11:11:00.00002 is output.
m_db=# SELECT maketime(11, 11, 2.2/time '08:30:23.01');
    maketime
----------------
 11:11:00.00002
(1 row)

-- In a simple query, carry is performed only on the result with the precision of 6, which is the maximum, and the result precision is 5. Therefore, 11:11:00.00002 is output.
m_db=# SELECT * FROM (SELECT maketime(11, 11, 2.2/time '08:30:23.01')) f1;
    maketime
----------------
 11:11:00.00002
(1 row)

Differences of SELECT in calculating and processing date and time functions of the numeric type and subquery

SELECT

When date and time functions of the numeric type and subquery are calculated using SELECT, if the GUC parameter m_format_behavior_compat_options is set to enable_precision_decimal, GaussDB converts the value of the date and time type returned by the function to the one of the numeric type and then performs calculation based on the numeric type. The result is also of the numeric type. MySQL truncates the values returned by the date and time functions in scenarios such as subquery condition query and group query.

Behavior in MySQL:

mysql> SELECT 1.5688 * (SELECT adddate('2020-10-20', interval 1 day) WHERE true GROUP BY 1 HAVING true);
+--------------------------------------------------------------------------------+
| 1.5688 * (SELECT adddate('2020-10-20', interval 1 day) WHERE true HAVING true) |
+--------------------------------------------------------------------------------+
|                                                                       3168.976 |

Behavior in GaussDB:

m_db=# SELECT 1.5688 * (SELECT adddate('2020-10-20', interval 1 day) WHERE true GROUP BY 1 HAVING true);
      ?column?      
--------------------
 31691361.744799998
(1 row)

Differences in unsigned types when SELECT nests subqueries

SELECT

When SELECT nests subqueries, the unsigned type is not overwritten, which is different from MySQL 5.7.

Behavior in MySQL 5.7:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (
    ->     c10 real(10, 4) zerofill
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(123.45);
Query OK, 1 row affected (0.00 sec)

mysql> DESC t1;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type                           | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| c10   | double(10,4) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> CREATE TABLE t1_sub_1 AS SELECT (SELECT * FROM t1);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC t1_sub_1;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| (SELECT * FROM t1) | double(10,4) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Behavior in GaussDB:

test=# DROP TABLE IF EXISTS t1;
DROP TABLE
test=# CREATE TABLE t1 (
test(#     c10 real(10, 4) ZEROFILL
test(# );
CREATE TABLE
test=# INSERT INTO t1 VALUES(123.45);
INSERT 0 1
test=# DESC t1;
 Field |              Type              | Null | Key | Default | Extra 
-------+--------------------------------+------+-----+---------+-------
 c10   | double(10,4) unsigned zerofill | YES  |     |         | 
(1 row)
test=# CREATE TABLE t1_sub_1 AS SELECT (SELECT * FROM t1);
INSERT 0 1
test=# DESC t1_sub_1;
 Field |         Type          | Null | Key | Default | Extra 
-------+-----------------------+------+-----+---------+-------
 c10   | double(10,4) unsigned | YES  |     |         | 
(1 row)

SELECT FOR SHARE/FOR UPDATE/LOCK IN SHARE MODE

SELECT
  • The FOR SHARE/FOR UPDATE/LOCK IN SHARE MODE and UNION/EXCEPT/DISTINCT/GROUP BY/HAVING clauses cannot be used together in GaussDB. They can be used together in MySQL 5.7 (except in the FOR SHARE/EXCEPT syntax) and MySQL 8.0.
  • When a lock clause is used together with the LEFT/RIGHT [OUTER] JOIN clause, the LEFT JOIN cannot be used to lock the right table, and the RIGHT JOIN clause cannot be used to lock the left table. In MySQL, tables on both sides of JOIN can be locked at the same time.
  • In MySQL, multiple lock clauses cannot be specified for the same table, while GaussDB supports this operation and the strongest lock will take effect. 
    -- GaussDB
m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE

m_db=# CREATE TABLE t1(a INT, b INT);
CREATE TABLE

m_db=# INSERT INTO t1 VALUES(1,2);
INSERT 0 1

m_db=# SELECT * FROM t1 FOR UPDATE OF t1 LOCK IN SHARE MODE;
 a | b 
---+---
 1 | 2
(1 row)

m_db=# DROP TABLE t1;
DROP TABLE

-- MySQL
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 VALUES(1,2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 FOR UPDATE OF t1 LOCK IN SHARE MODE;
ERROR 3569 (HY000): Table t1 appears in multiple locking clauses.

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.05 sec)

SELECT syntax

SELECT
  • In GaussDB, HAVING can only reference columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports more: it allows HAVING to reference SELECT columns in the list and columns in external subqueries.
  • In GaussDB, when an empty table is queried by using the specified WITH ROLLUP statement, the query result is an empty row. In contrast, the query result in MySQL is empty.
  • In GaussDB, a table alias with the column name can be specified by using the FROM clause. In MySQL 5.7, a table alias with the column name cannot be specified. In MySQL 8.0, it is allowed only in a subquery. 
    -- GaussDB
m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE
m_db=# CREATE TABLE t1(a INT, b INT);
CREATE TABLE
m_db=# INSERT INTO t1 VALUES(1,2);
INSERT 0 1
m_db=# SELECT * FROM t1 t2(a, b);
 a | b 
---+---
 1 | 2
(1 row)

m_db=# SELECT * FROM (SELECT * FROM t1) t2(a, b);
 a | b 
---+---
 1 | 2
(1 row)

-- MySQL 5.7
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(1,2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 t2(a, b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(a, b)' at line 1
mysql> SELECT * FROM (SELECT * FROM t1) t2(a, b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(a, b)' at line 1

-- MySQL 8.0
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO t1 VALUES(1,2);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM t1 t2(a, b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(a, b)' at line 1
mysql> SELECT * FROM (SELECT * FROM t1) t2(a, b);
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)
  • If a query statement does not contain the FROM clause, GaussDB supports the WHERE clause, which is the same as that in MySQL 8.0. MySQL 5.7 does not support the WHERE clause. 
    -- GaussDB
m_db=# SELECT 1 WHERE true;
 1 
---
 1
(1 row)

-- MySQL 5.7
mysql> SELECT 1 WHERE true;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where true' at line 1

-- MySQL 8.0
mysql> SELECT 1 WHERE true;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

When statements such as UNION and GROUP BY that do not carry the ORDER BY clause are used to merge or aggregate data, the output data sequence may not be the same as that in MySQL because the executor operators are different.

SELECT

Take the GROUP BY scenario as an example. If the hashagg operator is used, the sequence is different from the original one. You are advised to add the ORDER BY clause in the scenario where the data sequence needs to be ensured.

-- Initialize data.
DROP TABLE IF EXISTS test;
CREATE TABLE test(id INT);
INSERT INTO test VALUES (1),(2),(3),(4),(5);
-- GaussDB
-- If precision transfer is disabled, the ID sequence is (1 3 2 4 5).
m_db=# SET m_format_behavior_compat_options= '';
SET
m_db=# SELECT /*+ use_hash_agg*/ id, pi() FROM test GROUP BY 1,2;
 id |        pi
----+-------------------
  1 | 3.141592653589793
  3 | 3.141592653589793
  2 | 3.141592653589793
  4 | 3.141592653589793
  5 | 3.141592653589793
(5 rows)
-- When the precision transfer function is enabled, the ID sequence changes to (5 4 2 3 1) due to the value change.
m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
SET
m_db=# SELECT /*+ use_hash_agg*/ id, pi() FROM test GROUP BY 1,2;
 id |    pi
----+----------
  5 | 3.141593
  4 | 3.141593
  2 | 3.141593
  3 | 3.141593
  1 | 3.141593
(5 rows)
-- In MySQL, the ID sequence is the original one.
mysql> SELECT id, pi() FROM test GROUP BY 1,2;
+------+----------+
| id   | pi()     |
+------+----------+
|    1 | 3.141593 |
|    2 | 3.141593 |
|    3 | 3.141593 |
|    4 | 3.141593 |
|    5 | 3.141593 |
+------+----------+
5 rows in set (0.00 sec)

Support the WITH AS statement.

SELECT

UPDATE

DELETE
  • In the WITH RECURSIVE scenario of GaussDB, when the type, typmod, and collation of the non-recursive columns and subqueries are inconsistent with those of the result columns obtained by the subquery, a syntax error occurs, because this scenario does not support such inconsistency.
  • For the WITH recursion part, GaussDB does not support aggregate functions, window functions, FOR UPDATE/SHARE, LIMIT, and OFFSET; MySQL supports FOR UPDATE/SHARE, and MySQL 8.0.19 and later versions support LIMIT and OFFSET.
  • For the WITH RECURSIVE part, GaussDB supports DISTINCT and GROUP BY, but MySQL does not.
  • In the WITH RECURSIVE scenario, when the value generated by the WITH RECURSIVE column is wider than that generated by the non-recursive column, MySQL truncates data in loose mode, or reports an error in strict mode; GaussDB does not truncate data and generates results consistent with those after MySQL widens data length.
  • When the WITH RECURSIVE part is used for an outer join, the supported scope of GaussDB differs from that of MySQL. 
    -- The differences are as follows:
-- Initialize data.
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(c INT);
INSERT INTO t2 VALUES (5);
-- In GaussDB, the non-recursive part uses UNION SELECT 1::bigint to forcibly convert the column type of the non-recursive part.
m_db=# WITH RECURSIVE cte AS (SELECT 1 AS a UNION SELECT 1::bigint UNION SELECT a+1 FROM cte RIGHT JOIN t2 ON t2.c>cte.a WHERE cte.a<3) SELECT * FROM cte;
ERROR:  recursive reference to query "cte" must not appear within an outer join
LINE 1: ...AS a UNION SELECT 1::bigint UNION SELECT a+1 FROM cte RIGHT ...
                                                             ^
-- MySQL 8.0
mysql> WITH RECURSIVE cte AS (SELECT 1 AS a UNION SELECT a+1 FROM cte RIGHT JOIN t2 ON t2.c>cte.a WHERE cte.a<3) SELECT * FROM cte;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
DROP TABLE IF EXISTS t2;

INSERT ... ON DUPLICATE KEY UPDATE syntax

INSERT
  • The format of table-name.column-name is not supported by VALUES() in the ON DUPLICATE KEY UPDATE clause in GaussDB, but is supported in MySQL.
  • INSERT ... In the query ON DUPLICATE KEY UPDATE statement, if the query is a UNION or EXCEPT subquery, MySQL 5.7 allows the UPDATE clause to reference column names in the subquery; however, this is not allowed in MySQL 8.0 and GaussDB.
  • In MySQL, when you use the ON DUPLICATE KEY UPDATE clause to update multiple columns, the result of the previous UPDATE statement affects the subsequent results. In addition, you can update the same column for multiple times. In GaussDB, the result of the previous UPDATE operation does not affect the subsequent results. In addition, the same column cannot be updated for multiple times. You can set the GUC compatibility parameter m_format_dev_version to 's2' to make its behavior the same as that in MySQL. That is, the same column can be updated for multiple times and the updated result is referenced.
  • When the UPDATE operation is performed on inserted data that violates the unique constraint, the number of affected rows returned by GaussDB is different from that returned by MySQL. When a data record is updated, GaussDB returns 1 and MySQL returns 2. If such update does not change the value of an existing row, GaussDB returns 1 and MySQL returns 0.
  • When the ON DUPLICATE KEY UPDATE clause updates the auto-increment column to NULL and the column contains the NOT NULL constraint, GaussDB reports the error "The null value in column xxx violates the not-null constraint." However, the operation can be performed in MySQL, and the corresponding auto-increment column is updated to 0.
  • When a table column with a string is implicitly converted from VARCHAR to a numeric type, if the part that fails to be converted is 2 characters shorter than the whole string (for example, if no character of "AA" is successfully converted, the length difference is 2 – 0 = 2; if the first character of "4XY" is converted, the length difference is 3 – 1 = 2), no error is reported in MySQL. However, MySQL still reports an error when the length difference is not 2. GaussDB always reports an error if the string fails to be converted in the preceding scenario.
m_db=# CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(10));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
m_db=# INSERT INTO t1 values(1, 'A');
INSERT 0 1
m_db=# INSERT INTO t1 VALUES(1, 'X') ON DUPLICATE KEY UPDATE b=values(a)+values(b);
ERROR:  The double value 'X' is incorrect.
CONTEXT:  referenced column: b
m_db=# INSERT INTO t1 VALUES(1, 'YY') ON DUPLICATE KEY UPDATE b=values(a)+values(b);
ERROR:  The double value 'YY' is incorrect.
CONTEXT:  referenced column: b
m_db=# INSERT INTO t1 VALUES(1, 'ZZZ') ON DUPLICATE KEY UPDATE b=values(a)+values(b);
ERROR:  The double value 'ZZZ' is incorrect.
CONTEXT:  referenced column: b
m_db=# DROP TABLE t1;
DROP TABLE

mysql> CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(10));
Query OK, 0 rows affected (0.00 sec)

mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 values(1, 'A');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1, 'X') ON DUPLICATE KEY UPDATE b=values(a)+values(b);
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'X'

mysql> INSERT INTO t1 VALUES(1, 'YY') ON DUPLICATE KEY UPDATE b=values(a)+values(b);
Query OK, 2 rows affected, 2 warnings (0.00 sec)

mysql> INSERT INTO t1 VALUES(1, 'ZZZ') ON DUPLICATE KEY UPDATE b=values(a)+values(b);
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'ZZZ'

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)
Parent topic: SQL