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:
None 
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
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"));
[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.