MySQL Bugs: #38819: Incorrect Impossible WHERE noticed after reading const tables

Bug #38819	Incorrect Impossible WHERE noticed after reading const tables
Submitted:	15 Aug 2008 10:47	Modified:	18 Aug 2008 19:00
Reporter:	Eduard Boer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.45-5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binary hexadecimal unique index

Description:
I have a table with a binary(16) column

select * from t2 where ID=0x84A3009529691F6186975D71F1939320;

results in:'Impossible WHERE noticed after reading const tables'
and no rows return

when I drop the unique index on ID
drop index ID on t2;
and run the query again I do get a result
select * from t2 where ID=0x84A3009529691F6186975D71F1939320;
one row returned!

than I recreate the index
create unique index ID on t2 (HOST_ID);

and run the query again and the problem is back
select * from t2 where ID=0x84A3009529691F6186975D71F1939320;
results in:'Impossible WHERE noticed after reading const tables'
and no rows return

repair table doesn't make a difference

binary(16) is not the PK column. PK column is an auto incremental.

How to repeat:
I seems It has something to do with the index and the table. I can repeat it on an new table, but only if I create the table with a select on the old table.

Thank you for the bug report. Could you please test with the latest released version and if the issue continue please provide the complete test case with the table's dump file. Thanks in advance.

Hi again,

I believe I have found the problem. But I don't have the solution.

About one of every 250 rows has this problem. And of all those rows the ID ends with hex value 20. That is the space character!!

I have two 'work arounds' for the problem, but they are both not satisfying.

"select * from t2 force index() where ID=0x84A3009529691F6186975D71F1939320;" workt, but is a zillion time slower because the table has about a million lines.

"select * from t2 where BINARY ID=0x84A3009529691F6186975D71F1939320;
work also, but is will do a full table scan as well. So no option.

The column is binary(16);

Do you need more information and do you have any ideas?

By the way:
the suggestion on this forum:
http://forums.mysql.com/read.php?25,114183,114335#msg-114335
is faster, however, not correct!

Thanks,
Eduard

Sorry...

http://bugs.mysql.com/bug.php?id=30196

Thank you for the feedback.

Bug closed as duplicate of bug #29087. Please upgrade to current version 5.0.67.