Compatibility of DuckDB analytic instances - ApsaraDB RDS

This topic describes the compatibility of ApsaraDB RDS for MySQL instances that use the DuckDB analytic engine.

Supported data types

Type	MySQL data type	Compatibility notes
Numeric types	`BOOLEAN`	Compatible
	`TINYINT`, `TINYINT UNSIGNED`	Compatible
	`SMALLINT`, `SMALLINT UNSIGNED`	Compatible
	`INT`, `INTEGER`, `INT UNSIGNED`, `INTEGER UNSIGNED`	Compatible
	`BIGINT`, `BIGINT UNSIGNED`	Compatible
	`FLOAT`	Compatible
	`DOUBLE`	Compatible
	`DECIMAL(m,d)`	`m` is the maximum precision of the numeric value. If `m<=38`, the type is fully compatible. If `m>38`, the data is converted to the `DOUBLE` type for storage, which may cause a loss of precision.
Character types	`CHAR`, `VARCHAR`	Only UTF-8 character sets and collations are supported.
	`TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`	Compatible
	`JSON`	Compatible
	`SET`	Compatible
	`ENUM`	Compatible
Binary character types	`BINARY`, `VARBINARY`	Compatible
	`BIT`	Compatible
	`TINYBLOB`, `BLOB`, `MEDIUMBLOB`, `LONGBLOB`	Compatible
Time types	`YEAR`	Compatible
	`TIME`	DuckDB analytic instances support the range from `'00:00:00'` to `'23:59:59'` in the `'HH:MM:SS'` format. MySQL, on the other hand, supports the range `'-838:59:59'~'838:59:59'`. Because of this difference in value ranges, query results may be inconsistent if data is outside the range supported by a DuckDB analytic instance.
	`DATE`	DuckDB analytic instances support the range from `'0001-01-01'` to `'9999-12-31'` in the `'YYYY-MM-DD'` format. MySQL supports the range from `'0000-00-00'` to `'0001-01-01'`. Because the supported ranges differ, data outside the range of DuckDB analytic instances may cause inconsistent query results.
	`DATETIME`	DuckDB analytic instances support the range from `'0001-01-01 00:00:00.000000' UTC` to `'9999-12-31 00:00:00.999999' UTC` in the `'YYYY-MM-DD HH:MM:SS.MS'` format. MySQL supports the range from `'0000-00-00 00:00:00'` to `'0001-01-01 00:00:00'`. Because the supported ranges differ, data outside the range of DuckDB analytic instances may cause inconsistent query results.
	`TIMESTAMP`	Compatible
Spatial data types	`GEOMETRY`, `POINT`, `LINESTRING`, `POLYGON`, `MULTIPOINT`, `MULTILINESTRING`, `MULTIPOLYGON`, `GEOMETRYCOLLECTION`	Incompatible

SELECT statement limits

Comments
Comments that start with # are not supported. For example:
```
SELECT * FROM t1 #comment;
;
```
Character set conversion
Character set conversions of any kind are not supported, regardless of whether the target character set is supported. For example:
```
SELECT convert(id using gbk) FROM t1;
SELECT cast(id AS CHAR CHARACTER SET utf8mb4) FROM t1;
```
JOIN syntax
Using explicit and implicit JOINs in the same SQL statement is not supported. For example:
```
SELECT * FROM t1 JOIN (t2, t3);
```

Interval units

The following interval units are not supported.

YEAR_MONTH, DAY_HOUR, HOUR_MINUTE, DAY_MINUTE, HOUR_SECOND, DAY_SECOND, SECOND_MICROSECOND, HOUR_MICROSECOND, DAY_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SQL_TSI_HOUR

Interval expressions

In an `Interval expr unit` expression, if `expr` is a non-constant expression, you must enclose it in parentheses to define its scope. For example:

# The following SQL statement is not supported.
SELECT '2018-12-31 23:59:59' + INTERVAL -1 SECOND; 
# Rewrite the statement as follows:
SELECT '2018-12-31 23:59:59' + INTERVAL (-1) SECOND;

Alias definitions

The alias syntax expr 'alias' and expr "alias" are not supported. For example:

