Details
Description
When compiling/running relatively simple queries that construct JSON objects it seems like the query engine is re-computing function calls instead of using the actual value when extracting JSON data.
For example, given the query:
WITH RECURSIVE iter AS |
(
|
SELECT 1 AS 'i' |
UNION |
SELECT i + 1 |
FROM iter |
WHERE i < 10 |
),
|
json_data AS |
(
|
SELECT |
JSON_OBJECT(
|
'UUID', UUID() |
) AS 'Data' |
FROM iter |
)
|
SELECT
|
JSON_EXTRACT(jd.`Data`, '$.UUID') AS 'Extracted', |
jd.`Data`
|
FROM json_data jd |
you would expect the `Extracted` value to be the same as the value that was computed in the `Data` JSON_OBJECT, but it is actually different.
With this query I was able to get the same results for iter generating numbers up to 1,000,000, although in another query that consisted of more joins then I did not see this behavior; in that case the extracted UUID was the same as the originally computed UUID.
Attachments
Issue Links
- relates to
-
MDEV-32637 Implement native UUID7 function
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Attachment | Screenshot 2023-10-25 130737.png [ 72360 ] |
| Attachment | Screenshot 2023-10-25 130737.png [ 72361 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] |
| Summary | JSON_EXTRACT recomputes value for small data sets | UUID() should be treated as stochastic |
| Summary | UUID() should be treated as stochastic | UUID() should be treated as stochastic for the purposes of forcing query materialization |
| Fix Version/s | 10.4 [ 22408 ] |
| Labels | CTE JSON | CTE optimizer |
| Fix Version/s | 10.4.32 [ 29300 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Priority | Major [ 3 ] | Blocker [ 1 ] |
| Priority | Blocker [ 1 ] | Critical [ 2 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.0 [ 28320 ] | |
| Fix Version/s | 11.1 [ 28549 ] | |
| Fix Version/s | 11.2 [ 28603 ] | |
| Fix Version/s | 10.4.32 [ 29300 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.10 [ 27530 ] |
| Fix Version/s | 11.3 [ 28565 ] | |
| Fix Version/s | 11.4 [ 29301 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 11.5 [ 29506 ] |
| Fix Version/s | 11.0 [ 28320 ] | |
| Fix Version/s | 11.3 [ 28565 ] |
| Link |
This issue relates to |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Oleg Smirnov [ JIRAUSER50405 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Oleg Smirnov [ JIRAUSER50405 ] | Rex Johnston [ JIRAUSER52533 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Link |
This issue blocks |
| Link |
This issue blocks |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Fix Version/s | 10.5.26 [ 29832 ] | |
| Fix Version/s | 10.6.19 [ 29833 ] | |
| Fix Version/s | 10.11.9 [ 29834 ] | |
| Fix Version/s | 11.1.6 [ 29835 ] | |
| Fix Version/s | 11.2.5 [ 29836 ] | |
| Fix Version/s | 11.4.3 [ 29837 ] | |
| Fix Version/s | 11.5.2 [ 29838 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.1 [ 28549 ] | |
| Fix Version/s | 11.2 [ 28603 ] | |
| Fix Version/s | 11.4 [ 29301 ] | |
| Fix Version/s | 11.5 [ 29506 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |