Operators

1

<>

Supported, with differences.

MySQL supports indexes, but GaussDB does not.

2

<=>

Supported, with differences.

MySQL supports indexes, but GaussDB does not support indexes, hash joins, or merge joins.

3

Row expressions

Supported, with differences.

• MySQL supports row comparison using the <=> operator, but GaussDB does not support row comparison using the <=> operator.
• MySQL does not support comparison between row expressions and NULL values. In GaussDB, the <, <=, =, >=, >, and <> operators can be used to compare row expressions with NULL values.
• IS NULL or ISNULL operations on row expressions are not supported in MySQL. However, they are supported in GaussDB.
• For operations by using operators that cannot be performed on row expressions, the error information in GaussDB is inconsistent with that in MySQL.
• MySQL does not support ROW(values), in which values contains only one column of data, but GaussDB supports.

GaussDB:

m_db=# SELECT (1,2) <=> row(2,3);
ERROR:  could not determine interpretation of row comparison operator <=>
LINE 1: select (1,2) <=> row(2,3);
                     ^
HINT:  unsupported operator.
m_db=# SELECT (1,2) < NULL;
 ?column? 
----------

(1 row)
m_db=# SELECT (1,2) <> NULL;
 ?column? 
----------
(1 row)
m_db=# SELECT (1, 2)  IS NULL;
 ?column? 
----------
 f
(1 row)
m_db=# SELECT ISNULL((1, 2));
 ?column? 
----------
 f
(1 row)
m_db=# SELECT ROW(0,0) BETWEEN ROW(1,1) AND ROW(2,2);
ERROR:  un support type
m_db=#  SELECT ROW(NULL) AS x;
 x
----
 ()
(1 row)

MySQL:

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

mysql> SELECT (1,2) < NULL;
ERROR 1241 (21000): Operand should contain 2 column(s)
mysql> SELECT (1,2) <> NULL;
ERROR 1241 (21000): Operand should contain 2 column(s)
mysql> SELECT (1, 2)  IS NULL;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> SELECT ISNULL((1, 2));
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> SELECT NULL BETWEEN NULL AND ROW(2,2);
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> SELECT ROW(NULL) AS x;
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 ') as x' at line 1

4

--

Supported.

MySQL indicates that an operand is negated twice and the result is equal to the original operand. GaussDB indicates a comment.

5

!!

Supported, with differences.

MySQL: The meaning of !! is the same as that of !, indicating NOT.

GaussDB: ! indicates NOT. If there is a space between two exclamation marks (! !), it indicates NOT for twice. If there is no space between them (!!), it indicates factorial.

6

[NOT] REGEXP

Supported, with differences.

• GaussDB and MySQL support different metacharacters in regular expressions. For example, GaussDB allows \d to indicate digits, \w to indicate letters, digits, and underscores (_), and \s to indicate spaces. However, MySQL does not support these metacharacters and considers them as normal character strings.
• In GaussDB, '\b' can match '\\b', but in MySQL, the matching will fail.
• In the new GaussDB framework, a backslash (\) indicates an escape character. In MySQL, two backslashes (\\) are used.
• MySQL does not support two operators to be used together.
• GaussDB reports an error when the input parameter of the pattern string pat is invalid and only the right single parenthesis ')' exists. MySQL has a bug, which has been fixed in later versions.
• When de|abc matches de or abc, if there is a null value on the left or right of |, MySQL reports an error. This bug has been fixed in later versions.
• The regular expression of the blank character [\t] can match the character class [:blank:] in GaussDB, but MySQL's [\t] cannot match [:blank:]. MySQL has a bug, which has been fixed in later versions.
• GaussDB supports non-greedy pattern matching. That is, the number of matching characters is as small as possible. A question mark (?) is added after some special characters, for example, ??, *?, +?, {n}?, {n,}?, and {n,m}?. MySQL 5.7 does not support non-greedy pattern matching, and the error message "Got error 'repetition-operator operand invalid' from regexp" is displayed. MySQL 8.0 already supports this function.
• In the binary character set, the text and BLOB types are converted to the bytea type. The REGEXP operator does not support the bytea type. Therefore, the two types cannot be matched.

7

LIKE

Supported, with differences.

MySQL: The left operand of LIKE can only be an expression of a bitwise or arithmetic operation, or expression consisting of parentheses. The right operand of LIKE can only be an expression consisting of unary operators (excluding NOT) or parentheses.

GaussDB: The left and right operands of LIKE can be any expression.

8

[NOT] BETWEEN AND

Supported, with differences.

MySQL: [NOT] BETWEEN AND is nested from right to left. The first and second operands of [NOT] BETWEEN AND can only be expressions of bitwise or arithmetic operations, or expressions consisting of parentheses.

GaussDB: [NOT] BETWEEN AND is nested from left to right. The first and second operands of [NOT] BETWEEN AND can be any expression.

9

IN

Supported, with differences.

MySQL: The left operand of IN can only be an expression of a bitwise or arithmetic operation, or expression consisting of parentheses.

GaussDB: The left operand of IN can be any expression. The query in ROW IN (ROW,ROW...) format is not supported.

