revision-id: 5fc8dd8b82e995b3c685f4e71700201097431358 (mariadb-10.2.23-73-g5fc8dd8) parent(s): 6b5d3c51b3d4260ba692bb84c64eb2705635e051 author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-23 23:10:46 -0700 message: 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. --- mysql-test/r/order_by.result | 13 +++++++++++++ mysql-test/t/order_by.test | 14 ++++++++++++++ sql/sql_select.cc | 3 ++- 3 files changed, 29 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 1ca3034..28b63da 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 b047a31..d67c67d 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 c3acdf7..3c1cea6 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);