Bug #121223 GREATEST/LEAST(TIME column, big int): ordering flips between rows; SELECT display contradicts WHERE

• Submitted: 3 Sep 7:45
• Modified: 3 Sep 12:39
• Reporter: Chunling Qin
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 9.7.2
• OS: Any
• CPU Architecture: Any

[3 Sep 7:45] Chunling Qin

Description:
mysql> CREATE TABLE t (id INT PRIMARY KEY, c TIME);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES (1,'16:52:49'),(2,'00:00:00'),(3,'23:59:59');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT id, LEAST(c, 10000000000) FROM t;
+----+-----------------------+
| id | LEAST(c, 10000000000) |
+----+-----------------------+
|  1 | 10000000000           |
|  2 | 00:00:00              |
|  3 | 10000000000           |
+----+-----------------------+
3 rows in set (0.00 sec)

-- 1 → 10000000000   (this row: TIME > number)
-- 2 → 00:00:00      (this row: number > TIME)  ← same expression, ordering flips on row 2!
-- 3 → 10000000000

Row 2's 00:00:00 is exactly the TIME-ization overflow-zero value , which switches it to a different comparison path — the comparison semantics of one expression drift per row.

Root cause direction: two-layer path divergence between the comparison inside GREATEST/LEAST and the downstream = comparator.

How to repeat:
CREATE TABLE t (id INT PRIMARY KEY, c TIME);
INSERT INTO t VALUES (1,'16:52:49'),(2,'00:00:00'),(3,'23:59:59');

SELECT id, LEAST(c, 10000000000) FROM t;

[3 Sep 12:39] Roy Lyseng

Thank you for the bug report.

However, this is not a bug.

When evaluating LEAST and GREATEST on a temporal value and a string value, the temporal value is converted to a string before performing the operation.