When precision transfer is enabled and the in operator is used for data in a table, if the data in the table is of the FLOAT or DOUBLE type and includes the corresponding precision and scale, such as float (4,2) or double (4,2), GaussDB compares values based on the precision and scale, but MySQL reads values in the memory, which are distorted values, causing unequal comparison results.

-- GaussDB:
m_db=# CREATE TABLE test1(t_float float(4,2));
CREATE TABLE
m_db=# INSERT INTO test1 VALUES(1.42),(2.42);
INSERT 0 2
m_db=# SELECT t_float, t_float in (1.42,2.42) FROM test1;
 t_float | ?column? 
---------+----------
    1.42 | t
    2.42 | t
(2 rows)
--MySQL:
mysql> CREATE TABLE test1(t_float float(4,2));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test1 VALUES(1.42),(2.42);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT t_float, t_float in (1.42,2.42) FROM test1;
+---------+------------------------+
| t_float | t_float in (1.42,2.42) |
+---------+------------------------+
|    1.42 |                      0 |
|    2.42 |                      0 |
+---------+------------------------+
2 rows in set (0.00 sec)

10

!

Supported, with differences.

MySQL: The operand of ! can only be an expression consisting of unary operators (excluding NOT) or parentheses.

GaussDB: The operand of ! can be any expression.

11

#

Not supported.

