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

Buffer algorithm wrongly used on partitioned tables during UPDATE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.2.9, 10.1, 10.0
    • Fix Version/s: 10.2
    • Component/s: Optimizer
    • Labels:

      Description

      Hi,

      During UPDATE, the Buffer algorithm should be used only when a condition in the WHERE clause will be changed during the UPDATE.
      It seems there's a bug with partitioned tables where the buffer algorithm is used even when the value is not changed.

      How to repeat :

      CREATE TABLE `sc_param_index` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `gen_subtype_id` tinyint(3) unsigned NOT NULL,
        PRIMARY KEY (`id`,`gen_subtype_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=586511719 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
       PARTITION BY LIST (`gen_subtype_id`)
      (PARTITION `p0` VALUES IN (1) ENGINE = InnoDB,
       PARTITION `p1` VALUES IN (2) ENGINE = InnoDB
      )
       
      EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819, gen_subtype_id=1 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: sc_param_index
         partitions: p0
               type: range
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 5
                ref: NULL
               rows: 1
              Extra: Using where; Using buffer
      1 row in set (0.01 sec)
      

      => Using buffer here, but gen_subtype_id is not changed!

      EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: sc_param_index
         partitions: p0
               type: range
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 5
                ref: NULL
               rows: 1
              Extra: Using where
      1 row in set (0.00 sec)
      

      gen_subtype_id removed from the SET, no more Using buffer.

      Now without partition :

      DROP TABLE IF EXISTS sc_param_index;
       
      CREATE TABLE `sc_param_index` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `gen_subtype_id` tinyint(3) unsigned NOT NULL,
        PRIMARY KEY (`id`,`gen_subtype_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=586511719 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
       
      EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819, gen_subtype_id=1 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: sc_param_index
         partitions: NULL
               type: range
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 5
                ref: NULL
               rows: 1
              Extra: Using where
      1 row in set (0.00 sec)
      

      No Using buffer even with gen_subtype_id=1 in the SET, that's the expected behaviour.

      EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: sc_param_index
         partitions: NULL
               type: range
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 5
                ref: NULL
               rows: 1
              Extra: Using where
      1 row in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            joce jocelyn fournier
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration