≡ Menu

Mysql Null Values…. The Debate

I was surfing the web trying to solve a problem with MySql I was having tonight… Actually, not so much as a  problem as it was a rather interesting behavior. It had to do with the treatment of NULL values in multi-column null indexes. I ended up stumbling upon what I must admit is the most interesting database engine debate I’ve seen in awhile, so I felt I would share the bug report with you all… I’m not sure where I stand on the behavior… Whether Oracle or Mysql does it “right” (funny how they are the same company now…)

It basically all comes down to how you interpret this one sentence from the SQL 2003 spec: “A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.”

Hope some of my computer science friends find it interesting: http://bugs.mysql.com/bug.php?id=25544

Excerpt of the bug report:

Multiple column unique key allows multiple rows?

If result in 'How to repeat' is alright this means that unique key constraints on more
than one column are dangerous.

How to repeat:
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(200) collate utf8_bin default NULL,
  `url` varchar(200) collate utf8_bin default NULL,
  `url_method` enum('GET','POST') collate utf8_bin default NULL,
  `converter` varchar(255) collate utf8_bin default NULL,
  `description` varchar(80) collate utf8_bin default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `service_uk` (`email`,`url_method`,`url`,`converter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert into test (email, url_method, url, converter) values  ('test@bkwi.net', NULL  ,
NULL, NULL);
insert into test (email, url_method, url, converter) values  ('test@bkwi.net', NULL  ,
NULL, 'email.xslt');
insert into test (email, url_method, url, converter) values  (NULL           , 'POST',
'http://www.dummy.az', NULL);

-- duplicate row, violatesi unique key service_uk
insert into test (email, url_method, url, converter) values  (NULL           , 'POST',
'http://www.dummy.az', NULL);

commit;

select `email`,`url_method`,`url`,`converter`, count(*)
from test group by 1,2,3,4
having count(*) > 1;

+-------+------------+---------------------+-----------+----------+
| email | url_method | url                 | converter | count(*) |
+-------+------------+---------------------+-----------+----------+
| NULL  | POST       | http://www.dummy.az | NULL      |        2 |
+-------+------------+---------------------+-----------+----------+

Comments on this entry are closed.

  • PK May 21, 2010, 6:02 am

    I guess I can see both sides of that argument. However, I generally consider that NULL == NULL, so I say it's a bug.

  • Alex May 21, 2010, 8:29 pm

    Interesting! Unrelated: my least favorite part of mySQL: "By default, values that lie outside the TIME range but are otherwise legal are clipped to the closest endpoint of the range."