Bug #15351 | SQL result depending on condition order | ||
---|---|---|---|
Submitted: | 30 Nov 2005 15:51 | Modified: | 23 Jun 2006 4:56 |
Reporter: | Devis Lucato | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 4.1.16-BK, 4.1.14 | OS: | Linux (Linux) |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[30 Nov 2005 15:51]
Devis Lucato
[30 Nov 2005 16:08]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 4.1.16-BK (ChangeSet@1.2476, 2005-11-29 11:52:58-08:00): mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> DROP TABLE IF EXISTS `users`; Query OK, 0 rows affected (0,13 sec) mysql> CREATE TABLE `users` ( `login` varchar(70) NOT NULL DEFAULT '', `pwd` -> varchar(64) NOT NULL DEFAULT '', UNIQUE KEY `auth` (`login`,`pwd`)) -> ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0,04 sec) mysql> INSERT INTO users VALUES ( 'login1',MD5('test')), ('login2', MD5('BADPWD')); #Query OK, 2 rows affected (0,01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT SQL_NO_CACHE * FROM users WHERE `login`='login2' AND (`pwd`=MD5("BADPWD") -> OR `pwd`=MD5("badPWD")); +--------+----------------------------------+ | login | pwd | +--------+----------------------------------+ | login2 | f2be7ab0477da7119971d20a79dcaf2d | +--------+----------------------------------+ 1 row in set (0,00 sec) mysql> SELECT SQL_NO_CACHE * FROM users WHERE `login`='login2' AND (`pwd`=MD5("badPWD") -> OR `pwd`=MD5("BADPWD")); Empty set (0,00 sec) mysql> select md5("BADPWD"); +----------------------------------+ | md5("BADPWD") | +----------------------------------+ | f2be7ab0477da7119971d20a79dcaf2d | +----------------------------------+ 1 row in set (0,00 sec) mysql> SELECT SQL_NO_CACHE * FROM users WHERE `login`='login2' AND (`pwd`=MD5("b adPWD") OR `pwd`=MD5("BADPWD")); Empty set (0,01 sec) mysql> explain SELECT SQL_NO_CACHE * FROM users WHERE `login`='login2' AND (`pwd`=MD5("badPWD") OR `pwd`=MD5("BADPWD")); +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0,01 sec) mysql> explain SELECT SQL_NO_CACHE * FROM users WHERE `login`='login2' AND (`pwd`=MD5("BADPWD") OR `pwd`=MD5("badPWD")); +----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | users | const | auth | auth | 134 | const,const | 1 | Using index | +----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0,00 sec) Looks really strange.
[11 Apr 2006 13:20]
Devis Lucato
Is this bug still open ?
[12 Apr 2006 11:37]
Valeriy Kravchuk
The bug is verified and developers work on bug fix.
[28 May 2006 18:01]
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/commits/6943
[28 May 2006 18:02]
Evgeny Potemkin
md5() and sha() functions treat their arguments as case sensitive strings. But when they are compared their arguments were compared as a case insensitive strings which leads to two functions with different arguments and thus different results to being identical. This can lead to a wrong decision made in the range optimizer and thus lead to a wrong result set.
[19 Jun 2006 0:12]
Evgeny Potemkin
Fixed in 4.1.21, 5.0.23, 5.1.12
[23 Jun 2006 4:56]
Paul DuBois
Noted in 4.1.21, 5.0.23, 5.1.12 changelogs.