[MCOL-4236] ceiling() gives wrong answer for datetime Created: 2020-08-05  Updated: 2020-12-24  Resolved: 2020-08-27

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr, MDB Plugin, PrimProc
Affects Version/s: 1.5.3
Fix Version/s: 5.4.1

Type: Bug Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-4281 ROUND(<DATE field>) gives wrong answer Closed
relates to MCOL-4468 ALTER TABLE ADD COLUMN ... TIME fails... Closed
Sprint: 2020-8

 Description   

From working_tpch1/qa_fe_cnxFunctions/bug_3334_ceil.sql
select 'ceil(dtm)', id as id, dtm, ceiling(dtm) from dtypes;
I Should be:
ceil(dtm) id dtm ceiling(dtm)
-ceil(dtm) 1 2010-11-08 08:19:22 20101108081922
-ceil(dtm) 2 2010-11-01 08:18:22 20101101081822
-ceil(dtm) 3 2010-10-25 08:17:22 20101025081722
-ceil(dtm) 4 2010-10-18 08:16:22 20101018081622
-ceil(dtm) 5 2010-10-11 08:15:22 20101011081522
11:14
But in 1.5.4, we have
11:14
+ceil(dtm) 1 2010-11-08 08:19:22 0000-01-09 08:09:54
+ceil(dtm) 2 2010-11-01 08:18:22 0000-01-09 08:09:47
+ceil(dtm) 3 2010-10-25 08:17:22 0000-01-09 08:08:39
+ceil(dtm) 4 2010-10-18 08:16:22 0000-01-09 08:08:32
+ceil(dtm) 5 2010-10-11 08:15:22 0000-01-09 08:08:26

 Comments   
Comment by Daniel Lee (Inactive) [ 2020-08-12 ]

InnoDB returns the following:

MariaDB [mytest]> select 'ceil(dtm)', id as id, dtm, ceiling(dtm) from dtypes;
-----------------------------------------------------+

ceil(dtm) id dtm ceiling(dtm)

-----------------------------------------------------+

ceil(dtm) 1 2010-11-08 08:19:22 2010-11-08 08:19:22
ceil(dtm) 2 2010-11-01 08:18:22 2010-11-01 08:18:22
ceil(dtm) 3 2010-10-25 08:17:22 2010-10-25 08:17:22
ceil(dtm) 4 2010-10-18 08:16:22 2010-10-18 08:16:22
ceil(dtm) 5 2010-10-11 08:15:22 2010-10-11 08:15:22

Both output in the bug description are incorrect.

Comment by David Hall (Inactive) [ 2020-08-19 ]

Between MDB 10.4 and 10.5, the expected return type for ceil(datetime) has changed from BIGINT to DATETIME. This change also applies to TIME and TIMESTAMP, but not to DATE. I expect a change for DATE will appear in the near future.

This change does necessitate new ref files be generated, as the expected output is different than in previous versions.

Comment by David Hall (Inactive) [ 2020-08-19 ]

There is a PR for the regression suite (#238)

Comment by Daniel Lee (Inactive) [ 2020-08-27 ]

Build tested: 1.5.4-1 (drone #479)

The fix is in 1.5.4-1, which is the current develop branch. The release number will be changed to 1.5.5 later.

MariaDB [tpch1]> select 'ceil(dtm)', id as id, dtm, ceiling(dtm) from dtypes
-> ;
-----------------------------------------------------+

ceil(dtm) id dtm ceiling(dtm)

-----------------------------------------------------+

ceil(dtm) 1 2010-11-08 08:19:22 2010-11-08 08:19:22
ceil(dtm) 2 2010-11-01 08:18:22 2010-11-01 08:18:22
ceil(dtm) 3 2010-10-25 08:17:22 2010-10-25 08:17:22
ceil(dtm) 4 2010-10-18 08:16:22 2010-10-18 08:16:22
ceil(dtm) 5 2010-10-11 08:15:22 2010-10-11 08:15:22
ceil(dtm) 6 2010-10-04 08:14:22 2010-10-04 08:14:22
ceil(dtm) 7 2010-09-27 08:13:22 2010-09-27 08:13:22
Generated at Thu Feb 08 02:48:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.