|
The original query / on the user's schema and data produced a slightly different plan:
SELECT * FROM `navigation` WHERE `parent_id`=8 and (`project_id` = '1' OR `project_id` is null) ORDER BY `index` ASC;
|
|
+------+-------------+------------+-------------+-----------------------------+-----------+---------+-------------+------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------------+-----------------------------+-----------+---------+-------------+------+-----------------------------+
|
| 1 | SIMPLE | navigation | ref_or_null | navigation_ibfk_1,parent_id | parent_id | 10 | const,const | 3 | Using where; Using filesort |
|
+------+-------------+------------+-------------+-----------------------------+-----------+---------+-------------+------+-----------------------------+
|
1 row in set (0.00 sec)
|
|
Create Table: CREATE TABLE `navigation` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`parent_id` int(10) unsigned DEFAULT NULL,
|
`group_id` int(10) unsigned DEFAULT NULL,
|
`project_id` int(10) unsigned DEFAULT NULL,
|
`nav_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
|
`index` int(10) NOT NULL,
|
`label` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
|
`module` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
|
`controller` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
|
`action` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
|
`uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
|
`resource` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
|
`params` text COLLATE utf8_unicode_ci,
|
`class` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `group_id` (`group_id`),
|
KEY `navigation_ibfk_1` (`project_id`),
|
KEY `parent_id` (`parent_id`,`project_id`,`index`),
|
CONSTRAINT `navigation_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
CONSTRAINT `navigation_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `navigation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
CONSTRAINT `navigation_ibfk_3` FOREIGN KEY (`group_id`) REFERENCES `navigation_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT
|
|
|
Investigation:
- initial query plan constructs ref_or_null access.
- then, the optimizer figures out it will need to use filesort and calls get_quick_select_for_ref(). The latter produces a quick select with two ranges (it seems correct).
- quick select execution starts. It uses Default MRR Implementation and ICP. There seems to be a problem with detecting the end of first range, we just continue to scan and return all rows.
|
|
index condition:
(gdb) p dbug_print_item(idx_cond)
$46 = 0x19ae100 "((`j5`.`t1`.`b` = 1) or isnull(`j5`.`t1`.`b`))"
"a=8" is intended to be checked by JT_EQ_REF, it seems.
|
|
The index is
The WHERE clause
a = 8 AND (b = 1 OR b IS NULL)
|
Debugging here:
#0 row_sel_store_mysql_field_func (mysql_rec=0x7fffdb379f88 "\377\b", prebuilt=0x7fffcc90c078, rec=0x7fffe395c10a "\200", index=0x7fffcc895878, offsets=0x7ffff7f88e20, field_no=2, templ=0x7fffcc895df0) at /home/psergey/dev2/10.0-cp/storage/xtradb/row/row0sel.cc:2894
|
#1 0x0000000000c726f5 in row_search_idx_cond_check (mysql_rec=0x7fffdb379f88 "\377\b", prebuilt=0x7fffcc90c078, rec=0x7fffe395c10a "\200", offsets=0x7ffff7f88e20) at /home/psergey/dev2/10.0-cp/storage/xtradb/row/row0sel.cc:3592
|
#2 0x0000000000c74a4a in row_search_for_mysql (buf=0x7fffdb379f88 "\377\b", mode=2, prebuilt=0x7fffcc90c078, match_mode=0, direction=1) at /home/psergey/dev2/10.0-cp/storage/xtradb/row/row0sel.cc:4715
|
#3 0x0000000000b5308c in ha_innobase::general_fetch (this=0x7fffcc892088, buf=0x7fffdb379f88 "\377\b", direction=1, match_mode=0) at /home/psergey/dev2/10.0-cp/storage/xtradb/handler/ha_innodb.cc:8651
|
#4 0x0000000000b53322 in ha_innobase::index_next (this=0x7fffcc892088, buf=0x7fffdb379f88 "\377\b") at /home/psergey/dev2/10.0-cp/storage/xtradb/handler/ha_innodb.cc:8721
|
#5 0x000000000085b3c7 in handler::ha_index_next (this=0x7fffcc892088, buf=0x7fffdb379f88 "\377\b") at /home/psergey/dev2/10.0-cp/sql/handler.cc:2629
|
#6 0x000000000086105f in handler::read_range_next (this=0x7fffcc892088) at /home/psergey/dev2/10.0-cp/sql/handler.cc:5409
|
#7 0x00000000007c0751 in handler::multi_range_read_next (this=0x7fffcc892088, range_info=0x7ffff7f89990) at /home/psergey/dev2/10.0-cp/sql/multi_range_read.cc:263
|
InnoDB unpacks columns before checking index condition. It seems to unpack b and c. 'a' is not unpacked.
|
|
On the other hand, running with index_condition_pushdown=off still produces wrong query result.
|
|
Ok, it seems I've got it. The cause is this combination of actions:
1. Range optimizer constructs and attempts to use a eq_ref access.
This causes "a=8" to be removed from the WHERE condition.
2. get_quick_select_for_ref() switches from JT_REF_OR_NULL to a quick
select but doesn't put a=8 back into the WHERE clause.
3. find_all_keys() has a code around register_used_fields(param) call that
clears the table->read_set, and then adds only fields that are needed
for checking the WHERE clause and sorting.
Since "a=8" was removed from the WHERE condition, it is not present in the
table->read_set anymore.
4. then, quick selects starts to read rows. The first range is "a=8, b=1".
It is an equality range but is not marked as such. For non-equality ranges,
the read is a sequence of handler->index_next() calls and then
compare_key() calls are used to check when we've reached the end of the range.
Since column `a` is not in the read_set, we get a=NULL. NULL<8, and we never
find out that we've reached the end of the range.
|
|
Possible directions for fix:
1. Make get_quick_select_for_ref() produce equality ranges.
2. Put the equalities that were removed by EQ_REF back into the WHERE clause.
3. Let find_all_keys() require reading of all columns that are used by the
quick select.
|
|
re solution #1:
get_quick_select_for_ref() is not consistent about use of EQ_RANGE flag:
For the primary equality it has:
range->flag= (ref->key_length == key_info->key_length ? EQ_RANGE : 0);
|
for the "OR NULL" part of ref_or_null, EQ_RANGE flag is set unconditionally.
On the other hand, QUICK_SELECT_DESC::QUICK_SELECT_DESC has this code:
/* Remove EQ_RANGE flag for keys that are not using the full key */
|
however, other code, like QUICK_RANGE_SELECT::unique_key_range, looks as if
EQ_RANGE can be set for equalities that don't cover the whole key.
Also, when range optimizer constructs quick select for the WHERE clause in this table, it does set EQ_RANGE flag.
|
|
Approach #2 is not a good idea. We've had an intent of doing the reverse: implementing a feature where parts of WHERE clause that are guaranteed to be true by use of range access should be removed from the WHERE.
|
|
Implementing solution #1 alone is not sufficient. when scanning equality ranges, there will still be compare_key (or compare_key2) calls done inside either ICP or handler::range_read_record. Ergo, all index columns must be present in the read set.
|
|
Fix pushed into 5.5
|