| 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: | |
| 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
[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)
