List:Commits« Previous MessageNext Message »
From:V Narayanan Date:November 22 2009 11:06am
Subject:bzr commit into mysql-5.1-bugteam branch (v.narayanan:3214) Bug#47762
View as plain text  
#At file:///home/narayanan/Work/mysql_checkouts/shared_repository_directory/mysql-5.1-bugteam-47762-3/ based on revid:alfranio.correia@stripped

 3214 V Narayanan	2009-11-22
      Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
      
      The lookup of a NULL value in a column having a NOT NULL
      index was resulting in wrong query result. This was
      happening because NULLs were not being handled properly.
     @ mysql-test/r/min_null_cond.result
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Result file for test case.
     @ mysql-test/t/min_null_cond.test
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Add test cases testing various cases of comparison with NULL.
     @ sql/opt_sum.cc
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Handle the, WHERE condition being NULL, case separately.

    added:
      mysql-test/r/min_null_cond.result
      mysql-test/t/min_null_cond.test
    modified:
      sql/opt_sum.cc
=== added file 'mysql-test/r/min_null_cond.result'
--- a/mysql-test/r/min_null_cond.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/min_null_cond.result	2009-11-22 11:06:44 +0000
@@ -0,0 +1,199 @@
+# These tests test the output of the 'MIN' clause
+# when a 'WHERE' clause tests a NOT NULL column
+# for a NULL value
+#
+# More information on this issue can be found from
+# Bug#47762 Incorrect result from MIN() when WHERE 
+#           tests NOT NULL column for NULL
+# link: http://bugs.mysql.com/bug.php?id=47762
+#
+####################################################
+Test for col op NULL
+####################################################
+# Test for NULL's allowed
+CREATE TABLE t1 (a int(11), key k2 (a));
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 WHERE a = NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a = NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a <> NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a <> NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a > NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a > NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a < NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a < NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a between NULL and 10;
+a
+SELECT MIN(a) FROM t1 WHERE a between NULL and 10;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a between NULL and NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a between NULL and NULL;
+MIN(a)
+NULL
+DROP TABLE t1;
+##################################################
+# Test for NOT NULLs
+CREATE TABLE t1 (a int(11) NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 WHERE a = NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a = NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a <> NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a <> NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a > NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a > NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a < NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a < NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a between NULL and 10;
+a
+SELECT MIN(a) FROM t1 WHERE a between NULL and 10;
+MIN(a)
+NULL
+DROP TABLE t1;
+##################################################
+# Test for No Index
+CREATE TABLE t1 (a int(11));
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 WHERE a = NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a = NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a <> NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a <> NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a > NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a > NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a < NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a < NULL;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a between NULL and 10;
+a
+SELECT MIN(a) FROM t1 WHERE a between NULL and 10;
+MIN(a)
+NULL
+DROP TABLE t1;
+##############################################
+Test for NULL op col
+##############################################
+# Test for NULL's allowed
+CREATE TABLE t1 (a int(11), key k2 (a));
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 WHERE NULL = a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL = a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL <> a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL <> a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL > a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL > a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL < a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL < a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a between 10 and NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a between 10 and NULL;
+MIN(a)
+NULL
+DROP TABLE t1;
+##############################################
+# Test for NOT NULLs
+CREATE TABLE t1 (a int(11) NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 WHERE NULL = a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL = a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL <> a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL <> a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL > a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL > a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL < a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL < a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a between 10 and NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a between 10 and NULL;
+MIN(a)
+NULL
+DROP TABLE t1;
+##################################################
+# Test for No Index
+CREATE TABLE t1 (a int(11));
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 WHERE NULL = a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL = a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL <> a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL <> a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL > a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL > a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE NULL < a;
+a
+SELECT MIN(a) FROM t1 WHERE NULL < a;
+MIN(a)
+NULL
+SELECT a FROM t1 WHERE a between 10 and NULL;
+a
+SELECT MIN(a) FROM t1 WHERE a between 10 and NULL;
+MIN(a)
+NULL
+DROP TABLE t1;
+##################################################

=== added file 'mysql-test/t/min_null_cond.test'
--- a/mysql-test/t/min_null_cond.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/min_null_cond.test	2009-11-22 11:06:44 +0000
@@ -0,0 +1,149 @@
+--echo # These tests test the output of the 'MIN' clause
+--echo # when a 'WHERE' clause tests a NOT NULL column
+--echo # for a NULL value
+--echo #
+--echo # More information on this issue can be found from
+--echo # Bug#47762 Incorrect result from MIN() when WHERE 
+--echo #           tests NOT NULL column for NULL
+--echo # link: http://bugs.mysql.com/bug.php?id=47762
+--echo #
+--echo ####################################################
+--echo Test for col op NULL
+--echo ####################################################
+--echo # Test for NULL's allowed
+CREATE TABLE t1 (a int(11), key k2 (a));
+
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 WHERE a = NULL;
+SELECT MIN(a) FROM t1 WHERE a = NULL;
+
+SELECT a FROM t1 WHERE a <> NULL;
+SELECT MIN(a) FROM t1 WHERE a <> NULL;
+
+SELECT a FROM t1 WHERE a > NULL;
+SELECT MIN(a) FROM t1 WHERE a > NULL;
+
+SELECT a FROM t1 WHERE a < NULL;
+SELECT MIN(a) FROM t1 WHERE a < NULL;
+
+SELECT a FROM t1 WHERE a between NULL and 10;
+SELECT MIN(a) FROM t1 WHERE a between NULL and 10;
+
+SELECT a FROM t1 WHERE a between NULL and NULL;
+SELECT MIN(a) FROM t1 WHERE a between NULL and NULL;
+
+DROP TABLE t1;
+--echo ##################################################
+--echo # Test for NOT NULLs
+CREATE TABLE t1 (a int(11) NOT NULL PRIMARY KEY);
+
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 WHERE a = NULL;
+SELECT MIN(a) FROM t1 WHERE a = NULL;
+
+SELECT a FROM t1 WHERE a <> NULL;
+SELECT MIN(a) FROM t1 WHERE a <> NULL;
+
+SELECT a FROM t1 WHERE a > NULL;
+SELECT MIN(a) FROM t1 WHERE a > NULL;
+
+SELECT a FROM t1 WHERE a < NULL;
+SELECT MIN(a) FROM t1 WHERE a < NULL;
+
+SELECT a FROM t1 WHERE a between NULL and 10;
+SELECT MIN(a) FROM t1 WHERE a between NULL and 10;
+
+DROP TABLE t1;
+--echo ##################################################
+--echo # Test for No Index
+CREATE TABLE t1 (a int(11));
+
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 WHERE a = NULL;
+SELECT MIN(a) FROM t1 WHERE a = NULL;
+
+SELECT a FROM t1 WHERE a <> NULL;
+SELECT MIN(a) FROM t1 WHERE a <> NULL;
+
+SELECT a FROM t1 WHERE a > NULL;
+SELECT MIN(a) FROM t1 WHERE a > NULL;
+
+SELECT a FROM t1 WHERE a < NULL;
+SELECT MIN(a) FROM t1 WHERE a < NULL;
+
+SELECT a FROM t1 WHERE a between NULL and 10;
+SELECT MIN(a) FROM t1 WHERE a between NULL and 10;
+
+DROP TABLE t1;
+--echo ##############################################
+--echo Test for NULL op col
+--echo ##############################################
+--echo # Test for NULL's allowed
+CREATE TABLE t1 (a int(11), key k2 (a));
+
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 WHERE NULL = a;
+SELECT MIN(a) FROM t1 WHERE NULL = a;
+
+SELECT a FROM t1 WHERE NULL <> a;
+SELECT MIN(a) FROM t1 WHERE NULL <> a;
+
+SELECT a FROM t1 WHERE NULL > a;
+SELECT MIN(a) FROM t1 WHERE NULL > a;
+
+SELECT a FROM t1 WHERE NULL < a;
+SELECT MIN(a) FROM t1 WHERE NULL < a;
+
+SELECT a FROM t1 WHERE a between 10 and NULL;
+SELECT MIN(a) FROM t1 WHERE a between 10 and NULL;
+
+DROP TABLE t1;
+--echo ##############################################
+--echo # Test for NOT NULLs
+CREATE TABLE t1 (a int(11) NOT NULL PRIMARY KEY);
+
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 WHERE NULL = a;
+SELECT MIN(a) FROM t1 WHERE NULL = a;
+
+SELECT a FROM t1 WHERE NULL <> a;
+SELECT MIN(a) FROM t1 WHERE NULL <> a;
+
+SELECT a FROM t1 WHERE NULL > a;
+SELECT MIN(a) FROM t1 WHERE NULL > a;
+
+SELECT a FROM t1 WHERE NULL < a;
+SELECT MIN(a) FROM t1 WHERE NULL < a;
+
+SELECT a FROM t1 WHERE a between 10 and NULL;
+SELECT MIN(a) FROM t1 WHERE a between 10 and NULL;
+
+DROP TABLE t1;
+--echo ##################################################
+--echo # Test for No Index
+CREATE TABLE t1 (a int(11));
+
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 WHERE NULL = a;
+SELECT MIN(a) FROM t1 WHERE NULL = a;
+
+SELECT a FROM t1 WHERE NULL <> a;
+SELECT MIN(a) FROM t1 WHERE NULL <> a;
+
+SELECT a FROM t1 WHERE NULL > a;
+SELECT MIN(a) FROM t1 WHERE NULL > a;
+
+SELECT a FROM t1 WHERE NULL < a;
+SELECT MIN(a) FROM t1 WHERE NULL < a;
+
+SELECT a FROM t1 WHERE a between 10 and NULL;
+SELECT MIN(a) FROM t1 WHERE a between 10 and NULL;
+
+DROP TABLE t1;
+--echo ##################################################

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2009-10-14 08:46:50 +0000
+++ b/sql/opt_sum.cc	2009-11-22 11:06:44 +0000
@@ -527,11 +527,11 @@ bool simple_pred(Item_func *func_item, I
     {
       args[0]= item;
       item= func_item->arguments()[1];
-      if (!item->const_item())
+      if (!item->const_item() || item->type() == Item::NULL_ITEM)
         return 0;
       args[1]= item;
     }
-    else if (item->const_item())
+    else if (item->const_item() && (item->type() != Item::NULL_ITEM))
     {
       args[1]= item;
       item= func_item->arguments()[1];
@@ -552,7 +552,7 @@ bool simple_pred(Item_func *func_item, I
       for (int i= 1 ; i <= 2; i++)
       {
         item= func_item->arguments()[i];
-        if (!item->const_item())
+        if (!item->const_item() || item->type() == Item::NULL_ITEM)
           return 0;
         args[i]= item;
       }


Attachment: [text/bzr-bundle] bzr/v.narayanan@sun.com-20091122110644-e2sgcar4lfbxj1sl.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (v.narayanan:3214) Bug#47762V Narayanan22 Nov
  • Re: bzr commit into mysql-5.1-bugteam branch (v.narayanan:3214)Bug#47762Sergei Golubchik23 Nov