List:Commits« Previous MessageNext Message »
From:V Narayanan Date:November 20 2009 4:02am
Subject:bzr commit into mysql-5.1-bugteam branch (v.narayanan:3208) 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:sven.sandberg@stripped

 3208 V Narayanan	2009-11-20
      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-20 04:02:14 +0000
@@ -0,0 +1,96 @@
+# 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 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
+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;

=== 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-20 04:02:14 +0000
@@ -0,0 +1,75 @@
+--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 # 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;
+
+DROP TABLE t1;
+
+--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 # 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;

=== 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-20 04:02:14 +0000
@@ -527,7 +527,7 @@ 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;
     }


Attachment: [text/bzr-bundle] bzr/v.narayanan@sun.com-20091120040214-pdjmy1ke0809ar1d.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (v.narayanan:3208) Bug#47762V Narayanan20 Nov
  • Re: bzr commit into mysql-5.1-bugteam branch (v.narayanan:3208)Bug#47762Sergei Golubchik20 Nov
    • Re: bzr commit into mysql-5.1-bugteam branch (v.narayanan:3208)Bug#47762V Narayanan20 Nov
    • Re: bzr commit into mysql-5.1-bugteam branch (v.narayanan:3208)Bug#47762V Narayanan22 Nov