Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
Unexpected results
Description
Summary
For queries executed by ENGINE=DuckDB, CONCAT skips NULL arguments instead of propagating NULL as MariaDB does in the ordinary (non-Oracle) SQL mode. The statement succeeds but returns an incorrect value. The same expression on an InnoDB table returns the expected NULL.
The issue was also observed as the fn_concat wrong-result probe in the published duckdb-in-oltp benchmark. This report concerns NULL propagation, not CONCAT_WS.
Reproduction
Prerequisite: the DuckDB storage engine is loaded. Use a fresh schema or unused table names.
SET SESSION sql_mode=''; |
SET NAMES utf8mb4; |
|
|
CREATE TABLE concat_null_i ( |
id INT PRIMARY KEY, |
s VARCHAR(20) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
CREATE TABLE concat_null_d ( |
id INT PRIMARY KEY, |
s VARCHAR(20) |
) ENGINE=DuckDB DEFAULT CHARSET=utf8mb4; |
|
|
INSERT INTO concat_null_i VALUES (1,NULL),(2,'a'),(3,''); |
INSERT INTO concat_null_d VALUES (1,NULL),(2,'a'),(3,''); |
|
|
SELECT id, CONCAT(s,'-x') AS value, |
CONCAT(s,'-x') IS NULL AS is_null |
FROM concat_null_i ORDER BY id; |
|
|
SELECT id, CONCAT(s,'-x') AS value, |
CONCAT(s,'-x') IS NULL AS is_null |
FROM concat_null_d ORDER BY id; |
Expected result (both engines)
id value is_null
|
1 NULL 1
|
2 a-x 0
|
3 -x 0
|
Actual result before the fix (DuckDB)
id value is_null
|
1 -x 0
|
2 a-x 0
|
3 -x 0
|
A constant-NULL expression must also propagate NULL when the query touches a DuckDB table:
SELECT CONCAT('a',NULL) AS value FROM concat_null_d WHERE id=2; |
Expected: NULL. Before the fix: 'a'. A table-free SELECT alone is not a reliable reproduction of storage-engine pushdown, because MariaDB can evaluate it itself.
Cause and impact
Native DuckDB concat intentionally treats NULL arguments as empty strings. MariaDB query pushdown currently inherits that behavior. This silently changes projected values and predicates; the same mismatch also applies to CONCAT expressions executed through direct DML pushdown.
Setting DEFAULT_NULL_HANDLING alone is insufficient for non-constant input vectors containing NULLs: row validity must be propagated by the execution callback as well.
Local fix and validation
An uncommitted local fix is prepared on branch concat-w-null, based on engine commit 1e7a38c542f90abf31c2749d0fee6847fb4654ab:
- Clone the native ConcatFun descriptor in runtime/duckdb_mysql_compat.cc, retaining native type-resolution and bind callbacks.
- Replace the concat overload at engine initialization with a strict-NULL vectorized wrapper.
- Return constant NULL for a constant-NULL argument; otherwise execute the native kernel once and combine result validity with all input validity masks.
- Keep a non-NULL fast path without row-wise validity traversal. Handle dictionary/sliced vectors and writable result validity without modifying inputs.
- No DuckDB core changes, SQL text rewrites or pushdown fallback are required. CONCAT_WS and the || operator are unchanged.
Regression files: mysql-test/duckdb/t/duckdb_concat_null.test and mysql-test/duckdb/r/duckdb_concat_null.result. They cover column/constant NULLs, empty strings, nested calls, prepared execution, WHERE, INSERT ... SELECT, UPDATE/DELETE and multiple chunks. These new test files are present locally but are not yet tracked/committed.
The new regression fails on the rebuilt unmodified engine and passes with the wrapper. With the fixed freshly built plugin, all 63 enabled MTR runs passed; one debug-only test was skipped and 24 tests remain disabled by the existing suite configuration. Temporary low-level checks also passed 80 strict-path vector cases covering flat/constant/dictionary/sliced inputs and repeated executor use.
Performance smoke tests: 12 alternating SQL microbenchmark cases gave wrapper/native median ratios 0.982-1.009. Additional explicitly flat-vector tests, including a repeat run, observed up to 2.7% overhead. These are local single-threaded measurements without CPU affinity, not a guarantee of zero regression.
Scope
The local fix deliberately does not implement Oracle-mode differences or binary/BLOB/charset compatibility. Changing concat behavior in run_in_duckdb() is accepted. This is a local candidate fix, not a claim that a release already contains it.
Benchmark source: https://evgeniypatlan.github.io/duckdb-in-oltp-bench/explore.html#a=duckdb-2.0.0&b=alisql&q=q01 (SQL probe fn_concat, MariaDB).