MySQL Bugs: #106226: cast expression result is different when used with least expression

Bug #106226	cast expression result is different when used with least expression
Submitted:	20 Jan 2022 10:12	Modified:	20 Jan 2022 12:57
Reporter:	jiangtao guo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.23, 5.7.37, 8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The result of cast(least(...) as signed) is:
mysql> select cast(least('2001-01-01 00:00:00.1', c1) as signed) from t1;
+----------------------------------------------------+
| cast(least('2001-01-01 00:00:00.1', c1) as signed) |
+----------------------------------------------------+
|                                     20010101000000 |
+----------------------------------------------------+

and result of  least(..) is:

mysql> select least('2001-01-01 00:00:00.1', c1) from t1;
+------------------------------------+
| least('2001-01-01 00:00:00.1', c1) |
+------------------------------------+
| 2001-01-01 00:00:00.100000         |
+------------------------------------+
1 row in set (0.00 sec)

but when use cast directly, the result is different:
mysql> select cast('2001-01-01 00:00:00.1' as signed) from t1;
+-----------------------------------------+
| cast('2001-01-01 00:00:00.1' as signed) |
+-----------------------------------------+
|                                    2001 |
+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

How to repeat:
drop table if exists t1;
create table t1(c1 TIMESTAMP(6));
insert into t1 values('2001-01-01 00:00:00.1');
select cast(least('2001-01-01 00:00:00.1', c1) as signed) from t1;                                                                                                                                                                                                                                 select cast('2001-01-01 00:00:00.1' as signed) from t1;

Suggested fix:
IMHO, least()'s result type is inferred as timestamp when it's used in cast(), because.
mysql> select cast(c1 as signed) from t1;
+--------------------+
| cast(c1 as signed) |
+--------------------+
|     20010101000000 |
+--------------------+

Hello jiangtao,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
This is not a bug.
The result of the LEAST function is a timestamp, which cast to an integer gives 20010101000000.
The other CAST statement tries to interpret a character string as an integer, which stops at the first '-'.
You may want to use this statement instead:

  select cast(timestamp'2001-01-01 00:00:00.1' as signed) from t1;