matrixorigin / matrixorigin/matrixone
[Compatibility]: UUID() generates UUIDv7 instead of MySQL UUIDv1
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### MatrixOne version
Latest `main` at `f72ca9efbeb3a7c673701e4a135cc6670c33cfde` (`VERSION()` reports `8.0.30-MatrixOne-v1.3.0`).
MySQL control: 8.3.0.
### What is different
MatrixOne's parameterless `UUID()` generates UUID version 7 values, while MySQL's parameterless `UUID()` generates UUID version 1 values.
This affects more than the textual UUID value. MySQL documents the two-argument `UUID_TO_BIN(uuid, 1)` optimization in terms of the version-1 layout produced by `UUID()`, so applications that use the MySQL `UUID()` + `UUID_TO_BIN(..., 1)` pattern receive a different UUID layout in MatrixOne.
### Reproduction
```sql
CREATE DATABASE uuid_compat;
USE uuid_compat;
CREATE TABLE t(seq INT PRIMARY KEY, u VARCHAR(36) NOT NULL);
INSERT INTO t VALUES
(1, UUID()), (2, UUID()), (3, UUID()), (4, UUID()),
(5, UUID()), (6, UUID()), (7, UUID()), (8, UUID());
SELECT SUBSTRING(u, 15, 1) AS version_nibble, COUNT(*) AS n
FROM t
GROUP BY SUBSTRING(u, 15, 1)
ORDER BY version_nibble;
```
MatrixOne returns:
```text
version_nibble | n
7 | 8
```
MySQL 8.3 returns:
```text
version_nibble | n
1 | 8
```
The comparison was repeated three times with 32 generated values per engine per run. MatrixOne produced 96/96 v7 values and MySQL produced 96/96 v1 values.
### Controls
MatrixOne's version-specific functions behave consistently, which isolates the difference to the parameterless `UUID()` mapping:
```sql
SELECT
UUID_EXTRACT_VERSION(UUID()),
UUID_EXTRACT_VERSION(UUID_V1()),
UUID_EXTRACT_VERSION(UUID_V7());
```
MatrixOne returns `7, 1, 7` in all three runs. `IS_UUID()`, `UUID_TO_BIN()`, and the matching `BIN_TO_UUID()` round trip also succeed for all generated values. The same `UUID()`/`UUID_V1()`/`UUID_V7()` version result is preserved through SQL `PREPARE`/`EXECUTE`.
### Why this is a compatibility issue
- [MySQL 8.4 reference](https://dev.mysql.com/doc/refman/8.4/en/miscellaneous-functions.html) states that `UUID()` returns UUID version 1, and that `UUID_TO_BIN(..., 1)` time-part swapping assumes version-1 values such as those generated by `UUID()`.
- [MatrixOne's current UUID documentation](https://github.com/matrixorigin/matrixorigin.io/blob/main/docs/MatrixOne/Reference/Functions-and-Operators/Other/uuid.md) declares `mysql_compat: full`, lists no MySQL differences, and also states that `UUID()` conforms to version 1.
- The implementation intentionally routes `UUID()` to v7: [`builtInUUID` calls `uuid.NewV7`](https://github.com/matrixorigin/matrixone/blob/f72ca9efbeb3a7c673701e4a135cc6670c33cfde/pkg/sql/plan/function/func_builtin.go#L1897-L1899), and the function registry describes `uuid_v7` as an alias of plain `uuid()` ([source](https://github.com/matrixorigin/matrixone/blob/f72ca9efbeb3a7c673701e4a135cc6670c33cfde/pkg/sql/plan/function/function_id.go#L807-L808)).
### Expected behavior
For MySQL compatibility, parameterless `UUID()` should retain MySQL's version-1 semantics, while `UUID_V7()` remains the explicit version-7 generator. If the v7 mapping is an intentional incompatible MatrixOne extension, it should be represented as an explicit compatibility difference rather than documented as full MySQL compatibility.
Contributor guide
Assessment
This issue has not been assessed yet.