Bug #49517 Inconsistent behavior while using NULLable BIGINT and INT columns in comparison
Submitted: 7 Dec 2009 17:24 Modified: 12 Mar 2010 16:33
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.42 OS:Any
Assigned to: Ramil Kalimullin
Triage: Triaged: D2 (Serious)

[7 Dec 2009 17:24] Elena Stepanova
Description:
Starting from 5.1.42, results of comparing a NULL value in a BIGINT column to an integer constant became different from other integer types.

In 'How to repeat' section, columns c2 and c3 are BIGINT and INT, correspondingly. For the selects, I'm getting the following results for 5.1.42 vs 5.1.41:

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

mysql> SELECT * FROM t3 WHERE c2 < 102;
+-----+------+------+
| c1  | c2   | c3   |
+-----+------+------+
| 105 | NULL | NULL |
+-----+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t3 WHERE c3 < 102;
Empty set (0.00 sec)

mysql> SELECT @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.1.41-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t3 WHERE c2 < 102;
Empty set (0.00 sec)

mysql> SELECT * FROM t3 WHERE c3 < 102;
Empty set (0.00 sec)

How to repeat:
USE test;
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT NULL, c3 INT NULL);
INSERT INTO t3 VALUES(105,NULL,NULL);
SELECT @@version;
SELECT * FROM t3 WHERE c2 < 102;
SELECT * FROM t3 WHERE c3 < 102;

Suggested fix:
I'm not sure which is correct, i.e. if NULL is less or greater than a constant, but apparently the result should be the same for INT types.
[8 Dec 2009 6:13] Philip Stoev
Comparisons between NULL and a constant should always result in a NULL, so the entire WHERE should match no rows at all.
[11 Dec 2009 20:36] 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/93761

3263 Ramil Kalimullin	2009-12-12
      Fix for bug#49517: Inconsistent behavior while using 
      NULLable BIGINT and INT columns in comparison
      
      Problem: a consequence of the fix for 43668.
      Some Arg_comparator inner initialization missed,
      that may lead to unpredictable (wrong) comparison
      results.
      
      Fix: always properly initialize Arg_comparator
      before its usage.
     @ mysql-test/r/select.result
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          -test result.
     @ mysql-test/t/select.test
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          -test case.
     @ sql/item_cmpfunc.cc
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          - now all Arg_comparator::set_cmp_func() set
        Arg_comparator::set_null to ensure its initialization
        in all cases.
     @ sql/item_cmpfunc.h
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          - now all Arg_comparator::set_cmp_func() set
        Arg_comparator::set_null to ensure its initialization
        in all cases.
[14 Dec 2009 15:47] 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/93944

3269 Ramil Kalimullin	2009-12-14
      Fix for bug#49517: Inconsistent behavior while using 
      NULLable BIGINT and INT columns in comparison
      
      Problem: a consequence of the fix for 43668.
      Some Arg_comparator inner initialization missed,
      that may lead to unpredictable (wrong) comparison
      results.
      
      Fix: always properly initialize Arg_comparator
      before its usage.
     @ mysql-test/r/select.result
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          -test result.
     @ mysql-test/t/select.test
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          -test case.
     @ sql/item_cmpfunc.cc
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          - now all Arg_comparator::set_cmp_func() set
        Arg_comparator::set_null to ensure its proper initialization
        in all cases (by default it's set to TRUE in constructors).
     @ sql/item_cmpfunc.h
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          - now all Arg_comparator::set_cmp_func() set
        Arg_comparator::set_null to ensure its proper initialization
        in all cases (by default it's set to TRUE in constructors).
[15 Dec 2009 17: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/94216

3244 Ramil Kalimullin	2009-12-15
      Fix for bug#49517: Inconsistent behavior while using 
      NULLable BIGINT and INT columns in comparison
      
      Problem: a consequence of the fix for 43668.
      Some Arg_comparator inner initialization missed,
      that may lead to unpredictable (wrong) comparison
      results.
      
      Fix: always properly initialize Arg_comparator
      before its usage.
     @ mysql-test/r/select.result
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          -test result.
     @ mysql-test/t/select.test
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          -test case.
     @ sql/item_cmpfunc.cc
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          - now all Arg_comparator::set_cmp_func() set
        Arg_comparator::set_null to ensure its proper initialization
        in all cases (by default it's set to TRUE in constructors).
     @ sql/item_cmpfunc.h
        Fix for bug#49517: Inconsistent behavior while using 
        NULLable BIGINT and INT columns in comparison
          - now all Arg_comparator::set_cmp_func() set
        Arg_comparator::set_null to ensure its proper initialization
        in all cases (by default it's set to TRUE in constructors).
[16 Dec 2009 8:55] Bugs System
Pushed into 5.1.43 (revid:ramil@mysql.com-20091215170821-eexh9rqrnw1ruzh2) (version source revid:ramil@mysql.com-20091215170821-eexh9rqrnw1ruzh2) (merge vers: 5.1.43) (pib:14)
[17 Dec 2009 18:55] Paul Dubois
Actually pushed into 5.1.42 now.
[18 Dec 2009 2:11] Paul Dubois
Noted in 5.1.42 changelog.

The result of comparison between nullable BIGINT and INT columns was
inconsistent. 

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:31] 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:43] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.
[12 Mar 2010 14:20] 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:35] 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:51] 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)