Bug #27615 unnecessary push down of condition in presence of utilized index
Submitted: 3 Apr 2007 13:21 Modified: 26 Apr 2007 18:48
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1 OS:Linux (linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: 5.1.16, engine_condition_pushdown, ndb, ordered index
Triage: Triaged: D3 (Medium)

[3 Apr 2007 13:21] Roland Bouman
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
[23 Apr 2007 22:27] Sveta Smirnova
Thank you for the report.

Verified as described.
[23 Apr 2007 22:27] Sveta Smirnova
test case

Attachment: ndb_bug27615.test (application/octet-stream, text), 154.80 KiB.

[23 Apr 2007 22:29] Sveta Smirnova
Only version 5.1 is affected.