Fix for MDEV-4219 A simple select query returns random data (upstream bug#68473)

  
In the case of loose scan used as input for order by, end_send()
didn't detect correctly that a loose scan was used, and didn't copy
the non-aggregated fields from the temp table used for ORDER BY.
  
The fix uses the fact that the quick select used for sorting is
attached to JOIN::pre_sort_join_tab instead of JOIN::join_tab.

4 files changed, 101 insertions, 2 deletions

diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index cc7c9c4d364..229481f5ec8 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -3561,3 +3561,45 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	NULL	PRIMARY	5	NULL	9	Using index for group-by (scanning)
 drop table t1;
 # End of test#50539.
+#
+# MDEV-4219 A simple select query returns random data (upstream bug#68473)
+#
+drop table if exists faulty;
+CREATE TABLE faulty (
+a int(11) unsigned NOT NULL AUTO_INCREMENT,
+b int(11) unsigned NOT NULL,
+c datetime NOT NULL,
+PRIMARY KEY (a),
+UNIQUE KEY b_and_c (b,c)
+);
+INSERT INTO faulty (b, c) VALUES
+(1801, '2013-02-15 09:00:00'),
+(1802, '2013-02-28 09:00:00'),
+(1802, '2013-03-01 09:00:00'),
+(5,    '1990-02-15 09:00:00'),
+(5,    '2013-02-15 09:00:00'),
+(5,    '2009-02-15 17:00:00');
+EXPLAIN
+SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	faulty	range	b_and_c	b_and_c	12	NULL	2	Using where; Using index for group-by; Using filesort
+SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
+b	c
+1802	2013-02-28 09:00:00
+1802	2013-03-01 09:00:00
+drop table faulty;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
+INSERT INTO t1 SELECT a + 1, b FROM t1;
+INSERT INTO t1 SELECT a + 2, b FROM t1;
+CREATE INDEX break_it ON t1 (a, b);
+EXPLAIN
+SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	break_it	break_it	10	NULL	2	Using where; Using index for group-by; Using filesort
+SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
+a	b
+3	1
+3	2
+3	3
+drop table t1;
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index 82097c53fe0..034da4eb925 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -1400,3 +1400,50 @@ explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
  
 drop table t1;
 --echo # End of test#50539.
+
+--echo #
+--echo # MDEV-4219 A simple select query returns random data (upstream bug#68473)
+--echo #
+
+--disable_warnings
+drop table if exists faulty;
+--enable_warnings
+
+# MySQL's test case
+
+CREATE TABLE faulty (
+a int(11) unsigned NOT NULL AUTO_INCREMENT,
+b int(11) unsigned NOT NULL,
+c datetime NOT NULL,
+PRIMARY KEY (a),
+UNIQUE KEY b_and_c (b,c)
+);
+
+INSERT INTO faulty (b, c) VALUES
+(1801, '2013-02-15 09:00:00'),
+(1802, '2013-02-28 09:00:00'),
+(1802, '2013-03-01 09:00:00'),
+(5,    '1990-02-15 09:00:00'),
+(5,    '2013-02-15 09:00:00'),
+(5,    '2009-02-15 17:00:00');
+
+EXPLAIN
+SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
+SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
+
+drop table faulty;
+
+# MariaDB test case
+
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
+INSERT INTO t1 SELECT a + 1, b FROM t1;
+INSERT INTO t1 SELECT a + 2, b FROM t1;
+
+CREATE INDEX break_it ON t1 (a, b);
+
+EXPLAIN
+SELECT distinct a, b FROM t1 where a = '3' ORDER BY b; 
+SELECT distinct a, b FROM t1 where a = '3' ORDER BY b; 
+
+drop table t1;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 821c5d3b7b8..1515b0d78a1 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -13217,7 +13217,11 @@ QUICK_GROUP_MIN_MAX_SELECT::~QUICK_GROUP_MIN_MAX_SELECT()
     DBUG_ASSERT(file == head->file);
     if (doing_key_read)
       head->disable_keyread();
-    file->ha_index_end();
+    /*
+      There may be a code path when the same table was first accessed by index,
+      then the index is closed, and the table is scanned (order by + loose scan).
+    */
+    file->ha_index_or_rnd_end();
   }
   if (min_max_arg_part)
     delete_dynamic(&min_max_ranges);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index cacaea44d12..03d83cebeac 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -17404,7 +17404,13 @@ end_send(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
   if (!end_of_records)
   {
     if (join->table_count &&
-        join->join_tab->is_using_loose_index_scan())
+        (join->join_tab->is_using_loose_index_scan() ||
+         /*
+           When order by used a loose scan as its input, the quick select may
+           be attached to pre_sort_join_tab.
+         */
+         (join->pre_sort_join_tab &&
+          join->pre_sort_join_tab->is_using_loose_index_scan())))
     {
       /* Copy non-aggregated fields when loose index scan is used. */
       copy_fields(&join->tmp_table_param);