# Not supported
SELECT 1 '1';
SELECT 1 "1";
# You can rewrite the statements as follows:
SELECT 1 AS '1';
SELECT 1 AS "1";
SELECT 1 AS `1`;
SELECT 1 `1`;
# The preceding SQL statements are equivalent.

If an alias is a keyword, you must use `AS` or backticks. For example:

SELECT id time FROM t1;

# Rewrite the statement as one of the following:
SELECT id AS time FROM t1;
SELECT id `time` FROM t1;
SELECT id AS `time` FROM t1;
SELECT id AS 'time' FROM t1;
SELECT id AS "time" FROM t1;

Subqueries
Equality comparisons with non-scalar subqueries are not supported. For example:
```
SELECT * FROM t1 WHERE (id, col1) = (SELECT id, col1 FROM t1);
```
Data type conversion
Explicit conversions to BINARY(num), SIGNED, or UNSIGNED data types are not supported. For example:
```
SELECT CAST('abc' AS binary(1));
SELECT CAST(1 AS SIGNED);
SELECT CAST(1 AS UNSIGNED);
```
Column modifiers
The `Binary` modifier for column names is not supported. For example:
```
SELECT binary id FROM t1;
```

Complex arithmetic expressions

For complex arithmetic expressions, use parentheses to specify the order of operations and prevent parsing errors.

# The symbols (!=-) cannot be correctly parsed in DuckDB analytic instances.
SELECT 1 !=-1;
# We recommend that you rewrite the statement as follows:
SELECT 1 != (-1);

# The symbols (--) cannot be correctly parsed in DuckDB analytic instances.
SELECT --1;
# We recommend that you rewrite the statement as follows:
SELECT -(-1);

Type conversion issues

To ensure execution efficiency, DuckDB analytic instances use a strict type system. During query execution, the system automatically performs type conversions based on the context. This process is called implicit type conversion. For scenarios where implicit conversion is not possible, you must use the CAST or CONVERT function to explicitly specify the type conversion. This ensures that the query executes correctly.

Implicit type conversion rules in functions

Note

The `/` symbol indicates that no implicit conversion is involved. The `✔️` symbol indicates that implicit conversion is supported. The `✖️` symbol indicates that implicit conversion is not supported.
Regular string types refer to the MySQL types CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, JSON, SET, and ENUM.
Binary string types refer to the MySQL types BINARY, VARBINARY, BIT, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.
Converting integer types with a larger range to integer types with a smaller range is not supported.

Conversion to basic scalar types

Source type		Implicit conversion to basic scalar types
Source type		String literal	Numeric literal
Basic scalar	String literal	/	✖️
Basic scalar	Numeric literal	✖️	/
Numeric	BOOLEAN	✖️	✖️
	Integer types	✖️	✖️
	FLOAT	✖️	✖️
	DOUBLE	✖️	✖️
	DECIMAL	✖️	✖️
String	Regular string	✖️	✖️
String	Binary string	✖️	✖️
Date and time	YEAR	✖️	✖️
	DATE	✖️	✖️
	TIME	✖️	✖️
	DATETIME	✖️	✖️
	TIMESTAMP	✖️	✖️

Conversion to numeric types

Source type		Implicit conversion to numeric types
Source type		BOOLEAN	Integer types	FLOAT	DOUBLE	DECIMAL
Basic scalar	String literal	✖️	✖️	✖️	✔️	✖️
Basic scalar	Numeric literal	✖️	✔️	✔️	✔️	✔️
Numeric	BOOLEAN	/	✖️	✖️	✖️	✖️
	Integer types	✖️	/	✔️	✔️	✔️
	FLOAT	✖️	✔️: Only conversion to BIGINT is supported.	/	✔️	✖️
	DOUBLE	✖️	✔️: Only conversion to BIGINT is supported.	✖️	/	✖️
	DECIMAL	✖️	✔️	✔️	✔️	/
String	Regular string	✖️	✖️	✖️	✔️	✖️
String	Binary string	✖️	✖️	✖️	✖️	✖️
Date and time	YEAR	✖️	✔️	✔️	✔️	✔️
	DATE	✖️	✖️	✖️	✔️	✖️
	TIME	✖️	✖️	✖️	✔️	✖️
	DATETIME	✖️	✖️	✖️	✔️	✖️
	TIMESTAMP	✖️	✖️	✖️	✔️	✖️

