Bug #35820 UNIQUE not enforced
Submitted: 4 Apr 2008 2:49 Modified: 4 Apr 2008 6:35
Reporter: Daniel McDonald Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.38-Ubuntu_0ubuntu1.2-log OS:Linux (Ubuntu 7.04)
Assigned to: CPU Architecture:Any
Tags: unique, varchar

[4 Apr 2008 2:49] Daniel McDonald
Description:
If a varchar field is set as "NOT NULL UNIQUE", uniqueness is not enforced. If no value is specified on insert, the field gets set to the empty string. This string matches to an entry of "0" as well. This is not expected behavior, the field is not unique.

How to repeat:
create table test_unique (
  id varchar(30) unique not null,
  test varchar(30) not null,
  primary key(id)
) engine=innodb;

insert into test_unique (id, test) values ("0","test");
insert into test_unique (id, test) values ("1","test");
insert into test_unique (id, test) values ("2","test");
insert into test_unique (id, test) values ("3","test");
insert into test_unique (test) values ("test");
select * from test_unique where id=0;  # this will return 2 entries

Suggested fix:
Don't let the empty string compare equal to "0"
[4 Apr 2008 3:30] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of:

show variables like 'sql_mode';

Can you try to set sql_mode to STRICT_TRANS_TABLES and check if that will change anything? Read http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html.
[4 Apr 2008 3:51] Daniel McDonald
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   | 
+-------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    | 
+--------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_unique where id=0;
+----+------+
| id | test |
+----+------+
|    | test | 
| 0  | test | 
+----+------+
2 rows in set (0.00 sec)
[4 Apr 2008 6:14] Valeriy Kravchuk
Please, set strict sql_mode before INSERTing. This will prevent inserting row with empty id value to the table.
[4 Apr 2008 6:35] Daniel McDonald
thanks
-Daniel