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

TokuDB: Wrong query result using mrr=on

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.0.9
    • 10.0.12
    • None

    Description

      Compare to default settings we have

      join_cache_level = 6
      mrr_buffer_size = 96M
      tmp_table_size = 96M
      max_heap_table_size = 96M

      This is the correct result :

      Without touching optimizer switch :

      SELECT task_id,field FROM ym_admin_db.ym_tasks_options WHERE task_id IN  (SELECT t
      sk_id FROM ym_admin_db.ym_tasks WHERE NOT(type IN (8,11) ) ) ORDER BY task_id;

      +---------+-----------------------------------+
      | task_id | field                             |
      +---------+-----------------------------------+
      |       1 | RejectFilesOnPassBinlist          |
      |       1 | Title                             |
      |       1 | PassBinlistForRejectTest          |
      |       1 | DataPath                          |
      ….
      +---------+-----------------------------------+
      39 rows in set (0.01 sec)
       
      +------+-------------+------------------+-------+----------------------+---------------------+---------+------------------------------+------+-----------------------------------------------------------+
      | id   | select_type | table            | type  | possible_keys        | key                 | key_len | ref
                           | rows | Extra                                                     |
      +------+-------------+------------------+-------+----------------------+---------------------+---------+------------------------------+------+-----------------------------------------------------------+
      |    1 | PRIMARY     | ym_tasks         | index | PRIMARY,ymtasks_type | ymtasks_type        | 2       | NULL
                           |    1 | Using where; Using index; Using temporary; Using filesort |
      |    1 | PRIMARY     | ym_tasks_options | ref   | ymtasksoptions_task  | ymtasksoptions_task | 4       | ym_admin_db.ym_tasks.task_id |   47 |                                                           |
      +------+-------------+------------------+-------+----------------------+---------------------+---------+------------------------------+------+-----------------------------------------------------------+

      This is the wrong result with mrr=on

      SELECT task_id,field FROM ym_admin_db.ym_tasks_options WHERE task_id IN  (SELECT task_id FROM ym_admin_db.ym_tasks WHERE NOT(type IN
      (8,11) ) ) ORDER BY task_id;

      +---------+-------+
      | task_id | field |
      +---------+-------+
      |       1 | Title |
      +---------+-------+
      1 row in set (0.00 sec)
       
      +------+-------------+------------------+-------+----------------------+---------------------+---------+----
      ------------------------+------+--------------------------------------------------------------------+
      | id   | select_type | table            | type  | possible_keys        | key                 | key_len | ref
                              | rows | Extra                                                              |
      +------+-------------+------------------+-------+----------------------+---------------------+---------+----
      ------------------------+------+--------------------------------------------------------------------+
      |    1 | PRIMARY     | ym_tasks         | index | PRIMARY,ymtasks_type | ymtasks_type        | 2       | NUL
                              |    1 | Using where; Using index; Using temporary; Using filesort          |
      |    1 | PRIMARY     | ym_tasks_options | ref   | ymtasksoptions_task  | ymtasksoptions_task | 4       | ym_
      min_db.ym_tasks.task_id |   47 | Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
      +------+-------------+------------------+-------+----------------------+---------------------+---------+----
      ------------------------+------+--------------------------------------------------------------------+

      CREATE TABLE `ym_tasks` (
        `task_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `node_id` int(11) DEFAULT NULL,
        `user_id` int(11) DEFAULT NULL,
        `group_id` int(11) DEFAULT NULL,
        `database_id` int(11) DEFAULT NULL,
        `name` varchar(256) NOT NULL DEFAULT '',
        `type` tinyint(3) DEFAULT NULL,
        `enabled` tinyint(3) NOT NULL DEFAULT '1',
        `permisions` mediumint(8) NOT NULL DEFAULT '0',
        `creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `expiration_date` datetime DEFAULT NULL,
        `last_update` datetime DEFAULT NULL,
        `mutex` varchar(256) DEFAULT NULL,
        PRIMARY KEY (`task_id`),
        KEY `ymtasks_database` (`database_id`),
        KEY `ymtasks_node` (`node_id`),
        KEY `ymtasks_group` (`group_id`),
        KEY `ymtasks_user` (`user_id`),
        KEY `ymtasks_lastupdate` (`last_update`),
        KEY `ymtasks_mutex` (`mutex`),
        KEY `ymtasks_type` (`type`)
      ) ENGINE=TokuDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 `COMPRESSION`=TOKUDB_LZMA;
      CREATE TABLE `ym_tasks_options` (
        `task_id` int(10) unsigned NOT NULL DEFAULT '0',
        `field` varchar(256) NOT NULL DEFAULT '',
        `value` blob,
        KEY `ymtasksoptions_task` (`task_id`),
        KEY `ymtasksoptions_field` (`field`)
      ) ENGINE=TokuDB DEFAULT CHARSET=latin1 `COMPRESSION`=TOKUDB_LZMA;

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            stephane@skysql.com VAROQUI Stephane
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.