[MDEV-5976] TokuDB: Wrong query result using mrr=on Created: 2014-03-28  Updated: 2014-06-06  Resolved: 2014-06-06

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.9
Fix Version/s: 10.0.12

Type: Bug Priority: Critical
Reporter: VAROQUI Stephane Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: tokudb

Attachments: File dump_tasks.sql    

 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;



 Comments   
Comment by Elena Stepanova [ 2014-03-28 ]

Hi Stephane,

Please provide the data dump (upload it to the provide FTP if it shouldn't be published).

Thanks.

Comment by VAROQUI Stephane [ 2014-04-01 ]

on the same dataset

SELECT task_id, field,value FROM ym_tasks_options WHERE task_id IN ( SELECT task_id FROM ym_tasks WHERE type IN (99,10,2,3,4,6,9,1,7,5)
and
SELECT task_id, field FROM ym_tasks_options WHERE task_id IN ( SELECT task_id FROM ym_tasks WHERE type IN (99,10,2,3,4,6,9,1,7,5)

Does not produce same number of rows

Comment by Sergei Petrunia [ 2014-06-03 ]

Repeatable.
So, I'm using the dataset from dump_tasks.sql. My settings are 

set optimizer_switch='mrr=on,mrr_sort_keys=on';
 
set join_cache_level = 6;
 

explain SELECT task_id,field FROM ym_tasks_options WHERE task_id IN  (SELECT task_id FROM ym_tasks WHERE NOT(type IN (8,11) ) ) ORDER BY task_id\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: PRIMARY
 
        table: ym_tasks
 
         type: index
 
possible_keys: PRIMARY,ymtasks_type
 
          key: ymtasks_type
 
      key_len: 2
 
          ref: NULL
 
         rows: 2
 
        Extra: Using where; Using index; Using temporary; Using filesort
 
*************************** 2. row ***************************
 
           id: 1
 
  select_type: PRIMARY
 
        table: ym_tasks_options
 
         type: ref
 
possible_keys: ymtasksoptions_task
 
          key: ymtasksoptions_task
 
      key_len: 4
 
          ref: j1.ym_tasks.task_id
 
         rows: 15
 
        Extra: Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

SELECT produces two rows.

Now, if I do

set optimizer_switch='mrr=on,mrr_sort_keys=off';

and re-run the query:

explain SELECT task_id,field FROM ym_tasks_options WHERE task_id IN  (SELECT task_id FROM ym_tasks WHERE NOT(type IN (8,11) ) ) ORDER BY task_id\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: PRIMARY
 
        table: ym_tasks
 
         type: index
 
possible_keys: PRIMARY,ymtasks_type
 
          key: ymtasks_type
 
      key_len: 2
 
          ref: NULL
 
         rows: 2
 
        Extra: Using where; Using index; Using temporary; Using filesort
 
*************************** 2. row ***************************
 
           id: 1
 
  select_type: PRIMARY
 
        table: ym_tasks_options
 
         type: ref
 
possible_keys: ymtasksoptions_task
 
          key: ymtasksoptions_task
 
      key_len: 4
 
          ref: j1.ym_tasks.task_id
 
         rows: 15
 
        Extra: Using join buffer (flat, BKA join); Rowid-ordered scan

SELECT produces 78 rows.

(with join_cache_level=0 I also get 78 rows so I assume that's the correct
resultset)

Comment by Sergei Petrunia [ 2014-06-04 ]

The problem is not related to subqueries. The optimizer rewrites the subquery into join. Here is a join query that shows the same result set difference, depending on the value of mrr_sort_keys:

set join_cache_level=6;
 
set optimizer_switch='mrr=on,mrr_sort_keys=on';
 
SELECT ym_tasks_options.task_id,
 
       ym_tasks_options.field 
FROM 
  ym_tasks_options,ym_tasks
 
WHERE 
  ym_tasks_options.task_id=ym_tasks.task_id AND
 
  ym_tasks.type NOT IN (8,11)
 
ORDER BY task_id;

Comment by Sergei Petrunia [ 2014-06-04 ]

"ORDER BY task_id" is also irrelevant.

Comment by Sergei Petrunia [ 2014-06-06 ]

OK, let's switch to exploring the join query. The query plan that produces the wrong result is:

explain  SELECT ym_tasks_options.task_id, ym_tasks_options.field  FROM    ym_tasks_options,ym_tasks WHERE    ym_tasks_options.task_id=ym_tasks.task_id AND   ym_tasks.type NOT IN (8,11)
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: ym_tasks
 
         type: index
 
possible_keys: PRIMARY,ymtasks_type
 
          key: ymtasks_type
 
      key_len: 2
 
          ref: NULL
 
         rows: 2
 
        Extra: Using where; Using index
 
*************************** 2. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: ym_tasks_options
 
         type: ref
 
possible_keys: ymtasksoptions_task
 
          key: ymtasksoptions_task
 
      key_len: 4
 
          ref: j1.ym_tasks.task_id
 
         rows: 15
 
        Extra: Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan

Comment by Sergei Petrunia [ 2014-06-06 ]

Let's explore the dataset

MariaDB [j1]> select ym_tasks.task_id , ym_tasks.type,ym_tasks.type NOT IN (8,11) from ym_tasks ;
 
+---------+------+-----------------------------+
 
| task_id | type | ym_tasks.type NOT IN (8,11) |
 
+---------+------+-----------------------------+
 
|       1 |    1 |                           1 |
 
|       2 |    1 |                           1 |
 
+---------+------+-----------------------------+
 

MariaDB [j1]> SELECT ym_tasks_options.task_id, ym_tasks_options.field FROM ym_tasks_options WHERE ym_tasks_options.task_id=1;
 
...
 
39 rows in set (0.00 sec)
 

MariaDB [j1]> SELECT ym_tasks_options.task_id, ym_tasks_options.field FROM ym_tasks_options WHERE ym_tasks_options.task_id=2;
 
...
 
39 rows in set (0.00 sec)

There are two lookup keys, 1 and 2. Each has 39 matches. We should get 2*39= 78 rows. However, we only get two.

Comment by Sergei Petrunia [ 2014-06-06 ]

Debugging

Breakpoint 8, handler::ha_index_read_map (this=0x7fffb9a9e088, buf=0x7fffb988b088 "\377", key=0x7fffb9aa1072 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /home/psergey/dev2/10.0/sql/handler.cc:2580
(gdb) x/4c key
0x7fffb9aa1072: 1 '\001' 0 '\000' 0 '\000' 0 '\000'
(gdb) fini
...
Value returned is $111 = 0

Breakpoint 15, handler::ha_index_next_same (this=0x7fffb9a9e088, buf=0x7fffb988b088 "\377\001", key=0x7fffb9ac1050 "r\020\252\271\377\177", keylen=4) at /home/psergey/dev2/10.0/sql/handler.cc:2690

(gdb) x/4c key
0x7fffb9ac1050: 114 'r' 16 '\020' -86 '\252' -71 '\271'
(gdb) fini
...
Value returned is $164 = 137

^^ This is HA_ERR_END_OF_FILE, and it is already incorrect.

First guess at the cause of the problem: why isthe parameter key=0x7fffb9ac1050 "r\020\252\271\377\177" ? We're doing lookup on 0x01 0x00 0x00 0x00...

Comment by Sergei Petrunia [ 2014-06-06 ]

the parameter is actually a pointer to pointer to the key value.

Comment by Sergei Petrunia [ 2014-06-06 ]

Pushed to 10.0 tree

Generated at Thu Feb 08 07:08:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.