MySQL Bugs: #120987: Inconsistent boolean evaluation of LEAST(TIME, VARCHAR) between dynamic calculation and CTAS materialized column

Bug #120987	Inconsistent boolean evaluation of LEAST(TIME, VARCHAR) between dynamic calculation and CTAS materialized column
Submitted:	23 Jul 12:16	Modified:	6 Aug 7:44
Reporter:	Xiaoyuan Xie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.10, 9.7.1	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
A query result divergence occurs when evaluating LEAST(TIME, VARCHAR) in a boolean predicate context dynamically versus evaluating a materialized column generated via CREATE TABLE ... AS SELECT (CTAS).

Specifically:

In base table t0, c0 is TIME ('16:28:55') and c1 is VARCHAR ('cXtrY6O'). Dynamic evaluation of WHERE (LEAST(c0, c1)) evaluates to FALSE (returning Empty set), because the comparison coercion handles 'cXtrY6O' as 0 in boolean evaluation.

In table t1 (created via CREATE TABLE t1 AS SELECT LEAST(c0, c1) AS c0, c2 FROM t0), CTAS materializes c0 with the string/time value '16:28:55'. When evaluating WHERE (c0) on t1, the value '16:28:55' evaluates to TRUE in boolean context, returning 1 row.

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

-- Step 1: Create base table t0 with TIME and VARCHAR columns
CREATE TABLE t0 (c0 TIME, c1 VARCHAR(7), c2 DECIMAL);

-- Step 2: Insert sample row
INSERT INTO t0 (c0, c1, c2) VALUES ('16:28:55', 'cXtrY6O', -1389339889.422922);

-- Step 3: Materialize expression into table t1 via CTAS
CREATE TABLE t1 AS SELECT (LEAST(c0,c1)) AS c0, c2 AS c2 FROM t0;

-- Query 1: Dynamic evaluation on base table t0
SELECT (LEAST(c0,c1)), c2 FROM t0 WHERE (LEAST(c0,c1));
-- Returns: Empty set

-- Query 2: Evaluation on materialized table t1
SELECT (c0), c2 FROM t1 WHERE (c0);
-- Returns: 1 row ('16:28:55', -1389339889)

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 #120987 is related to, or possibly a duplicate of, Bug #120848?

Both reports appear to involve inconsistent type or comparison semantics for TIME-derived expressions between direct evaluation and materialization:

In Bug #120987, LEAST(TIME, VARCHAR) has different boolean evaluation results when computed directly and when materialized through CREATE TABLE ... AS SELECT.
In Bug #120848, a MAKETIME() result is compared differently when evaluated through a VIEW/CTE and when materialized into a TEMPORARY TABLE.

Although the specific expressions and predicates are different, both issues seem potentially related to the type derivation or conversion semantics of TIME expressions before and after materialization.

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

Thank you!