MySQL supports the comment tag (#), but GaussDB does not.

12

BINARY

Supported, with differences.

Expressions (including some functions and operators) supported by GaussDB are different from those supported by MySQL. For GaussDB-specific expressions such as '~' and 'IS DISTINCT FROM', due to the higher priority of the BINARY keyword, when BINARY expr is used, BINARY is combined with the left parameters of '~' and 'IS DISTINCT FROM' first. As a result, an error is reported.

13

Negation (-)

Supported, with differences.

The type and precision of the negation result are inconsistent with those in the MySQL.

CREATE TABLE t as select - -1;

• The return type of MySQL table fields is decimal(2,0).
• The return type of GaussDB table fields is integer(1).

When precision transfer is enabled (m_format_behavior_compat_options is set to 'enable_precision_decimal'), the precision of the negative constant data type may be different from that in MySQL. In MySQL 5.7, when the expression contains negation operators, the max_length of the result precision increases based on the number of the negation operators, but this will not happen in GaussDB. For example:

• GaussDB:

  m_db=# DROP TABLE IF EXISTS test;
  NOTICE:  table "test" does not exist, skipping
  DROP TABLE
  m_db=# CREATE TABLE test as
  m_db-# SELECT format(-4.4600e3,1) f9;
  INSERT 0 1
  m_db=# DESC test;
   Field |    Type     | Null | Key | Default | Extra 
  -------+-------------+------+-----+---------+-------
   f9    | varchar(45) | YES  |     |         | 
  (1 row)
  
  m_db=# DROP TABLE IF EXISTS t1;
  NOTICE:  table "t1" does not exist, skipping
  DROP TABLE
  m_db=# CREATE TABLE t1 AS SELECT CAST(- -4.46 AS BINARY) c4,CONVERT(- - -002.2600,binary) c14;
  INSERT 0 1
  m_db=# DESC t1;
   Field |     Type      | Null | Key | Default | Extra 
  -------+---------------+------+-----+---------+-------
   c4    | varbinary(5)  | YES  |     |         | 
   c14   | varbinary(10) | YES  |     |         | 
  (2 rows)
  
  m_db=# DROP VIEW IF EXISTS v2;
  NOTICE:  view "v2" does not exist, skipping
  DROP VIEW
  m_db=# CREATE VIEW v2 AS SELECT CAST(- -4.46 AS BINARY) c4,CONVERT(- - -002.2600,binary) c14;
  CREATE VIEW
  m_db=# DESC v2;
   Field |     Type     | Null | Key | Default | Extra 
  -------+--------------+------+-----+---------+-------
   c4    | varbinary(5) | YES  |     |         | 
   c14   | varbinary(8) | YES  |     |         | 
  (2 rows)

• MySQL:

  mysql> DROP TABLE IF EXISTS test;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> CREATE TABLE test as
      -> select format(-4.4600e3,1) f9;
  Query OK, 1 row affected (0.01 sec)
  Records: 1  Duplicates: 0  Warnings: 0
  
  mysql> DESC test;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | f9    | varchar(63) | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  1 row in set (0.00 sec)
  
  mysql> DROP TABLE IF EXISTS t1;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  
  mysql> CREATE TABLE t1 AS SELECT CAST(- -4.46 AS BINARY) c4,CONVERT(- - -002.2600,BINARY) c14;
  Query OK, 1 row affected (0.02 sec)
  Records: 1  Duplicates: 0  Warnings: 0
  
  mysql> DESC t1;
  +-------+---------------+------+-----+---------+-------+
  | Field | Type          | Null | Key | Default | Extra |
  +-------+---------------+------+-----+---------+-------+
  | c4    | varbinary(7)  | YES  |     | NULL    |       |
  | c14   | varbinary(12) | YES  |     | NULL    |       |
  +-------+---------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  
  mysql> DROP VIEW IF EXISTS v2;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  
  mysql> CREATE VIEW v2 AS SELECT CAST(- -4.46 AS BINARY) c4,CONVERT(- - -002.2600,BINARY) c14;
  Query OK, 0 rows affected (0.03 sec)
  
  mysql> DESC v2;
  +-------+---------------+------+-----+---------+-------+
  | Field | Type          | Null | Key | Default | Extra |
  +-------+---------------+------+-----+---------+-------+
  | c4    | varbinary(7)  | YES  |     | NULL    |       |
  | c14   | varbinary(10) | YES  |     | NULL    |       |
  +-------+---------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

14

/**/

Not supported.

Comments enclosed by /**/ are not supported in GaussDB statements.

15

xor

Supported, with differences.

The behavior of XOR in GaussDB is different from that in MySQL. The GaussDB optimizer performs constant optimization. As a result, the results that are constants are calculated first.

GaussDB:

m_db=#  SELECT 1 xor null xor pow(200, 2000000) FROM dual;
ERROR:  value out of range: overflow
m_db=# CREATE TABLE t1(a int, b int);
CREATE TABLE
m_db=# INSERT INTO t1 VALUES(2,2), (200, 2000000000);
INSERT 0 2
m_db=#
m_db=#
m_db=# SELECT 1 xor null xor pow(a, b) FROM t1;
 ?column?
----------


(2 rows)

MySQL:

mysql>  SELECT 1 xor null xor pow(200, 2000000) FROM dual;
+----------------------------------+
| 1 xor null xor pow(200, 2000000) |
+----------------------------------+
|                             NULL |
+----------------------------------+
1 row in set (0.00 sec)
ysql> CREATE TABLE t1(a int, b int);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES(2,2), (200, 2000000000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>  SELECT 1 xor null xor pow(a, b) FROM t1;
+--------------------------+
| 1 xor null xor pow(a, b) |
+--------------------------+
|                     NULL |
|                     NULL |
+--------------------------+
2 rows in set (0.00 sec)

16

IS NULL and IS NOT NULL

Supported, with differences.

In MySQL, these operators are inferior to logical operators, but they are prior to logical operators in GaussDB.

17

XOR, |, &, <, >, <=, >=, =, and !=

Supported, but the execution mechanism is different.

The execution mechanism of MySQL is as follows: After the left operand is executed, the system checks whether the result is empty and then determines whether to execute the right operand.

As for the execution mechanism of GaussDB, after the left and right operands are executed, the system checks whether the result is empty.

If the result of the left operand is empty and an error is reported during the execution of the right operand, MySQL does not report an error but directly returns an error. GaussDB reports an error during the execution.

Behavior in MySQL:

mysql> SELECT version();
+------------------+
| version()        |
+------------------+
| 5.7.44-debug-log |
+------------------+
1 row in set (0.00 sec)

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

mysql> CREATE TABLE data_type_table (
    -> MyBool BOOL,
    -> MyBinary BINARY(10),
    -> MyYear YEAR
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO data_type_table VALUES (TRUE, 0x1234567890, '2021');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT (MyBool % MyBinary) | (MyBool - MyYear) FROM data_type_table;
+-----------------------------------------+
| (MyBool % MyBinary) | (MyBool - MyYear) |
+-----------------------------------------+
|                                    NULL |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

Behavior in GaussDB:

m_db=# DROP TABLE IF EXISTS data_type_table;
DROP TABLE
m_db=# CREATE TABLE data_type_table (
m_db(# MyBool BOOL,
m_db(# MyBinary BINARY(10),
m_db(# MyYear YEAR
m_db(# );
CREATE TABLE
m_db=# INSERT INTO data_type_table VALUES (TRUE, 0x1234567890, '2021');
INSERT 0 1
m_db=# SELECT (MyBool % MyBinary) | (MyBool - MyYear) FROM data_type_table;
WARNING:  Truncated incorrect double value: '4Vx     '
CONTEXT:  referenced column: (MyBool % MyBinary) | (MyBool - MyYear)
WARNING:  division by zero
CONTEXT:  referenced column: (MyBool % MyBinary) | (MyBool - MyYear)
ERROR:  Bigint is out of range.
CONTEXT:  referenced column: (MyBool % MyBinary) | (MyBool - MyYear)

18

+, -, *, /, %, mod, div

Supported, with differences.

When the b "constant is embedded in the CREATE VIEW AS SELECT arithmetic operator ('+', '-', '*', '/', '%', 'mod', or 'div'), the return type in MySQL 5.7 may contain the unsigned identifier, but in GaussDB, the return type does not contain the unsigned identifier.

MySQL output:

mysql> CREATE VIEW v22 as SELECT b'101' / b'101' c22;
Query OK, 0 rows affected (0.00 sec)

mysql> DESC v22;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| c22   | decimal(5,4) unsigned | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

m_db=# CREATE VIEW v22 AS SELECT b'101' / b'101' c22;
CREATE VIEW
m_db=# DESC v22;
 Field |     Type     | Null | Key | Default | Extra 
-------+--------------+------+-----+---------+-------
 c22   | decimal(5,4) | YES  |     |         | 
(1 row)