MDEV-17796 WHERE filter is ignored by DISTINCT IFNULL(GROUP_CONCAT(X), Y)

           with GROUP BY + ORDER BY

The method JOIN::create_postjoin_aggr_table() should not call
call JOIN::add_sorting_to_table() unless the first non-constant join
table is passed as the first parameter to the method.

3 files changed, 29 insertions, 1 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 1ca3034c610..28b63dab22e 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -3253,3 +3253,16 @@ Warnings:
 Note	1003	select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id`
 set optimizer_switch= @save_optimizer_switch;
 DROP TABLE books, wings;
+#
+# MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY
+#
+CREATE TABLE t1 (id int, gr int, v1 varchar(10));
+INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C');
+SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1
+WHERE gr in (4,2)
+GROUP BY id
+ORDER BY id+1 DESC;
+NULLIF(GROUP_CONCAT(v1), null)
+C
+B
+DROP TABLE t1;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index b047a31c863..d67c67de89c 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -2187,3 +2187,17 @@ eval explain extended $q;
 set optimizer_switch= @save_optimizer_switch;
 
 DROP TABLE books, wings;
+
+--echo #
+--echo # MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY
+--echo #
+
+CREATE TABLE t1 (id int, gr int, v1 varchar(10));
+INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C');
+
+SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1
+  WHERE gr in (4,2)
+GROUP BY id
+ORDER BY id+1 DESC;
+
+DROP TABLE t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c3acdf7415b..3c1cea6be51 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3007,7 +3007,8 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields,
     if (setup_sum_funcs(thd, sum_funcs))
       goto err;
 
-    if (!group_list && !table->distinct && order && simple_order)
+    if (!group_list && !table->distinct && order && simple_order &&
+        tab == join_tab + const_tables)
     {
       DBUG_PRINT("info",("Sorting for order"));
       THD_STAGE_INFO(thd, stage_sorting_for_order);