This discussion is archived
1 2 3 4 5 Previous Next 60 Replies Latest reply: Dec 20, 2012 6:22 AM by jeneesh Go to original post RSS
  • 15. Re: null values unique key
    William Robertson Oracle ACE
    Currently Being Moderated
    > So it's pretty clear that Oracle's in the wrong here.

    Nothing about that ANSI legalese is clear at all. After reading it five times I think it's saying that the following results are correct:
    SQL> CREATE TABLE testit (col1 INT NOT NULL, col2 INT);
    
    Table created.
    
    SQL> INSERT INTO testit VALUES(1, NULL);
    
    1 row created.
    
    SQL> INSERT INTO testit VALUES(1, NULL);
    
    1 row created.
    
    SQL> SELECT col1, col2, COUNT(*) FROM testit GROUP BY col1, col2;
    
          COL1       COL2   COUNT(*)
    ---------- ---------- ----------
             1                     2
    
    1 row selected.
    
    SQL> SELECT UNIQUE col1, col2 FROM testit;
    
          COL1       COL2
    ---------- ----------
             1
    
    1 row selected.
    So the two rows I inserted are identical according to GROUP BY and UNIQUE/DISTINCT. By the same criteria, they cannot support a unique constraint.

    Are you saying GROUP BY and UNIQUE/DISTINCT are broken as well?

    For the ANSI "UNIQUE" test to be true, there would have to be no two rows such that the value of col1 in one row matches the value of col1 in the other row.

    Surely, if I'm reading this right, there are two such rows, so the UNIQUE test returns false.


    Message was edited by:
    William Robertson
    Sorry, added clarifications after posting example.
  • 16. Re: null values unique key
    537882 Newbie
    Currently Being Moderated
    >>"the value of each column in one row is non-null "

    SQL-92 specifies the ambiguous behaviour by not mentioning the case for nulls explicitly.

    This is a fairly well known failing of the SQL standard to implementing relational model correctly.
  • 17. Re: null values unique key
    SomeoneElse Guru
    Currently Being Moderated
    So it's pretty clear that Oracle's in the wrong here.
    You're quoting sections that have to do with constraints and predicates. Your initial question was about unique INDEXES.

    I don't think the ANSI document has much to say about indexes.
  • 18. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    You're quoting sections that have to do with
    constraints and predicates. Your initial question
    was about unique INDEXES.

    I don't think the ANSI document has much to say about
    indexes.
    The UNIQUE INDEX enforces a UNIQUE constraint.
  • 19. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    >>"the value of each column in one row is
    non-null "


    SQL-92 specifies the ambiguous behaviour by not
    mentioning the case for nulls explicitly.

    This is a fairly well known failing of the SQL
    standard to implementing relational model correctly.
    I'm not sure what's ambiguous. The standard states that the UNIQUE predicate is true only if there are any two rows where every column's value is non-null and equal to the value in the other row's column. Even if you ignored the "non-null" part, it still wouldn't be triggered by (1, NULL) and (1, NULL) because NULL <> NULL.
  • 20. Re: null values unique key
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > Even if you ignored the "non-null" part, it still wouldn't be triggered by
    (1, NULL) and (1, NULL) because NULL <> NULL.

    Yes, but does the index contain (1,NULL) or simply (1)? Which explains why inserting (1, NULL) again will cause a unique violation as the index will see it too as (1).
  • 21. Re: null values unique key
    537882 Newbie
    Currently Being Moderated
    I'm not sure what's ambiguous. The standard states
    that the UNIQUE predicate is true only if there are
    any two rows where every column's value is non-null
    and equal to the value in the other row's column.
    Even if you ignored the "non-null" part, it
    But you can't ignore the "non-null" part of it, because it's there.

    The question remains of, what if is a NULL? This is ambiguity I'm talking about.

    There is most full examine of SQL standard correspondence to NULL in "A Guide to SQL Standard by C. J. Date, Hugh Darwen" This is important consideration before thinking about how Oracle corresponds to SQL standard in regard to NULL.
  • 22. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    I'm not sure what's ambiguous. The standard states
    that the UNIQUE predicate is true only if there are
    any two rows where every column's value is non-null
    and equal to the value in the other row's column.
    Even if you ignored the "non-null" part, it
    But you can't ignore the "non-null" part of it,
    because it's there.
    No, I'm saying that it'd be non-ambiguous even if it didn't say "non-null". With "non-null" in there it's absolutely clear. The uniqueness constraint is only violated if all columns are non-null and all columns are equal.

    The MySQL guys have hit this as well, read the same specifications, and come to the same conclusion as I have: http://bugs.mysql.com/bug.php?id=8173
  • 23. Re: null values unique key
    SomeoneElse Guru
    Currently Being Moderated
    The theory of this stuff never interested me much. I'm more concerned with the practical side. As such, I don't understand all the fuss. If you have a table like this:
    SQL> select col1, col2, col3, rowid from t;

      COL1   COL2 COL3       ROWID
    ------ ------ ---------- ------------------
                2 ABC        AAA4NTAClAABDj4AAA
                2 XYZ        AAA4NTAClAABDj4AAB
    And want to create a unique index on (COL1, COL2) it must point to ONE row. Which one? It can't point to one and ignore the other. And it certainly can't point to both.
  • 24. Re: null values unique key
    537882 Newbie
    Currently Being Moderated
    No, I'm saying that it'd be non-ambiguous even if it
    didn't say "non-null". With "non-null" in
    there it's absolutely clear. The uniqueness
    constraint is only violated if all columns are
    non-null and all columns are equal.
    No that is not correct.

    You only infer that "uniqueness constraint is only violated if all columns are non-null and all columns are equal.", the statement is ambiguous because IT DOES say "non-null".

    You cannot state that the contra argument hold when without precondition because the definition is stated with the predicate.

    It says nothing about the truth of the uniqueness contraint if either column is null.
  • 25. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    You only infer that "uniqueness constraint is only
    violated if all columns are non-null and all columns
    are equal.", the statement is ambiguous because IT
    DOES say "non-null".

    You cannot state that the contra argument hold when
    without precondition because the definition is stated
    with the predicate.

    It says nothing about the truth of the uniqueness
    contraint if either column is null.
    You're simply not reading the section correctly. Here it is again:

    2) If there are no two rows in T such that the value of each column
    in one row is non-null and is equal to the value of the cor-
    responding column in the other row according to Subclause 8.2,
    "<comparison predicate>", then the result of the <unique predi-
    cate> is true; otherwise, the result of the <unique predicate>
    is false.


    When is the predicate false (and hence the constraint violated)? When there are two rows (A and B) in the set T such that

    the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row

    It's pretty clear that this is not the case for (1, NULL) and (1, NULL).
  • 26. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    The theory of this stuff never interested me much.
    I'm more concerned with the practical side. As
    such, I don't understand all the fuss. If you have
    a table like this:
    SQL> select col1, col2, col3, rowid from t;

    COL1   COL2 COL3       ROWID
    ---- ------ ---------- ------------------
              2 ABC        AAA4NTAClAABDj4AAA
              2 XYZ        AAA4NTAClAABDj4AAB
    And want to create a unique index on (COL1, COL2) it
    must point to ONE row. Which one? It can't point to
    one and ignore the other. And it certainly can't
    point to both.
    I want to use the constraint for maintaining database consistency, which means that it does not bother me at all the the answer to your question is neither. This is SQL and NULL <> NULL in SQL.

    Along those lines, however, why aren't you raising the same objection to the way Oracle handles sincle-column UNIQUE indexes? If the index in your example was only on (COL1), Oracle would happily accept the two rows.
  • 27. Re: null values unique key
    SomeoneElse Guru
    Currently Being Moderated
    at all the the answer to your question is neither
    Neither? You would rather have Oracle ignore an otherwise index-able row?

    <scratches head>
    Along those lines, however, why aren't you raising
    the same objection to the way Oracle handles
    sincle-column UNIQUE indexes? If the index in your
    example was only on (COL1), Oracle would happily
    accept the two rows.
    I'm not raising any objection, you're the one doing that. But this is easy to answer: Oracle does not index rows where all the columns of the index are null.

    What about this:
    select decode(some_column
                 ,'A', 1
                 ,'B', 2
                 ,NULL,0
                 ,-1)
    from ...
    Should that third condition every occur?
  • 28. Re: null values unique key
    547137 Newbie
    Currently Being Moderated
    at all the the answer to your question is neither
    Neither? You would rather have Oracle ignore an
    otherwise index-able row?
    Uh, yeah. Because -- just like when an index contains only NULL values -- it doesn't make sense to do it any other way.
    What about this:
    select decode(some_column
                 ,'A', 1
                  ,'B', 2
                 ,NULL,0
                 ,-1)
    from ...
    Should that third condition every occur?
    DECODE is an Oracle-ism, and Oracle can make it do whatever it wants with that input.
  • 29. Re: null values unique key
    JWBerg51 Newbie
    Currently Being Moderated
    david's example answers that. just replace 'N' with 2