matrixorigin / matrixorigin/matrixone

[Compatibility]: projected SET values with an empty member cannot be sorted or grouped

Open
#28,222 0 comments 0 reactions 1 assignee Claimed by @XuPeng-SH View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

### Is there an existing issue for the same bug?

- [x] I have checked the existing issues.

No issue was found for the explicit projected-`SET` limitation.

### Branch Name

main

### Commit ID

`e7bb0572235ec4bac81eb098eb0ad8900f0065ab`

### Other Environment Information

- Local standalone MatrixOne
- Reference: MySQL 8.3.0

### Actual Behavior

`SET` permits an empty member. For `SET('', 'a')`, bitmap 0 and bitmap 1 both render as an empty string. MatrixOne can order the base-table values and can use them in a window order, but rejects equivalent queries after projection or aggregation:

```sql
CREATE TABLE amb(id INT PRIMARY KEY, v SET('', 'a'));
INSERT INTO amb VALUES (1,1), (2,0), (3,2);

SELECT id,v FROM (SELECT id,v FROM amb) d ORDER BY v,id;
-- MatrixOne: ERROR 20105: not supported: definition-order sorting of projected
-- ENUM/SET values with non-unique display labels or ambiguous SET display values
-- MySQL: (2,''), (1,''), (3,'a')

WITH c AS (SELECT id,v FROM amb)
SELECT id,v FROM c ORDER BY v,id;
-- MatrixOne: the same ERROR 20105
-- MySQL: (2,''), (1,''), (3,'a')

SELECT v,COUNT(*) FROM amb GROUP BY v ORDER BY v;
-- MatrixOne: the same ERROR 20105
-- MySQL: ('',2), ('a',1)

SELECT DISTINCT v FROM amb ORDER BY v;
-- MatrixOne: the same ERROR 20105
-- MySQL: (''), ('a')
```

The four rejected paths reproduced identically in three runs. Direct base-table ordering, a persisted view, a window order, and an explicit character cast remain executable, which narrows the gap to preservation of the raw `SET` ordering/grouping identity through selected query boundaries.

### Expected Behavior

Derived tables, CTEs, `GROUP BY`, and `DISTINCT` should preserve enough `SET` storage identity to execute the same valid queries that MySQL accepts. They should not reject a legal `SET` definition merely because two bitmaps share a display value.

### Steps to Reproduce

```sql
CREATE DATABASE ambiguous_set_repro;
USE ambiguous_set_repro;
CREATE TABLE amb(id INT PRIMARY KEY, v SET('', 'a'));
INSERT INTO amb VALUES (1,1), (2,0), (3,2);
SELECT id,v FROM (SELECT id,v FROM amb) d ORDER BY v,id;
WITH c AS (SELECT id,v FROM amb) SELECT id,v FROM c ORDER BY v,id;
SELECT v,COUNT(*) FROM amb GROUP BY v ORDER BY v;
SELECT DISTINCT v FROM amb ORDER BY v;
```

### Additional information

`mysql_special_types.go` intentionally returns this `not supported` error when projected display values cannot be inverted unambiguously to their raw ordinal/bitmap. This is therefore a defined implementation limitation rather than a transient execution failure.

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.