Conversion to string types

Source type		Implicit conversion to string types
Source type		Regular string	Binary string
Basic scalar	String literal	✔️	✖️
Basic scalar	Numeric literal	✖️	✖️
Numeric	BOOLEAN	✖️	✖️
	Integer types	✔️	✖️
	FLOAT	✔️	✖️
	DOUBLE	✔️	✖️
	DECIMAL	✔️	✖️
String	Regular string	/	✖️
String	Binary string	✖️	/
Date and time	YEAR	✔️	✖️
	DATE	✔️	✖️
	TIME	✔️	✖️
	DATETIME	✔️	✖️
	TIMESTAMP	✔️	✖️

Conversion to date and time types

Source type		Implicit conversion to date and time types
Source type		YEAR	DATE	TIME	DATETIME	TIMESTAMP
Basic scalar	String literal	✖️	✖️	✖️	✖️	✖️
Basic scalar	Numeric literal	✔️	✖️	✖️	✖️	✖️
Numeric	BOOLEAN	✖️	✖️	✖️	✖️	✖️
	Integer types	✖️	✖️	✖️	✖️	✖️
	FLOAT	✖️	✖️	✖️	✖️	✖️
	DOUBLE	✖️	✖️	✖️	✖️	✖️
	DECIMAL	✖️	✖️	✖️	✖️	✖️
String	Regular string	✖️	✖️	✖️	✖️	✖️
String	Binary string	✖️	✖️	✖️	✖️	✖️
Date and time	YEAR	/	✖️	✖️	✖️	✖️
	DATE	✖️	/	✖️	✔️	✔️
	TIME	✖️	✖️	/	✔️	✔️
	DATETIME	✖️	✖️	✖️	/	✔️
	TIMESTAMP	✖️	✖️	✖️	✔️	/

Implicit conversion rules for comparisons

DuckDB analytic instances have stricter and more consistent rules for type conversion and comparison. However, the behavior may differ from MySQL in some scenarios. The differences are as follows:

Implicit conversion from string to date: When a string is implicitly converted to a date, the SQL query fails and returns an error if the string format cannot be parsed into a valid date value.
Comparison rules between integer types: When different integer types are compared, DuckDB analytic instances convert them to the integer type with the larger value range.
Type conversion order for multi-element expressions: For multi-element expressions such as col1 in (col2, col3, col4, ...), col1 between col2 and col3, and coalesce(col1, col2, col3, ...), type conversion is performed sequentially.

Compatibility differences for the YEAR type: In DuckDB analytic instances, the YEAR type is converted to the INTEGER type for comparison. This behavior may cause incompatibilities with MySQL. For example:

CREATE TABLE t1 (id YEAR PRIMARY KEY);
INSERT INTO t1 VALUES (1980);
SELECT * FROM t1 WHERE id BETWEEN 70 AND 90;

# MySQL query result
+------+
| id   |
+------+
| 1980 |
+------+

# DuckDB analytic instance query result
Empty set.

String conversion rules for the Boolean type: DuckDB analytic instances support converting the strings '1', '0', 'yes', 'no', 'true', and 'false' to the BOOLEAN type. An attempt to convert other strings returns an error. In contrast, MySQL converts '1' to true and all other strings to false. Therefore, the following SQL statement may produce inconsistent results:
```
CREATE TABLE t1 (id INT PRIMARY KEY);
INSERT INTO t1 VALUES (1);
SELECT id FROM t1 WHERE 'true';

# MySQL query result
Empty set

# DuckDB analytic instance query result
+------+
| id   |
+------+
|    1 |
+------+
```

Note

When data of different types is compared, the system converts the data to the same type according to the rules in the following tables before it performs the comparison. For unsupported comparisons (✖️), an error is returned.
Regular string types refer to the MySQL types CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, JSON, SET, and ENUM.
Binary string types refer to the MySQL types BINARY, VARBINARY, BIT, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

Comparison with basic scalar types

Source type		Comparison with basic scalar types
Source type		String literal	Numeric literal
Basic scalar	String literal	String	Numeric literal
Basic scalar	Numeric literal	Numeric literal	Numeric type with the larger range
Numeric	BOOLEAN	BOOLEAN	Numeric type with the larger range
	Integer types	Integer types	Numeric type with the larger range
	FLOAT	FLOAT	Numeric type with the larger range
	DOUBLE	DOUBLE	Numeric type with the larger range
	DECIMAL	DECIMAL	Numeric type with the larger range
