MDEV-26996: Support descending indexes in the range optimizerpreview-10.8-MDEV-26996-desc-indexes-range-optimizer

- Code cleanup
- Disable "Using index for GROUP BY" over indexes with DESC keyparts

6 files changed, 82 insertions, 47 deletions

diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result
index 53a608fe2d9..feec5dc1720 100644
--- a/mysql-test/main/desc_index_range.result
+++ b/mysql-test/main/desc_index_range.result
@@ -154,5 +154,28 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
         "(4,80) <= (a,b) <= (2,50)"
     ]
 ]
-set optimizer_trace=default;
 drop table t2;
+#
+# Check that "Using index for group-by" is disabled (it's not supported, yet)
+#
+CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc));
+insert into t1 select 2,seq from seq_0_to_1000;
+EXPLAIN select MIN(a) from t1 where p = 2 group by p;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	4	const	1000	Using index
+select json_detailed(json_extract(trace, '$**.potential_group_range_indexes'))
+from information_schema.optimizer_trace;
+json_detailed(json_extract(trace, '$**.potential_group_range_indexes'))
+[
+    
+    [
+        
+        {
+            "index": "PRIMARY",
+            "usable": false,
+            "cause": "Reverse-ordered (not supported yet)"
+        }
+    ]
+]
+drop table t1;
+set optimizer_trace=default;
diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test
index 94d6b76258d..bcb9ce83318 100644
--- a/mysql-test/main/desc_index_range.test
+++ b/mysql-test/main/desc_index_range.test
@@ -73,5 +73,16 @@ select * from t2 where a between 2 and 4 and b between 50 and 80;
 select json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
 from information_schema.optimizer_trace;
 
-set optimizer_trace=default;
 drop table t2;
