Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11172

EXPLAIN shows non-sensical value for key_len with type=index

Details

    • 10.1.31

    Description

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
      

      create table t1 (
        pk varchar(50),
        a varchar(20),
        filler varchar(100),
        index(a),
        primary key(pk)
      ) engine=innodb;
      insert into t1 select a,a,a from test.one_k;
      

      explain select pk from t1 order by a;
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  t1      index   NULL    a       25      NULL    1120    Using index
      

      The key_len=25 does not make any sense. They key format is

      offset what
      0-1 Column a, NULL-byte
      1-3 Column a, length bytes
      3-23 Column a, 20 characters
      23-25 PK, length bytes
      25- PK, 50 values

      25 bytes means include PK's length but not include the PK value itself.

      Attachments

        Issue Links

          Activity

            MySQL 5.6 shows key_len=23.

            psergei Sergei Petrunia added a comment - MySQL 5.6 shows key_len=23 .

            The wrong values comes from KEY_INFO::key_length It is computed in TABLE_SHARE::init_from_binary_frm_image(), create_key_infos(). One can see HA_KEY_BLOB_LENGTH to be added twice.

            psergei Sergei Petrunia added a comment - The wrong values comes from KEY_INFO::key_length It is computed in TABLE_SHARE::init_from_binary_frm_image(), create_key_infos(). One can see HA_KEY_BLOB_LENGTH to be added twice.

            This issue was discovered when working on MDEV-9658. (but there's really no connection. For MDEV-9658, I can just update the .result files)

            psergei Sergei Petrunia added a comment - This issue was discovered when working on MDEV-9658 . (but there's really no connection. For MDEV-9658 , I can just update the .result files)
            varun Varun Gupta (Inactive) added a comment - Patch http://lists.askmonty.org/pipermail/commits/2018-January/011917.html
            monty Michael Widenius added a comment - - edited

            Patch generally ok. However I would like to see the following additions:

            Change in structs.h:
            typedef struct st_key {
            uint key_length; /* Tot length of key */
            to
            uint key_length; /* total length of user defined key parts */

            In table.cc, add a comment before:
            keyinfo->key_length+= HA_KEY_NULL_LENGTH;
            Why we don't have to use
            if (i < keyinfo->user_defined_key_parts)
            here

            monty Michael Widenius added a comment - - edited Patch generally ok. However I would like to see the following additions: Change in structs.h: typedef struct st_key { uint key_length; /* Tot length of key */ to uint key_length; /* total length of user defined key parts */ In table.cc, add a comment before: keyinfo->key_length+= HA_KEY_NULL_LENGTH; Why we don't have to use if (i < keyinfo->user_defined_key_parts) here

            Made the changes and pushed the patch

            varun Varun Gupta (Inactive) added a comment - Made the changes and pushed the patch

            People

              varun Varun Gupta (Inactive)
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.