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
commit without rand_seed setting is 9e800eda8602a09212f6c35b6f8b32c48c8df8e8
5f628add4562172371f88679053e913748686e8a is ok to push
although you can remove
set @@rand_seed1=31415926,@@rand_seed2=2718281828; |
from the test, as it doesn't do anything for UUID()
Thanks for addressing the questions. The fix seems legitimate to me. I don't think I'm allowed to approve the merge so please someone else for approval.
Helper functions Create_func_uuid::create_builder() and Create_func_uuid_short::create_builder() *currently set *lex::safe_to_cache_query to false (this is used to en/disable the main query result cache), but this is not enough to disable selects with item types like this from being merged into parents. lex::uncacheable() should be used to indicate that lex::current_select is both uncacheable and unmergeable.
For the release notes: The optimizer must not merge CTEs or derived tables that use the UUID() function. Failing to honor this limitation caused wrong query results.