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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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 |
      +-------+----------------------------------------------------------------------------+

        Attachments

          Issue Links

            Activity

              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:

                  Git Integration