Oracle Scratchpad

March 11, 2016

Wrong Results ?

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 9:18 am BST Mar 11,2016

I gather that journalistic style dictates that if the headline is a question then the answer is no. So, following on from a discussion of possible side effects of partition exchange, let’s look at an example which doesn’t involve partitions.  I’ve got a schema that holds nothing by two small, simple heap tables, parent and child, (with declared primary keys and the obvious referential integrity constraint) and I run a couple of very similar queries that produce remarkably different results:


select
        par.id      parent_id,
        chi.id      child_id,
        chi.name    child_name
from
        parent  par,
        child   chi
where
        chi.id_p = par.id
order by
        par.id, chi.id
;

 PARENT_ID   CHILD_ID CHILD_NAME
---------- ---------- ----------
         1          1 Simon
         1          2 Sally
         2          1 Janet
         2          2 John
         3          1 Orphan

5 rows selected.

Having got this far with my first query I’ve decided to add the parent name to the report:


select
        par.id      parent_id,
        par.name    parent_name,
        chi.id      child_id,
        chi.name    child_name
from
        parent  par,
        child   chi
where
        chi.id_p = par.id
order by
        par.id, chi.id
;

 PARENT_ID PARENT_NAM   CHILD_ID CHILD_NAME
---------- ---------- ---------- ----------
         1 Smith2              1 Simon
         1 Smith               1 Simon
         1 Smith2              2 Sally
         1 Smith               2 Sally
         2 Jones               1 Janet
         2 Jones               2 John

6 rows selected.

How could adding a column to the select list result in one child row disappearing and two child rows being duplicated; and is this a bug ?

To avoid any confusion, here’s the complete script I used for creating the schema owner, in 11.2.0.4, with no extra privileges granted to PUBLIC:


create user u1
        identified by u1
        default tablespace test_8k
        quota unlimited on test_8k
;

grant
        create session,
        create table
to
        u1
;


Update

It didn’t take long for a couple of people to suggest that the oddity was the consequence of constraints that had not been enabled and validated 100% of the time, but the suggestions offered were a little more convoluted than necessary. Here’s the code I ran from my brand new account before running the two select statements:


create table parent (
        id      number(4),
        name    varchar2(10),
        constraint par_pk primary key (id)
        rely disable novalidate
)
;

create table child(
        id_p    number(4)
                constraint chi_fk_par
                references parent
                on delete cascade
                rely disable novalidate,
        id      number(4),
        name    varchar2(10),
        constraint chi_pk primary key (id_p, id)
                rely disable novalidate
)
;

insert into parent values (1,'Smith');
insert into parent values (1,'Smith2');
insert into parent values (2,'Jones');

insert into child values(1,1,'Simon');
insert into child values(1,2,'Sally');

insert into child values(2,1,'Janet');
insert into child values(2,2,'John');

insert into child values(3,1,'Orphan');

commit;

begin
        dbms_stats.gather_table_stats(user,'child');
        dbms_stats.gather_table_stats(user,'parent');
end;
/


In a typical data warehouse frame of mind I’ve added plenty of constraints, but left them all disabled and novalidated, but told Oracle to rely on them for optimisation strategies. This means all sorts of incorrect data could get into the tables, with all sorts of unexpected side effects on reporting. The example above shows duplicates on primary keys (and if you checked the table definition you’d find that the primary key columns were nullable as well), child rows with no parent key.

In fact 11g and 12c behave differently – the appearance of the Orphan row in the first sample query is due, as Chris_cc pointed out in the first comment, to the optimizer deciding that it could use join elimination because it was joining to a single-column primary key without selecting any other columns from the referenced table. In 12c the optimizer doesn’t use join elimination for this query, so both queries have the same (duplicated) output.

Update:

Make sure you read the articles linked to by Dani Schneider’s comment below, and note especially the impact on the query_rewrite_integrity parameter.

