Description:
If a query can use an index, the largest prefix of the utilized index for which equals comparisons with constants are supplied in the WHERE should not be pushed down, as this would lead to a double evaluation of the comparison.
However, then entire condition is pushed down.
How to repeat:
CREATE TABLE `City` (
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`CountryCode`,`District`,`Name`)
) ENGINE=ndbcluster
explain extended select * from City where CountryCode = 'NLD';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------------------------------------+
| 1 | SIMPLE | City | ref | PRIMARY | PRIMARY | 3 | const | 10 | 100.00 | Using where with pushed condition: (`world_ndb`.`City`.`CountryCode` = _latin1'NLD') |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------------------------------------+
(Expected CountryCode condition not to be pushed down)
another example:
explain extended select * from City where CountryCode = 'NLD' and District = 'Noord-Holland' and Name >= 'Amsterdam';
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | City | range | PRIMARY | PRIMARY | 58 | NULL | 1 | 100.00 | Using where with pushed condition: ((`world_ndb`.`City`.`District` = _latin1'Noord-Holland') and (`world_ndb`.`City`.`CountryCode` = _latin1'NLD') and (`world_ndb`.`City`.`Name` >= _latin1'Amsterdam')) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(Expected pusdown only of `world_ndb`.`City`.`Name` >= _latin1'Amsterdam') as CountryCode and District form a prefix of the utilized index
Suggested fix:
Uhm..don't push down conditions that are already evaluated as part of index access
