bk commit into 5.0 tree (gkodinov:1.2389) BUG#25551

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-01-24 14:06:17+02:00, gkodinov@stripped +3 -0
  Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
   The optimizer takes away columns from GROUP BY/DISTINCT if they constitute
   all the parts of an unique index.
   However if some of the columns can contain NULLs this cannot be done 
  (because an UNIQUE index can have multiple rows with NULL values).
   Fixed by not using UNIQUE indexes with nullable columns to remove
   grouping columns from GROUP BY/DISTINCT.

  mysql-test/r/distinct.result@stripped, 2007-01-24 14:06:10+02:00, gkodinov@stripped +17
-1
    Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
     - test case

  mysql-test/t/distinct.test@stripped, 2007-01-24 14:06:10+02:00, gkodinov@stripped +16 -1
    Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
     - test case

  sql/sql_select.cc@stripped, 2007-01-24 14:06:11+02:00, gkodinov@stripped +2 -1
    Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
     - UNIQUE NULL indices don't guarantee GROUP BY/DISTINCT.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B25551-5.0-opt

--- 1.481/sql/sql_select.cc	2007-01-19 17:33:29 +02:00
+++ 1.482/sql/sql_select.cc	2007-01-24 14:06:11 +02:00
@@ -11903,7 +11903,8 @@ list_contains_unique_index(TABLE *table,
            key_part < key_part_end;
            key_part++)
       {
-        if (!find_func(key_part->field, data))
+        if (key_part->field->maybe_null() || 
+            !find_func(key_part->field, data))
           break;
       }
       if (key_part == key_part_end)

--- 1.50/mysql-test/r/distinct.result	2007-01-05 14:02:43 +02:00
+++ 1.51/mysql-test/r/distinct.result	2007-01-24 14:06:10 +02:00
@@ -530,7 +530,8 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
-CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
+CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 
+PRIMARY KEY (a,b));
 INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
 EXPLAIN SELECT DISTINCT a FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -644,3 +645,18 @@ SELECT COUNT(*) FROM 
 COUNT(*)
 2
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT, UNIQUE (a));
+INSERT INTO t1 VALUES (null),(null);
+EXPLAIN SELECT DISTINCT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	2	Using index
+SELECT DISTINCT a FROM t1;
+a
+NULL
+EXPLAIN SELECT a FROM t1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	2	Using index
+SELECT a FROM t1 GROUP BY a;
+a
+NULL
+DROP TABLE t1;

--- 1.29/mysql-test/t/distinct.test	2007-01-05 14:02:44 +02:00
+++ 1.30/mysql-test/t/distinct.test	2007-01-24 14:06:10 +02:00
@@ -364,7 +364,8 @@ EXPLAIN SELECT a FROM t1 GROUP BY a;
 EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
 EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
 
-CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
+CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 
+                PRIMARY KEY (a,b));
 INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
 EXPLAIN SELECT DISTINCT a FROM t2;
 EXPLAIN SELECT DISTINCT a,a FROM t2;
@@ -525,3 +526,17 @@ SELECT COUNT(*) FROM 
   (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
 
 DROP TABLE t1, t2;
+
+#
+# Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
+#
+CREATE TABLE t1 (a INT, UNIQUE (a));
+INSERT INTO t1 VALUES (null),(null);
+EXPLAIN SELECT DISTINCT a FROM t1;
+#result must have one row with NULL
+SELECT DISTINCT a FROM t1;
+EXPLAIN SELECT a FROM t1 GROUP BY a;
+#result must have one row with NULL
+SELECT a FROM t1 GROUP BY a;
+
+DROP TABLE t1;