Bug #120112 Incorrect column mapping in EXCEPT query involving UNION ALL with a false constant condition in a derived table

• Submitted: 19 Mar 9:32
• Modified: 19 Mar 14:22
• Reporter: Guo Yuxiao
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 9.6.0
• OS: Any
• CPU Architecture: Any

[19 Mar 9:32] 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 {22105307, 0}.

How to repeat:
-- create table
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
    c0 TEXT,
    c2 BIGINT UNSIGNED ZEROFILL
);
INSERT INTO t0 VALUES (NULL, 22105307), (NULL, 0);

-- query, expect:empty set, actual:{22105307, 0}
SELECT COALESCE(a, b) AS c0 FROM (
  SELECT t_sub0.c0 AS a, t_sub0.c2 AS b 
  FROM (SELECT * FROM t0 AS t_sub0 WHERE CAST('b' AS DECIMAL(65, 30))) AS t_sub0
  UNION ALL
  SELECT t_sub1.c0 AS a, t_sub1.c2 AS b 
  FROM (SELECT * FROM t0 AS t_sub1 WHERE NOT (CAST('b' AS DECIMAL(65, 30)))) AS t_sub1
) AS t_branch
EXCEPT
SELECT COALESCE(`t3`.`c0`, `t3`.`c2`) AS `c0` FROM `t0` AS `t3`;

[19 Mar 14:22] Roy Lyseng

Thank you for the bug report.
However, this does lok like a bug.
It is probably the ZEROFILL attribute that changes type propagation
so that EXCEPT does not see compatible data types.
Without ZEROFILL, which is also a deprecated feature, results look correct.