Re: 12.2 bug for ANSI outer join syntax

Good Morning,

We upgraded to 12.2. in our development environment in June and have been extensively testing our code without encountering this issue. I ran this by our developers and they supplied me with code containing a nested sub-query using the left outer join. In the code we're using, the 'ON' component of the join occurs after the the actual query portion:

left outer join ( select ar.iuser_oid

          from assessment_result ar
          join skill on skill.oid = ar.skill_oid
          join dbstring on dbstring.oid = skill.module_name_oid and
mnemonic = 'ISIP_ARPM'
                                                  where ar.serial = 0 and
ar.period = :PERIOD {0}
                                                  having count(ar.oid) >=
:ARPMSKILLCOUNT
                                                  group by ar.iuser_oid
                                               ) arfiltered
                                               on arfiltered.iuser_oid =

There is a similar example on stackoverflow at: https://stackoverflow.com/questions/14571254/ora-01799-a-column-may-not-be-outer-joined-to-a-subquery

It seems that if the 'ON' clause is self-contained, the error does not appear. If I re-write our code to:

left outer join assessment_result ar
 on
(subquery)

the query returns the error code.

Just thought it was interesting.

Thanks for the heads-up!

On Mon, Aug 13, 2018 at 10:50 AM, Redacted sender Jay.Miller for DMARC < dmarc-noreply_at_freelists.org> wrote:

> The sql_id never shows up in v$sql despite displaying in OEM while it runs
> which makes things more difficult. In hindsight was a clue that something
> strange was going on. It started happening as soon as we migrated from 12.1
> to 12.2.
>
>
>
> I’m working on trimming the SQL down, so far I’ve eliminated about 60% of
> it and am still able to reproduce the issue. Will keep working on it later
> this afternoon. I’ll provide the EXPLAIN PLAN output once it’s small enough
> that be easily legible.
>
>
>
>
>
>
>
>
>
> Jay Miller
>
> Sr. Oracle DBA
>
> 201.369.8355
>
>
>
> *From:* Andy Sayer [mailto:andysayer_at_gmail.com]
> *Sent:* Friday, August 10, 2018 6:58 PM
> *To:* Miller, Jay
> *Cc:* dmarc-noreply_at_freelists.org; oracle-l_at_freelists.org
> *Subject:* Re: 12.2 bug for ANSI outer join syntax
>
>
>
> Sounds like a good path to try.
>
>
>
> Explain plan might be reliable enough to point out the issue, otherwise
> you could search for the appropriate sql_id in v$sql and call
> dbms_xplan.display_cursor with the sql_id explicitly passed to it.
>
>
>
> It’s possible your left join is conditional and you’re suffering from
> something like what I talk about in
>
> https://ctandrewsayer.wordpress.com/2018/06/06/conditional-outer-joins-
> forcing-nested-loops/
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__ctandrewsayer.wordpress.com_2018_06_06_conditional-2Douter-2Djoins-2Dforcing-2Dnested-2Dloops_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=wF2FXjoCVstv5FWb8tXTkNYblqCjZLiVty4VuSaEDXY&s=i8ylVFmrNk5ON3iPV-C-aFMwgraH2IvWuMSnH2x_a1w&e=>
> I.e a join that looks like it ought to be done by a hash join is really
> nested looped. It might be worse than that and the join predicate is not
> being pushed all the way to act against the table (and take advantage of
> indexes) But is going against the subquery as a non mergeable view instead.
>
>
>
> If this is something that has only started happening then maybe there’s
> some funny transformations occurring around lateral views. There were some
> bugs surrounding them originally, it’s possible that they’ve been fixed but
> at the cost of performance.
>
>
>
> If you share how the join is performed in the plan (along with the
> predicates section), then that could help an explanation.
>
>
>
> On Fri, 10 Aug 2018 at 21:50, <Jay.Miller_at_tdameritrade.com> wrote:
>
> I should have time to do some testing next week. I hope to prune the query
> down quite a bit to make it simpler/more legible. I do have both execution
> plans though I had use EXPLAIN PLAN to get the 12.2 one as the query never
> completed. They are similar but not identical.
>
>
>
> Fortunately since optimizer_features_enable is settable on the session
> level it will be easy to test.
>
>
>
> Jay Miller
>
> Sr. Oracle DBA
>
> 201.369.8355
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Andy Sayer
> *Sent:* Friday, August 10, 2018 3:06 PM
> *To:* dmarc-noreply_at_freelists.org
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: 12.2 bug for ANSI outer join syntax
>
>
>
> Left outer joins to subqueries are common, are you sure the scenario is as
> simple as that?
>
>
>
> Was there an execution plan? Perhaps the optimizer had correlated the
> subquery whereas that needs to not happen for your query performance to be
> acceptable (or vice versa).
>
>
>
> Have you got an end-to-end demo you can share?
>
>
>
> Regards,
>
> Andy
>
>

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