matrixorigin / matrixorigin/matrixone

[Compatibility]: UUID() generates UUIDv7 instead of MySQL UUIDv1

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

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.