List:Commits« Previous MessageNext Message »
From:V Narayanan Date:November 11 2009 7:18pm
Subject:bzr commit into mysql-5.1-bugteam branch (v.narayanan:3197) 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:anurag.shekhar@stripped

 3197 V Narayanan	2009-11-12
      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
      in opt_sum_query.
     @ mysql-test/r/key.result
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Result file for test case.
     @ mysql-test/t/key.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.

    modified:
      mysql-test/r/key.result
      mysql-test/t/key.test
      sql/opt_sum.cc
=== modified file 'mysql-test/r/key.result'
--- a/mysql-test/r/key.result	2007-12-05 19:33:36 +0000
+++ b/mysql-test/r/key.result	2009-11-11 19:18:32 +0000
@@ -604,3 +604,37 @@ SELECT 1 as RES FROM t1 AS t1_outer WHER
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 RES
 DROP TABLE t1;
+#
+# Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
+#
+CREATE TABLE t1 (a int(11), key k2 (a)) engine=innodb;
+Warnings:
+Warning	1286	Unknown table engine 'innodb'
+Warning	1266	Using storage engine MyISAM for table 't1'
+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;

=== modified file 'mysql-test/t/key.test'
--- a/mysql-test/t/key.test	2007-12-05 19:33:36 +0000
+++ b/mysql-test/t/key.test	2009-11-11 19:18:32 +0000
@@ -561,3 +561,28 @@ SELECT 1 as RES FROM t1 AS t1_outer WHER
   (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 
 DROP TABLE t1;
+
+--echo #
+--echo # Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
+--echo #
+
+CREATE TABLE t1 (a int(11), key k2 (a)) engine=innodb;
+
+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-11 19:18:32 +0000
@@ -908,6 +908,14 @@ static bool find_key_for_maxmin(bool max
 static int reckey_in_range(bool max_fl, TABLE_REF *ref, Field* field,
                             COND *cond, uint range_fl, uint prefix_len)
 {
+  /*
+    If the where condition is NULL and the IS NULL function
+    is not being called return 1 to signify that the WHERE
+    is not true.
+  */
+  if (cond->is_null() && 
+     (((Item_func*) cond)->functype() != Item_func::ISNULL_FUNC))
+    return 1;
   if (key_cmp_if_same(field->table, ref->key_buff, ref->key, prefix_len))
     return 1;
   if (!cond || (range_fl & (max_fl ? NO_MIN_RANGE : NO_MAX_RANGE)))


Attachment: [text/bzr-bundle] bzr/v.narayanan@sun.com-20091111191832-rymhz0bxt6zxjb55.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (v.narayanan:3197) Bug#47762V Narayanan11 Nov