[MCOL-4263] function FLOOR may have changed behavior for DATETIME Created: 2020-08-20  Updated: 2021-01-20  Resolved: 2021-01-07

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.0.0
Fix Version/s: 5.5.2, 5.6.1

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

Issue Links:
Relates
relates to MCOL-4472 floor/ceil fails the existing MTR test. Closed
Epic Link: ColumnStore Compatibility Improvements

 Description   

Ceiling has changed behavior when processing DATETIME, TIMESTAMP and TIME col types (Not DATE, for some reason). It's probable that FLOOR has changed in the same way.

The change is from expecting a BIGINT return with format yyyymmddHHMMSS, to a fully formatted datetime string.

 Comments   
Comment by Daniel Lee (Inactive) [ 2021-01-04 ]

Build tested: 5.6.1-1 (Drone build 1442)

These is an issue with floor() on time data type.

MariaDB [mytest]> show create table t1;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

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

t1 CREATE TABLE `t1` (
`c1` datetime DEFAULT NULL,
`c2` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`c3` time DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

insert into t1 values ('2021-01-04 11:34:00', now(), '11:34:00');

MariaDB [mytest]> select floor(c1), floor(c2), floor(c3) from t1;
-------------------------------------------------

floor(c1) floor(c2) floor(c3)

-------------------------------------------------

2021-01-04 11:34:00 2021-01-04 17:56:09 00:00:00

-------------------------------------------------
1 row in set (0.016 sec)

MariaDB [mytest]> select ceiling(c1), ceiling(c2), ceiling(c3) from t1;
---------------------------------------------------

ceiling(c1) ceiling(c2) ceiling(c3)

---------------------------------------------------

2021-01-04 11:34:00 2021-01-04 17:56:09 11:34:00

---------------------------------------------------

floor() on c3 returned '00:00:00'

It worked correctly on InnoDB table

The same test returned

MariaDB [mytest]> show create table t2;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

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

t2 CREATE TABLE `t2` (
`c1` datetime DEFAULT NULL,
`c2` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`c3` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

insert into t2 values ('2021-01-04 11:34:00', now(), '11:34:00');

MariaDB [mytest]> select floor(c1), floor(c2), floor(c3) from t2;
-------------------------------------------------

floor(c1) floor(c2) floor(c3)

-------------------------------------------------

2021-01-04 11:34:00 2021-01-04 18:06:36 11:34:00

-------------------------------------------------
1 row in set (0.002 sec)

Comment by David Hall (Inactive) [ 2021-01-07 ]

The floor problems are addressed in MCOL-4472

Generated at Thu Feb 08 02:49:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.