Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4263

function FLOOR may have changed behavior for DATETIME

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.0.0
    • 5.5.2, 5.6.1
    • PrimProc
    • None

    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.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MCOL-4472 floor/ceil fails the existing MTR test.

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            David.Hall David Hall (Inactive) created issue -
            David.Hall David Hall (Inactive) made changes -
            Field Original Value New Value
            Assignee David Hall [ david.hall ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Fix Version/s 5.5.1 [ 25030 ]
            Fix Version/s 5.5.1 [ 25030 ]
            Fix Version/s 1.5.5 [ 24414 ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Affects Version/s 1.0.0 [ 22028 ]
            Affects Version/s 1.5.4 [ 24413 ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked higher
            David.Hall David Hall (Inactive) made changes -
            Fix Version/s 5.6.1 [ 25031 ]
            Fix Version/s 6.1 [ 25201 ]
            Fix Version/s 5.5.1 [ 25030 ]
            David.Hall David Hall (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            toddstoffel Todd Stoffel (Inactive) made changes -
            Fix Version/s 6.1 [ 25201 ]
            David.Hall David Hall (Inactive) made changes -
            Assignee David Hall [ david.hall ] Ben Thompson [ ben.thompson ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            ben.thompson Ben Thompson (Inactive) made changes -
            Status In Review [ 10002 ] In Testing [ 10301 ]
            ben.thompson Ben Thompson (Inactive) made changes -
            Assignee Ben Thompson [ ben.thompson ] Daniel Lee [ dleeyh ]
            dleeyh Daniel Lee (Inactive) added a comment -

            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)

            dleeyh Daniel Lee (Inactive) added a comment - 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)
            dleeyh Daniel Lee (Inactive) made changes -
            Assignee Daniel Lee [ dleeyh ] David Hall [ david.hall ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            David.Hall David Hall (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            David.Hall David Hall (Inactive) made changes -
            David.Hall David Hall (Inactive) added a comment -

            The floor problems are addressed in MCOL-4472

            David.Hall David Hall (Inactive) added a comment - The floor problems are addressed in MCOL-4472
            David.Hall David Hall (Inactive) made changes -
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            David.Hall David Hall (Inactive) made changes -
            Fix Version/s 5.5.2 [ 25601 ]

            People

              David.Hall David Hall (Inactive)
              David.Hall David Hall (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.