String	Regular string	Regular string	Numeric literal
String	Binary string	Binary string	✖️
Date and time	YEAR	YEAR	Numeric type with the larger range
	DATE	DATETIME	✖️
	TIME	TIME	✖️
	DATETIME	DATETIME	✖️
	TIMESTAMP	TIMESTAMP	✖️

Comparison with numeric types

Source type		Comparison with numeric types
Source type		BOOLEAN	Integer types	FLOAT	DOUBLE	DECIMAL
Basic scalar	String literal	BOOLEAN	Integer types	FLOAT	DOUBLE	DECIMAL
Basic scalar	Numeric literal	Numeric type with the larger range	Numeric type with the larger range	Numeric type with the larger range	Numeric type with the larger range	Numeric type with the larger range
Numeric	BOOLEAN	BOOLEAN	Integer types	FLOAT (Inequality comparison ✖️)	DOUBLE (Inequality comparison ✖️)	DECIMAL (Inequality comparison ✖️)
	Integer types	Integer types	Integer types	FLOAT	DOUBLE	DECIMAL
	FLOAT	FLOAT (Inequality comparison ✖️)	FLOAT	FLOAT	DOUBLE	FLOAT
	DOUBLE	DOUBLE (Inequality comparison ✖️)	DOUBLE	DOUBLE	DOUBLE	DOUBLE
	DECIMAL	DECIMAL (Inequality comparison ✖️)	DECIMAL	FLOAT	DOUBLE	DECIMAL
String	Regular string	BOOLEAN	Integer types	FLOAT	DOUBLE	DECIMAL
String	Binary string	✖️	✖️	✖️	✖️	✖️
Date and time	YEAR	INTEGER	Integer type with the larger range	FLOAT	DOUBLE	DECIMAL
	DATE	✖️	✖️	✖️	DOUBLE	✖️
	TIME	✖️	✖️	✖️	DOUBLE	✖️
	DATETIME	✖️	✖️	✖️	DOUBLE	✖️
	TIMESTAMP	✖️	✖️	✖️	DOUBLE	✖️

Comparison with string types

Source type		Comparison with string types
Source type		Regular string	Binary string
Basic scalar	String literal	Regular string	Binary string
Basic scalar	Numeric literal	Numeric literal	✖️
Numeric	BOOLEAN	BOOLEAN	✖️
	Integer types	Integer types	✖️
	FLOAT	FLOAT	✖️
	DOUBLE	DOUBLE	✖️
	DECIMAL	DECIMAL	✖️
String	Regular string	Regular string	Binary string
String	Binary string	Binary string	Binary string
Date and time	YEAR	INTEGER	✖️
	DATE	DATE	✖️
	TIME	TIME	✖️
	DATETIME	DATETIME	✖️
	TIMESTAMP	TIMESTAMP	✖️

Comparison with date and time types

Source type		Comparison with date and time types
Source type		YEAR	DATE	TIME	DATETIME	TIMESTAMP
Basic scalar	String literal	YEAR	DATETIME	TIME	DATETIME	TIMESTAMP
Basic scalar	Numeric literal	Numeric type with the larger range	✖️	✖️	✖️	✖️
Numeric	BOOLEAN	INTEGER	✖️	✖️	✖️	✖️
	Integer types	Integer type with the larger range	✖️	✖️	✖️	✖️
	FLOAT	FLOAT	✖️	✖️	✖️	✖️
	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE
	DECIMAL	DECIMAL	✖️	✖️	✖️	✖️
String	Regular string	INTEGER	DATE	TIME	DATETIME	TIMESTAMP
String	Binary string	✖️	✖️	✖️	✖️	✖️
Date and time	YEAR	INTEGER	✖️	✖️	✖️	✖️
	DATE	✖️	DATE	✖️	DATETIME	TIMESTAMP
	TIME	✖️	✖️	TIME	✖️	✖️
	DATETIME	✖️	DATETIME	✖️	DATETIME	DATETIME
	TIMESTAMP	✖️	TIMESTAMP	✖️	DATETIME	TIMESTAMP

