Bug #94737 MySQL uses composite hash index when not possible and returns wrong result
Submitted: 21 Mar 2019 16:03 Modified: 25 Mar 2019 14:45
Reporter: Simon Banaan Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[21 Mar 2019 16:03] Simon Banaan
When creating a memory table with a composite hash index (defined as index, or as unique, maybe also as primary) the index is falsely used in the execution plan when filtering on the first column. In my case the table is a MEMORY table with these properties

id (PK)
project (int)
datum (date)

with HASH UNIQUE(project, date) (name pd)

when running a query with `project IN (..log comma separated set of id's)` the explain query says it can use index `pd`. But in fact it can not; hash indices can only be used in the where when all fields are used in the filtering. The result is always an empty set of rows, which is wrong.

When i switch the index order to (date, project) it works fine. So it looks like it uses the strategry used in BTREEs or something.

How to repeat:
1.) create table t with HASH(x1,x2) index (my case it was about 2500 rows)
2.) run 'explain select * from t where x2 IN(1,2,3...., many more, make sure they are present in x2, in my case about 80 keys)
3.) check whether it says the index is in the possible keys
4.) When executing the actual query the result is empty

Suggested fix:
? dont now
[21 Mar 2019 16:33] Simon Banaan
The database

Attachment: tmp_projectdays.sql (application/octet-stream, text), 313.56 KiB.

[21 Mar 2019 16:35] Simon Banaan
Running this query returns falsely no results

SELECT COUNT(1) FROM `tmp_projectdays_4` WHERE `project` IN(15409,15911,15929,15936,16004,16005,16007,16029,16031,16052,16054,16040,12485,15892,16035,16060,16066,16093,16057,16027,15988,15440,15996,11457,15232,15704,12512,12508,14896,15594,16039,14997,16058,14436,16006,15761,15536,16016,16019,11237,13332,16037,14015,15537,15369,15756,12038,14327,13673,11393,14377,15983,12514,12511,13585,12732,14139,14141,12503,15727,15531,15746,15773,15207,13675,15676,15663,10412,13677,15528,15530,10032,15535,15693,15532,15533,15534,15529,16056,16064,16070,15994,15918,16045,16073,16074,16077,16069,16022,16081,15862,16048,16062,15610,15421,16001,15896,15004,15881,15882,15883,15884,15886,16065,15814,16076,16085,16174,15463,15873,15874,15880,15636,16092,15909,16078,15923,16026,16047,16094,16111,15914,15919,16041,16063,16068,15971,16080,15961,16038,16096,16127,15641,13295,16146,15762,15811,15937,16150,16152,14438,16086,16156,15593,16147,15910,16106,16107,16161,16132,16095,16137,16072,16097,16110,16114,16162,16166,16175,16176,16178,15473,16160,15958,16036,16042,16115,16165,16167,16170,16177,16185,15823,16190,16169,15989,16194,16116,16131,16157,16192,16197,16203,16193,16050,16180,16209,15522,16148,16205,16201,15990,16158,16216,16033,15974,16112,16133,16181,16188,16189,16212,16238,16241,16183,15640,15638,16087,16088,16129,16186,16164,16108,15985,16244,15991,15763,16049,15999,16104,16208,13976,16122,15924,16046,16242,16151,16117,16187)
[22 Mar 2019 15:53] MySQL Verification Team

Thank you for your bug report.

I have managed to report it. First run is with index and second run is without one:


Verified as reported.
[22 Mar 2019 15:57] MySQL Verification Team
Sorry. Meant that I managed to repeat it ..... 

Just a typo ......
[25 Mar 2019 14:45] Simon Banaan
Thanks! I am very curious of my analysis is right, that it uses the composite index with the left-most prefix strategy even though that is not possible on hash indices.

What can we expect in terms of time frame for a fix? It seems like a serious bug to me since it gives invalid results and no errors
[26 Mar 2019 13:44] MySQL Verification Team

First of all, no need to thank me ...

Second, regarding timeframes, scheduling is an internal process in the company to which even I do not have access.
[21 Feb 12:34] Jon Stephens
Documented fix as follows in the MySQL 8.0.20 changelog:

    The execution plan for a query that filtered on the first
    column of a composite hash index wrongly used this index,
    producing erroneous results.

[21 Feb 13:14] MySQL Verification Team
Thank you very much, Jon.