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.

1 files changed, 13 insertions, 0 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;