ApsaraDB RDS:Compatibility of DuckDB analytic instances

Implicit conversion in functions

The tables below show which conversions are performed automatically when passing values to functions.

Legend: / = no conversion needed (same type) · ✔ = implicit conversion supported · ✖ = not supported; use CAST or CONVERT

Type definitions used in these tables:

• Regular string types: CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, JSON, SET, ENUM

• Binary string types: BINARY, VARBINARY, BIT, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

• Converting an integer type to another integer type with a smaller value range is not supported.

Implicit conversion in comparisons

DuckDB analytic instances apply stricter, more consistent rules for type conversion in comparisons. The following behaviors differ from standard MySQL.

String to date conversion

When a string is implicitly converted to a date type for comparison, the query fails with an error if the string cannot be parsed into a valid date value. In MySQL, invalid date strings may silently return unexpected results.

Integer type comparison

When two different integer types are compared, both are converted to the integer type with the larger value range.

Multi-element expression evaluation order

In 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 from left to right.

YEAR type comparison

DuckDB converts the YEAR type to INTEGER for comparison. MySQL treats YEAR values using two-digit year logic for values between 00 and 99, so the same query can return different rows.

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

-- MySQL result: two-digit year 70–90 maps to 1970–1990, so 1980 matches
+------+
| id   |
+------+
| 1980 |
+------+

-- DuckDB analytic instance result: compares 1980 BETWEEN 70 AND 90 as integers, no match
Empty set.

Boolean type string conversion

DuckDB converts the strings '1', '0', 'yes', 'no', 'true', and 'false' to BOOLEAN. Any other string returns an error. MySQL converts '1' to true and all other strings to false, so queries using non-numeric boolean strings produce different results.

CREATE TABLE t1 (id INT PRIMARY KEY);
INSERT INTO t1 VALUES (1);
SELECT id FROM t1 WHERE 'true';

-- MySQL result: 'true' is not '1', so it is treated as false and returns no rows
Empty set

-- DuckDB analytic instance result: 'true' is a valid boolean string, so rows are returned
+------+
| id   |
+------+
|    1 |
+------+

Comparison result types

When data of different types is compared, DuckDB converts both operands to a common type before performing the comparison. For unsupported comparisons (✖), the query returns an error.

Type definitions: same as the conversion tables above.

Floating-point comparison

DuckDB's strict floating-point semantics can produce different comparison results. The root cause is that MySQL promotes FLOAT columns to higher precision during comparisons, so the stored value appears to exceed the literal threshold. DuckDB evaluates at the declared precision.

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 result: the stored FLOAT 1.23 rounds up in comparison,
-- so it is treated as greater than the literal 1.23
+------+
| id   |
+------+
| 1.23 |
| 1.24 |
+------+

-- DuckDB analytic instance result: comparison uses declared FLOAT precision
+------+
| id   |
+------+
| 1.24 |
+------+

Complex compound operations on floating-point numbers may also differ because of intermediate rounding.

Integer and DECIMAL arithmetic overflow

When performing arithmetic between integer and DECIMAL types, the result must not exceed the value range of the column type. MySQL promotes the result to a wider type automatically. DuckDB enforces the declared type, so an overflow causes the query to fail.

To prevent this error, cast the column to a wider type before the operation.

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

-- MySQL result: result is promoted to a wider integer type
+--------+
| id * 2 |
+--------+
|    200 |
+--------+

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

-- Fix: cast to a wider type first
SELECT CAST(id AS INT) * 2 FROM t1;

Collation differences for symbol characters

Collations in the utf8mb4_0900_xx series sort some symbol characters differently than MySQL. This is because DuckDB uses Unicode sort order, while MySQL's utf8mb4_0900_xx collations apply language-specific weights that place certain symbols in a different position.

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

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

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

NULL handling in vector subqueries with IN

DuckDB handles NULL values differently in vector subqueries with IN. When a subquery contains NULL values, MySQL may return 0 for rows with no matching prefix. DuckDB returns NULL because the presence of a NULL value in the subquery makes the result indeterminate — consistent with SQL three-valued logic.

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 result
+-----------------------------------------+
| (id, col1) in (select id, col1 from t2) |
+-----------------------------------------+
|                                    NULL |
|                                       0 |
+-----------------------------------------+

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

For the row (2, 2), there is no matching vector prefix in t2. MySQL returns 0. DuckDB returns NULL because the NULL in t2 means a match cannot be ruled out.

Aggregate functions

Numeric functions

Numeric functions do not support the BOOLEAN type.

String functions

DuckDB analytic instances strictly distinguish binary string types (BLOB, VARBINARY) from regular string types (VARCHAR, TEXT, JSON). String functions accept only regular strings unless noted otherwise.

Date functions

Date functions do not accept strings as input. Pass values using explicit type conversion with CAST, or use typed literal syntax.

-- For string columns in a table
SELECT ADDDATE(CAST(varchar_col AS DATE), INTERVAL 1 DAY) FROM t1;
SELECT ADDDATE(CAST(varchar_col AS TIME), INTERVAL 1 DAY) FROM t1;
SELECT ADDDATE(CAST(varchar_col AS DATETIME), INTERVAL 1 DAY) FROM t1;

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

JSON functions

Window functions

All standard window functions are fully supported.