[Commits] 9769baf3fc0: MDEV-20922: Adding an order by changes the query results

Varun varunraiko1803 at gmail.com
Fri Nov 8 10:23:50 EET 2019


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:
       {


More information about the commits mailing list