The issue here has nothing to do with JSON_EXTRACT(), rather query materialization.

If you force the optimizer to materialize the cte, rather than merge it, you will get consistent results. For example

WITH 

json_data AS 

(

	SELECT

		JSON_OBJECT(

			'UUID', UUID()

		) AS 'Data'

	FROM seq_1_to_10 where rand() > 0

)

SELECT 

	JSON_EXTRACT(jd.`Data`, '$.UUID') AS 'Extracted',

	jd.`Data`

FROM json_data jd

will produce consistent UUIDs, but if the CTE can be merged into the parent query, then you will get 2 calls to UUID() rather than one, which is expected to produce 2 different values.

I'm not sure this is a bug.

Very interesting.

I agree that this doesn't appear to be a bug with JSON.

One could argue that a call to UUID should cause the CTE to be materialized instead of merging it because that function is non-deterministic.

UUID() should be treated as stochastic (the same as RAND()) for the purposes of forcing query materialization.

For example, the following query

WITH 

cte AS 

(

	SELECT UUID() as r

	FROM seq_1_to_10

)

SELECT 

	r as r1,

	r

FROM cte

produces data where r1 != r

whereas

WITH 

cte AS 

(

	SELECT RAND() as r

	FROM seq_1_to_10

)

SELECT 

	r as r1,

	r

FROM cte

produces data where r1==r

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.

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.

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()

commit without rand_seed setting is 9e800eda8602a09212f6c35b6f8b32c48c8df8e8

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.