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, but 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. 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 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)
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.
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.	If the number of consecutive negation times exceeds 1, the result type is different from that in MySQL.
14	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)

**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

**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

Parent topic: MySQL Compatibility in M-compatible Mode

Previous topic: Other Functions

Next topic: Character Sets

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot