Bug #120996 Incorrect evaluation of NOT (LEAST(c,c) AND predicate) causes duplicate rows in TLP query partitioning

• Submitted: 23 Jul 15:28
• Modified: 3 Aug 8:17
• Reporter: Xiaoyuan Xie
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.6.0
• OS: Ubuntu
• CPU Architecture: Any

[23 Jul 15:28] Xiaoyuan Xie

Description:
A logical bug in expression evaluation/optimization causes a row to satisfy both WHERE P and WHERE NOT P when P is LEAST(c, c) AND c = 0.5.

For a table t containing a single DECIMAL row 1:

LEAST(1, 1) AND 1 = 0.5 evaluates to 1 AND 0, which is 0 (FALSE).

Therefore, WHERE P should evaluate to FALSE.

WHERE NOT P should evaluate to TRUE.

However, MySQL evaluates both WHERE LEAST(c,c) AND c=0.5 and WHERE NOT (LEAST(c,c) AND c=0.5) to TRUE for c = 1, leading to invalid duplicate outputs when partitioned via Ternary Logic Partitioning (TLP).

How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t(c DECIMAL);
INSERT INTO t VALUES (1);

-- Baseline Query (Returns 1 row)
SELECT c FROM t;

-- Bug Query: TLP Partitioning (Returns 2 rows instead of 1)
SELECT c FROM t WHERE LEAST(c,c) AND c=0.5
UNION ALL
SELECT c FROM t WHERE NOT (LEAST(c,c) AND c=0.5)
UNION ALL
SELECT c FROM t WHERE (LEAST(c,c) AND c=0.5) IS NULL;

Expected Result:

The TLP query should return exactly 1 row (1), as c = 1 should only satisfy the NOT P branch.

Actual Result:

The TLP query returns 2 rows (1 and 1), demonstrating that the row 1 satisfies both P and NOT P.

[3 Aug 8:17] Chaithra Marsur Gopala Reddy

Hi Xiaoyuan Xie,

Thank you for the test case. However this bug is fixed in MySQL-9.7 with the patch for Bug#118033. So we are marking it as duplicate.

Thanks,
Chaithra