LATERAL Inline Views / GROUP BY And Scalar Subquery Caching
I’ll be short into an introduction and will go straight to the point.
Oracle 12c introduced new SQL clause LATERAL which enables us to refer tables in the FROM clause which appear left from the lateral inline view.
Most probably the bug it’s not related to LATERAL clause and it is related to the optimzer “grouping and scalar subquery caching” mechanism in 12c.
At first my assumptions were that it is related to LATERAL clause because I was able to reproduce the “bad” result set, but still LATERAL can lead the optimizer to buggy execution plan which most likely at this stage will produce wrong results if it’s used with scalar subquery caching.
The examples bellow will definitely give you an example what happens and be careful when implementing scalar subquery caching.
Also this is only 12c behavior:
As workaround you can disable 12c adaptive features (which I wouldn’t recommend) or add ORDER BY clause which will eliminate this buggy behavior.
Basically you can do something like
Pretty nice for better syntax and less code.
However these day I’ve seen something strange with LATERAL views and so far I couldn’t fully understand why it is happening.
Here is the tricky test case:
Next I want to run the following sql:
I’ve added the hints in order reproduce the “bad” plan, but In the database where I’ve discovered this behavior the statement was executed without any hints and the optimizer still was using the “bad” plan. Also I needed couple of hours to successfully create a test case (I had to derive the bad behavior from a big sql 🙂 )
Sofar so good, I’ve got correct results.
But look what happens when I create an index on T3 join column T3.ID
The plan clearly shows when this behavior happens, but I couldn’t understand what causes this (maybe some kind of logical bug with lateral views and scalar subquery caching) ?
And this is happening only if we select column different from the join column in this case (T3.ID2).
It’s kind of specific case but it’s definitely wrong if I’m not missing something.
Also, if we remove the aggregate function the optimizer jumps from HASH OUTER JOIN to HASH JOIN producing correct results.