strange unique index behaviour on null values

hi all,

I get a very strange result from mysql when selecting null values from a
unique indexed innodb table.

so this is the structure and content of my table:

##################################
CREATE TABLE `testunique` (
`id` varchar(10) default NULL,
`a` char(2) NOT NULL default '',
`b` char(2) NOT NULL default '',
UNIQUE KEY `id` (`id`)
) TYPE=InnoDB;

INSERT INTO `testunique` VALUES (NULL, 'e', 'f');
INSERT INTO `testunique` VALUES (NULL, 'v', 's');
INSERT INTO `testunique` VALUES (NULL, 'r', 's');
INSERT INTO `testunique` VALUES ('eee', 'f', 'f');
##################################

now, if I do a

##################################
select * from testunique where id is null
##################################

I always get only the first row.
If I remove the unique index then I get the expected result. also there is no problem if I use myisam.
I am using mysql 4.0.16 on a debian box.

thanks for your help.

bye




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: strange unique index behaviour on null values

Hi!

Looks like a bug#2483.

http://bugs.mysql.com/bug.php?id=2483

Use this url to follow the progress of this issue.

(you may also add your test case there)

On Jan 26, mehdi wrote:
> hi all,
>
> I get a very strange result from mysql when selecting null values from a
> unique indexed innodb table.
>
> so this is the structure and content of my table:
>
> ##################################
> CREATE TABLE `testunique` (
> `id` varchar(10) default NULL,
> `a` char(2) NOT NULL default '',
> `b` char(2) NOT NULL default '',
> UNIQUE KEY `id` (`id`)
> ) TYPE=InnoDB;
>
> INSERT INTO `testunique` VALUES (NULL, 'e', 'f');
> INSERT INTO `testunique` VALUES (NULL, 'v', 's');
> INSERT INTO `testunique` VALUES (NULL, 'r', 's');
> INSERT INTO `testunique` VALUES ('eee', 'f', 'f');
> ##################################
>
> now, if I do a
>
> ##################################
> select * from testunique where id is null
> ##################################
>
> I always get only the first row.
> If I remove the unique index then I get the expected result. also there is
> no problem if I use myisam.
> I am using mysql 4.0.16 on a debian box.
>
> thanks for your help.
>
> bye
>
Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

strange unique index behaviour on null values

Just FYI:

mehdi writes:
> hi all,
>
> I get a very strange result from mysql when selecting null values from a
> unique indexed innodb table.


This is fixed now.

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org