lh, April 06, 2020 - 11:48 am UTC
Hi
Sorry about not being able to give good explanation of the suspected error.
In our environment (12.2) the version using CTE is not outputting values from joined cte b2 to all rows.
..
16376
16487
16488
16608
16627
16674 16674
16911
16963
17000
17198
17331
..
To me this looks like that because sample(1) is nondeterministic and result is cte is not materialized, the joined cte:s do have different datasets.
I tried to use materialize hint, but I couldn't get it to work.
This is a very simplified test case of real world case
(managed to get it eventually work by using random -function to get sample data instead of sample() clause )
lh