MySQL Bugs: #114794: Incorrect query result

Bug #114794	Incorrect query result
Submitted:	26 Apr 2024 9:36	Modified:	26 Apr 2024 10:20
Reporter:	John Jove	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

View
Add Comment
Files
Developer
Edit Submission
View Progress Log
Contributions

Description:
Run the following statements, in which an empty result is expected to be retuned by the query.

How to repeat:
CREATE TABLE t1 (c1 varbinary(10));
CREATE TABLE t2 (c1 bit(7), UNIQUE KEY i1 (c1));
INSERT INTO t2(c1) VALUES (0x00);
INSERT INTO t1(c1) VALUES (0x30);

SELECT c1 FROM t1 WHERE 1 = (c1 IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ c1 FROM t2 USE INDEX (i1))); -- actual:{0}, expected: {}

Hi Mr. Jove,

Thank you for your bug report.

When we ran your query with the optimiser hint we got the empty result.

When we ran your query without any hint we got a single result, being 0, which is correct result.

This is a bug, but a very low priority bug.

Verified for version 8.0 and higher.