[MDEV-30476] EXPLAIN shows ref access, while it should have been eq_ref Created: 2023-01-26  Updated: 2023-01-31

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File extended_keys.diff    

 Description   

I've hit this when doing patch #2 for MDEV-30218:
https://github.com/MariaDB/server/commit/678f2220cc25b3495f1f16fe2732e49a0cd120f8

EXPLAIN in subselect2.result shows "ref|filter":

1	PRIMARY	t3	ref|filter	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	FFOLDERID_IDX|CMFLDRPARNT_IDX	34|35	test.t3.PARENTID	1 (29%)	Using where; Using rowid filter

The nature of the patch hints that this access should have been eq_ref.
MDEV-30218.

Debugging, one can see that best_access_path() considers eq_ref on that index.
Optimizer Trace also shows:

              {
 
                "access_type": "eq_ref",
 
                "index": "FFOLDERID_IDX",
 
                "rowid_filter_key": "CMFLDRPARNT_IDX",
 
                "rows": 1,
 
                "cost": 15.444,
 
                "chosen": true
 
              },



 Comments   
Comment by Sergei Petrunia [ 2023-01-26 ]

The index in question:

(gdb) p key_flags & HA_NOSAME
 
  $61 = 0 
(gdb) p key_flags & HA_EXT_NOSAME
 
  $62 = 131072
 
(gdb) p keyinfo->name.str
 
  $63 = 0x7fff980bab91 "FFOLDERID_IDX"


keyinfo->user_defined_key_parts=1
 
keyinfo->usable_key_parts = 1
 
keyinfo->ext_key_parts = 1

Comment by Sergei Petrunia [ 2023-01-26 ]

This code in create_ref_for_key() considers the index non-unique:

=>  else if (!((keyparts == keyinfo->user_defined_key_parts &&
 
                (
 
                  (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
 
                  /* Unique key and all keyparts are NULL rejecting */
 
                  ((key_flags & HA_NOSAME) && keyparts == not_null_keyparts)
 
                )) ||
 
                /* true only for extended keys */
 
                (keyparts > keyinfo->user_defined_key_parts &&
 
                 MY_TEST(key_flags & HA_EXT_NOSAME) &&
 
                 keyparts == keyinfo->ext_key_parts)
 
              ) ||
 
              null_ref_key)
 
    {
 
      /* Must read with repeat */
 
      j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
 
      j->ref.null_ref_key= null_ref_key;
 
      j->ref.null_ref_part= null_ref_part;
 
    }

while this code in best_access_path considers the index unique:

            if ((key_flags & (HA_NOSAME | HA_EXT_NOSAME)) &&   // (1)
 
                (!(key_flags & HA_NULL_PART_KEY) ||            //  (2)
 
                 all_key_parts == notnull_part))               //  (3)
 
            {
 
=>            type= JT_EQ_REF;

Comment by Sergei Petrunia [ 2023-01-26 ]

Patch from Monty extended_keys.diff

Generated at Thu Feb 08 10:16:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.