Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.9
-
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
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...
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: 1select_type: SIMPLEtable: ym_taskstype: indexpossible_keys: PRIMARY,ymtasks_typekey: ymtasks_typekey_len: 2ref: NULLrows: 2Extra: Using where; Using index*************************** 2. row ***************************id: 1select_type: SIMPLEtable: ym_tasks_optionstype: refpossible_keys: ymtasksoptions_taskkey: ymtasksoptions_taskkey_len: 4ref: j1.ym_tasks.task_idrows: 15Extra: Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan