MySQL Bugs: #27019: nulls in unique composite keys

Bug #27019	nulls in unique composite keys - again!
Submitted:	9 Mar 2007 22:41	Modified:	21 Feb 2009 3:28
Reporter:	Robert Nice	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
I know this has been discussed many times, but I have no choice but to reopen it all again and put the common sense case forward.

See bugs: 5685 and 4140 among many others.

The typical rebuttal is sql92:
2) If there are no two rows in T such that the value of each column in one row
is non-null and is not distinct from the value of the corresponding column in the other row, then the result of the <unique predicate> is True; otherwise, the result of the <unique predicate> is False.

MySQL devs seem to think that you have to have rows 'equal' to each other to match, when the standard clearly says 'distinct'. If you're telling me NULL and NULL are not equal, that's fine(correct), but they're not distinct either.

For example, MySQL contradicts itself when you do a select and group on a column that may have a NULL value, it certainly doesn't list each NULL value distinctly.

If you want to test whether a value is distinct from another value you can't always evaluate this with a comparison operator. That's why IS[NOT] NULL exists.

Next, the common sense approach. Every other database engine (almost) at least provides an option on this. Without it it's almost impossible to map Hibernate many-to-one properties that may be null and maintain consistency. Nothing else expects MySQL to behave this way.

I'm appealing to the developers to at least include an option on this. Please?

How to repeat:
See above

Quick patch for anyone who needs this (innodb only). It basically comments out a couple of sections of the innodb insert handler which check for null prefixes. I'm no mysql internals expert, but it seems fine on my dev boxes so far. ('rnice at nicey dot com' if anyone wants to collaborate on a better patch for this) Thx.

mysql-5.0.32:

--- innobase/row/row0ins.c.orig 2007-03-09 23:58:21.000000000 -0500
+++ innobase/row/row0ins.c      2007-03-09 23:59:26.000000000 -0500
@@ -1600,7 +1600,7 @@
 
        /* In a unique secondary index we allow equal key values if they
        contain SQL NULLs */
-
+/*
        if (!(index->type & DICT_CLUSTERED)) {
 
                for (i = 0; i < n_unique; i++) {
@@ -1611,7 +1611,7 @@
                        }
                }
        }
-
+*/
        return(!rec_get_deleted_flag(rec, rec_offs_comp(offsets)));
 }
 
@@ -1649,7 +1649,7 @@
        /* If the secondary index is unique, but one of the fields in the
        n_unique first fields is NULL, a unique key violation cannot occur,
        since we define NULL != NULL in this case */
-
+/*
        for (i = 0; i < n_unique; i++) {
                if (UNIV_SQL_NULL == dfield_get_len(
                                          dtuple_get_nth_field(entry, i))) {
@@ -1657,7 +1657,7 @@
                        return(DB_SUCCESS);
                }
        }
-
+*/
        mtr_start(&mtr);
 
        /* Store old value on n_fields_cmp */

This feature request is not InnoDB-specific.

Just a note about the quoted SQL92 section. Which IMHO is misunderstood. (Never mind this, I do not mean to interfere with the feature request itself) 

2) If there are no two rows in T such that the value of each column in one row
is non-null and is not distinct from the value of the corresponding column in
the other row, then the result of the <unique predicate> is True; otherwise, the
result of the <unique predicate> is False.

so if 
 row1.field1 = null
 row2.field2 = null
Then according to the above uniqueness is given.

We a seeking for 2 rows (with a column) for which
> is non-null and is not distinct
> from the value of the corresponding column ...
is true.
(Because if there "are no two rows" like this, then "the <unique predicate> is True")

Equality or distinctness never come to matter, because "is non-null", is never given.

"Every other database engine (almost) at least provides an option on this."

Robert,

Can you, please, provide a list of RDBMSes and explanations of how they provide the feature you requested.

Wow, it's been a long time. I think if you just look at Innobase as InnoDB in MySQL. The engine is obviously designed the way I'd like it to behave, and in the glue code there are special extra checks to make it go the way MySQL expects. Seeing as (in InnoDB) we're talking about maybe 50 lines of code, why not simply put in an option?

I just thought you'd like to know that this bug, and it is a bug, just cost you well over $100,000 in lost revenue, because the customer went with Oracle for their server cluster. They have A LOT of servers that need supporting.

Sorry, meant to post this rant in a materialized views ticket.