ApsaraDB RDS:Compatibility of DuckDB-based analytical instances

Rules for implicit type conversion in functions

Rules for implicit type conversion in comparisons

DuckDB-based analytical instances have stricter and more consistent rules for type conversion and comparison. However, this behavior may differ from that of MySQL in some scenarios:

• Implicit conversion from string to date: When a string is implicitly converted to a date, if the string format cannot be correctly parsed into a valid date value, the SQL query fails and reports an error.

• Comparison rules between integer types: When different integer types are compared, DuckDB-based analytical instances convert them to the integer type with the larger value range.

• Type conversion order for multiple-operand expressions: For multiple-operand expressions such as col1 in (col2, col3, col4, ...), col1 between col2 and col3, coalesce(col1, col2, col3, ...), type conversion is performed in sequence.

• Compatibility difference for the YEAR type: In DuckDB-based analytical instances, the YEAR type is converted to the INTEGER type for comparison, which may cause incompatibility with MySQL. 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-based analytical instance query result
  Empty set.

• String conversion rules for the Boolean type: DuckDB-based analytical instances support the conversion of six strings ('1', '0', 'yes', 'no', 'true', and 'false') to the BOOLEAN type. An attempt to convert any other string results in an error. MySQL converts '1' to true and all other strings to false. Therefore, the following SQL statement may return 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-based analytical instance query result
  +------+
  | id   |
  +------+
  |    1 |
  +------+

Numeric operations

• Comparisons of floating-point numbers may be incompatible. 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-based analytical instance query result
  +------+
  | id   |
  +------+
  | 1.24 |
  +------+

• The results of complex operations on floating-point numbers may be inconsistent due to floating-point errors.

• When an operation is performed between integer and DECIMAL types, the result must not exceed the value range of the type. Otherwise, an overflow error 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-based analytical instance query result
  ERROR 7577 (HY000): [DuckDB] Out of Range Error: Overflow in multiplication of INT8 (100 * 2)!.

Inconsistent results caused by collation rules

utf8mb4_0900_xx series collations are incompatible when comparing certain 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-based analytical instance query result
+----+
| id |
+----+
| !  |
| _  |
+----+

Handling of NULL values in vector subqueries with IN

In DuckDB-based analytical instances, the handling of NULL values in vector subqueries with IN may be incompatible with MySQL. 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-based analytical instance query result
+-----------------------------------------+
| (id, col1) in (select id, col1 from t2) |
+-----------------------------------------+
|                                    NULL |
|                                    NULL |
+-----------------------------------------+

For the data (2, 2), no matching item for the vector prefix exists in the IN clause. In this case, MySQL returns 0, whereas the DuckDB-based analytical instance returns NULL.

Aggregate functions

Numeric functions

Numeric functions do not support the BOOLEAN type.

String functions

MySQL DuckDB strictly distinguishes between binary strings (such as BLOB and VARBINARY) and strings (such as VARCHAR, TEXT, and JSON). Therefore, the following usage limits for string functions apply only to string inputs, not binary string inputs. Some functions that can accept binary strings as input include the following: CONCAT(), CONCAT_WS(), LENGTH(), MID(), OCTET_LENGTH(), REPEAT(), and TO_BASE64(). Unless otherwise specified, these functions have no usage restrictions.

Date functions

JSON functions

Window functions