Bug #49489 Comparison using a DOUBLE column started producing different results
Submitted: 6 Dec 2009 19:45 Modified: 12 Mar 2010 16:36
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.42, 5.1.43 OS:Any
Assigned to: Evgeny Potemkin
Tags: regression
Triage: Triaged: D2 (Serious)

[6 Dec 2009 19:45] Elena Stepanova
Description:
Server 5.1.42 returns different results comparing to previous versions when a condition is using DOUBLE column for comparison. For example, the select in 'How to reproduce' section does not return the row anymore:

mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.1.41-enterprise-commercial-advanced-log |
+-------------------------------------------+
mysql> SELECT * FROM t5 WHERE c1 < 9.12345;
+--------+
| c1     |
+--------+
| 9.1234 |
+--------+

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.1.42-log |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t5 WHERE c1 < 9.12345;
Empty set (0.00 sec)

It might be related to fixes for bug#48370 / bug#45261.
Also, there is bug#49089, but I'm not sure whether the provided test case is inside the scope of the bug.

How to repeat:
USE test;
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(c1 DOUBLE(5,4) NOT NULL);
INSERT INTO t5 VALUES (9.1234);
select @@version;
SELECT * FROM t5 WHERE c1 < 9.12345;
[7 Dec 2009 5:02] Valerii Kravchuk
Verified just as described with recent 5.1.43 from bzr on Mac OS X:

77-52-7-73:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS t5;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t5(c1 DOUBLE(5,4) NOT NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t5 VALUES (9.1234);
Query OK, 1 row affected (0.02 sec)

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.1.43-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t5 WHERE c1 < 9.12345;
Empty set (0.02 sec)

This is also a regression comparing to recent 5.0.x.
[7 Dec 2009 13:41] Valerii Kravchuk
This bug does NOLT appear in 5.1.41 or any previous version as you can see:

mysql> DROP TABLE IF EXISTS t5;
Query OK, 0 rows affected, 1 warning (0.27 sec)

mysql> CREATE TABLE t5(c1 DOUBLE(5,4) NOT NULL);
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO t5 VALUES (9.1234);
Query OK, 1 row affected (0.17 sec)

mysql> select @@version;
+------------------+
| @@version        |
+------------------+
| 5.1.41-community |
+------------------+
1 row in set (0.06 sec)

mysql> SELECT * FROM t5 WHERE c1 < 9.12345;
+--------+
| c1     |
+--------+
| 9.1234 |
+--------+
1 row in set (0.03 sec)
[7 Dec 2009 18:39] Davi Arnaut
FWIW, this has rarely anything to do with Bug#48370, Bug#45261 or Bug#49089. Those are about truncation when deducing the appropriate field precision for a expression. This is a probably a bug revealed by the patch for Bug#34384. The "fix" below should give a hint to the assigned developer:

=== modified file 'sql/item.cc'
--- sql/item.cc	2009-11-24 15:30:21 +0000
+++ sql/item.cc	2009-12-07 18:34:15 +0000
@@ -6973,9 +6973,10 @@ Item_cache* Item_cache::get_cache(const 
 
 void Item_cache::store(Item *item)
 {
-  example= item;
   if (!item)
     null_value= TRUE;
+  else
+    setup(item);
   value_cached= FALSE;
 }
 
Arg_comparator::set_compare_func attempts to access Item_cache::decimals before the item is setup..
[8 Dec 2009 14:03] 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/93202

3252 Evgeny Potemkin	2009-12-08
      Bug#49489: Uninitialized cache led to a wrong result.
      
      Arg_comparator uses Item_cache objects to store constants being compared when
      they're need a type conversion. Because this cache wasn't initialized properly
      Arg_comparator might produce wrong comparison result.
      
      The Arg_comparator::cache_converted_constant function now initializes cache
      prior to usage.
     @ mysql-test/r/select.result
        Added a test case for he bug#49489.
     @ mysql-test/t/select.test
        Added a test case for he bug#49489.
     @ sql/item_cmpfunc.cc
        Bug#49489: Uninitialized cache led to a wrong result.
        The Arg_comparator::cache_converted_constant function now initializes cache
        prior to usage.
[9 Dec 2009 15:44] 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/93357

3252 Evgeny Potemkin	2009-12-09
      Bug#49489: Uninitialized cache led to a wrong result.
      
      Arg_comparator uses Item_cache objects to store constants being compared when
      they're need a type conversion. Because this cache wasn't initialized properly
      Arg_comparator might produce wrong comparison result.
      
      The Arg_comparator::cache_converted_constant function now initializes cache
      prior to usage.
     @ mysql-test/r/select.result
        Added a test case for he bug#49489.
     @ mysql-test/t/select.test
        Added a test case for he bug#49489.
     @ sql/item_cmpfunc.cc
        Bug#49489: Uninitialized cache led to a wrong result.
        The Arg_comparator::cache_converted_constant function now initializes cache
        prior to usage.
[11 Dec 2009 13:09] 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/93680

3262 Evgeny Potemkin	2009-12-11 [merge]
      Auto-merged fix for bug#49489.
[15 Dec 2009 8:55] 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/94064

3242 Georgi Kodinov	2009-12-15
      Bug#49489: Uninitialized cache led to a wrong result.
      
      Merge the fix from 5.1-bugteam to 5.1-main
[16 Dec 2009 8:55] Bugs System
Pushed into 5.1.43 (revid:ramil@mysql.com-20091215170821-eexh9rqrnw1ruzh2) (version source revid:joro@sun.com-20091215085453-zrmxqdap0ezzxfqn) (merge vers: 5.1.43) (pib:14)
[17 Dec 2009 18:58] Paul Dubois
Actually pushed into 5.1.42 now.
[18 Dec 2009 2:18] Paul Dubois
Noted in 5.1.42 changelog.

Incorrect cache initialization prevented storage of converted
constant values and could produce incorrect comparison results. 

Setting report to NDI pending push to 5.5.x+.
[19 Dec 2009 8:27] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:30] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alik@sun.com-20091216183525-vbefoeydwonfxkye) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216183710-1stho81j3b8f0kds) (pib:15)
[20 Dec 2009 0:40] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.
[15 Jan 2010 8:59] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:joro@sun.com-20091211152409-0dongvpiwe75xtn7) (merge vers: 5.1.42) (pib:16)
[12 Mar 2010 14:16] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:32] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:48] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)