Potential inconsistencies in query results

Numeric operations

Incompatibilities exist in the comparison of floating-point numbers. For example:

CREATE TABLE t1 (id FLOAT PRIMARY KEY);
INSERT INTO t1 VALUES (1.22), (1.23), (1.24);
SELECT * FROM t1 WHERE t1.id > 1.23;

# MySQL query result
+------+
| id   |
+------+
| 1.23 |
| 1.24 |
+------+

# DuckDB analytic instance query result
+------+
| id   |
+------+
| 1.24 |
+------+

The results of complex compound operations on floating-point numbers may be inconsistent because of floating-point errors.

When you perform operations between integer and DECIMAL types, the result cannot exceed the value range of the type. Otherwise, an overflow may cause the execution to fail.

CREATE TABLE t1 (id TINYINT PRIMARY KEY);
INSERT INTO t1 VALUES (100);
SELECT id * 2 FROM t1;

# MySQL query result
+--------+
| id * 2 |
+--------+
|    200 |
+--------+

# DuckDB analytic instance query result
ERROR 7577 (HY000): [DuckDB] Out of Range Error: Overflow in multiplication of INT8 (100 * 2)!.

Inconsistencies caused by collation rules

Collations in the utf8mb4_0900_xx series are incompatible when comparing some symbol characters. For example:

CREATE TABLE t1 ( id varchar(20) COLLATE utf8mb4_0900_ai_ci PRIMARY KEY );
INSERT INTO t1 VALUES ('!'), ('_');
SELECT * FROM t1 ORDER BY id;

# MySQL query result
+----+
| id |
+----+
| _  |
| !  |
+----+

# DuckDB analytic instance query result
+----+
| id |
+----+
| !  |
| _  |
+----+

Handling of NULL values in vector subqueries with IN

In DuckDB analytic instances, there are incompatibilities in how vector subqueries with `IN` handle NULL values. For example:

CREATE TABLE t1 (id INT PRIMARY KEY, col1 INT);
INSERT INTO t1 VALUES (1, 1), (2, 2);
CREATE TABLE t2 (id INT PRIMARY KEY, col1 INT);
INSERT INTO t2 VALUES (1, NULL);

select (id, col1) in (select id, col1 from t2) from t1;

# MySQL query result
+-----------------------------------------+
| (id, col1) in (select id, col1 from t2) |
+-----------------------------------------+
|                                    NULL |
|                                       0 |
+-----------------------------------------+

# DuckDB analytic instance query result
+-----------------------------------------+
| (id, col1) in (select id, col1 from t2) |
+-----------------------------------------+
|                                    NULL |
|                                    NULL |
+-----------------------------------------+

For the data (2, 2), there is no matching vector prefix after `IN`. MySQL returns 0, whereas the DuckDB analytic instance returns NULL.

Function limits

Aggregate functions

Function name	Supported	Limits
`AVG`	Yes	No limits
`BIT_AND`	Yes	String, DECIMAL, and date types are not supported.
`BIT_OR`	Yes	String, DECIMAL, and date types are not supported.
`BIT_XOR`	Yes	String, DECIMAL, and date types are not supported.
`COUNT`	Yes	No limits
`COUNT(DISTINCT)`	Yes	No limits
`GROUP_CONCAT`	Yes	Multi-column `GROUP_CONCAT` and `SEPARATOR` are not supported.
`JSON_ARRAYAGG`	No	N/A
`JSON_OBJECTAGG`	No	N/A
`MAX`	Yes	No limits
`MIN`	Yes	No limits
`STD`	Yes	No limits
`STDDEV`	Yes	No limits
`STDDEV_POP`	Yes	No limits
`STDDEV_SAMP`	Yes	No limits
`SUM`	Yes	No limits
`VAR_POP`	Yes	No limits
`VAR_SAMP`	Yes	No limits
`VARIANCE`	Yes	No limits

Numeric functions

Numeric functions do not support the BOOLEAN type.

