SQL & PL/SQL

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 ...

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

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

;

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...

Thanks Martin!! for your detailed explanation

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

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 ...

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

Regards

Jonathan Lewis

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...

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

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

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;

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."

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';