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

Wrong metadata for MAX(CAST(time_column AS DATETIME))

    XMLWordPrintable

Details

    • 10.1.8-4

    Description

      I run "mysql --column-type-info test" and execute this SQL script:

      SET timestamp=UNIX_TIMESTAMP('2015-01-01 00:00:00');
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME);
      INSERT INTO t1 VALUES ('00:00:00'),('00:01:00');
      SELECT MAX(CAST(a AS DATETIME)) FROM t1;

      It returns the following metadata:

      Field   1:  `MAX(CAST(a AS DATETIME))`
      ...
      Type:       VAR_STRING
      Collation:  utf8_general_ci (33)
      Length:     57
      Max_length: 19
      Decimals:   0
      Flags:  

      This is wrong. The expected Type is DATETIME.

      Note, if I now run:

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 AS SELECT MAX(CAST(a AS DATETIME)) FROM t1;
      SHOW CREATE TABLE t2;

      it correctly creates a DATETIME column:

      +-------+---------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                  |
      +-------+---------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `MAX(CAST(a AS DATETIME))` datetime DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+---------------------------------------------------------------------------------------------------------------+

      So the problem is only with the metadata returned.

      Attachments

        Activity

          People

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