MySQL Bugs: #49517: Inconsistent behavior while using NULLable BIGINT and INT columns in comparison

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	CPU Architecture:	Any

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.

Comparisons between NULL and a constant should always result in a NULL, so the entire WHERE should match no rows at all.

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.

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).

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).

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)

Actually pushed into 5.1.42 now.

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+.

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216183710-1stho81j3b8f0kds) (pib:15)

Noted in 5.5.1, 6.0.14 changelogs.

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)

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)

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)