SQL

Updated on 2024-12-06 GMT+08:00

View PDF

GaussDB is compatible with most MySQL syntax, but there are some differences. This section describes the MySQL syntax supported by GaussDB.

Some keywords can be used as identifiers in MySQL, but cannot or are restricted to be identifiers in M-compatible mode, as listed in Table 1.

**Table 1** Keywords restricted to be identifiers
Keyword Type	Keyword	Constraint
Reserved (Type or function is allowed.)	COLLATION and COMPACT	They cannot be used as identifiers in other databases except for functions and variables.
Non-reserved (Type or function is not allowed.)	BIT, BOOLEAN, COALESCE, DATE, NATIONAL, NCHAR, NONE, NUMBER, TEXT, TIME, TIMESTAMP, and TIMESTAMPDIFF	They cannot be used as identifiers for functions or variables.
Reserved	ANY, ARRAY, BUCKETS, DO, END, LESS, MODIFY, OFFSET, ONLY, RETURNING, SOME, and USER	They cannot be used as identifiers in any database.

The GaussDB optimizer is different from the MySQL optimizer. Due to the difference in the execution plans generated by optimizers, the GaussDB behavior may be inconsistent with the MySQL behavior, but it does not affect the service data result.

For example, in the following scenario, when GaussDB calculates col1 and uses col1 for WHERE comparison, the cast function is called and two WARNING records are generated.

MySQL calls the cast function when calculating col1. During WHERE comparison, the calculated value is used for comparison. As a result, a WARNING record is generated.

-- Behavior in GaussDB:
m_db=# select * from (select cast('abc' as decimal) as col1) t1 where col1=0;
WARNING:  Truncated incorrect DECIMAL value: 'abc'
WARNING:  Truncated incorrect DECIMAL value: 'abc'
CONTEXT:  referenced column: col1
 col1 
------
    0
(1 row)

m_db=# explain verbose select * from (select cast('abc' as decimal) as col1) t1 where col1=0;
WARNING:  Truncated incorrect DECIMAL value: 'abc'
WARNING:  Truncated incorrect DECIMAL value: 'abc'
CONTEXT:  referenced column: col1
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
   Output: 0::decimal
(2 rows)

-- Behavior in MySQL:
mysql>  select * from (select cast('abc' as decimal) as col1) t1 where col1=0;
+------+
| col1 |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: 'abc' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from (select cast('abc' as decimal) as col1) t1 where col1=0;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: 'abc'                      |
| Note    | 1003 | /* select#1 */ select '0' AS `col1` from dual where ('0' = 0) |
+---------+------+---------------------------------------------------------------+
2 rows in set (0.00 sec)