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

psergey sergey at mariadb.com
Fri Jun 11 12:38:11 EEST 2021


revision-id: dbc0683f13d07461c50ddd61b93f3c102bc1a9b3 (mariadb-10.2.31-1001-gdbc0683f13d)
parent(s): 152c83d49ca821c54aa49f6b43e33cba63e4d19f
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-06-11 12:37:32 +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" optimization.

This is because Range-Checked-for-each-record may decide to use an index
(or an 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                   | 12 +++++++++
 3 files changed, 115 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..b6f5385d653 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21917,6 +21917,12 @@ 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" */
+        if (tab->use_quick == 2)
+        {
+          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 +21935,12 @@ 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" */
+        if (tab->use_quick == 2)
+        {
+          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