This discussion is archived
2 Replies Latest reply: Nov 17, 2012 8:56 AM by odie_63 RSS

DEREF and IS OF operator in WHERE clause give wrong results

odie_63 Guru
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Thanks Gerard.

    The TREAT workaround is interesting.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points