revision-id: 9769baf3fc0ad550bb8c2175c206a33e4c635299 (mariadb-10.1.41-86-g9769baf3fc0) parent(s): 4e99e67c4e8a04bd03cb0e7efc2ce0129af60c34 author: Varun Gupta committer: Varun Gupta timestamp: 2019-11-08 13:51:34 +0530 message: MDEV-20922: Adding an order by changes the query results In the case of sorting the first non-const table, sort key should be made by the items referring to the base table and not to the temporary table. --- mysql-test/r/derived.result | 31 +++++++++++++++++++++++++++++++ mysql-test/t/derived.test | 25 +++++++++++++++++++++++++ sql/filesort.cc | 4 ++-- 3 files changed, 58 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index be241c0e928..9f825ffa993 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1164,5 +1164,36 @@ a 5 DROP TABLE t1; # +# MDEV-20922: Adding an order by changes the query results +# +CREATE TABLE t1(a int, b int); +INSERT INTO t1 values (1, 1), (2, 2), (3, 1), (4, 2); +explain SELECT q.x, COUNT(DISTINCT q.a) AS y +FROM (select b+1 as x, a FROM t1)q +GROUP BY x ORDER BY y; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +SELECT q.x, COUNT(DISTINCT q.a) AS y +FROM (select b+1 as x, a FROM t1)q +GROUP BY x ORDER BY y; +x y +2 2 +3 2 +set optimizer_switch='derived_merge=off'; +explain SELECT q.x, COUNT(DISTINCT q.a) AS y +FROM (select b+1 as x, a FROM t1)q +GROUP BY x ORDER BY y; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +2 DERIVED t1 ALL NULL NULL NULL NULL 4 +SELECT q.x, COUNT(DISTINCT q.a) AS y +FROM (select b+1 as x, a FROM t1)q +GROUP BY x ORDER BY y; +x y +2 2 +3 2 +set optimizer_switch= @save_derived_optimizer_switch; +drop table t1; +# # End of 10.1 tests # diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 470060d83db..87c332df01a 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -990,6 +990,31 @@ SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1; SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1; DROP TABLE t1; +--echo # +--echo # MDEV-20922: Adding an order by changes the query results +--echo # + +CREATE TABLE t1(a int, b int); +INSERT INTO t1 values (1, 1), (2, 2), (3, 1), (4, 2); + +let $query= SELECT q.x, COUNT(DISTINCT q.a) AS y + FROM (select b+1 as x, a FROM t1)q + GROUP BY x ORDER BY y; + +eval explain $query; +eval $query; + +set optimizer_switch='derived_merge=off'; +let $query= SELECT q.x, COUNT(DISTINCT q.a) AS y + FROM (select b+1 as x, a FROM t1)q + GROUP BY x ORDER BY y; + +eval explain $query; +eval $query; + +set optimizer_switch= @save_derived_optimizer_switch; +drop table t1; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/filesort.cc b/sql/filesort.cc index 4f195f68059..648e1d58747 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -987,8 +987,8 @@ static void make_sortkey(Sort_param *param, } else { // Item - Item *item=sort_field->item; - maybe_null= item->maybe_null; + Item *item=sort_field->item->real_item(); + maybe_null= sort_field->item->maybe_null; switch (sort_field->result_type) { case STRING_RESULT: {