+
+--echo #
+--echo # Check that "Using index for group-by" is disabled (it's not supported, yet)
+--echo #
+CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc));
+insert into t1 select 2,seq from seq_0_to_1000;
+EXPLAIN select MIN(a) from t1 where p = 2 group by p;
+select json_detailed(json_extract(trace, '$**.potential_group_range_indexes'))
+from information_schema.optimizer_trace;
+drop table t1;
+
+set optimizer_trace=default;
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 4913aac6c30..f7f5476ab23 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1203,8 +1203,8 @@ EXPLAIN SELECT DISTINCT a FROM t1	{
                     },
                     {
                       "index": "a",
-                      "key_parts": ["a"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["a"]
                     }
                   ],
                   "best_covering_index_scan": {
@@ -1386,8 +1386,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a	{
                   "potential_range_indexes": [
                     {
                       "index": "a",
-                      "key_parts": ["a", "b", "c", "d"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["a", "b", "c", "d"]
                     }
                   ],
                   "best_covering_index_scan": {
@@ -1585,8 +1585,8 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id	{
                   "potential_range_indexes": [
                     {
                       "index": "id",
-                      "key_parts": ["id", "a"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["id", "a"]
                     }
                   ],
                   "best_covering_index_scan": {
@@ -1773,8 +1773,8 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id	{
                   "potential_range_indexes": [
                     {
                       "index": "id",
-                      "key_parts": ["id", "a"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["id", "a"]
                     }
                   ],
                   "best_covering_index_scan": {
@@ -2012,13 +2012,13 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                     },
                     {
                       "index": "a_c",
-                      "key_parts": ["a", "c"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["a", "c"]
                     },
                     {
                       "index": "a_b",
-                      "key_parts": ["a", "b"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["a", "b"]
                     }
                   ],
                   "setup_range_conditions": [],
@@ -2215,8 +2215,8 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                 },
                 {
                   "index": "a_c",
-                  "key_parts": ["a", "c"],
-                  "usable": true
+                  "usable": true,
+                  "key_parts": ["a", "c"]
                 },
                 {
                   "index": "a_b",
@@ -3231,18 +3231,18 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
                   "potential_range_indexes": [
                     {
                       "index": "pk",
-                      "key_parts": ["pk"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["pk"]
                     },
                     {
                       "index": "pk_a",
-                      "key_parts": ["pk", "a"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["pk", "a"]
                     },
                     {
                       "index": "pk_a_b",
-                      "key_parts": ["pk", "a", "b"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["pk", "a", "b"]
                     }
                   ],
                   "best_covering_index_scan": {
@@ -3749,8 +3749,8 @@ explain delete from t0 where t0.a<3	{
         "potential_range_indexes": [
           {
             "index": "a",
-            "key_parts": ["a"],
-            "usable": true
+            "usable": true,
+            "key_parts": ["a"]
           }
         ],
         "setup_range_conditions": [],
@@ -3887,8 +3887,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                   "potential_range_indexes": [
                     {
                       "index": "a",
-                      "key_parts": ["a"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["a"]
                     }
                   ],
                   "best_covering_index_scan": {
@@ -3952,8 +3952,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                   "potential_range_indexes": [
                     {
                       "index": "a",
-                      "key_parts": ["a"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["a"]
                     }
                   ],
                   "best_covering_index_scan": {
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 011875762d1..f1e13586eda 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -78,13 +78,13 @@ explain select * from t1 where a=1 or b=1	{
                   "potential_range_indexes": [
                     {
                       "index": "a",
-                      "key_parts": ["a"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["a"]
                     },
                     {
                       "index": "b",
-                      "key_parts": ["b"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["b"]
                     },
                     {
                       "index": "c",
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index d372be85bd8..0ddaaeae89d 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -93,13 +93,13 @@ explain select * from t1 where pk1 != 0  and key1 = 1	{
                   "potential_range_indexes": [
                     {
                       "index": "PRIMARY",
-                      "key_parts": ["pk1", "pk2"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["pk1", "pk2"]
                     },
                     {
                       "index": "key1",
-                      "key_parts": ["key1"],
-                      "usable": true
+                      "usable": true,
+                      "key_parts": ["key1"]
                     },
                     {
                       "index": "key2",
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 541a921435a..ae2b5060625 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2809,12 +2809,11 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
         trace_idx_details.add("usable", false).add("cause", "fulltext");
         continue;    // ToDo: ft-keys in non-ft ranges, if possible   SerG
       }
-
+      trace_idx_details.add("usable", true);
       param.key[param.keys]=key_parts;
       key_part_info= key_info->key_part;
       uint cur_key_len= 0;
       Json_writer_array trace_keypart(thd, "key_parts");
-      bool unusable_has_desc_keyparts= false;
       for (uint part= 0 ; part < n_key_parts ; 
            part++, key_parts++, key_part_info++)
       {
@@ -2829,18 +2828,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
           (key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW;
         /* Only HA_PART_KEY_SEG is used */
         key_parts->flag=         (uint8) key_part_info->key_part_flag;
-        if (key_part_info->key_part_flag & HA_REVERSE_SORT)
-          unusable_has_desc_keyparts= true;
         trace_keypart.add(key_parts->field->field_name);
       }
       trace_keypart.end();
-      trace_idx_details.add("usable", !unusable_has_desc_keyparts);
-      unusable_has_desc_keyparts= false;
-      if (unusable_has_desc_keyparts) // TODO MDEV-13756
-      {
-        key_parts= param.key[param.keys];
-        continue;
-      }
       param.real_keynr[param.keys++]=idx;
       if (cur_key_len > max_key_len)
         max_key_len= cur_key_len;
@@ -13833,6 +13823,17 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time)
       cause= "not covering";
       goto next_index;
     }
+
+    {
+      for (uint i= 0; i < table->actual_n_key_parts(cur_index_info); i++)
+      {
+        if (cur_index_info->key_part[i].key_part_flag & HA_REVERSE_SORT)
+        {
+          cause="Reverse-ordered (not supported yet)";
+          goto next_index;
+        }
+      }
+    }
     
     /*
       This function is called on the precondition that the index is covering.