| Bug #120093 | Incorrect result with OUTE JOIN and NULL-dependent LIKE predicate on FLOAT values when partitioned by IS NULL conditions | ||
|---|---|---|---|
| Submitted: | 18 Mar 8:12 | Modified: | 18 Mar 9:18 |
| Reporter: | Weipeng Wang | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.45 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
[18 Mar 9:18]
Roy Lyseng
Thank you for the bug report. However, this is not a bug since -0 and 0 are equivalent values.
Description: An OUTER JOIN query produces incorrect results when the WHERE clause uses an IS NULL predicate on the joined table. Partitioning the query using complementary predicates (IS NULL / IS NOT NULL) and combining with UNION ALL changes the result compared to the original query. This occurs with FLOAT values including both 0 and "-0". How to repeat: CREATE TABLE t0(c0 FLOAT); CREATE TABLE t1(c0 FLOAT); INSERT INTO t0 VALUES (0), ("-0"); INSERT INTO t1 VALUES (0), ("-0"); (SELECT * FROM t0 LEFT JOIN t1 AS t1 ON t0.c0 = t1.c0 WHERE t0.c0 LIKE (t1.c0 IS NULL) AND t1.c0 IS NOT NULL) UNION ALL (SELECT * FROM t0 LEFT JOIN t1 AS t1 ON t0.c0 = t1.c0 WHERE t0.c0 LIKE (t1.c0 IS NULL) AND t1.c0 IS NULL); /* returns 2 rows: (0, 0) (-0, 0) */ SELECT * FROM t0 LEFT JOIN t1 AS t1 ON t0.c0 = t1.c0 WHERE t0.c0 LIKE (t1.c0 IS NULL); /* returns 2 rows: (0, 0) (0, -0) */