Bug #120270 Incorrect result for INTERSECT ALL with semantically equivalent EXCEPT operands when right EXCEPT left operand is wrap

• Submitted: 15 Apr 9:49
• Modified: 16 Apr 21:19
• Reporter: Guo Yuxiao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[15 Apr 9:49] Guo Yuxiao

Description:
Hi, I found a logic bug in MySQL 9.6.0.

Expected result:
+----------+----------+
| c0       | c1       |
+----------+----------+
| 3.141593 | 3.141593 |
+----------+----------+

Actual result:
Empty set

How to repeat:
-- query
(
  (SELECT PI() AS c0, PI() AS c1)
  EXCEPT
  (SELECT CAST(NULL AS UNSIGNED) AS c0,
          CAST(0 AS SIGNED) AS c1
   WHERE 0)
)
INTERSECT ALL
(
  (SELECT *
   FROM (
     (SELECT PI() AS c0, PI() AS c1)
   ) AS a_sub
   WHERE 1 = 1)
  EXCEPT
  (SELECT CAST(NULL AS UNSIGNED) AS c0,
          CAST(0 AS SIGNED) AS c1
   WHERE 0)
);

[16 Apr 21:19] Roy Lyseng

Thank you for the bug report.
Verified as described.

The underlying problem may be that the function PI is defined to return a DOUBLE value with precision and number of decimals, instead of a DOUBLE value without such constraints.
Maybe the best solution to the problem is to change metadata for the function.