Oracle Scratchpad

April 2, 2014

Easy – Oops.

Filed under: Bugs,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 7:47 pm BST Apr 2,2014

A question came up on OTN today asking for suggestions on how to enforce uniqueness on a pair of columns only when the second column was not null. There’s an easy and obvious solution – but I decided to clone the OP’s example and check that I’d typed my definition up before posting it; and the result came as a bit of a surprise. Here’s a demo script (not using the OP’s table):


create table t1  
(  
	col1	int not null,
	col2	varchar2(1)
);  

create unique index t1_i1 on t1( 
--	case col2 when null then cast(null as int) else col1 end,
--	case when col2 is null then cast(null as int) else col1 end,
	case when col2 is not null then col1 end,
	col2
)
;

insert into t1 values(1,null);
insert into t1 values(1,null);
insert into t1 values(1,'x');
insert into t1 values(1,'y');
insert into t1 values(1,'y');

commit;

column ind1_is   format a5
column ind1_when format 9999

set null N/A

select
	case when col2 is null then cast (null as int) else col1 end	ind1_is,
	case col2 when null then cast (null as int)  else col1 end	ind1_when
from 
	t1
;

The strategy is simple, you create a unique function-based index with two columns; the first column of the index id defined to show the first column of the table if the second column of the table is not null, the second column of the index is simply the second column of the table. So if the second column of the table is null, both columns in the index are null and there is no entry in the index; but if the second column of the table is not null then the index copies both columns from the table and a uniqueness test applies.

Based on the requirement and definition you would expect the first 4 of my insert statements to succeed and the last one to fail. The index will then have two entries, corresponding to my 3rd and 4th insertions.

I’ve actually shown three ways to use the case statement to produce the first column of the index. The last version is the cleanest, but the first option is the one I first thought of – it’s virtually a literal translation the original requirement. The trouble is, with my first definition the index acquired an entry it should not have got, and the second insert raised a “duplicate key” error; the error didn’t appear when I switched the syntax of the case statement to the second version.

That’s why the closing query of the demo is there – when you run it the two values reported should be the same as each other for all four rows in the table – but they’re not. This is what I got on 11.2.0.4:


IND1_IS IND1_WHEN
------- ---------
N/A             1
N/A             1
      1         1
      1         1


I’m glad I did a quick test before suggesting my original answer.

Anyone who has other versions of Oracle available is welcome to repeat the test and report back which versions they finding working correctly (or not).

Update

It’s not a bug (see note 2 below from Jason Bucata), it’s expected behaviour.

 

4 Comments »

  1. 10.2.0.4 on hp-ux:

    For the case when col2 is not null then col1 end, I got (insert with the error‽):

    ...
    insert into mytable1 values(1,'y')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (JEG.MYTABLE1_I1) violated
    
    Commit complete.
    
       IND1_IS IND1_WHEN
    ---------- ---------
    N/A                1
    N/A                1
    ##########         1
    ##########         1
    
    

    For the case col2 when null then cast(null as int) else col1 end, I got (only three‽):

    ...
    insert into mytable1 values(1,'y')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (JEG.MYTABLE1_I1) violated
    
    Commit complete.
    
    
       IND1_IS IND1_WHEN
    ---------- ---------
    N/A                1
    ##########         1
    ##########         1
    

    Not sure why the overflows, straight select shows those values as 1.

    JEG@TTST> select * from mytable1;
    
          COL1 C
    ---------- -
             1 N
               /
               A
    
             1 x
             1 y
    

    Comment by jgarry — April 2, 2014 @ 10:31 pm BST Apr 2,2014 | Reply

    • When you got only 3 rows in the final result you should have got two errors about duplicate key – the first one from the second insert statement.
      I have no answer to the overflow output.

      Comment by Jonathan Lewis — April 2, 2014 @ 11:02 pm BST Apr 2,2014 | Reply

  2. I believe it’s working as designed (if not as expected): Namely that “CASE x WHEN y” always does exactly the same thing as “x = y”. (That’s probably what the ANSI standard says, if we were to dig it up somehow.) Since, as you know, “col = NULL” is never true, you’re actually getting precisely what you asked for.

    You’re probably too used to decode() which differs from CASE in this regard and does what you expect. I seem to remember back in 9i when searched CASE was introduced, that there was a small stir about it behaving differently from decode() when nulls where involved.

    Comment by Jason Bucata — April 3, 2014 @ 12:03 am BST Apr 3,2014 | Reply

    • Jason,

      Thanks for that – you’re absolutely correct. I couldn’t think of a rationale for the behaviour, but it seems a bit obvious now that you’ve explained it.

      I did a search on MoS after your comment, using: <b<wrong results case “when null” and found Bug 16600335, dated April 2013, marked as “Closed – not a bug”. The client test case in the bug report was:

        select case null when null then 'IS NULL' else 'IS NOT NULL' end from dual;
        

        -> ‘IS NOT NULL’ is returned instead of ‘IS NULL’.

      I’d guess there are many more reporting the same “not bug”.

      Comment by Jonathan Lewis — April 3, 2014 @ 5:56 am BST Apr 3,2014 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers