April 06, 2020 - 11:48 am UTC
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.
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 )