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

[21 Mar 16:03] Simon Banaan
Description:
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 16:33] Simon Banaan
The database

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

[21 Mar 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 15:53] Sinisa Milivojevic
Hi,

Thank you for your bug report.

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

COUNT(1)
0
COUNT(1)
2544

Verified as reported.
[22 Mar 15:57] Sinisa Milivojevic
Sorry. Meant that I managed to repeat it ..... 

Just a typo ......
[25 Mar 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 13:44] Sinisa Milivojevic
Hi,

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.