14 Replies Latest reply on Apr 22, 2014 3:22 AM by user4678078

    Outer Join Bug in Oracle 12c?

    user6748224

      In previous versions of Oracle (and on SQL Server and DB2), the query below returns 1 record.  In Oracle 12c, no records are returned.  Is this a known issue in Oracle 12c? CREATE TABLE FOO (id NUMBER, inherit NUMBER(1), parentId NUMBER); INSERT INTO FOO VALUES (1, 0, null); COMMIT; SELECT FOO.id, PARENT.id, GRANDPARENT.id FROM FOO LEFT JOIN FOO PARENT ON FOO.parentId = PARENT.id AND FOO.inherit = 1 LEFT JOIN FOO GRANDPARENT ON PARENT.parentId = GRANDPARENT.id AND PARENT.inherit = 1

        • 1. Re: Outer Join Bug in Oracle 12c?
          Martin Preiss

          I don't know if the bug is known - but it looks disturbing:

          -- 11.2.0.1

          Plan hash value: 852994604

          ----------------------------------------------------------------------------

          | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

          ----------------------------------------------------------------------------

          |   0 | SELECT STATEMENT    |      |     1 |    91 |     7  (15)| 00:00:01 |

          |*  1 |  HASH JOIN OUTER    |      |     1 |    91 |     7  (15)| 00:00:01 |

          |*  2 |   HASH JOIN OUTER   |      |     1 |    78 |     5  (20)| 00:00:01 |

          |   3 |    TABLE ACCESS FULL| FOO  |     1 |    39 |     2   (0)| 00:00:01 |

          |   4 |    TABLE ACCESS FULL| FOO  |     1 |    39 |     2   (0)| 00:00:01 |

          |   5 |   TABLE ACCESS FULL | FOO  |     1 |    13 |     2   (0)| 00:00:01 |

          ----------------------------------------------------------------------------

           

          Predicate Information (identified by operation id):

          ---------------------------------------------------

             1 - access("PARENT"."PARENTID"="GRANDPARENT"."ID"(+) AND

                        "PARENT"."INHERIT"=CASE  WHEN ("GRANDPARENT"."ID"(+) IS NOT NULL) THEN

                        1 ELSE 1 END )

             2 - access("FOO"."PARENTID"="PARENT"."ID"(+) AND

                        "FOO"."INHERIT"=CASE  WHEN ("PARENT"."ID"(+) IS NOT NULL) THEN 1 ELSE 1

                        END )

           

          -- 12.1.0.1

          Plan hash value: 689884292

          ----------------------------------------------------------------------------

          | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

          ----------------------------------------------------------------------------

          |   0 | SELECT STATEMENT    |      |     1 |    91 |     9   (0)| 00:00:01 |

          |*  1 |  HASH JOIN OUTER    |      |     1 |    91 |     9   (0)| 00:00:01 |

          |*  2 |   HASH JOIN         |      |     1 |    78 |     6   (0)| 00:00:01 |

          |*  3 |    TABLE ACCESS FULL| FOO  |     1 |    39 |     3   (0)| 00:00:01 |

          |   4 |    TABLE ACCESS FULL| FOO  |     1 |    39 |     3   (0)| 00:00:01 |

          |   5 |   TABLE ACCESS FULL | FOO  |     1 |    13 |     3   (0)| 00:00:01 |

          ----------------------------------------------------------------------------

           

          Predicate Information (identified by operation id):

          ---------------------------------------------------

             1 - access("PARENT"."PARENTID"="GRANDPARENT"."ID"(+) AND

                        "PARENT"."INHERIT"=CASE  WHEN ("GRANDPARENT"."ID"(+) IS NOT NULL) THEN

                        1 ELSE 1 END )

             2 - access("FOO"."PARENTID"="PARENT"."ID")

             3 - filter("FOO"."INHERIT"=1)

          In 12.1 the filter in step 3 seems to throw away everything - maybe a CBO trace sheds some light on this. But at least the solution in 12.1 is cheaper ...

          • 2. Re: Outer Join Bug in Oracle 12c?
            user10857924

            also thinking what is the point in evaluting join condition (FOO.inherit = 1) on the driver table? As Left join has to output all rows regardless of join condition match or not?

            Also per your plan from  12c optimizer pushing that predicate as filter on Foo result in no row.

             

            Thanks Martin!! I hope jonathan can jump in to answer

            • 3. Re: Outer Join Bug in Oracle 12c?
              Brian Bontrager

              To me this looks like a bug fixed in 12c.

               

              In the original, I would expect the clause FOO.INHERIT=1 to behave like 12c does.

               

              In LEFT JOIN FOO PARENT ON FOO.parentId = PARENT.id AND FOO.inherit = 1 FOO.inherit refers to FOO, which is on the left side of the join and should be filtered.  There are no rows where FOO.inherit = 1, so I would expect no rows, even with the outer join.  I personally would be suspicious of this SQL anyway, since I try to practice anything in the join clause should be filtering columns from PARENT....  filtering for columns from FOO belong in the WHERE clause.

               

              Take grandparent out completely, and you see the same situation.

               

              Rewrite it with using Oracle classic syntax and it behaves like 12c, because you HAVE to put the FOO.inherit=1 in the where clause.

               

              SELECT FOO.id,

                PARENT.id,

                GRANDPARENT.id

              FROM FOO,

                   FOO PARENT,

                   foo grandparent

              where

                      FOO.parentId = PARENT.id(+)

                  AND FOO.inherit = 1

                  and PARENT.parentId = GRANDPARENT.id(+)

                  AND PARENT.inherit = 1

              ;

              • 4. Re: Outer Join Bug in Oracle 12c?
                Martin Preiss

                I think Brian is right: the 12c behaviour is plausible. Being defined in the join clause the inherit = 1 filter has to applied on foo and I would expect a left outer join of an empty set with something else to be an empty set again. Since the ANSI join has been known to be fragile in some situations this could be regarded as a step to a consolidation of this syntax style.

                 

                That said it is still strange that Oracle until version 11 and almost all other RDBMS (I can add postgres 9.2 to the list) think that the query should return one row.

                 

                And the logic in the 11.2 plan is indeed strange:

                SELECT FOO.id

                     , PARENT.id

                   FROM FOO

                      , FOO PARENT

                  where "FOO"."PARENTID"="PARENT"."ID"(+)

                    AND "FOO"."INHERIT"=1;

                 

                --> no rows selected

                 

                -- using the access predicate from the plan

                SELECT FOO.id

                     , PARENT.id

                   FROM FOO

                      , FOO PARENT

                  where "FOO"."PARENTID"="PARENT"."ID"(+)

                    AND "FOO"."INHERIT"=CASE  WHEN ("PARENT"."ID"(+) IS NOT NULL) THEN 1 ELSE 1 END    ;

                 

                        ID         ID

                ---------- ----------

                         1

                So for foo.inherit = 1 we get no result, but for foo.inherit = (case when something then 1 else 1 end) there is one row. Taking a look at the CBO trace I only see what the plans already tell: in 11g both inherit conditions use the strange case logic while in 12c the condition for foo does not use the case:

                -- 12.1.0.1

                SELECT "FOO"."ID" "ID"

                     ,"PARENT"."ID" "ID"

                     ,"GRANDPARENT"."ID" "ID"

                  FROM "TEST"."FOO" "FOO"

                     , "TEST"."FOO" "PARENT"

                     , "TEST"."FOO" "GRANDPARENT"

                WHERE "PARENT"."PARENTID"="GRANDPARENT"."ID"(+)

                   AND "PARENT"."INHERIT"=CASE  WHEN ("GRANDPARENT"."ID"(+) IS NOT NULL) THEN 1 ELSE 1 END 

                   AND "FOO"."PARENTID"="PARENT"."ID"

                   AND "FOO"."INHERIT"=1

                 


                -- 11.2.0.1

                 

                 

                SELECT "FOO"."ID" "ID"

                     , "PARENT"."ID" "ID"

                     , "GRANDPARENT"."ID" "ID"

                  FROM "TEST"."FOO" "FOO"

                     , "TEST"."FOO" "PARENT"

                     , "TEST"."FOO" "GRANDPARENT"

                WHERE "PARENT"."PARENTID"="GRANDPARENT"."ID"(+)

                   AND "PARENT"."INHERIT"=CASE  WHEN ("GRANDPARENT"."ID"(+) IS NOT NULL) THEN 1 ELSE 1 END 

                   AND "FOO"."PARENTID"="PARENT"."ID"(+)

                   AND "FOO"."INHERIT"=CASE  WHEN ("PARENT"."ID"(+) IS NOT NULL) THEN 1 ELSE 1 END

                The trace also contains a lot of information on the considered transformations but at the moment I don't I find the explanation of the different decision (if it is there).

                 

                I think it's always surprising how many strange details appear when you take a look at basic SQL functionality...

                • 5. Re: Outer Join Bug in Oracle 12c?
                  user10857924

                  Thanks Martin!! for your detailed explanation

                  • 6. Re: Outer Join Bug in Oracle 12c?
                    Jonathan Lewis

                    It certainly looks like a bug to me.

                    Consider just the first join alone:

                     

                    SELECT

                      FOO.id f_id,

                      PARENT.id p_id

                    FROM

                      FOO

                    LEFT JOIN

                      FOO PARENT

                    ON FOO.parentId = PARENT.id

                    AND FOO.inherit = 1

                    ;

                     

                    This returns one row ... which HAS to be preserved by the next outer join. The fact that it disappears demonistrates a bug.

                    Alternatively - rewrite your query with an inline view:

                     

                    select

                      f_id,

                      p_id,

                      grandparent.id

                    from

                      (

                      SELECT

                      FOO.id f_id,

                      PARENT.id p_id,

                      parent.inherit p_in,

                      parent.parentid pp_id

                      FROM

                      FOO

                      LEFT JOIN

                      FOO PARENT

                      ON FOO.parentId = PARENT.id

                      AND FOO.inherit = 1

                      ) v

                    left join

                      foo grandparent

                    ON pp_id = GRANDPARENT.id

                    AND p_in = 1

                    ;

                     

                     

                     

                    I can't spot any error in the rewrite - but this returns a row. So the original must have introduced an error in the outer join to lateral view that Oracle has (probably) used in the internal transformation. (I haven't looked at the 10053 trace yet).

                     

                     

                     

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: Outer Join Bug in Oracle 12c?
                      Martin Preiss

                      though detailed obviously my explanation was basically wrong - and now I think Jonathan is right... - the on clause doesn't restrict the results of the table to which other tables are left joined at all: it only defines to which rows of the left table rows of the right table are joined (and implicitely where NULL values are added). And so the outer join of foo and parent should return one row (and does so in 11.2 and 12.1) and the second join shouldn't remove this row.

                       

                      Time to take a closer look at ANSI join syntax it seems ...

                      • 8. Re: Outer Join Bug in Oracle 12c?
                        Jonathan Lewis

                        I have an example here which might jog your memory: http://jonathanlewis.wordpress.com/2011/01/31/ansi-outer/

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Outer Join Bug in Oracle 12c?
                          Martin Preiss

                          thank you. "If you’re familiar with ANSI SQL you won’t need more than a couple of moments to interpret the following query – but I have to admit that I had to think about it carefully before I figured out what it was trying to achieve" - that makes me already feel better...

                          • 10. Re: Outer Join Bug in Oracle 12c?
                            chris227

                            HI Jonathan,

                             

                            The phrase "The ON clause includes a reference to the t1 table that is NOT a join condition" in your blog made me think somehow.

                             

                            If i have explained such queries to others i always stressed that this is one difference compared to the oracle syntax as the predicate "t1.n1 in (7, 11, 13)" now is part of the join condition, since it is placed in the on-clause rather than in the where-clause.

                            It limits the rows-"outer-joined" but changes nothing on the fact that all rows from the left table are returned (aside from any additional where clause).

                             

                            Am i really  wrong with the statement that the predicate "t1.n1 in (7, 11, 13)" is part of the join condition?

                             

                            Thanks

                            • 11. Re: Outer Join Bug in Oracle 12c?
                              BrendanP

                              I also regarded that predicate as part of the join condition - that is why it doesn't (or shouldn't) restrict the rows in the earlier table.

                               

                              I recently came across this scenario in querying Oracle ebs profile values, which can be defined at any of four levels, when I wanted to bring back all values for one particular profile name. I wrote the query first in ANSI and then wondered if it could be done also in old-style syntax, so I re-wrote it that way out of curiosity. Here are the two queries (used in v11.2 for ebs R12.1.3, I don't think v12 is certified for ebs yet).

                               

                              ANSI Joins

                              SELECT app.application_name,
                                      pop.profile_option_name,
                                      pop.user_profile_option_name,
                                      pov.profile_option_value,
                                      CASE pov.level_id       WHEN 10001 THEN 'Site'
                                                              WHEN 10002 THEN 'Application'
                                                              WHEN 10003 THEN 'Responsibility'
                                                              WHEN 10004 THEN 'User'
                                      END "Level",
                                      app_l.application_name  "Application",
                                      app_r.application_name  "Resp. Application",
                                      rsp.responsibility_name "Resposibility",
                                      usr.user_name           "User"
                                FROM  fnd_profile_options_vl          pop
                                JOIN  fnd_application_vl              app
                                  ON  app.application_id              = pop.application_id
                                LEFT JOIN fnd_profile_option_values   pov
                                  ON pov.application_id               = pop.application_id
                                 AND pov.profile_option_id            = pop.profile_option_id
                                LEFT JOIN fnd_application_vl          app_l
                                  ON app_l.application_id             = pov.level_value
                                 AND pov.level_id                     = 10002
                                LEFT JOIN fnd_application_vl          app_r
                                  ON app_r.application_id             = pov.level_value_application_id
                                 AND pov.level_id                     = 10003
                                LEFT JOIN fnd_responsibility_vl       rsp
                                  ON rsp.application_id               = pov.level_value_application_id
                                 AND rsp.responsibility_id            = pov.level_value
                                LEFT JOIN fnd_user                    usr
                                  ON usr.user_id                      = pov.level_value
                                 AND pov.level_id                     = 10004
                               WHERE pop.profile_option_name          = 'AP_USE_INV_BATCH_CONTROLS'
                              

                              Oracle Old Style Joins

                              SELECT app.application_name,
                                      pop.profile_option_name,
                                      pop.user_profile_option_name,
                                      pov.profile_option_value,
                                      CASE pov.level_id       WHEN 10001 THEN 'Site'
                                                              WHEN 10002 THEN 'Application'
                                                              WHEN 10003 THEN 'Responsibility'
                                                              WHEN 10004 THEN 'User'
                                      END "Level",
                                      app_l.application_name  "Application",
                                      app_r.application_name  "Resp. Application",
                                      rsp.responsibility_name "Resposibility",
                                      usr.user_name           "User"
                                FROM  fnd_profile_options_vl          pop
                                ,     fnd_application_vl              app
                                ,     fnd_profile_option_values       pov
                                ,     fnd_application_vl              app_l
                                ,     fnd_application_vl              app_r
                                ,     fnd_responsibility_vl           rsp
                                ,     fnd_user                        usr
                               WHERE pop.profile_option_name          = 'AP_USE_INV_BATCH_CONTROLS'
                                 AND app.application_id               = pop.application_id
                                 AND pov.application_id (+)           = pop.application_id
                                 AND pov.profile_option_id (+)        = pop.profile_option_id
                                 AND app_l.application_id (+)         = CASE pov.level_id WHEN 10002 THEN pov.level_value END
                                 AND app_r.application_id (+)         = CASE pov.level_id WHEN 10003 THEN pov.level_value_application_id END
                                 AND rsp.application_id (+)           = pov.level_value_application_id
                                 AND rsp.responsibility_id (+)        = pov.level_value
                                 AND usr.user_id (+)                  = CASE pov.level_id WHEN 10004 THEN pov.level_value END
                              
                              • 12. Re: Outer Join Bug in Oracle 12c?
                                Rafu

                                Most likely a you are hitting some of known bugs titled like WRONG RESULT IN LEFT OUTER JOIN WHEN USING ADAPTIVE PLAN

                                 

                                WORKAROUND INFORMATION

                                ======================

                                alter session  set optimizer_adaptive_features=false;

                                • 13. Re: Outer Join Bug in Oracle 12c?
                                  Brian Bontrager

                                  Thanks Jonathan, this line in particular helped to adjust my understanding (and expectation) of the ANSI syntax:

                                  "This condition means that Oracle will try to find a matching row in t2 only if the n1 value in t1 is in 7,11, or 13."

                                  • 14. Re: Outer Join Bug in Oracle 12c?
                                    user4678078

                                    Thanks to everyone for their replies. We also logged this issue with Oracle Support. The initial response is "It seems to be an optimizer bug in 12.1.0.1". A workaround seems to be: alter session set optimizer_features_enable='11.20.3';