Function name	Supported	Limits
`%`	Yes	No limits
`MOD`	Yes	No limits
`*`	Yes	No limits
`+`	Yes	No limits
`-`	Yes	No limits
`/`	Yes	No limits
`ABS()`	Yes	No limits
`ACOS()`	Yes	No limits
`ASIN()`	Yes	No limits
`ATAN()`	Yes	`ATAN(y, x)` is not supported.
`ATAN2()`	No	N/A
`CEIL()`	Yes	No limits
`CEILING()`	Yes	No limits
`CONV()`	No	N/A
`COS()`	Yes	No limits
`COT()`	Yes	No limits
`CRC32()`	No	N/A
`DEGREES()`	Yes	No limits
`DIV`	Yes	No limits
`EXP()`	Yes	No limits
`FLOOR()`	Yes	No limits
`LN()`	Yes	No limits
`LOG()`	Yes	No limits
`LOG10()`	Yes	No limits
`LOG2()`	Yes	No limits
`MOD()`	Yes	No limits
`PI()`	Yes	No limits
`POW()`	Yes	No limits
`POWER()`	Yes	No limits
`RADIANS()`	Yes	No limits
`RAND()`	Yes	No limits
`ROUND()`	Yes	No limits
`SIGN()`	Yes	No limits
`SIN()`	Yes	No limits
`SQRT()`	Yes	No limits
`TAN()`	Yes	No limits
`TRUNCATE()`	No	N/A

String functions

DuckDB analytic instances strictly distinguish between binary strings, such as BLOB and VARBINARY, and regular strings, such as VARCHAR, TEXT, and JSON. Therefore, the following string functions accept only regular strings as input, not binary strings. For functions that can accept binary strings, special notes are provided.

Function name	Supported	Limits
`ASCII()`	Yes	No limits
`BIN()`	Yes	In MySQL, `BIN('')` returns NULL. In DuckDB analytic instances, it returns `'0'`.
`BIT_LENGTH()`	Yes	No limits
`CHAR()`	No	N/A
`CHAR_LENGTH()`	Yes	No limits
`CHARACTER_LENGTH()`	Yes	No limits
`CONCAT()`	Yes: Can accept binary strings as input.	No limits
`CONCAT_WS()`	Yes: Can accept binary strings as input.	No limits
`ELT()`	No	N/A
`EXPORT_SET()`	No	N/A
`FIELD()`	No	N/A
`FIND_IN_SET()`	Yes	The first parameter of this function must be a character type. Non-character types may produce results inconsistent with MySQL.
`FORMAT()`	No	N/A
`FROM_BASE64()`	Yes	This function uses Base64 decoding rules. If decoding fails, an error is returned.
`HEX()`	Yes	No limits
`INSERT()`	Yes	No limits
`INSTR()`	Yes	No limits
`LCASE()`	Yes	No limits
`LEFT()`	Yes	No limits
`LENGTH()`	Yes: Can accept binary strings as input.	No limits
`LIKE`	Yes	`LIKE` is not affected by collation rules.
`LOAD_FILE()`	No	N/A
`LOCATE()`	Yes	No limits
`LOWER()`	Yes	No limits
`LPAD()`	Yes	No limits
`LTRIM()`	Yes	No limits
`MAKE_SET()`	No	N/A
`MATCH()`	No	N/A
`MID()`	Yes: Can accept binary strings as input.	No limits
`NOT LIKE`	Yes	The `NOT LIKE` function is not affected by collation rules.
`NOT REGEXP`	No	N/A
`OCT()`	Yes	In MySQL, `OCT('')` returns NULL. In DuckDB analytic instances, it returns `'0'`.
`OCTET_LENGTH()`	Yes: Can accept binary strings as input.	No limits
`ORD()`	Yes	No limits
`POSITION()`	Yes	No limits
`QUOTE()`	No	N/A
`REGEXP`	No	N/A
`REGEXP_INSTR()`	No	N/A
`REGEXP_LIKE()`	No	N/A
`REGEXP_REPLACE()`	No	N/A
`REGEXP_SUBSTR()`	No	N/A
`REPEAT()`	Yes: Can accept binary strings as input.	No limits
`REPLACE()`	Yes	No limits
`REVERSE()`	Yes	No limits
`RIGHT()`	Yes	No limits
`RLIKE`	No	N/A
`RPAD()`	Yes	No limits
`RTRIM()`	Yes	No limits
`SOUNDEX()`	No	N/A
`SOUNDEX LIKE`	No	N/A
`SPACE()`	Yes	No limits
`STRCMP()`	Yes	No limits
`SUBSTR()`	Yes	The syntax `SUBSTR(str FROM pos)` and `SUBSTR(str FROM pos FOR len)` is not supported.
`SUBSTRING()`	Yes	The syntax `SUBSTRING(str FROM pos)` and `SUBSTRING(str FROM pos FOR len)` is not supported.
`SUBSTRING_INDEX()`	Yes	No limits
`TO_BASE64()`	Yes: Can accept binary strings as input.	This function returns a binary string.
`TRIM()`	Yes	No limits
`UCASE()`	Yes	No limits
`UNHEX()`	Yes	This function returns a binary string. An error is returned if a non-hexadecimal digit is encountered.
`UPPER()`	Yes	No limits
`WEIGHT_STRING()`	No	N/A

