Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Krishna pavan kumar.

Asked: May 02, 2019 - 9:07 am UTC

Last updated: May 03, 2019 - 10:20 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

Hi,

From the sql script, I would like to understand the difference in the behavior between executions of statement 8 and statement 11.
While statement 8 updates the flag_1 to null, statement 11 updates it to 2.
What difference it makes when I use another instance of t1_test as t4 for outer join inside the parenthesis

Thanks

with LiveSQL Test Case:

and Chris said...

A good way to check the subquery for update statements is to turn them into a scalar subquery in a select.

Do this and I see similar behaviour:

create table t1_test(g_id number, t_id number, r_id number, flag_1 number);

create table t2_test(r_id number, flag_1 number);

create table t3_test(r_id number, flag_1 number);

insert into t1_test 
values(10, 20, 1, null);

insert into t2_test 
values(1, 2);

insert into t3_test 
values(2, 2);

commit;

select t1.*,  
     (select nvl(t3.flag_1, t2.flag_1) 
      from t2_test t2, t3_test t3 
      where t2.r_id = t1.r_id 
      and t3.r_id(+) = t1.r_id) subq
from   t1_test t1
where t1.g_id = 10;

G_ID   T_ID   R_ID   FLAG_1   SUBQ     
     10      20       1    <null>    <null> 


Digging around in the trace file reveals the database transforms the query to:

select "T1"."G_ID"     "G_ID",
       "T1"."T_ID"     "T_ID",
       "T1"."R_ID"     "R_ID",
       "T1"."FLAG_1"   "FLAG_1",
       (
         select nvl ("T3"."FLAG_1","T2"."FLAG_1") "NVL(T3.FLAG_1,T2.FLAG_1)"
         from "CHRIS"."T2_TEST"   "T2",
              "CHRIS"."T3_TEST"   "T3"
         where "T2"."R_ID" = "T1"."R_ID"
         and   "T3"."R_ID" = "T1"."R_ID"
       ) "(SE"
from "CHRIS"."T1_TEST" "T1"
where "T1"."G_ID" = 10;


Notice the outer join is lost!

I've logged a bug for this.

You can get around this by using ANSI-style outer joins:

select t1.*,  
     (select nvl(t3.flag_1, t2.flag_1) 
      from   t2_test t2
      left   join  t3_test t3 
      on     t3.r_id = t1.r_id
      where   t2.r_id = t1.r_id 
      ) subq
from   t1_test t1
where t1.g_id = 10;

G_ID       T_ID   R_ID     FLAG_1    SUBQ   
     10      20       1    <null>       2 

update t1_test t1 
set flag_1 = (select nvl(t3.flag_1, t2.flag_1) 
              from   t2_test t2
              left   join t3_test t3 
              on     t3.r_id(+) = t1.r_id
              where  t2.r_id = t1.r_id ) 
where t1.g_id = 10;

select * from t1_test;

G_ID       T_ID    R_ID   FLAG_1   
     10      20       1        2


Though this doesn't work on 11.2 :/

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.