Bug #120146 Wrong result due to incorrect const-table pruning for NOT IN on FLOAT UNIQUE column in MEMORY engine

Submitted:	25 Mar 6:22	Modified:	13 Oct 9:32
Reporter:	策吕	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.6.0	OS:	Linux (ubuntu 20.04)
Assigned to:		CPU Architecture:	x86

Description:
Summary

MySQL returns different results for two logically equivalent queries on a FLOAT UNIQUE column in the MEMORY (HEAP) engine. The original query is incorrectly optimized to Zero rows (no matching row in const table), while an equivalent derived-table form returns one row. This indicates a wrong-result bug caused by incorrect constant-table pruning or predicate simplification.

Actual Result
Query 1 returns:

Empty set

Query 2 returns one row:

0

Expected Result
Both queries should return the same result.

The predicate in Query 2 is exactly the same predicate as in Query 1, except that it is first projected as a boolean column ref1 and then filtered by WHERE ref1. This rewriting should preserve semantics. Since Query 2 returns one row, Query 1 should also return one row.

Execution Plans

Query 1:

EXPLAIN SELECT t0.c0 AS ref0 FROM t0 WHERE NOT (-("") NOT IN (t0.c0));

Plan:

-> Zero rows (no matching row in const table)  (cost=0..0 rows=0)

Query 2:

EXPLAIN SELECT ref0
FROM (
    SELECT t0.c0 AS ref0, (NOT (-("") NOT IN (t0.c0))) AS ref1
    FROM t0
) AS s
WHERE ref1;

Plan:

-> Filter: (0 <> (<cache>(-('')) = t0.c0))  (cost=2.73 rows=2)
    -> Table scan on t0  (cost=2.73 rows=2)

Additional Notes
This issue appears to require both of the following conditions:

  1. ENGINE = HEAP / MEMORY
  2. a UNIQUE index on the FLOAT column

If either condition is removed, both queries return 0 rows.

This suggests that the bug may be related to the interaction between:

  constant folding or predicate simplification,
  unique-key reasoning / const-table optimization,
  floating-point comparison in the MEMORY engine.

How to repeat:
DROP DATABASE IF EXISTS test_db;
CREATE DATABASE test_db;
USE test_db;

-- Key factors: ENGINE = HEAP and UNIQUE index. If any condition is removed, both Query 1 and Query 2 return 0
CREATE TABLE t0(c0 FLOAT UNIQUE) ENGINE = HEAP;

INSERT INTO t0(c0) VALUES(0.6840049395257071);
REPLACE INTO t0(c0) VALUES(0);

-- Query 1: Returns Empty Set (Incorrect)
-- Explain shows: "Zero rows (no matching row in const table)"
SELECT t0.c0 AS ref0 FROM t0 WHERE NOT (-("") NOT IN (t0.c0));

-- Query 2: Returns 1 row (Correct)
SELECT ref0 FROM (SELECT t0.c0 AS ref0, (NOT (-("") NOT IN (t0.c0))) AS ref1 FROM t0) AS s WHERE ref1;

change category.

Could anyone take a look at this? The bug is still reproducible on MySQL 8.0. Thanks.

Thank you for your bug report.
Verified as described.
reproducible on all four tested branches : mysql-trunk, mysql-8.0, mysql-8.4, and mysql-9.7

Looks like the same issue as bug#67978. Closing as duplicate.

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.