Date functions

Date functions in DuckDB analytic instances do not currently accept strings as input. You must first perform an explicit type conversion. For example:

For string columns in a table:

SELECT ADDDATE(CAST(varchar_test as TIME), INTERVAL 1 DAY) FROM t1;
SELECT ADDDATE(CAST(varchar_test as DATE), INTERVAL 1 DAY) FROM t1;
SELECT ADDDATE(CAST(varchar_test as DATETIME), INTERVAL 1 DAY) FROM t1;

For string literal constants:

SELECT ADDDATE(TIME '12:00:00', INTERVAL 1 DAY) FROM t1;
SELECT ADDDATE(DATE '2020-01-01', INTERVAL 1 DAY) FROM t1;
SELECT ADDDATE(TIMESTAMP '2020-01-01 12:00:00', INTERVAL 1 DAY) FROM t1;

Function name	Supported	Limits
`ADDDATE()`	Yes	When the first parameter of the `ADDDATE` function is a TIME type, such as in `ADDDATE(TIME '12:00:00', INTERVAL 1 HOUR)`, the function performs an implicit type conversion from TIME to TIMESTAMP for the calculation. To avoid this implicit type conversion, use the `DATE_ADD` function.
`ADDTIME()`	Yes	If the return value exceeds the range of the time data type for the DuckDB analytic instance, the results will be inconsistent.
`CONVERT_TZ()`	Yes	No limits
`CURRENT_DATE()，CURRENT_DATE`	Yes	No limits
`CURRENT_TIME()，CURRENT_TIME`	Yes	No limits
`CURRENT_TIMESTAMP()，CURRENT_TIMESTAMP`	Yes	No limits
`CURTIME()`	Yes	No limits
`DATE()`	Yes	No limits
`DATE_ADD()`	Yes	When the first parameter of the `DATE_ADD` function is a TIME type, such as in `DATE_ADD(TIME '12:00:00', INTERVAL 1 HOUR)`, the function does not perform an implicit type conversion from TIME to TIMESTAMP.
`DATE_FORMAT()`	Yes	The `%X`, `%V`, and `%u` format specifiers are not supported.
`DATE_SUB()`	Yes	Same as `DATE_ADD()`.
`DATEDIFF()`	Yes	No limits
`DAY()`	Yes	No limits
`DAYNAME()`	Yes	No limits
`DAYOFMONTH()`	Yes	No limits
`DAYOFWEEK()`	Yes	No limits
`DAYOFYEAR()`	Yes	No limits
`EXTRACT()`	Yes	No limits
`FROM_DAYS()`	Yes	No limits
`FROM_UNIXTIME()`	Yes	No limits
`GET_FORMAT()`	No	Not applicable
`HOUR()`	Yes	No limits
`LAST_DAY`	Yes	No limits
`LOCALTIME()，LOCALTIME`	Yes	No limits
`LOCALTIMESTAMP，LOCALTIMESTAMP()`	Yes	No limits
`MAKEDATE()`	Yes	No limits
`MAKETIME()`	Yes	No limits
`MICROSECOND()`	Yes	No limits
`MINUTE()`	Yes	No limits
`MONTH()`	Yes	No limits
`MONTHNAME()`	Yes	No limits
`NOW()`	Yes	No limits
`PERIOD_ADD()`	Yes	Years greater than 9999 are not supported.
`PERIOD_DIFF()`	Yes	Years greater than 9999 are not supported.
`QUARTER()`	Yes	No limits
`SEC_TO_TIME()`	Yes	If the return value exceeds the range of the time data type for the DuckDB analytic instance, the results will be inconsistent.
`SECOND()`	Yes	No limits
`STR_TO_DATE()`	Yes	The `%X`, `%V`, and `%u` format specifiers are not supported. If a `format specifier` is not correctly matched, the function returns NULL.
`SUBDATE()`	Yes	Same as `ADDDATE()`.
`SUBTIME()`	Yes	If the return value exceeds the range of the time data type for the DuckDB analytic instance, the results will be inconsistent.
`SYSDATE()`	Yes	No limits
`TIME()`	No	Not applicable
`TIME_FORMAT()`	Yes	No limits
`TIME_TO_SEC()`	Yes	Inputs in the DAY TIME format are not supported, for example, `select time_to_sec('1 12:00:00');`.
`TIMEDIFF()`	No	Not applicable
`TIMESTAMP()`	No	Not applicable
`TIMESTAMPADD()`	Yes	No limits
`TIMESTAMPDIFF()`	Yes	No limits
`TO_DAYS()`	Yes	No limits
`TO_SECONDS()`	Yes	No limits
`UNIX_TIMESTAMP()`	Yes	No limits
`UTC_DATE()`	Yes	No limits
`UTC_TIME()`	Yes	No limits
`UTC_TIMESTAMP()`	Yes	No limits
`WEEK()`	Yes	No limits
`WEEKDAY()`	Yes	No limits
`WEEKOFYEAR()`	Yes	No limits
`YEAR()`	Yes	No limits
`YEARWEEK()`	Yes	No limits

