| Bug #9393 | Searching through IN(...) with 0 and NULL value in list fails | ||
|---|---|---|---|
| Submitted: | 24 Mar 2005 22:03 | Modified: | 29 Jun 2005 22:38 |
| Reporter: | Frank Sweetser | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.10a | OS: | Linux (Fedora Core 3) |
| Assigned to: | Bugs System | Target Version: | |
[24 Mar 2005 22:09]
Frank Sweetser
Just correcting the version....
[24 Mar 2005 22:43]
Miguel Solorzano
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 18: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 19: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 21: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
[29 Jun 2005 0:21]
Oleksandr Byelkin
pushed to 4.1.13
[29 Jun 2005 22: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.