Bug #115088 different time rounding in Insert and select
Submitted: 23 May 2024 3:23 Modified: 24 May 2024 3:09
Reporter: Ecee no Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.3.0, 5.7, 8.0 OS:Windows
Assigned to: CPU Architecture:Any

[23 May 2024 3:23] Ecee no 
Description:
Time rounding may be different in Insert and Select.

How to repeat:
1. 
select maketime(2.2/time '08:30:23.01',2.2/time '08:30:23.01',2.2/time '08:30:23.01') f14;
2.
select * from (select maketime(2.2/time '08:30:23.01',2.2/time '08:30:23.01',2.2/time '08:30:23.01')) f14;
drop view if exists v1;
CREATE OR REPLACE VIEW v1 as select maketime(2.2/time '08:30:23.01',2.2/time '08:30:23.01',2.2/time '08:30:23.01') f14;
select * from v1;
drop table if exists t1;
CREATE table t1 as select maketime(2.2/time '08:30:23.01',2.2/time '08:30:23.01',2.2/time '08:30:23.01') f14;
select * from t1;

Suggested fix:
I think the data in the select and insert operations should be consistent.
[23 May 2024 7:33] MySQL Verification Team 
Hello Ecee no,

Thank you for the report and test case.

regards,
Umesh
[24 May 2024 3:09] Ecee no 
1. reuslt is 00:00:00.00002
select maketime(2.4/time '08:30:23.01',2.4/time '08:30:23.01',2.4/time '08:30:23.01') f14;

2. reuslt is 00:00:00.00003
select * from (select maketime(2.4/time '08:30:23.01',2.4/time '08:30:23.01',2.4/time '08:30:23.01')) f14;

drop view if exists v1;
CREATE OR REPLACE VIEW v1 as select maketime(2.4/time '08:30:23.01',2.4/time '08:30:23.01',2.4/time '08:30:23.01') f14;
select * from v1;

drop table if exists t1;
CREATE table t1(f14 time(6)) as select maketime(2.4/time '08:30:23.01',2.4/time '08:30:23.01',2.4/time '08:30:23.01') f14;
select * from t1;

Could you respond to that? Why are the results different? Is this expected behavior or a bug?
Thanks.