| 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 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.
Description: I found a problem with a query that was not returning expected result. As you can see from the example the first query is ok, the second query, where I change the conditions order in the parethesis, the result change. The problems seems to depend on the key, as if I remove it the problem vanishes. The "NOT NULL" and "DEFAULT" does not influence this bug. EXPLAIN returns "Impossible WHERE noticed after reading const tables" for the second query. How to repeat: # TABLE STRUCTURE DROP TABLE IF EXISTS `users`; 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; # TABLE DATA INSERT INTO users VALUES ( 'login1',MD5('test')), ('login2', MD5('BADPWD')); # QUERY 1: OK SELECT SQL_NO_CACHE * FROM users WHERE `login`='login2' AND (`pwd`=MD5("BADPWD") OR `pwd`=MD5("badPWD")); # QUERY 2: NOT OK SELECT SQL_NO_CACHE * FROM users WHERE `login`='login2' AND (`pwd`=MD5("badPWD") OR `pwd`=MD5("BADPWD"));