Bug #119324 Inconsistent Data Type Inference in Aggregate Functions with CTE

• Submitted: 7 Nov 6:19
• Modified: 7 Nov 8:52
• Reporter: cl hl
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[7 Nov 6:19] cl hl

Description:
avg's value type unexpectedly changes from time 'HH:MM:SS' to numeric 'HHMMSS.00'.That's fault.

mysql> select MAX(NOW());
+---------------------+
| MAX(NOW())          |
+---------------------+
| 2025-11-07 06:17:48 |
+---------------------+
1 row in set (0.00 sec)

mysql> select AVG(NOW());
+---------------------+
| AVG(NOW())          |
+---------------------+
| 20251107061820.0000 |
+---------------------+
1 row in set (0.00 sec)

How to repeat:
select AVG(NOW());
select MAX(NOW());

[7 Nov 8:52] Roy Lyseng

AVG is not implemented for temporal data types like DATETIME,
only for DECIMAL and floating point types.
Thus, to be able to take AVG of a datetime expression, it has to be converted
to a DECIMAL value first.
Not a bug.