JSON functions

Function name	Supported	Limits
`JSON_ARRAY()`	Yes	No limits
`JSON_ARRAY_APPEND()`	No	Not applicable
`JSON_ARRAY_INSERT()`	No	Not applicable
`JSON_CONTAINS()`	No	Not applicable
`JSON_CONTAINS_PATH()`	No	Not applicable
`JSON_DEPTH()`	Yes	No limits
`JSON_EXTRACT()`	Yes	No limits
`JSON_INSERT()`	No	Not applicable
`JSON_KEYS()`	Yes	No limits
`JSON_LENGTH()`	Yes	No limits
`JSON_MERGE()`	No	Not applicable
`JSON_MERGE_PATCH()`	Yes	Supports merging only two JSON objects. The field order of the merged JSON object may differ from that in MySQL.
`JSON_MERGE_PRESERVE()`	No	Not applicable
`JSON_OBJECT()`	Yes	No limits
`JSON_OVERLAPS()`	Yes	No limits
`JSON_PRETTY()`	Yes	No limits
`JSON_QUOTE()`	Yes	No limits
`JSON_REMOVE()`	No	Not applicable
`JSON_REPLACE()`	No	Not applicable
`JSON_SCHEMA_VALID()`	No	Not applicable
`JSON_SCHEMA_VALIDATION_REPORT()`	No	Not applicable
`JSON_SEARCH()`	No	Not applicable
`JSON_SET()`	No	Not applicable
`JSON_STORAGE_FREE()`	No	Not applicable
`JSON_STORAGE_SIZE()`	No	Not applicable
`JSON_TABLE()`	No	Not applicable
`JSON_TYPE()`	No	Not applicable
`JSON_UNQUOTE()`	No	Not applicable
`JSON_VALID()`	Yes	No limits
`JSON_VALUE()`	No	Not applicable
`MEMBER OF()`	No	Not applicable

Window functions

Function name	Supported	Limits
`CUME_DIST()`	Yes	No limits
`DENSE_RANK()`	Yes	No limits
`FIRST_VALUE()`	Yes	No limits
`LAG()`	Yes	No limits
`LAST_VALUE()`	Yes	No limits
`LEAD()`	Yes	No limits
`NTH_VALUE()`	Yes	No limits
`NTILE()`	Yes	No limits
`PERCENT_RANK()`	Yes	No limits
`RANK()`	Yes	No limits
`ROW_NUMBER()`	Yes	No limits

Other limitations

DuckDB analytic instances do not support view queries.