Bug #120992 Inconsistent comparison behavior between CHARSET() string expression vs CTAS materialized column against hex literal (0x

• Submitted: 23 Jul 13:06
• Modified: 3 Aug 6:53
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.10, 9.7.1
• OS: Ubuntu
• CPU Architecture: Any

[23 Jul 13:06] Xiaoyuan Xie

Description:
A query result divergence occurs when comparing a dynamic CHARSET() expression with a hex literal (0x51), compared to evaluating the same hex literal comparison against a materialized VARCHAR column derived via CREATE TABLE ... AS SELECT (CTAS).

Specifically:

Dynamic expression evaluation: SELECT 1 WHERE CHARSET(0) > 0x51 evaluates 'binary' vs 0x51 as TRUE and returns 1 row.

Materialized table evaluation: CREATE TABLE t1 AS SELECT CHARSET(0) AS c; materializes column c with value 'binary'. Executing SELECT 1 FROM t1 WHERE c > 0x51 evaluates to FALSE and returns Empty set.

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

-- Step 1: Create materialized table t1 via CTAS storing CHARSET(0)
CREATE TABLE t1 AS SELECT CHARSET(0) AS c;

-- Query 1: Dynamic function comparison against hex literal (without FROM table)
SELECT 1 WHERE CHARSET(0) > 0x51;
-- Returns: 1 row

-- Query 2: Materialized column comparison against hex literal
SELECT 1 FROM t1 WHERE c > 0x51;
-- Returns: Empty set

[3 Aug 6:53] Chaithra Marsur Gopala Reddy

Hi Xiaoyuan Xie,

Thank you for the test case. Verified as described.