Other Functions

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

View PDF

**Table 1** Other functions
No.	MySQL	GaussDB	Difference
1	DATABASE()	Supported.	-
2	UUID()	Supported.	-
3	UUID_SHORT()	Supported.	-
4	ANY_VALUE()	Supported, with differences.	The first data record in a group is uncertain, depending on the underlying operator. For example, for the same SQL statement, GaussDB returns 5 and 4, and MySQL returns 5 and 2. CREATE TABLE t1(a INT, b INT); INSERT INTO t1 VALUES(1, 5); INSERT INTO t1 VALUES(2, 4); INSERT INTO t1 VALUES(2, 2); CREATE TABLE t2(a INT, b INT); INSERT INTO t2 VALUES(2, 7); INSERT INTO t2 VALUES(3, 9); m_db=# SELECT ANY_VALUE(t1.b) FROM t1 LEFT JOIN t2 ON t1.a=t1.b GROUP BY t1.a; any_value ----------- 5 4 (2 rows) mysql> SELECT ANY_VALUE(t1.b) FROM t1 LEFT JOIN t2 ON t1.a=t1.b GROUP BY t1.a; +-----------------+ \| ANY_VALUE(t1.b) \| +-----------------+ \| 5 \| \| 2 \| +-----------------+ 2 rows in set (0.04 sec) DROP TABLE t1; DROP TABLE t2; When used with the DISTINCT keyword, if the columns to be sorted in ORDER BY are not included in the columns of the result set retrieved by the SELECT statement, the ANY_VALUE function cannot be used in GaussDB in case of errors. CREATE TABLE t1(a INT, b INT); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(1, 3); m_db=# SELECT DISTINCT a FROM t1 ORDER BY ANY_VALUE(b); ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in select list. LINE 1: SELECT DISTINCT a FROM t1 ORDER BY ANY_VALUE(b); ^ mysql> SELECT DISTINCT a FROM t1 ORDER BY ANY_VALUE(b); +------+ \| a \| +------+ \| 1 \| +------+ 1 row in set (0.00 sec) DROP TABLE t1; When an input parameter of the ANY_VALUE function is NULL or of the string type, the return value type is different from that in MySQL. For example: If the input parameter is NULL, the return value type in GaussDB is BIGINT and that in MySQL is binary. If the input parameter is of the VARCHAR type, the return value type is VARCHAR in GaussDB, but may be VARCHAR or TEXT in MySQL.
5	SLEEP()	Supported, with differences.	When the SLEEP function is being called, if you press Ctrl+C to end the process in advance, only "Cancel request sent" is displayed in GaussDB, which is different from the display information in MySQL. In addition to the above situation, when the SLEEP function is being called in other SQL statements, if you press Ctrl+C to end the statement in advance and the operation is obtained by the SLEEP function, no error is reported; if the value is obtained by other functions in the system, an error is reported. This behavior is different from that in MySQL. During the execution of the SLEEP function, if the process is ended by a related command (for example, SELECT PG_TERMINATE_BACKEN(xxx);), GaussDB reports an error, which is different from MySQL.
6	COLLATION()	Supported, with differences.	GaussDB supports only the collation in the utf8, utf8mb4, gbk, gb18030, and latin1 character sets.
7	FOUND_ROWS()	Supported.	-
8	ROW_COUNT()	Supported, with differences.	GaussDB does not have SIGNAL statements, but MySQL supports SIGNAL statements. In GaussDB, the connection parameter CLIENT_FOUND_ROWS does not exist. Even if this parameter is set, it does not take effect and the number of matched rows is returned instead of the number of affected rows. Therefore, the number of affected rows is returned in a unified manner. In MySQL, the number of affected rows is affected by this parameter. For each conflict triggered by INSERT ON DUPLICATE KEY UPDATE, 1 is returned in GaussDB, and 2 is returned in MySQL.
9	SYSTEM_USER()	Supported, with differences.	In MySQL, if skip-name-resolve is included in a configuration file, 127.0.0.1 or ::1 is not parsed as localhost, but GaussDB does not have related parameters and always parses 127.0.0.1 and ::1 as localhost.
10	DEFAULT()	Supported, with differences.	GaussDB supports column aliases, but MySQL does not.
11	BENCHMARK()	Supported, with differences.	The execution layer frameworks of MySQL and GaussDB are different. Therefore, the execution time of the same expression estimated by the function in MySQL and GaussDB is not comparable. This function is used only to compare the execution efficiency of different GaussDB expressions. If the execution takes a long time, when you press Ctrl+C on the client, the MySQL returns 0 and ends the task. The GaussDB displays "Cancel request sent" and ends the task.