| Bug #9393 | Searching through IN(...) with 0 and NULL value in list fails | ||
|---|---|---|---|
| Submitted: | 24 Mar 2005 21:03 | Modified: | 29 Jun 2005 20:38 |
| Reporter: | Frank Sweetser | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.10a | OS: | Linux (Fedora Core 3) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[24 Mar 2005 21:09]
Frank Sweetser
Just correcting the version....
[24 Mar 2005 21:43]
MySQL Verification Team
I was able to repeat the behavior reported however I guess this report is duplicate but I wasn't able for to find the related one. Thank you for the bug report.
[28 Jun 2005 16:49]
Oleksandr Byelkin
ChangeSet 1.2323 05/06/28 18:56:45 bell@sanja.is.com.ua +3 -0 fixed not_null_tables() for IN() (BUG#9393) (IN() remove NULL rows only for tables from first argument (value which we looking for in IN() list) but not for tables from IN() list) Also it will be better change Item::not_null_tables() to prohibit this optimisation by default for new created items in 5.0 or 5.1.
[28 Jun 2005 17:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/26489
[28 Jun 2005 19:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/26493
[28 Jun 2005 22:21]
Oleksandr Byelkin
pushed to 4.1.13
[29 Jun 2005 20:38]
Mike Hillyer
Documented in 4.1.13 changelog.
Description: I'm not 100% positive if this is really a bug or not, but it definatelly behaves differently than 4.0, and I can't find anything in the relnotes or manual that explains the new behavior. Basically, when using an IN statement where the value being matched is 0, and one of the values in the list is NULL, 4.1 returns false, while 4.0 returns true. Schema and queries that demonstrate the problem included. How to repeat: CREATE TABLE `memb` ( `gid` int(11) default NULL, `uid` int(11) default NULL ); CREATE TABLE `prot` ( `ident` int(11) default NULL, `level` char(16) default NULL ); INSERT INTO `prot` VALUES (0,'READ'); CREATE TABLE `user` ( `id` int(11) default NULL, `name` char(16) default NULL ); INSERT INTO `user` VALUES (1,'fs'); select * from user left join memb on user.id = memb.uid, prot where prot.ident in (memb.gid, user.id, 0); In 4.0, this returns +------+------+------+------+-------+-------+ | id | name | gid | uid | ident | level | +------+------+------+------+-------+-------+ | 1 | fs | NULL | NULL | 0 | READ | +------+------+------+------+-------+-------+ In 4.1, no rows are returned.