matrixorigin / matrixorigin/matrixone
[Bug]: COALESCE and IFNULL CHAR keys lose PAD SPACE semantics
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
`COALESCE` and `IFNULL` over a `CHAR` column lose PAD SPACE key semantics in `GROUP BY`, `DISTINCT`, and window keys. The same expression still compares equal to the unpadded literal, so logically equivalent values take different code paths depending on the SQL operator.
## Environment
- Branch: `main`
- Commit: `3ec2c2524f88cb3365b9bc464d02458c9a77798a`
- Deployment: local official-main build, 1 CN / 1 TN / 1 Log
## Steps to reproduce
```sql
CREATE DATABASE char_expression_key_probe;
USE char_expression_key_probe;
CREATE TABLE t (id INT PRIMARY KEY, ch CHAR(8));
INSERT INTO t VALUES (1, 'a'), (2, 'a '), (3, 'b');
-- Normal comparison recognizes ids 1 and 2 as equal.
SELECT id, COALESCE(ch, '') = 'a' AS eq_a FROM t ORDER BY id;
-- But COALESCE/IFNULL keys split the same two values.
SELECT COALESCE(ch, '') AS x, COUNT(*) FROM t GROUP BY x;
SELECT DISTINCT IFNULL(ch, '') AS x FROM t;
SELECT id, COUNT(*) OVER (PARTITION BY COALESCE(ch, '')) AS pc,
RANK() OVER (ORDER BY COALESCE(ch, '')) AS rk
FROM t ORDER BY id;
-- Explicit promotion is a control.
SELECT CAST(COALESCE(ch, '') AS VARCHAR(8)) AS x, COUNT(*) FROM t GROUP BY x;
```
## Actual behavior
- `COALESCE(ch, '') = 'a'` returns `1` for ids 1 and 2.
- `GROUP BY COALESCE(ch, '')` returns three groups, and `DISTINCT COALESCE(ch, '')` returns three rows. `IFNULL` has the same three-way split.
- The window query returns `(id,pc,rk)` as `(1,1,1)`, `(2,1,2)`, `(3,1,3)`; rows 1 and 2 are different partitions and different peers.
- Explicit `VARCHAR` promotion returns two groups and merges ids 1 and 2 in the window control.
## Expected behavior
For expressions that choose and return the original `CHAR` value, key operations should preserve the same PAD SPACE relation as ordinary equality. Since ids 1 and 2 compare equal under the expression, they should be one group, one DISTINCT value, one window partition, and one ordered peer group.
## Stability and controls
- Reproducer: 3/3 identical results on current `main`.
- Affected expressions: `COALESCE(ch, '')` and `IFNULL(ch, '')` split in GROUP BY, DISTINCT, and windows in every run.
- `CASE WHEN id > 0 THEN ch ELSE '' END` has the same split in window keys; its GROUP BY/DISTINCT result is a separate planner path and remains two groups.
- Controls: ordinary equality returns true for both padded variants; explicit `CAST(... AS VARCHAR(8))` returns two groups and the merged window result.
- Failure atomicity: read-only queries left the three source rows unchanged.
## Code analysis
The planner records PAD SPACE provenance on value-selecting expressions only when the result type is `VARCHAR` or `TEXT` (`pkg/sql/plan/base_binder.go`, `BindFuncExprImplByPlanExpr`). These expressions are typed as `CHAR` after resolution (`INFORMATION_SCHEMA.COLUMNS` for a view exposes `CHAR`), so the physical-key construction used by GROUP BY/DISTINCT and the window-key normalization do not receive durable PAD SPACE metadata after expression materialization. This is a focused hypothesis supported by the explicit-VARCHAR control; the repair should preserve the metadata or derive a canonical comparison key without changing visible CHAR values.
## Related
- #28023: direct `CHAR` window keys are a separate current-main bug. This issue concerns value-selecting `CHAR` expressions and also affects GROUP BY/DISTINCT.
- #25240: open CHAR retrieval compatibility issue; this report concerns incorrect relational key semantics after expression evaluation.
## Regression coverage
After a fix, add planner/runtime coverage for `COALESCE`, `IFNULL`, and `CASE` over padded `CHAR` values across equality, GROUP BY, DISTINCT, UNION/INTERSECT and window `PARTITION BY`/`ORDER BY`; include an explicit-VARCHAR control. No test file is added in this exploration pass.
Contributor guide
Assessment
This issue has not been assessed yet.