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:
  Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
   - test case
mysql-test/t/distinct.test:
  Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
   - test case
sql/sql_select.cc:
  Bug #25551: inconsistent behaviour in grouping NULL, depending on index type
   - UNIQUE NULL indices don't guarantee GROUP BY/DISTINCT.

1 files changed, 16 insertions, 1 deletions

diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index 8734b940241..476e4ce7735 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -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 (4),(null),(2),(1),(null),(3);
+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;