MySQL Bugs: #121356: `LEAST()` combined with a correlated scalar subquery and `LIKE` produces an unexpected boolean result.

Bug #121356	`LEAST()` combined with a correlated scalar subquery and `LIKE` produces an unexpected boolean result.
Submitted:	23 Sep 6:13	Modified:	23 Sep 10:23
Reporter:	Hust DBTesting	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.x	OS:	MacOS (15.6.1)
Assigned to:		CPU Architecture:	ARM (Apple Silicon)

Description:
In MySQL 8.0.x, the following query returns 0, but the expected boolean result is 1.

The expression combines a correlated scalar subquery, LEAST(), a DECIMAL column, and the LIKE operator.

The scalar subquery:

    (SELECT 1.0 WHERE 1 OR t93.c0)

returns 1.0 because the WHERE condition is true.

Therefore, LEAST() operates on the scalar-subquery result and t93.c0. Since t93.c0 is 0, the LEAST() expression should evaluate to the minimum of 1.0 and 0, i.e. 0.

The resulting value is then used as the left operand of LIKE against the DECIMAL column t93.c0, which is also 0. Therefore, 0 LIKE 0 should be TRUE, i.e. 1.

Actual result:
The query returns 0.

Expected result:
The query should return 1.

How to repeat:
1. Create the test table:

    DROP TABLE IF EXISTS t93;

    CREATE TABLE t93
    (
        c0 DECIMAL
    );

2. Insert one row:

    INSERT INTO t93 (c0)
    VALUES (0);

3. Execute:

    SELECT LEAST(
               (SELECT 1.0
                WHERE 1 OR t93.c0),
               t93.c0
           ) LIKE t93.c0 AS ref1
    FROM t93;

4. Observed result:

    +------+
    | ref1 |
    +------+
    |    0 |
    +------+

5. Expected result:

    +------+
    | ref1 |
    +------+
    |    1 |
    +------+

Additional information:
This behavior appears to involve the interaction between LEAST(), correlated scalar-subquery evaluation, DECIMAL type handling, and implicit conversion for the LIKE operator.

The intermediate LEAST() expression evaluates to the minimum of 1.0 and 0, so the left operand of LIKE should be 0. Since the right operand is also the DECIMAL column value 0, the LIKE comparison should be true.

Thank you for the bug report.

However, this is not a bug.

DECIMAL defaults to DECIMAL(10,0). The subquery returns decimal 1.0 with scale 1; LEAST() aggregates its argument types, giving DECIMAL(11,1) and the value 0.0. Numeric equality with zero is true, but character matching against the pattern `0` is false. This follows the documented DECIMAL defaults.