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
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