MDEV-10880: Assertions `keypart_map' or `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and GROUP BY constantbb-10.1-MDEV-10880

add_group_and_distinct_keys() should take into account JOIN::simple_group.

9 files changed, 65 insertions, 37 deletions

diff --git a/mysql-test/r/bench_count_distinct.result b/mysql-test/r/bench_count_distinct.result
index 8b67e4be38a..79e12afd237 100644
--- a/mysql-test/r/bench_count_distinct.result
+++ b/mysql-test/r/bench_count_distinct.result
@@ -5,7 +5,7 @@ count(distinct n)
 100
 explain extended select count(distinct n) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	range	NULL	n	4	NULL	10	100.00	Using index for group-by
+1	SIMPLE	t1	index	NULL	n	4	NULL	200	100.00	Using index
 Warnings:
 Note	1003	select count(distinct `test`.`t1`.`n`) AS `count(distinct n)` from `test`.`t1`
 drop table t1;
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index d6e5a69e217..b5e8cefca69 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -212,7 +212,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	1	Using index
 explain SELECT distinct a from t3 order by a desc limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	index	NULL	a	5	NULL	40	Using index
+1	SIMPLE	t3	index	NULL	a	5	NULL	2	Using index
 explain SELECT distinct a,b from t3 order by a+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	204	Using temporary; Using filesort
diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result
index 568373f1e16..a46a3bcefa5 100644
--- a/mysql-test/r/explain_json.result
+++ b/mysql-test/r/explain_json.result
@@ -1030,10 +1030,10 @@ Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
 explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
+1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
 explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	65	Using where; Using index for group-by (scanning)
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
 explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
 EXPLAIN
 {
@@ -1041,14 +1041,14 @@ EXPLAIN
     "select_id": 1,
     "table": {
       "table_name": "t1",
-      "access_type": "range",
-      "key": "idx_t1_1",
+      "access_type": "index",
+      "key": "idx_t1_2",
       "key_length": "147",
       "used_key_parts": ["a1", "a2", "b"],
-      "rows": 17,
+      "rows": 128,
       "filtered": 100,
       "attached_condition": "((t1.b = 'a') and (t1.a2 >= 'b'))",
-      "using_index_for_group_by": true
+      "using_index": true
     }
   }
 }
@@ -1059,14 +1059,14 @@ EXPLAIN
     "select_id": 1,
     "table": {
       "table_name": "t1",
-      "access_type": "range",
+      "access_type": "index",
       "key": "idx_t1_1",
       "key_length": "163",
       "used_key_parts": ["a1", "a2", "b", "c"],
-      "rows": 65,
+      "rows": 128,
       "filtered": 100,
       "attached_condition": "((t1.b = 'a') and (t1.c = 'i121') and (t1.a2 >= 'b'))",
-      "using_index_for_group_by": "scanning"
+      "using_index": true
     }
   }
 }
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 8fbcdae38cd..cd7f1014ec0 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -1707,13 +1707,13 @@ select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1
 a1	a2	b
 explain select distinct a1,a2,b from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
+1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using index
 explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
+1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
 explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	50.78	Using where; Using index
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	100.00	Using where; Using index
 Warnings:
 Note	1003	select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 'a') and (`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`a2` >= 'b'))
 explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
@@ -1724,13 +1724,13 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
 explain select distinct a1,a2,b from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using index for group-by
+1	SIMPLE	t2	index	NULL	idx_t2_2	146	NULL	#	Using index
 explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
+1	SIMPLE	t2	index	NULL	idx_t2_2	146	NULL	#	Using where; Using index
 explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	50.61	Using where; Using index
+1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	100.00	Using where; Using index
 Warnings:
 Note	1003	select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`b` = 'a') and (`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`a2` >= 'b'))
 explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
@@ -1855,7 +1855,7 @@ c	e
 d	e
 explain select distinct a1,a2,b from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using index for group-by
+1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using index
 explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
@@ -1870,7 +1870,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by; Using temporary; Using filesort
 explain select distinct a1,a2,b from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using index for group-by
+1	SIMPLE	t2	index	NULL	idx_t2_2	146	NULL	#	Using index
 explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
@@ -1953,10 +1953,10 @@ b
 a
 explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
+1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
 explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	163	NULL	65	Using where; Using index for group-by (scanning)
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
 explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	14	100.00	Using where; Using index for group-by
@@ -2173,7 +2173,7 @@ c
 d
 explain select distinct a1 from t1 where a2 = 'b';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using where; Using index for group-by
+1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using where; Using index
 select distinct a1 from t1 where a2 = 'b';
 a1
 a
@@ -2283,7 +2283,7 @@ INSERT INTO t1 (a) VALUES
 ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
 EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	a	66	NULL	6	Using index for group-by
+1	SIMPLE	t1	index	NULL	a	66	NULL	11	Using index
 SELECT DISTINCT a,a FROM t1 ORDER BY a;
 a	a
 	
@@ -2499,7 +2499,7 @@ INSERT INTO t1 VALUES
 (4), (2), (1), (2), (2), (4), (1), (4);
 EXPLAIN SELECT DISTINCT(a) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx	5	NULL	9	Using index for group-by
