Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3838

Standard SQL temporal literals are recognized by MySQL 5.6 but ignored by MariaDB 10.0

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.0.0, 5.5.28, 5.3.9, 5.2.12, 5.1.62
        • Fix Version/s: 10.0.4
        • Component/s: None
        • Labels:
          None

          Description

          In MySQL 5.6, the syntax TIME 'str' and alike is recognized and the type is taken into account.
          In previous versions of MySQL and in MariaDB, including 10.0, the syntax is accepted, but the type is ignored, and the str is treated as a string. It can cause various incompatibilities between MySQL 5.6 and MariaDB 10.0.

          MariaDB 10.0:

          CREATE TABLE t1 AS SELECT TIME'12:12:12' AS t, DATE'2012-12-12' AS d, TIMESTAMP'2012-12-12 12:12:12' AS ts;
SHOW CREATE TABLE t1;

          +-------+-------------------------------------------------------+
| Table | Create Table                                                                                                                                                                   |
+-------+-------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `t` varchar(8) NOT NULL DEFAULT '',
  `d` varchar(10) NOT NULL DEFAULT '',
  `ts` varchar(19) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------+

          MySQL 5.6:

          CREATE TABLE t1 AS SELECT TIME'12:12:12' AS t, DATE'2012-12-12' AS d, TIMESTAMP'2012-12-12 12:12:12' AS ts;
SHOW CREATE TABLE t1;

          +-------+----------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+----------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `t` time NOT NULL DEFAULT '00:00:00',
  `d` date NOT NULL DEFAULT '0000-00-00',
  `ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------+

            Gliffy Diagrams

              Attachments

                Issue Links

                is part of

                Task - A task that needs to be done. MDEV-330 merge microsecond feature from mysql-5.6

                • Major - Major loss of function.
                • Closed

                  Activity

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  Done

                  Show
                  bar Alexander Barkov added a comment - Done

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      4 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: