MySQL Bugs: #120985: ENUM comparison with a CHAR() binary-string expression changes after CREATE TABLE AS SELECT materialization

Bug #120985	ENUM comparison with a CHAR() binary-string expression changes after CREATE TABLE AS SELECT materialization
Submitted:	23 Jul 10:39	Modified:	6 Aug 12:46
Reporter:	Xiaoyuan Xie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.6.0,9.7.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
A comparison between an ENUM column containing its index-0 empty error value and the result of CHAR() produces different Boolean results depending on whether the CHAR() expression is evaluated directly or materialized using CREATE TABLE ... AS SELECT.

When evaluated against the original table, the following comparison returns false:

c2 != CHAR(c0, c1)

After CHAR(c0, c1) is materialized into another table, the corresponding comparison returns true:

c2 != c0

The CASE expressions in the original test only make this difference visible as a row-count difference. The fundamental issue is that the two comparison expressions return opposite Boolean values.

How to repeat:
DROP DATABASE IF EXISTS test; 
CREATE DATABASE test; 
USE test;
CREATE TABLE t0 ( c0 TINYBLOB, c1 LINESTRING, c2 ENUM('val0', 'val1') ); 
INSERT INTO t0 VALUES ( 'A', ST_GeomFromText('LINESTRING(0 0,1 1)'), '0' ); 
CREATE TABLE t1 AS SELECT CHAR(c0, c1) AS c0, c2 FROM t0; 
SELECT c2 != CHAR(c0, c1) AS direct_cmp FROM t0; 
SELECT c2 != c0 AS materialized_cmp FROM t1;

Actual result

direct_cmp       = 0
materialized_cmp = 1

Hi Xiaoyuan Xie,

Thank you for the test case. Verified as described.

Hi Chaithra Marsur Gopala Reddy,

Thank you for verifying this report.

Could you please confirm whether Bug #120985 is related to, or possibly a duplicate of, Bug #120847?

Both reports involve an ENUM-derived value whose comparison behavior changes after materialization. In Bug #120985, the comparison between an index-0 ENUM value and a `CHAR()` expression changes after `CREATE TABLE ... AS SELECT`. In Bug #120847, the comparison behavior of an ENUM-derived `CASE` expression changes after `CREATE TEMPORARY TABLE ... SELECT` materializes the result as a `VARCHAR` column.

Although the expressions and comparison predicates are different, both issues appear potentially related to ENUM result-type derivation or comparison semantics between direct evaluation and materialization.

Could you please check whether they share the same underlying root cause or fix? If they are distinct issues, a brief clarification about the difference would also be very helpful.

Thank you!

Hi Xiaoyuan Xie,

Thank you for letting us know about this. We do not think at this point that they are duplicates. So we would like to keep the bug reports separate. Once we start fixing this issue, we will have more clarity and can then confirm that they are related or indeed duplicates.

Thanks,
Chaithra

Hi Chaithra Marsur Gopala Reddy,

Thank you for the clarification. We appreciate your help and look forward to any further updates once the issue has been investigated in more detail.

Thanks,
Xiaoyuan