Bug #120087 Incorrect result with RIGHT JOIN and IS NULL predicate on partitioned indexed table

• Submitted: 17 Mar 13:12
• Modified: 18 Mar 4:58
• Reporter: Weipeng Wang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Linux
• CPU Architecture: x86

[17 Mar 13:12] Weipeng Wang

Description:
A query involving a RIGHT JOIN applied to a join result may produce incorrect results when an IS NULL predicate on a column from the left-hand side is used to partition the result.

Since the predicates (IS NULL and IS NOT NULL) are complementary and exhaustive, the UNION ALL of the partitioned queries should be logically equivalent to the original query. 

However, the result contains an extra row with NULL values, indicating incorrect behavior.

How to repeat:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT KEY) PARTITION BY KEY(c0);
CREATE TABLE t2(c0 INT);

INSERT INTO t0 VALUES(1);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1);

(SELECT * FROM t0 CROSS JOIN t1 RIGHT JOIN t2 ON t0.c0 = t2.c0 WHERE t1.c0 IS NOT NULL)
UNION ALL
(SELECT * FROM t0 CROSS JOIN t1 RIGHT JOIN t2 ON t0.c0 = t2.c0 WHERE t1.c0 IS NULL);
/* returns 2 rows: (1, 1, 1) (NULL, NULL, 1) */

SELECT * FROM t0 CROSS JOIN t1 RIGHT JOIN t2 ON t0.c0 = t2.c0;
/* returns 1 row: (1, 1, 1) */

[18 Mar 4:58] Chaithra Marsur Gopala Reddy

Hi Weipeng Wang,

Thank you for the test case. Verified as described.