Details
Critical
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
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.
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...
the parameter is actually a pointer to pointer to the key value.
Pushed to 10.0 tree
Hi Stephane,
Please provide the data dump (upload it to the provide FTP if it shouldn't be published).
Thanks.