+1	SIMPLE	t1	index	NULL	idx	5	NULL	16	Using index
 SELECT DISTINCT(a) FROM t1;
 a
 1
@@ -2507,7 +2507,7 @@ a
 4
 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx	5	NULL	9	Using index for group-by
+1	SIMPLE	t1	index	NULL	idx	5	NULL	16	Using index
 SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
 a
 1
@@ -2646,7 +2646,7 @@ INSERT INTO t1 SELECT * FROM t1;
 INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
 EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	foo	10	NULL	9	Using where; Using index for group-by
+1	SIMPLE	t1	index	NULL	foo	20	NULL	32	Using where; Using index
 SELECT DISTINCT c FROM t1 WHERE d=4;
 c
 1
@@ -3339,19 +3339,19 @@ INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2;
 INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2;
 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	a	5	NULL	9	Using index for group-by
+1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
 SELECT COUNT(DISTINCT a) FROM t1;
 COUNT(DISTINCT a)
 2
 EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	a	10	NULL	9	Using index for group-by
+1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
 SELECT COUNT(DISTINCT a,b) FROM t1;
 COUNT(DISTINCT a,b)
 16
 EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	a	10	NULL	9	Using index for group-by
+1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
 SELECT COUNT(DISTINCT b,a) FROM t1;
 COUNT(DISTINCT b,a)
 16
@@ -3414,7 +3414,7 @@ COUNT(DISTINCT a)
 2
 EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	a	5	NULL	9	Using index for group-by
+1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
 SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10;
 1
 1
@@ -3435,19 +3435,19 @@ COUNT(DISTINCT t1_1.a)
 1
 EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	a	5	NULL	9	Using index for group-by
+1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index
 SELECT COUNT(DISTINCT a), 12 FROM t1;
 COUNT(DISTINCT a)	12
 2	12
 EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	NULL	a	15	NULL	9	Using index for group-by
+1	SIMPLE	t2	index	NULL	a	15	NULL	16	Using index
 SELECT COUNT(DISTINCT a, b, c) FROM t2;
 COUNT(DISTINCT a, b, c)
 16
 EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	NULL	a	5	NULL	9	Using index for group-by
+1	SIMPLE	t2	index	NULL	a	15	NULL	16	Using index
 SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2;
 COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT a)
 2	3	1.5000
@@ -3459,7 +3459,7 @@ COUNT(DISTINCT a)	SUM(DISTINCT a)	AVG(DISTINCT f)
 2	3	1.0000
 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	NULL	a	10	NULL	9	Using index for group-by
+1	SIMPLE	t2	index	NULL	a	15	NULL	16	Using index
 SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2;
 COUNT(DISTINCT a, b)	COUNT(DISTINCT b, a)
 16	16
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index e24d9a94891..71aa32f0cee 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -3145,3 +3145,19 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 drop table t0,t1,t2,t3;
 set @@optimizer_switch=@tmp_8989;
 set optimizer_switch='orderby_uses_equalities=on';
+#
+# MDEV-10880: Assertions `keypart_map' or
+# `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and
+# GROUP BY constant
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT DISTINCT pk FROM t1 GROUP BY 'foo';
+pk
+1
+SELECT DISTINCT pk FROM t1;
+pk
+1
+2
+3
+DROP TABLE t1;
diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result
index 97bfbbe4eaf..5c29f5f8283 100644
--- a/mysql-test/suite/vcol/r/vcol_select_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result
@@ -133,7 +133,7 @@ count(distinct c)
 3
 explain select count(distinct c) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	c	5	NULL	6	Using index for group-by (scanning)
+1	SIMPLE	t1	index	NULL	c	5	NULL	5	Using index
 ###
 ### filesort & range-based utils
 ###
diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result
index 6dee132b3e5..7c371a1008a 100644
--- a/mysql-test/suite/vcol/r/vcol_select_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result
@@ -133,7 +133,7 @@ count(distinct c)
 3
 explain select count(distinct c) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	c	5	NULL	6	Using index for group-by (scanning)
+1	SIMPLE	t1	index	NULL	c	5	NULL	5	Using index
 ###
 ### filesort & range-based utils
 ###
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 2d0c134a2d9..f61a6a8be34 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -2091,3 +2091,15 @@ set @@optimizer_switch=@tmp_8989;
 
 set optimizer_switch='orderby_uses_equalities=on';
 
+
+--echo #
+--echo # MDEV-10880: Assertions `keypart_map' or
+--echo # `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and
+--echo # GROUP BY constant
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT DISTINCT pk FROM t1 GROUP BY 'foo';
+SELECT DISTINCT pk FROM t1;
+DROP TABLE t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 50f121ce47f..9f339146646 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5708,7 +5708,7 @@ add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab)
   Item_field *cur_item;
   key_map possible_keys(0);
 
-  if (join->group_list)
+  if (join->group_list || join->simple_group)
   { /* Collect all query fields referenced in the GROUP clause. */
     for (cur_group= join->group_list; cur_group; cur_group= cur_group->next)
       (*cur_group->item)->walk(&Item::collect_item_field_processor, 0,