MySQL Bugs: #120091: Incorrect result with OUTER JOIN on DECIMAL compared to floating-point condition when partitioned by IS NOT NULL predica

Bug #120091	Incorrect result with OUTER JOIN on DECIMAL compared to floating-point condition when partitioned by IS NOT NULL predica
Submitted:	18 Mar 6:13	Modified:	18 Mar 9:01
Reporter:	Weipeng Wang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.45	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
This issue is similar to Bug #119994, but it occurs with a DECIMAL column and does not require any KEY or PARTITION.

This problem appears when comparing a DECIMAL value (especially 0) with a floating-point constant in the range (-1, 0) in a LEFT JOIN condition.

When the query is partitioned using complementary predicates (IS NOT NULL and IS NULL) and combined with UNION ALL, the result differs from the original query.

How to repeat:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 DECIMAL);
CREATE TABLE t2(c0 INT);

INSERT INTO t0(c0) VALUES(0);
INSERT INTO t1(c0) VALUES(0);
INSERT INTO t2(c0) VALUES(0);

(SELECT * FROM t0 CROSS JOIN t1 LEFT JOIN t2 ON t1.c0 <= -0.1 WHERE t2.c0 IS NOT NULL) 
UNION ALL 
(SELECT * FROM t0 CROSS JOIN t1 LEFT JOIN t2 ON t1.c0 <= -0.1 WHERE t2.c0 IS NULL);
/* returns 2 rows: (0, 0, 0) (0, 0, NULL) */

SELECT * FROM t0 CROSS JOIN t1 LEFT JOIN t2 ON t1.c0 <= -0.1;
/* returns 1 row: (0, 0, NULL) */

Correction: The issue is similar to Bug #120087, not Bug #119994 as previously stated.

A smaller minimal reproduction has been found, which reproduces the issue using only two tables:

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 DECIMAL);
  
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES (0);
  
(SELECT * FROM t0 AS a LEFT JOIN t1 AS b ON b.c0 <= -0.1 WHERE b.c0 IS NOT NULL)
UNION ALL
(SELECT * FROM t0 AS a LEFT JOIN t1 AS b ON b.c0 <= -0.1 WHERE b.c0 IS NULL);
/* returns 2 rows: (0, 0) (0, NULL) */

SELECT * FROM t0 AS a LEFT JOIN t1 AS b ON b.c0 <= -0.1;
/* returns 1 row: (0, NULL) */

I have changed the title from 
"Incorrect result with CROSS JOIN and LEFT JOIN on floating-point condition when partitioned by IS NOT NULL predicate" 
to 
"Incorrect result with OUTER JOIN on DECIMAL compared to floating-point condition when partitioned by IS NOT NULL predicate".

Also, I no longer think this issue is similar to Bug #120087, based on the newer minimal reproduction.

Thank you for the bug report.
Verified as described.