Bug #120122 Inconsistent evaluation of GREATEST() with ZEROFILL column in OUTER JOIN queries

• Submitted: 20 Mar 4:58
• Modified: 20 Mar 12:06
• Reporter: Weipeng Wang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Linux
• CPU Architecture: x86

[20 Mar 4:58] Weipeng Wang

Description:
An OUTER JOIN query produces inconsistent results when using GREATEST() (or LEAST()) on a ZEROFILL column together with a string constant (especially between 0 and 1).

The issue is sensitive to the join condition: replacing `ON !t1.c0` with a constant condition such as `ON TRUE` makes the issue disappear.

The problem occurs when the query is partitioned using complementary predicates (IS NULL / IS NOT NULL) and combined with UNION ALL. In this case, the result of the partitioned query differs from that of the original query.

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

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

(SELECT * FROM t0 RIGHT JOIN t1 ON !t1.c0 WHERE GREATEST(t1.c0, "0.9") && (t0.c0 IS NOT NULL)) 
UNION ALL
(SELECT * FROM t0 RIGHT JOIN t1 ON !t1.c0 WHERE GREATEST(t1.c0, "0.9") && (t0.c0 IS NULL));
/* returns 1 row: (0, 0) */

 SELECT * FROM t0 RIGHT JOIN t1 ON !t1.c0 WHERE GREATEST(t1.c0, "0.9");
/* returns empty set */

[20 Mar 12:06] Roy Lyseng

Thank you for the bug report.
Verified as described.
It seems the issue is related to use of ZEROFILL, a deprecated feature.