Bug #45360 wrong results
Submitted: 7 Jun 2009 13:30 Modified: 20 Jun 2010 22:27
Reporter: Matthew Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.82, 5.0.84-bzr, 5.1.36-bzr OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any

[7 Jun 2009 13:30] Matthew Lord
Description:
mysql> select obj_crc, MD5(obj_crc) from PL_OBJECTS where MD5(obj_crc) = 'e6106477d9a4071941c60301c41284d8';
+----------------------+----------------------------------+
| obj_crc              | MD5(obj_crc)                     |
+----------------------+----------------------------------+
| 15493286598316916735 | e6106477d9a4071941c60301c41284d8 | 
+----------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> select obj_crc, MD5(obj_crc) from PL_OBJECTS where obj_crc = 15493286598316916735;                                                
+----------------------+----------------------------------+
| obj_crc              | MD5(obj_crc)                     |
+----------------------+----------------------------------+
| 15493286598316916735 | e6106477d9a4071941c60301c41284d8 | 
+----------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> select obj_crc, MD5(obj_crc) from PL_OBJECTS where obj_crc = 15493286598316916735 AND MD5(1) = 'e6106477d9a4071941c60301c41284d8';
Empty set (0.00 sec)

How to repeat:
CREATE TABLE `PL_OBJECTS` ( `obj_crc` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`obj_crc`) );

insert into PL_OBJECTS values (15493286598316916735);

select obj_crc, MD5(obj_crc) from PL_OBJECTS where MD5(obj_crc) = 'e6106477d9a4071941c60301c41284d8';

select obj_crc, MD5(obj_crc) from PL_OBJECTS where obj_crc = 15493286598316916735; 

select obj_crc, MD5(obj_crc) from PL_OBJECTS where obj_crc = 15493286598316916735 AND MD5(1) = 'e6106477d9a4071941c60301c41284d8';
[7 Jun 2009 14:01] Matthew Lord
I accidentally cut and pasted the wrong query.  It's academic but here's
the final query that I meant to include:

select obj_crc, MD5(obj_crc) from PL_OBJECTS where obj_crc = 15493286598316916735 AND MD5(obj_crc) = 'e6106477d9a4071941c60301c41284d8';
[7 Jun 2009 14:09] Valeriy Kravchuk
This is an optimizer bug (repeatable with latest 5.0.84 from bzr as well):

mysql> explain select obj_crc, MD5(obj_crc) from PL_OBJECTS where obj_crc = 15493286598316916735 AND MD5(obj_crc) = 'e6106477d9a4071941c60301c41284d8';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> select obj_crc, MD5(obj_crc) from PL_OBJECTS;
+----------------------+----------------------------------+
| obj_crc              | MD5(obj_crc)                     |
+----------------------+----------------------------------+
| 15493286598316916735 | e6106477d9a4071941c60301c41284d8 | 
| 15493286598316916736 | 5da80114755c208154407222c1ab2709 | 
+----------------------+----------------------------------+
2 rows in set (0.00 sec)
[9 Jun 2009 21:47] Peter Laursen
Once again: How can this be 'target 6.0 beta' when there will be no more 6.0
releases?

Could the people at Oracle/SUN/MySQL please oordinate a litte?
[9 Jun 2009 21:48] Peter Laursen
'coordinate' I meant!
[23 Feb 2010 18:25] Gleb Shchepa
Preliminary patch is one-liner:

=== modified file 'sql/item.cc'
--- old/sql/item.cc     2010-02-17 12:13:42 +0000
+++ new/sql/item.cc     2010-02-23 16:41:46 +0000
@@ -2209,7 +2209,7 @@ String *Item_int::val_str(String *str)
 {
   // following assert is redundant, because fixed=1 assigned in constructor
   DBUG_ASSERT(fixed == 1);
-  str->set(value, &my_charset_bin);
+  str->set_int(value, unsigned_flag, &my_charset_bin);
   return str;
 }
[24 Feb 2010 14:54] 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/101332

3350 Gleb Shchepa	2010-02-24
      Bug #45360: wrong results
      
      Propagation of a large unsigned numeric constant
      in the WHERE expression led to wrong result.
      
      For example,
      "WHERE a = CAST(0xFFFFFFFFFFFFFFFF AS USIGNED) AND FOO(a)",
      where a is an UNSIGNED BIGINT, and FOO() accepts strings,
      was transformed to "... AND FOO('-1')".
      
      That has been fixed.
      
      Also EXPLAIN EXTENDED printed incorrect numeric constants in
      transformed WHERE expressions like above. That has been
      fixed too.
     @ mysql-test/r/bigint.result
        Added test case for bug #45360.
     @ mysql-test/t/bigint.test
        Added test case for bug #45360.
     @ sql/item.cc
        Bug #45360: wrong results
        
        As far as Item_int_with_ref (and underlaying Item_int)
        class accepts both signed and unsigned 64bit values,
        Item_int::val_str and Item_int::print methods have been
        modified to take into account unsigned_flag.
[25 Feb 2010 19:29] 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/101517

3357 Gleb Shchepa	2010-02-25
      Bug #45360: wrong results
      
      Propagation of a large unsigned numeric constant
      in the WHERE expression led to wrong result.
      
      For example,
      "WHERE a = CAST(0xFFFFFFFFFFFFFFFF AS USIGNED) AND FOO(a)",
      where a is an UNSIGNED BIGINT, and FOO() accepts strings,
      was transformed to "... AND FOO('-1')".
      
      That has been fixed.
      
      Also EXPLAIN EXTENDED printed incorrect numeric constants in
      transformed WHERE expressions like above. That has been
      fixed too.
     @ mysql-test/r/bigint.result
        Added test case for bug #45360.
     @ mysql-test/t/bigint.test
        Added test case for bug #45360.
     @ sql/item.cc
        Bug #45360: wrong results
        
        As far as Item_int_with_ref (and underlaying Item_int)
        class accepts both signed and unsigned 64bit values,
        Item_int::val_str and Item_int::print methods have been
        modified to take into account unsigned_flag.
[1 Mar 2010 8:45] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:gshchepa@mysql.com-20100225191311-1x71dkk0h5e1alvx) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:34] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:40] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100226130631-8czhisohzf6jyo2x) (merge vers: 5.5.3-m2) (pib:16)
[2 Mar 2010 14:45] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301093944-a4rvrmqqco6c0qao) (pib:16)
[16 Mar 2010 0:22] Paul Dubois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

Propagation of a large unsigned numeric constant in WHERE expressions
could lead to incorrect results. This also affected EXPLAIN EXTENDED,
which printed incorrect numeric constants in such transformed WHERE
expressions.
[17 Jun 2010 11:50] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:28] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:15] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)