Re: Optimizer "enhancements" in 19.9?

Mikhail,

I'm looking forward to seeing your write-up of this.

Bragging rights:
my option (a)

    "Oracle Corp. has realised that there's a boundary condition with this pattern that could produce wrong results and blocks the transformation" So now we're into:

    "and then to watch newer versions to see if the restriction is gradually relaxed"

Interestingly if you take the sample query and write the subquery inline (for testing purposes):

select

        /*+ dynamic_sampling(0) */
        t1.c1,
        t1.c2,
        (
        select  /*+ unnest */
                count(t2.c1)
        from    t2
        where   t2.c1 = t1.c1
        ) c3
from
        t1

Oracle 19.3 refuses to unnest it, with the error

   "SU: bypassed: Scalar subquery has null-mutating select item." So it looks as if there's already some code that recognises the problem in a different way.

Regards
Jonathan Lewis

On Wed, 17 Feb 2021 at 19:14, Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> The interesting thing now is to see if it's possible to come up with an

>> example where the bypass is necessary to avoid getting wrong results
>> (because that's presumable why it's been introduced) and then to watch
>> newer versions to see if the restriction is gradually relaxed.
>>
>

>
>
>

--
http://www.freelists.org/webpage/oracle-l