[Commits] 5fc8dd8: MDEV-17796 WHERE filter is ignored by DISTINCT IFNULL(GROUP_CONCAT(X), Y)

IgorBabaev igor at mariadb.com
Wed Apr 24 09:10:46 EEST 2019


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);


More information about the commits mailing list