MySQL Bugs: #118710: Optimizer incorrectly simplifies negative float/decimal comparison in JOIN condition, leading to wrong results

Bug #118710	Optimizer incorrectly simplifies negative float/decimal comparison in JOIN condition, leading to wrong results
Submitted:	23 Jul 12:08	Modified:	23 Jul 12:33
Reporter:	策吕	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.36, 8.0.42, 8.0.43, 8.4.6	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When a JOIN condition involves a comparison between a column and a negative, high-precision constant (e.g., t0.c0 < -0.159...), the query optimizer in certain MySQL versions incorrectly simplifies this predicate.
It erroneously transforms t0.c0 < -0.159... into t0.c0 <= 0. This is logically incorrect and causes the query to return more rows than expected.
This behavior is observed in MySQL version 8.0.36. 
While I haven't tested other higher versions, I presume this issue should affect many versions after 8.0.36.Because I also found this problem in Percona for MySQL (Ver. 8.4.5-5).

How to repeat:
DROP DATABASE IF EXISTS database60;
CREATE DATABASE database60;
USE database60;
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL ZEROFILL   COMMENT 'asdf'  PRIMARY KEY) ;
CREATE TABLE IF NOT EXISTS t1 LIKE t0;

INSERT  IGNORE INTO t0(c0) VALUES(0.6092186899021483);
REPLACE INTO t0(c0) VALUES(0.28981613730687783);
REPLACE INTO t1(c0) VALUES(748123898);
REPLACE INTO t1(c0) VALUES(1481272451);

INSERT IGNORE INTO t1(c0) VALUES(1331725754);

INSERT IGNORE INTO t0(c0) VALUES(NULL), (87887787), ("y0y*[k");

INSERT  IGNORE INTO t1(c0) VALUES(1705827418);
INSERT IGNORE INTO t1(c0) VALUES(0.13522962748540124);
INSERT INTO t1(c0) VALUES(0.5272172690621615);
REPLACE INTO t0(c0) VALUES(0.7599456737109825);
REPLACE INTO t0(c0) VALUES(0.35229271129153394), (188180185), (0.4941660460941426);
REPLACE INTO t1(c0) VALUES(627745109);

INSERT  INTO t1(c0) VALUES(119420093);

INSERT IGNORE INTO t0(c0) VALUES(NULL);

INSERT  IGNORE INTO t1(c0) VALUES(-1241635147);
INSERT  IGNORE INTO t0(c0) VALUES(-1.911138865E9);

INSERT  IGNORE INTO t1(c0) VALUES(-2.38122679E8);
INSERT IGNORE INTO t0(c0) VALUES('/1#');

INSERT IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t0(c0) VALUES(0.4770454728934377);

INSERT IGNORE INTO t1(c0) VALUES(-2.051634794E9);

REPLACE INTO t1(c0) VALUES(1481272451);

REPLACE INTO t0(c0) VALUES(4.41199826E8), (1106025383), (1898318456);
INSERT IGNORE INTO t0(c0) VALUES(NULL);

INSERT  IGNORE INTO t0(c0) VALUES(0.7266650139725989);

INSERT IGNORE INTO t1(c0) VALUES("");

ALTER TABLE t0 ;

INSERT  IGNORE INTO t1(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c0) VALUES(NULL);

REPLACE INTO t1(c0) VALUES(0.14146102720157439);
REPLACE INTO t1(c0) VALUES(0.9812794415075463);
INSERT IGNORE INTO t1(c0) VALUES(0.47426926824040205);

REPLACE INTO t0(c0) VALUES(0.9482036944105979);
INSERT  IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t1(c0) VALUES(0.35823418572545795);

REPLACE INTO t0(c0) VALUES(6.71552397E8);
INSERT IGNORE INTO t0(c0) VALUES("3");

INSERT IGNORE INTO t0(c0) VALUES('');

INSERT IGNORE INTO t0(c0) VALUES(-356234390), (809375353), ('G&aP4U');

INSERT IGNORE INTO t0(c0) VALUES(-1682859619);

REPLACE INTO t1(c0) VALUES(1447762607);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
INSERT  IGNORE INTO t1(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c0) VALUES("n*");
INSERT  IGNORE INTO t1(c0) VALUES(0.3687805209526718);

INSERT IGNORE INTO t0(c0) VALUES("0.5605009994662532");
REPLACE INTO t1(c0) VALUES(0.8492108746138904), (0.5184193894075909), (1945089543);

REPLACE INTO t1(c0) VALUES(0.8564391496155429);
REPLACE INTO t1(c0) VALUES(1507595971);

INSERT IGNORE INTO t0(c0) VALUES(NULL), (2119876541), (NULL);

INSERT INTO t0(c0) VALUES(1841450698);
TRUNCATE TABLE t1;

INSERT  IGNORE INTO t1(c0) VALUES(NULL);

REPLACE INTO t0(c0) VALUES(156335365);

REPLACE INTO t1(c0) VALUES(352844017);

INSERT IGNORE INTO t1(c0) VALUES("zzla");
INSERT IGNORE INTO t1(c0) VALUES(-1241320305);

REPLACE INTO t0(c0) VALUES(570406715);

INSERT  IGNORE INTO t1(c0) VALUES(0.1303618478098888);

INSERT INTO t1(c0) VALUES(0.9690807743979165);
INSERT  IGNORE INTO t0(c0) VALUES(NULL);

INSERT  INTO t0(c0) VALUES(595671651);
REPLACE INTO t1(c0) VALUES("556272821");
INSERT IGNORE INTO t1(c0) VALUES(0.9508420994606236), (0.18962222149300334), (1148227438);

REPLACE INTO t1(c0) VALUES(0.7417843047268576);

INSERT  IGNORE INTO t0(c0) VALUES("0.6850420785372549");
INSERT IGNORE INTO t0(c0) VALUES(0.16981953605266542);

REPLACE INTO t1(c0) VALUES(723636020);
INSERT  IGNORE INTO t0(c0) VALUES(1208023101);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c0) VALUES(NULL);

(SELECT t1.c0 AS ref0, t0.c0 AS ref1 FROM t1 LEFT JOIN t0 ON (t0.c0) < ((- (0.15932309967572778))) WHERE  EXISTS (SELECT 1)) EXCEPT ALL ((SELECT t1.c0 AS ref0, t0.c0 AS ref1 FROM t1 LEFT JOIN t0 ON (t0.c0) < ((- (0.15932309967572778))) WHERE  EXISTS (SELECT 1)) EXCEPT ALL (SELECT t1.c0 AS ref0, t0.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON (t0.c0) < ((- (0.15932309967572778))) WHERE  EXISTS (SELECT 1)));-- cardinality: 0

EXPLAIN ANALYZE SELECT t1.c0 AS ref0, t0.c0 AS ref1 FROM t1 INNER JOIN t0 ON (t0.c0) < ((- (0.15932309967572778))) WHERE  EXISTS (SELECT 1);-- cardinality: 6

Hello 策 吕,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh