Bug #104562 UUID function not executed in implicit subquery, but execute in explicit subquer
Submitted: 6 Aug 2021 16:55 Modified: 6 Aug 2021 17:51
Reporter: Chen Woods Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.26 OS:Debian (10.9)
Assigned to: CPU Architecture:x86
Tags: subquery, UUID

[6 Aug 2021 16:55] Chen Woods
Description:
When the subquery is executed implicitly, `NULL` is generated by `UUID` function, however a valid UUID is generated if the `UUID` function is in a explicit subquery.

## What I did ##:

```
CREATE TABLE `upsert_test` (
  `ID` int unsigned DEFAULT NULL COMMENT 'id;',
  `UUID` varchar(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```

```
INSERT INTO upsert_test
SELECT 1 AS ID, UUID() AS "UUID"
FROM upsert_test
WHERE ID=1
HAVING COUNT(*)=0;
```

```
INSERT INTO upsert_test
SELECT * FROM (
SELECT 2 AS ID, UUID() AS "UUID"
FROM upsert_test
WHERE ID=2
HAVING COUNT(*)=0
) t;
```

the difference between the two `insert` commands is that the subquery in the first one is implicit, and explicit in the second one.

## What I expect ##:

```
mysql> select * from upsert_test;
+------+--------------------------------------+
| ID   | UUID                                 |
+------+--------------------------------------+
|    1 | a1e99a2c-f6d6-11eb-ae8c-0242ac110002 |
|    2 | a809f11c-f6d6-11eb-ae8c-0242ac110002 |
+------+--------------------------------------+
```

## What actually happened ##

```
mysql> select * from upsert_test;
+------+--------------------------------------+
| ID   | UUID                                 |
+------+--------------------------------------+
|    1 | NULL                                 |
|    2 | 0befdc79-f6d6-11eb-ae8c-0242ac110002 |
+------+--------------------------------------+
```

and we can see that, the uuid column of the first row inserted is `NULL`.

How to repeat:

```
CREATE TABLE `upsert_test` (
  `ID` int unsigned DEFAULT NULL COMMENT 'id;',
  `UUID` varchar(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```

```
INSERT INTO upsert_test
SELECT 1 AS ID, UUID() AS "UUID"
FROM upsert_test
WHERE ID=1
HAVING COUNT(*)=0;
```

```
INSERT INTO upsert_test
SELECT * FROM (
SELECT 2 AS ID, UUID() AS "UUID"
FROM upsert_test
WHERE ID=2
HAVING COUNT(*)=0
) t;
```
[6 Aug 2021 17:51] MySQL Verification Team
Thank you for the bug report.