Bug #120099 Query returns wrong result when condition involves CAST(division AS CHAR)

• Submitted: 18 Mar 12:08
• Modified: 19 Mar 8:03
• Reporter: Guo Yuxiao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[18 Mar 12:08] Guo Yuxiao

Description:
Hi, I found a logic bug in MySQL 9.6.0.
A query that should have returned an empty set actually returned {0}.

How to repeat:
-- create database
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c0` int(180) unsigned DEFAULT NULL,
  KEY `i0` (`c0`)
) CHARSET=utf8mb4;
INSERT INTO `t1` VALUES (0);

-- query, expected:{}, actual:{0}
SELECT * FROM (
  SELECT
    c0_1
  FROM (
    SELECT
      c0 AS c0_1
    FROM t1
  ) AS t_inner
  WHERE
    c0_1 <> CAST(1 / (-87.45) AS CHAR)
    AND NOT (c0_1 <> CAST(1 / (-87.45) AS CHAR))
) AS t_outer;

[19 Mar 8:03] Roy Lyseng

Thank you for the bug report.
Verified as described.