MySQL Bugs: #113313: Unexpected Result for ENGINE HEAP

Bug #113313	Unexpected Result for ENGINE HEAP
Submitted:	3 Dec 2023 0:44	Modified:	4 Dec 2023 11:36
Reporter:	JINSHENG BA	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	8.2.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE t0(c0 INT, c1 FLOAT) ;
CREATE TABLE t1(c0 FLOAT UNIQUE, c1 FLOAT) ENGINE = HEAP;
INSERT IGNORE INTO t0(c1) VALUES("a");
INSERT INTO t1(c0, c1) VALUES(1, 2);
INSERT IGNORE INTO t1(c0, c1) VALUES('b', 3);

SELECT t1.c0 IN (-t0.c1) FROM t0, t1; -- {0}, {1}
SELECT * FROM t0, t1 WHERE t1.c0 IN (-t0.c1); -- empty result

The expression t1.c0 IN (-t0.c1) is evaluated to true for one row in the first query, while the second query returns empty.

How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.2.0

Then execute the above test case.

Hi Mr. BA,

Thank you for your bug report.

However, this is not a bug.

Your first query only has a select list which returns rows from the Cartesian product.

Your second query used a condition in the WHERE clause, which is not satisfied for a single row. Hence, no results.

Not a bug.

Thanks for your reply!

For the first query, if we cast the expression to a boolean result, the result includes a true and false. Therefore, the second query should return one row, instead of empty.

Like this query, it returns one true. There is an inconsistency between this query and the second query.
SELECT (t1.c0 IN (-t0.c1)) IS TRUE FROM t0, t1; -- {true},{false}

Hi,

Your first query has no filtering and hence you get a result.

Your second query has filtering, which is not met, hence no results.

Not a bug.

The bug does not mean both queries return the same result, instead, the same expression is evaluated to different results for two queries.

For the first query, the expression is evaluated to {true} and {false} for two rows.
For the second query, the expression is evaluated to {false} and {false} for two rows.

if we remove ENGINE = HEAP, the second query returns one row, instead of an empty result.

Hi,

Yes, your last observation is correct.

That is due to the default hash indices that are used by that storage engine.

Expected behaviour.