2 Replies Latest reply: Nov 17, 2012 10:56 AM by odie_63 RSS

    DEREF and IS OF operator in WHERE clause give wrong results

    odie_63
      DB version :
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      Please see the following test case, it creates :

      - a supertype
      - two subtypes
      - a table of the supertype
      - a table storing REFs to the supertype in the first table
      create or replace type my_type as object (attr1 integer) not final;
      /
      
      create or replace type my_subtype1 under my_type (attr_s1 number) final;
      /
      
      create or replace type my_subtype2 under my_type (attr_s2 number) final;
      /
      
      create table my_table of my_type;
      /
      
      create table my_ref_table (
        id       integer
      , obj_ref  ref my_type scope is my_table
      );
      
      insert into my_table values(my_subtype1(1,1));
      insert into my_table values(my_subtype2(1,2));
      
      insert into my_ref_table (id, obj_ref)
      select 1, ref(t) 
      from my_table t 
      where object_value is of (my_subtype1);
      
      insert into my_ref_table (id, obj_ref)
      select 2, ref(t) 
      from my_table t 
      where object_value is of (my_subtype2);
      The problem :
      SQL> select id
        2       , case when deref(obj_ref) is of (my_subtype1) then 'SUBTYPE1'
        3              when deref(obj_ref) is of (my_subtype2) then 'SUBTYPE2'
        4         end as type_check
        5  from my_ref_table
        6  where deref(obj_ref) is of (my_subtype1)
        7  ;
       
                                 ID TYPE_CHECK
      ----------------------------- ----------
                                  1 SUBTYPE1
                                  2 SUBTYPE2
       
      The IS OF comparison is ignored when dereferencing directly in the WHERE clause, but it works in the projection.

      This works too :
      SQL> select id
        2       , case when deref(obj_ref) is of (my_subtype1) then 'SUBTYPE1'
        3              when deref(obj_ref) is of (my_subtype2) then 'SUBTYPE2'
        4         end as type_check
        5  from my_ref_table
        6  where case when deref(obj_ref) is of (my_subtype1) then 'SUBTYPE1'
        7             when deref(obj_ref) is of (my_subtype2) then 'SUBTYPE2'
        8        end = 'SUBTYPE1'
        9  ;
       
                                 ID TYPE_CHECK
      ----------------------------- ----------
                                  1 SUBTYPE1
       
      Of course I could always join explicitly but using DEREF is way "cooler" :)
      select r.id 
      from my_ref_table r
           join my_table t on ref(t) = r.obj_ref
      where t.object_value is of (my_subtype1)
      ;
      Any ideas?

      Thanks.
        • 1. Re: DEREF and IS OF operator in WHERE clause give wrong results
          gaverill
          you might want to open a SR with oracle, as this would appear to be a bug.

          a couple workarounds i discovered:

          interestingly enough, "is NOT of" does seem to work correctly...
          select id
               , case when deref(obj_ref) is of (my_subtype1) then 'SUBTYPE1'
                      when deref(obj_ref) is of (my_subtype2) then 'SUBTYPE2'
                 end as type_check
          from my_ref_table
          where deref(obj_ref) is NOT of (my_subtype2);
          
                                     ID TYPE_CHECK
          ----------------------------- ----------
                                      1 SUBTYPE1
          
          select id
               , case when deref(obj_ref) is of (my_subtype1) then 'SUBTYPE1'
                      when deref(obj_ref) is of (my_subtype2) then 'SUBTYPE2'
                 end as type_check
          from my_ref_table
          where deref(obj_ref) is NOT of (my_subtype1);
          
                                     ID TYPE_CHECK
          ----------------------------- ----------
                                      2 SUBTYPE2
          also, the "treat" operator works...
          select id
               , case when deref(obj_ref) is of (my_subtype1) then 'SUBTYPE1'
                      when deref(obj_ref) is of (my_subtype2) then 'SUBTYPE2'
                 end as type_check
          from my_ref_table
          where treat(obj_ref as ref my_subtype1) is not null;
          
                                     ID TYPE_CHECK
          ----------------------------- ----------
                                      1 SUBTYPE1
          
          select id
               , case when deref(obj_ref) is of (my_subtype1) then 'SUBTYPE1'
                      when deref(obj_ref) is of (my_subtype2) then 'SUBTYPE2'
                 end as type_check
          from my_ref_table
          where treat(obj_ref as ref my_subtype2) is not null;
          
                                     ID TYPE_CHECK
          ----------------------------- ----------
                                      2 SUBTYPE2
          Gerard
          • 2. Re: DEREF and IS OF operator in WHERE clause give wrong results
            odie_63
            Thanks Gerard.

            The TREAT workaround is interesting.