[Commits] 3e7f8502d33: MDEV-25858: Query results are incorrect when indexes are added

psergey sergey at mariadb.com
Thu Jun 10 23:54:22 EEST 2021


revision-id: 3e7f8502d33816678eac58e216842e1ee97b29ca (mariadb-10.2.31-1001-g3e7f8502d33)
parent(s): 152c83d49ca821c54aa49f6b43e33cba63e4d19f
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-06-10 23:54:14 +0300
message:

MDEV-25858: Query results are incorrect when indexes are added

If test_if_skip_sort_order() decides to use an index to produce required
ordering,  it should disable "Range Checked for each record" (RCfER)
optimization.

This is because RCfER may decide to use an index (or index_merge) which
will not produce the required ordering.

---
 mysql-test/r/order_by_innodb.result | 52 +++++++++++++++++++++++++++++++++++++
 mysql-test/t/order_by_innodb.test   | 51 ++++++++++++++++++++++++++++++++++++
 sql/sql_select.cc                   |  6 +++++
 3 files changed, 109 insertions(+)

diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result
index 9cdf9800cee..14b9b861a14 100644
--- a/mysql-test/r/order_by_innodb.result
+++ b/mysql-test/r/order_by_innodb.result
@@ -147,4 +147,56 @@ i	n
 656	eight
 set optimizer_switch= @save_optimizer_switch;
 DROP TABLE t1,t2,t3;
+#
+# MDEV-25858: Query results are incorrect when indexes are added
+#
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb;
+insert into t1 values (1),(2),(3);
+CREATE TABLE t2 (
+id int NOT NULL PRIMARY KEY,
+id2 int NOT NULL,
+d1 datetime,
+d2 timestamp NOT NULL,
+KEY id2 (id2)
+) engine=innodb;
+insert into t2 values
+(1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+(2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+(3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00');
+select
+t1.id,t2.id
+from
+t1 left join
+t2 on t2.id2 = t1.id and
+t2.id = (select dd.id
+from t2 dd
+where
+dd.id2 = t1.id and
+d1 > '2019-02-06 00:00:00'
+                 order by
+dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+);
+id	id
+1	NULL
+2	1
+3	3
+create index for_latest_sort on t2 (d1 desc, d2 desc, id desc);
+select
+t1.id,t2.id
+from
+t1 left join
+t2 on t2.id2 = t1.id and
+t2.id = (select dd.id
+from t2 dd
+where
+dd.id2 = t1.id and
+d1 > '2019-02-06 00:00:00'
+                 order by
+dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+);
+id	id
+1	NULL
+2	1
+3	3
+drop table t1,t2;
 # End of 10.2 tests
diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test
index f4c738263ae..97c043b8dbc 100644
--- a/mysql-test/t/order_by_innodb.test
+++ b/mysql-test/t/order_by_innodb.test
@@ -135,4 +135,55 @@ set optimizer_switch= @save_optimizer_switch;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # MDEV-25858: Query results are incorrect when indexes are added
+--echo #
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb;
+insert into t1 values (1),(2),(3);
+
+CREATE TABLE t2 (
+  id int NOT NULL PRIMARY KEY,
+  id2 int NOT NULL,
+  d1 datetime,
+  d2 timestamp NOT NULL,
+  KEY id2 (id2)
+) engine=innodb;
+
+insert into t2 values
+  (1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+  (2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+  (3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00');
+
+select
+  t1.id,t2.id
+from
+  t1 left join
+  t2 on t2.id2 = t1.id and
+        t2.id = (select dd.id
+                 from t2 dd
+                 where
+                    dd.id2 = t1.id and
+                    d1 > '2019-02-06 00:00:00'
+                 order by
+                   dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+                );
+
+create index for_latest_sort on t2 (d1 desc, d2 desc, id desc);
+
+select
+  t1.id,t2.id
+from
+  t1 left join
+  t2 on t2.id2 = t1.id and
+        t2.id = (select dd.id
+                 from t2 dd
+                 where
+                    dd.id2 = t1.id and
+                    d1 > '2019-02-06 00:00:00'
+                 order by
+                   dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+                );
+drop table t1,t2;
+
 --echo # End of 10.2 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ce706209017..d91be9f4705 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21917,6 +21917,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
         if (select->quick == save_quick)
           save_quick= 0;                // make_reverse() consumed it
         select->set_quick(tmp);
+        /* Cancel "Range checked for each record" */
+        tab->use_quick= 1;
+        tab->read_first_record= join_init_read_record;
       }
       else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL &&
                tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts)
@@ -21929,6 +21932,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
         */
         tab->read_first_record= join_read_last_key;
         tab->read_record.read_record= join_read_prev_same;
+        /* Cancel "Range checked for each record" */
+        tab->use_quick= 1;
+        tab->read_first_record= join_init_read_record;
         /*
           Cancel Pushed Index Condition, as it doesn't work for reverse scans.
         */


More information about the commits mailing list