MySQL Bugs: #94737: MySQL uses composite hash index when not possible and returns wrong result

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

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

The database

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

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)

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.

Sorry. Meant that I managed to repeat it ..... 

Just a typo ......

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

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.

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.

Closed.

Thank you very much, Jon.