matrixorigin / matrixorigin/matrixone

[Bug]: COALESCE and IFNULL CHAR keys lose PAD SPACE semantics

Open
#28,024 0 comments 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
kind/bug needs-triage
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.