MySQL Bugs: #121248: RIGHT() returns different results for semantically equivalent scalar-subquery arguments

Bug #121248	RIGHT() returns different results for semantically equivalent scalar-subquery arguments
Submitted:	9 Sep 6:44	Modified:	17 Sep 7:59
Reporter:	Hust DBTesting	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	26.7.0	OS:	MacOS (15.6.1)
Assigned to:		CPU Architecture:	ARM (Apple Silicon)
Tags:	expression-evaluation, FLOAT, implicit-conversion, right, scalar-subquery, string-function, type-conversion

Description:
RIGHT() returns different results when its first argument is produced by two semantically equivalent scalar subqueries.

The following two scalar subqueries are semantically equivalent for the data in this test case:

    (SELECT t0.c0 WHERE 1)

and:

    (SELECT t0.c0 WHERE t0.c0 > 0)

There is only one row in the table, and t0.c0 > 0 evaluates to TRUE for that row. Therefore, both scalar subqueries select the same column value from the same row.

However, when these expressions are used as the first argument of RIGHT(), MySQL produces different results:

    RIGHT((SELECT t0.c0 WHERE 1), 81)
    RIGHT((SELECT t0.c0 WHERE t0.c0 > 0), 81)

The first expression returns:

    1098980000

while the second returns:

    1098979968

The difference is 32.

The inconsistency is unexpected because the only semantic difference between the two scalar subqueries is the predicate used to select the row, and that predicate is TRUE for the only row in the table.

The issue appears to involve the conversion of the FLOAT result of a scalar subquery to the string representation consumed by RIGHT(). The same underlying column value appears to be converted differently depending on the form of the scalar subquery.

This is particularly surprising because the difference is observable at the function level: changing only a semantically redundant predicate changes the result of RIGHT().

The problem does not appear to be related to multiple matching rows, NULL handling, or different selected rows. The table contains exactly one row, and both scalar subqueries reference the same column from that row.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 FLOAT);

INSERT INTO t0 VALUES (1098980000);

SELECT RIGHT((SELECT t0.c0 WHERE 1), 81) AS r1,
       RIGHT((SELECT t0.c0 WHERE t0.c0 > 0), 81) AS r2
FROM t0;

Actual:
1098980000 | 1098979968
Expected:
1098980000 | 1098980000

Suggested fix:
Please investigate the type/value conversion path used when a scalar subquery returning a FLOAT is passed to RIGHT().

In particular, please check whether the two scalar-subquery forms:

    (SELECT t0.c0 WHERE 1)

and:

    (SELECT t0.c0 WHERE t0.c0 > 0)

produce different internal Item/result representations or take different FLOAT-to-string conversion paths.

The conversion of the scalar-subquery result should be consistent regardless of whether the subquery contains a predicate that is TRUE for the selected row.

A possible fix would be to ensure that equivalent scalar-subquery results use the same result type and the same FLOAT-to-string conversion path before RIGHT() processes the value.

Thank you for the bug report.

However, this is not a bug.

When processing floating point values, approximate values may sometimes be expected.

What happens here is that the first subquery is optimized to a simple column reference, which gived the FLOAT value, whereas the second subquery calculates its value in a cache object, which has its type set as DOUBLE. Thus, we get a slightly different value because of the added precision for DOUBLE.

We verified this mechanism with the explicit `CAST` tests above. However, this explains the cause rather than justifying the behavior.

The user did not request a `DOUBLE` value. Both scalar subqueries select the same `FLOAT` column `t0.c0`. The fact that one subquery is internally promoted to `DOUBLE` only because of its predicate form is the optimizer inconsistency being reported.

We also ran the same minimal test case in TiDB. TiDB returns identical results for both expressions; in our run, both were `1098980000`.

This indicates that the MySQL behavior is not required by SQL semantics. Another SQL implementation can preserve consistent behavior for the same schema, data, and expressions.

Why This Is a Bug: SQL Semantics and Optimizer Correctness

1. Both scalar subqueries select the same `FLOAT` column `t0.c0`. No explicit `CAST` is present. Under normal SQL type inference, both should have the result type of `t0.c0`.

2. For the given data, the predicates are semantically equivalent. The table has exactly one row, and `t0.c0 > 0` is `TRUE`. Both subqueries select the same value from the same row. There is no multiple-row issue, no `NULL` issue, and no different-row issue.

3. The optimizer changes an observable result. The first subquery keeps `FLOAT`; the second becomes `DOUBLE`. As a result, `RIGHT()` returns different strings. Query optimization must preserve observable semantics. A different internal execution or caching path must not change the result type or the final output.

4. Floating-point approximation does not dismiss the bug. We are not asking MySQL to make `FLOAT` exact. The issue is that the same `FLOAT` value should use the same conversion path in semantically equivalent expressions. If MySQL treats `FLOAT`-to-string conversion as implementation-dependent, it should at least be consistent for the same underlying `FLOAT` value.

5. TiDB does not show the inconsistency for the same test case, further indicating that this is an implementation-level optimizer/type-inference bug in MySQL.

Related: Bug#121255 (and Bug#121261 and Bug#121265 which are marked as duplicate of Bug#121255).

> What happens here is that the first subquery is optimized to a simple column reference, which gived the FLOAT value, whereas the second subquery calculates its value in a cache object, which has its type set as DOUBLE. Thus, we get a slightly different value because of the added precision for DOUBLE.

Roy, let me summarize my understanding.

a) "SELECT t0.c0 WHERE 1" returns a FLOAT;

b) "SELECT t0.c0 WHERE t0.c0 > 0" also returns a FLOAT, but the value passes through a cache object whose type is DOUBLE; 

c) in "RIGHT((SELECT t0.c0 WHERE t0.c0 > 0), 81)", RIGHT receives the cached DOUBLE value directly, which explains the different result.

If that understanding is correct, why would Hust DBTesting's suggestion to cast the cached DOUBLE to a FLOAT before passing it to RIGHT be considered unacceptable ?

Also, as Hust DBTesting pointed out, the current explanation describes why the observed behaviour occurs, but it does not justify the resolution as "Not a Bug".  Could you explain why casting the cached DOUBLE to a FLOAT before passing it to RIGHT is not the correct expectation, or why passing the cached DOUBLE directly to RIGHT without a cast is the correct behaviour.

In other words, the explanation establishes the implementation details, but it does not explain why the resulting type change and visible precision difference should be regarded as correct behaviour rather than a bug.

Thanks, Jean-François

Hi Jean-François,

First note, there is no semantic difference between the results. The difference is solely in the precision of the returned value.

Casting the result to a FLOAT value might give you the identically same value as the one expected. However, a CAST operation on float values is also inexact, thus there is no way to guarantee that the returned value will be identical. Thus, in my view, it is simpler and better to ignore the problem altogether and accept the precision difference.

This is also hardly a serious problem. How often do you need to serialize a float value into a string value and expect an exact answer?

We understand that FLOAT is approximate and that this issue may have limited practical impact. However, we are asking for consistent string results from expressions selecting the same stored FLOAT value, not exact reproduction of the original decimal literal. The internal conversion path explains the difference, but does not establish that it is correct. Limited impact may justify a low priority or a decision not to fix the issue, but is that sufficient grounds to classify it as “Not a Bug”?

Since there are inevitable precision issues with FLOAT values, and this is specified in our documentation, this is "Not a bug".
If that is unacceptable for the user, it must be ensured that such problems are avoided by using other data types or operands that do not have precision issues.