matrixorigin / matrixorigin/matrixone
[Compatibility]: projected SET values with an empty member cannot be sorted or grouped
- 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
Assessment
This issue has not been assessed yet.