Description:
When nondeterministicfunctions are used in derived tables, the drived_merge configuration will result in incorrect query results
but user variables is fixed (#104918)
How to repeat:
with a as (
select sysdate() as val
from (values row(1)) a)
select
a.val,sleep(1),a.val
from a;
with a as (
select uuid() as val
from (values row(1)) a)
select
a.val,a.val
from a;
with a as (
select rand() as val
from (values row(1)) a)
select
a.val,a.val
from a;
The two val values in the above query are not equal
but after adding set_var hint, the result is correct.
like this:
with a as (
select uuid() as val
from (values row(1)) a)
select /*+ set_var(optimizer_switch='derived_merge=off')*/
a.val,a.val
from a;
I think the official should prevent drived_merge when non deterministic functions are included in the derived table, rather than requiring developers to write hints
Suggested fix:
Do not automatically merge when there are nondeterministicfunctions in the derived table
