Operators

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

View PDF

GaussDB is compatible with most MySQL operators, but there are some differences. If they are not listed, the operator behavior is the native behavior of GaussDB by default. Currently, there are statements that are not supported by MySQL but supported by GaussDB. You are advised not to use these statements.

Operator Differences

NULL values in ORDER BY are sorted in different ways. MySQL sorts NULL values first, while GaussDB sorts NULL values last. In GaussDB, nulls first and nulls last can be used to set the sorting sequence of NULL values.
If ORDER BY is used, the output sequence of GaussDB is the same as that of MySQL. Without ORDER BY, GaussDB does not guarantee that the results are ordered.
MySQL operators must use parentheses to strictly combine expressions. Otherwise, an error is reported. For example, SELECT 1 regexp ('12345' regexp '123').
The GaussDB M-compatible operators can be successfully executed without using parentheses to strictly combine expressions.
NULL values are displayed in different ways. MySQL displays a NULL value as "NULL". GaussDB displays a NULL value as empty.
MySQL output:
```
mysql> Select NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
```
GaussDB output:
```
m_db=# select NULL;
 ?column? 
----------

(1 row)
```
After the operator is executed, the column names are displayed in different ways. MySQL displays a NULL value as "NULL". GaussDB displays a NULL value as empty.
When character strings are being converted to the double type but there is an invalid one, the alarm is reported differently. MySQL reports an error when there is an invalid constant character string, but does not report an error for an invalid column character string. GaussDB reports an error in either situation.
The results returned by the comparison operator are different. For MySQL, 1 or 0 is returned. For GaussDB, t or f is returned.

**Table 1** Operators
No.	MySQL	GaussDB	Difference
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. NOTE: In GaussDB, when both factorial (!!) and NOT (!) are used, a space must be added between them. Otherwise, an error is reported. In GaussDB, when multiple NOT operations are required, use a space between exclamation marks (! !).
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) GaussDB output: 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)

**Table 2** Differences in operator combinations
Example of Operator Combination	MySQL	GaussDB	Description
SELECT 1 LIKE 3 & 1;	Not supported	Supported	The right operand of LIKE cannot be an expression consisting of bitwise operators.
SELECT 1 LIKE 1 +1;	Not supported	Supported	The right operand of LIKE cannot be an expression consisting of arithmetic operators.
SELECT 1 LIKE NOT 0;	Not supported	Supported	The right operand of LIKE can only be an expression consisting of unary operators (such as +, -, or ! but except NOT) or parentheses.
SELECT 1 BETWEEN 1 AND 2 BETWEEN 2 AND 3;	Right-to-left combination	Left-to-right combination	It is recommended that parentheses be added to specify the priority.
SELECT 2 BETWEEN 1=1 AND 3;	Not supported	Supported	The second operand of BETWEEN cannot be an expression consisting of comparison operators.
SELECT 0 LIKE 0 BETWEEN 1 AND 2;	Not supported	Supported	The first operand of BETWEEN cannot be an expression consisting of pattern matching operators.
SELECT 1 IN (1) BETWEEN 0 AND 3;	Not supported	Supported	The first operand of BETWEEN cannot be an expression consisting of IN operators.
SELECT 1 IN (1) IN (1);	Not supported	Supported	The second left operand of the IN expression cannot be an expression consisting of INs.
SELECT ! NOT 1;	Not supported	Supported	The operand of ! can only be an expression consisting of unary operators (such as +, -, or ! but except NOT) or parentheses.

NOTE:

Combinations of operators that are supported in GaussDB but not supported in MySQL are not recommended. You are advised to combine operators according to the rules in MySQL.

Index Differences

Currently, GaussDB supports only UB-tree and B-tree indexes.
For fuzzy match (LIKE operator), the default index created can be used in MySQL, but cannot be used in GaussDB. You need to use the following syntax to specify opclass to, for example, text_pattern_ops, so that LIKE operators can be used as indexes:
```
CREATE INDEX indexname ON tablename(col [opclass]);
```
In the B-tree/UB-tree index scenario, the original logic of the native GaussDB is retained. That is, index scan supports comparison of types in the same operator family, but does not support other index types currently.

In the operation scenarios involving index column type and constant type, the conditions that indexes of a WHERE clause are supported in GaussDB is different from those in MySQL, as shown in the following table. For example, GaussDB does not support indexes in the following statement:

create table t(_int int);
create index idx on t(_int) using BTREE;
select * from t where _int >  2.0;

NOTE:

In the operation scenarios involving index column type and constant type in the WHERE clause, you can use the cast function to convert the constant type to the column type for indexing.

select * from t where _int > cast(2.0 as signed);

**Table 3** Differences in index support
Index Column Type	Constant Type	GaussDB	MySQL
Integer	Integer	Yes	Yes
Floating-point	Floating-point	Yes	Yes
Fixed-point	Fixed-point	Yes	Yes
String	String	Yes	Yes
Binary	Binary	Yes	Yes
Time with date	Time with date	Yes	Yes
TIME	TIME	Yes	Yes
Time with date	Type that can be converted to time type with date (for example, integers such as 20231130)	Yes	Yes
Time with date	TIME	Yes	Yes
TIME	Constants that can be converted to the TIME type (for example, integers such as 203008)	Yes	Yes
Floating-point	Integer	Yes	Yes
Floating-point	Fixed-point	Yes	Yes
Floating-point	String	Yes	Yes
Floating-point	Binary	Yes	Yes
Floating-point	Time with date	Yes	Yes
Floating-point	TIME	Yes	Yes
Fixed-point	Integer	Yes	Yes
String	Time with date	Yes	No
String	TIME	Yes	No
Binary	String	Yes	Yes
Binary	Time with date	Yes	No
Binary	TIME	Yes	No
Integer	Floating-point	No	Yes
Integer	Fixed-point	No	Yes
Integer	String	No	Yes
Integer	Binary	No	Yes
Integer	Time with date	No	Yes
Integer	TIME	No	Yes
Fixed-point	Floating-point	No	Yes
Fixed-point	String	No	Yes
Fixed-point	Binary	No	Yes
Fixed-point	Time with date	No	Yes
Fixed-point	TIME	No	Yes
String	Binary	No	Yes
Time with date	Integer (that cannot be converted to the time type with date)	No	Yes
Time with date	Floating-point (that cannot be converted to the time type with date)	No	Yes
Time with date	Fixed-point (that cannot be converted to the time type with date)	No	Yes
TIME	Integer (that cannot be converted to the TIME type)	No	Yes
TIME	Character string (that cannot be converted to the TIME type)	No	Yes
TIME	Binary (that cannot be converted to the TIME type)	No	Yes
TIME	Time with date	No	Yes
SET/ENUM	String	No	Yes
SET/ENUM	Integer	No	Yes
SET/ENUM	Floating-point	No	Yes
SET/ENUM	Time	No	Yes

**Table 4** Whether index use is supported
Index Column Type	Constant Type	Use Index or Not	MySQL
String	Integer	No	No
String	Floating-point	No	No
String	Fixed-point	No	No
Binary	Integer	No	No
Binary	Floating-point	No	No
Binary	Fixed-point	No	No
Time with date	Character string (that cannot be converted to the time type with date)	No	No
Time with date	Binary (that cannot be converted to the time type with date)	No	No
TIME	Floating-point (that cannot be converted to the TIME type)	No	No
TIME	Fixed-point (that cannot be converted to the TIME type)	No	No