From 4aec3e6a157cb3c61742a835e31eb7bc43b5d8dd Mon Sep 17 00:00:00 2001
From: Xingyu Yang <xingyuyang@tencent.com>
Date: Tue, 9 Sep 2025 10:56:00 +0800
Subject: [PATCH] primary key

---
 sql/sql_optimizer.cc | 35 +++++++++++++++++++++++++++++++----
 1 file changed, 31 insertions(+), 4 deletions(-)

diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc
index f4fe4d96e58..cf02bb1e27d 100644
--- a/sql/sql_optimizer.cc
+++ b/sql/sql_optimizer.cc
@@ -7819,14 +7819,23 @@ bool add_key_fields(THD *thd, JOIN *join, Key_field **key_fields,
    1 - Out of memory.
 */
 
-static bool add_key_part(Key_use_array *keyuse_array, Key_field *key_field) {
+static bool add_key_part(Key_use_array *keyuse_array, Key_field *key_field,
+                         Key_use_array *primary_keyuse_array = nullptr) {
   if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS)) {
     const Field *const field = key_field->item_field->field;
     Table_ref *const tl = key_field->item_field->m_table_ref;
     TABLE *const table = tl->table;
 
+    Key_use_array *cur_keyuse_array = keyuse_array;
     for (uint key = 0; key < table->s->keys; key++) {
-      if (!(table->keys_in_use_for_query.is_set(key))) continue;
+      cur_keyuse_array = keyuse_array;
+      if (!(table->keys_in_use_for_query.is_set(key))) {
+        if (key == table->s->primary_key && primary_keyuse_array) {
+          cur_keyuse_array = primary_keyuse_array;
+        } else {
+          continue;
+        }
+      }
       if (table->key_info[key].flags & (HA_FULLTEXT | HA_SPATIAL))
         continue;  // ToDo: ft-keys in non-ft queries.   SerG
 
@@ -7840,7 +7849,7 @@ static bool add_key_part(Key_use_array *keyuse_array, Key_field *key_field) {
                                ~(ha_rows)0,  // will be set in optimize_keyuse
                                key_field->null_rejecting, key_field->cond_guard,
                                key_field->sj_pred_no);
-          if (keyuse_array->push_back(keyuse))
+          if (cur_keyuse_array->push_back(keyuse))
             return true; /* purecov: inspected */
         }
       }
@@ -8430,9 +8439,27 @@ static bool update_ref_and_keys(THD *thd, Key_use_array *keyuse,
   if (query_block->materialized_derived_table_count) {
     if (join->generate_derived_keys()) return true;
   }
+
+  // If a secondary index is specified using a hint, the primary key index will
+  // not participate in optimization. This causes the primary key part of the
+  // secondary index to be overlooked when determining whether the secondary
+  // index can be used for sorting, see test_if_order_by_key().
+  Key_use_array primary_keyuses(thd->mem_root);
   /* fill keyuse with found key parts */
   for (; field != end; field++) {
-    if (add_key_part(keyuse, field)) return true;
+    if (add_key_part(keyuse, field, &primary_keyuses)) return true;
+  }
+
+  // const_key_parts could be used in test_if_order_by_key() even if the primary
+  // index is not used. This is because const_key_parts is used to determine
+  // if a key can be used for sorting, and this information is needed even if
+  // the primary index is not used.
+  for (auto use = primary_keyuses.begin(); use != primary_keyuses.end();
+       ++use) {
+    if (use->val->const_for_execution() &&
+        use->optimize != KEY_OPTIMIZE_REF_OR_NULL) {
+      use->table_ref->table->const_key_parts[use->key] |= use->keypart_map;
+    }
   }
 
   if (query_block->ftfunc_list->elements) {
-- 
2.43.5