Description:
MySQL 26.7.0 produces inconsistent results when `GREATEST()` is called with mixed TIME and DECIMAL arguments.
The function returns `693.47` when evaluated directly, but returns `143000` when its result is used in a numeric expression such as `+ 0`.
According to the MySQL documentation, `GREATEST()` should evaluate its arguments and determine the result before any type conversion required by the surrounding expression.
In this case, the function returns a VARCHAR result containing `693.47`. Therefore, adding zero should convert the returned value to a number without changing which argument was selected.
However, MySQL appears to reevaluate the arguments using numeric comparison rules, causing the TIME argument (`14:30:00`, numerically represented as `143000`) to be selected instead.
**Suspected root cause:**
The relevant implementation is in `sql/item_func.cc`, particularly `Item_func_min_max::val_real()` and `Item_func_min_max::real_op()`.
The implementation contains the following special handling:
```cpp
if (has_temporal_arg() && data_type() == MYSQL_TYPE_VARCHAR)
return real_op();
```
The `real_op()` function evaluates and compares the original arguments using their numeric values rather than converting the previously determined `GREATEST()` result.
Consequently, different evaluation contexts may select different arguments:
- String evaluation: `"693.47"` is greater than `"14:30:00"`, returning `693.47`.
- Numeric evaluation: `143000` is greater than `693.47`, returning `143000`.
This appears inconsistent with the documented behavior that conversion required by an outer expression should occur after the `GREATEST()` function has been evaluated.
Source code:
https://github.com/mysql/mysql-server/blob/mysql-26.7.0/sql/item_func.cc
Documentation:
https://dev.mysql.com/doc/refman/26.7/en/comparison-operators.html
How to repeat:
No tables or additional setup are required.
**1. Minimal reproduction**
```sql
SELECT
GREATEST(
TIME('14:30:00'),
CAST(693.47 AS DECIMAL(10,2))
) AS direct_value,
GREATEST(
TIME('14:30:00'),
CAST(693.47 AS DECIMAL(10,2))
) + 0 AS numeric_value,
CAST(GREATEST(
TIME('14:30:00'),
CAST(693.47 AS DECIMAL(10,2))
) AS CHAR) + 0 AS text_then_numeric;
```
**Actual result:**
```text
+--------------+---------------+-------------------+
| direct_value | numeric_value | text_then_numeric |
+--------------+---------------+-------------------+
| 693.47 | 143000 | 693.47 |
+--------------+---------------+-------------------+
```
**Expected result:**
```text
+--------------+---------------+-------------------+
| direct_value | numeric_value | text_then_numeric |
+--------------+---------------+-------------------+
| 693.47 | 693.47 | 693.47 |
+--------------+---------------+-------------------+
```
**2. Metamorphic Oracle: Numeric conversion equivalence**
For this expression, `GREATEST()` has a VARCHAR result containing a valid numeric string.
Let:
```sql
E = GREATEST(
TIME('14:30:00'),
CAST(693.47 AS DECIMAL(10,2))
)
```
The following expressions should therefore produce the same numeric value:
```text
E + 0
CAST(E AS CHAR) + 0
```
Inserting an explicit CHAR conversion should not change the value selected by `GREATEST()`.
However, the observed results are:
```text
E + 0 = 143000
CAST(E AS CHAR) + 0 = 693.47
```
The equivalence is violated, providing a single-DBMS metamorphic correctness oracle.
**3. Additional confirmation: Materialized result**
```sql
CREATE TEMPORARY TABLE greatest_test AS
SELECT GREATEST(
TIME('14:30:00'),
CAST(693.47 AS DECIMAL(10,2))
) AS v;
SHOW COLUMNS FROM greatest_test;
SELECT v, v + 0 AS numeric_value
FROM greatest_test;
DROP TEMPORARY TABLE greatest_test;
```
The materialized column has type `VARCHAR(22)`.
**Observed result:**
```text
+--------+---------------+
| v | numeric_value |
+--------+---------------+
| 693.47 | 693.47 |
+--------+---------------+
```
This confirms that the function's materialized result is `693.47` and that numeric conversion of this value correctly produces `693.47`.
The incorrect result occurs when the original `GREATEST()` expression is evaluated directly in numeric context.
Description: MySQL 26.7.0 produces inconsistent results when `GREATEST()` is called with mixed TIME and DECIMAL arguments. The function returns `693.47` when evaluated directly, but returns `143000` when its result is used in a numeric expression such as `+ 0`. According to the MySQL documentation, `GREATEST()` should evaluate its arguments and determine the result before any type conversion required by the surrounding expression. In this case, the function returns a VARCHAR result containing `693.47`. Therefore, adding zero should convert the returned value to a number without changing which argument was selected. However, MySQL appears to reevaluate the arguments using numeric comparison rules, causing the TIME argument (`14:30:00`, numerically represented as `143000`) to be selected instead. **Suspected root cause:** The relevant implementation is in `sql/item_func.cc`, particularly `Item_func_min_max::val_real()` and `Item_func_min_max::real_op()`. The implementation contains the following special handling: ```cpp if (has_temporal_arg() && data_type() == MYSQL_TYPE_VARCHAR) return real_op(); ``` The `real_op()` function evaluates and compares the original arguments using their numeric values rather than converting the previously determined `GREATEST()` result. Consequently, different evaluation contexts may select different arguments: - String evaluation: `"693.47"` is greater than `"14:30:00"`, returning `693.47`. - Numeric evaluation: `143000` is greater than `693.47`, returning `143000`. This appears inconsistent with the documented behavior that conversion required by an outer expression should occur after the `GREATEST()` function has been evaluated. Source code: https://github.com/mysql/mysql-server/blob/mysql-26.7.0/sql/item_func.cc Documentation: https://dev.mysql.com/doc/refman/26.7/en/comparison-operators.html How to repeat: No tables or additional setup are required. **1. Minimal reproduction** ```sql SELECT GREATEST( TIME('14:30:00'), CAST(693.47 AS DECIMAL(10,2)) ) AS direct_value, GREATEST( TIME('14:30:00'), CAST(693.47 AS DECIMAL(10,2)) ) + 0 AS numeric_value, CAST(GREATEST( TIME('14:30:00'), CAST(693.47 AS DECIMAL(10,2)) ) AS CHAR) + 0 AS text_then_numeric; ``` **Actual result:** ```text +--------------+---------------+-------------------+ | direct_value | numeric_value | text_then_numeric | +--------------+---------------+-------------------+ | 693.47 | 143000 | 693.47 | +--------------+---------------+-------------------+ ``` **Expected result:** ```text +--------------+---------------+-------------------+ | direct_value | numeric_value | text_then_numeric | +--------------+---------------+-------------------+ | 693.47 | 693.47 | 693.47 | +--------------+---------------+-------------------+ ``` **2. Metamorphic Oracle: Numeric conversion equivalence** For this expression, `GREATEST()` has a VARCHAR result containing a valid numeric string. Let: ```sql E = GREATEST( TIME('14:30:00'), CAST(693.47 AS DECIMAL(10,2)) ) ``` The following expressions should therefore produce the same numeric value: ```text E + 0 CAST(E AS CHAR) + 0 ``` Inserting an explicit CHAR conversion should not change the value selected by `GREATEST()`. However, the observed results are: ```text E + 0 = 143000 CAST(E AS CHAR) + 0 = 693.47 ``` The equivalence is violated, providing a single-DBMS metamorphic correctness oracle. **3. Additional confirmation: Materialized result** ```sql CREATE TEMPORARY TABLE greatest_test AS SELECT GREATEST( TIME('14:30:00'), CAST(693.47 AS DECIMAL(10,2)) ) AS v; SHOW COLUMNS FROM greatest_test; SELECT v, v + 0 AS numeric_value FROM greatest_test; DROP TEMPORARY TABLE greatest_test; ``` The materialized column has type `VARCHAR(22)`. **Observed result:** ```text +--------+---------------+ | v | numeric_value | +--------+---------------+ | 693.47 | 693.47 | +--------+---------------+ ``` This confirms that the function's materialized result is `693.47` and that numeric conversion of this value correctly produces `693.47`. The incorrect result occurs when the original `GREATEST()` expression is evaluated directly in numeric context.