6 Comments »

  1. A quick assumption would be the foreign key constraint was created with the RELY ENABLE NOVALIDATE options so the first query can eliminate the parent table as we are not retrieving any columns from it.
    The second query has to actually perform the join to get the parent_name so the orphan row disappears as there is not actually a row in the parent table for parent_id = 3.
    The duplicates could be caused by disabling the primary key on the parent table loading some dodgy data then enabling the primary key with the no validate option.

    Neither of these is a bug in Oracle, both are bugs in the application/process design as declaring the novalidate options tells Oracle that you have already done the work so the optimiser is then entitled to make choices that appear to give bad results.

    Comment by chris_cc — March 11, 2016 @ 10:19 am BST Mar 11,2016 | Reply

  2. Hello Jonathan,
    I guess you did something like this:

    SQL> create table parent (
      2    id int,
      3    name varchar2(6),
      4    constraint parent_pk primary key(id) using index (create index parent_pk on parent(id)) );
    
    Table created.
    
    SQL> insert into parent values (1, 'Simon');
    
    1 row created.
    
    SQL> insert into parent values (2, 'Jones');
    
    1 row created.
    
    SQL> insert into parent values (3, 'Harry');
    
    1 row created.
    
    SQL> 
    SQL> create table child(
      2    id   int,
      3    id_p int,
      4    name varchar2(6),
      5    constraint child_parent_fk foreign key(id_p) references parent);
    
    Table created.
    
    SQL> 
    SQL> insert into child values (1, 1, 'Simon');
    
    1 row created.
    
    SQL> insert into child values (2, 1, 'Sally');
    
    1 row created.
    
    SQL> insert into child values (1, 2, 'Janet');
    
    1 row created.
    
    SQL> insert into child values (2, 2, 'John');
    
    1 row created.
    
    SQL> insert into child values (1, 3, 'Orphan');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> 
    SQL> alter table parent disable primary key cascade;
    
    Table altered.
    
    SQL> insert into parent values (1, 'Simon2');
    
    1 row created.
    
    SQL> delete parent where id=3;
    
    1 row deleted.
    
    SQL> alter table parent enable novalidate primary key;
    
    Table altered.
    
    SQL> alter table child modify constraint child_parent_fk rely enable novalidate;
    
    Table altered.
    
    SQL> 
    SQL> select
      2          par.id      parent_id,
      3          chi.id      child_id,
      4          chi.name    child_name
      5  from
      6          parent  par,
      7          child   chi
      8  where
      9          chi.id_p = par.id
     10  order by
     11          par.id, chi.id
     12  ;
    
    
     PARENT_ID   CHILD_ID CHILD_NAME
    ---------- ---------- ------------------
             1          1 Simon
             1          2 Sally
             2          1 Janet
             2          2 John
             3          1 Orphan
    
    5 rows selected.
    
    SQL> 
    SQL> select
      2          par.id      parent_id,
      3          par.name    parent_name,
      4          chi.id      child_id,
      5          chi.name    child_name
      6  from
      7          parent  par,
      8          child   chi
      9  where
     10          chi.id_p = par.id
     11  order by
     12          par.id, chi.id
     13  ;
    
    
     PARENT_ID PARENT_NAME          CHILD_ID CHILD_NAME
    ---------- ------------------ ---------- ------------------
             1 Simon2                      1 Simon
             1 Simon                       1 Simon
             1 Simon2                      2 Sally
             1 Simon                       2 Sally
             2 Jones                       1 Janet
             2 Jones                       2 John
    
    6 rows selected.
    

    Comment by Mikhail Velikikh — March 11, 2016 @ 10:48 am BST Mar 11,2016 | Reply

  3. Seems to be a similar problem I had last year in a Data Warehouse an Oracle 11.2:
    https://danischnider.wordpress.com/2015/09/18/the-advantage-of-silly-mistakes/
    The reason for the different behaviour in 11g and 12c could be this one:
    https://danischnider.wordpress.com/2015/06/29/join-elimination-difference-in-oracle-11g-and-12c/

    Comment by Dani Schnider — March 13, 2016 @ 5:23 pm BST Mar 13,2016 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.