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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.0, 5.5.28, 5.3.9, 5.2.12, 5.1.62
    • 10.0.4
    • None
    • 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

              bar Alexander Barkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.