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

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

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

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description I run "mysql --column-type-info test" and execute this SQL script:
          {code}
          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;
          {code}
          It returns the following metadata:
          {noformat}
          Field 1: `MAX(CAST(a AS DATETIME))`
          ...
          Type: VAR_STRING
          Collation: utf8_general_ci (33)
          Length: 57
          Max_length: 19
          Decimals: 0
          Flags:
          {noformat}
          This is wrong. The expected Type is DATETIME.
          		 I run "mysql --column-type-info test" and execute this SQL script:
          {code}
          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;
          {code}
          It returns the following metadata:
          {noformat}
          Field 1: `MAX(CAST(a AS DATETIME))`
          ...
          Type: VAR_STRING
          Collation: utf8_general_ci (33)
          Length: 57
          Max_length: 19
          Decimals: 0
          Flags:
          {noformat}
          This is wrong. The expected Type is DATETIME.

          Note, if I now run:
          {code}
          DROP TABLE IF EXISTS t2;
          CREATE TABLE t2 AS SELECT MAX(CAST(a AS DATETIME)) FROM t1;
          SHOW CREATE TABLE t2;
          {code}
          it correctly creates a DATETIME column:
          {noformat}

          {noformat}
          bar Alexander Barkov made changes -
          Description I run "mysql --column-type-info test" and execute this SQL script:
          {code}
          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;
          {code}
          It returns the following metadata:
          {noformat}
          Field 1: `MAX(CAST(a AS DATETIME))`
          ...
          Type: VAR_STRING
          Collation: utf8_general_ci (33)
          Length: 57
          Max_length: 19
          Decimals: 0
          Flags:
          {noformat}
          This is wrong. The expected Type is DATETIME.

          Note, if I now run:
          {code}
          DROP TABLE IF EXISTS t2;
          CREATE TABLE t2 AS SELECT MAX(CAST(a AS DATETIME)) FROM t1;
          SHOW CREATE TABLE t2;
          {code}
          it correctly creates a DATETIME column:
          {noformat}

          {noformat}
          		 I run "mysql --column-type-info test" and execute this SQL script:
          {code}
          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;
          {code}
          It returns the following metadata:
          {noformat}
          Field 1: `MAX(CAST(a AS DATETIME))`
          ...
          Type: VAR_STRING
          Collation: utf8_general_ci (33)
          Length: 57
          Max_length: 19
          Decimals: 0
          Flags:
          {noformat}
          This is wrong. The expected Type is DATETIME.

          Note, if I now run:
          {code}
          DROP TABLE IF EXISTS t2;
          CREATE TABLE t2 AS SELECT MAX(CAST(a AS DATETIME)) FROM t1;
          SHOW CREATE TABLE t2;
          {code}
          it correctly creates a DATETIME column:
          {noformat}
          +-------+---------------------------------------------------------------------------------------------------------------+
          | Table | Create Table |
          +-------+---------------------------------------------------------------------------------------------------------------+
          | t2 | CREATE TABLE `t2` (
            `MAX(CAST(a AS DATETIME))` datetime DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          +-------+---------------------------------------------------------------------------------------------------------------+
          {noformat}

          So the problem is only with metadata.
          bar Alexander Barkov made changes -
          Description I run "mysql --column-type-info test" and execute this SQL script:
          {code}
          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;
          {code}
          It returns the following metadata:
          {noformat}
          Field 1: `MAX(CAST(a AS DATETIME))`
          ...
          Type: VAR_STRING
          Collation: utf8_general_ci (33)
          Length: 57
          Max_length: 19
          Decimals: 0
          Flags:
          {noformat}
          This is wrong. The expected Type is DATETIME.

          Note, if I now run:
          {code}
          DROP TABLE IF EXISTS t2;
          CREATE TABLE t2 AS SELECT MAX(CAST(a AS DATETIME)) FROM t1;
          SHOW CREATE TABLE t2;
          {code}
          it correctly creates a DATETIME column:
          {noformat}
          +-------+---------------------------------------------------------------------------------------------------------------+
          | Table | Create Table |
          +-------+---------------------------------------------------------------------------------------------------------------+
          | t2 | CREATE TABLE `t2` (
            `MAX(CAST(a AS DATETIME))` datetime DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          +-------+---------------------------------------------------------------------------------------------------------------+
          {noformat}

          So the problem is only with metadata.
          		 I run "mysql --column-type-info test" and execute this SQL script:
          {code}
          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;
          {code}
          It returns the following metadata:
          {noformat}
          Field 1: `MAX(CAST(a AS DATETIME))`
          ...
          Type: VAR_STRING
          Collation: utf8_general_ci (33)
          Length: 57
          Max_length: 19
          Decimals: 0
          Flags:
          {noformat}
          This is wrong. The expected Type is DATETIME.

          Note, if I now run:
          {code}
          DROP TABLE IF EXISTS t2;
          CREATE TABLE t2 AS SELECT MAX(CAST(a AS DATETIME)) FROM t1;
          SHOW CREATE TABLE t2;
          {code}
          it correctly creates a DATETIME column:
          {noformat}
          +-------+---------------------------------------------------------------------------------------------------------------+
          | Table | Create Table |
          +-------+---------------------------------------------------------------------------------------------------------------+
          | t2 | CREATE TABLE `t2` (
            `MAX(CAST(a AS DATETIME))` datetime DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          +-------+---------------------------------------------------------------------------------------------------------------+
          {noformat}

          So the problem is only with the metadata returned.
          bar Alexander Barkov made changes -
          Sprint 10.1.8-4 [ 16 ]
          bar Alexander Barkov made changes -
          Rank Ranked higher
          bar Alexander Barkov made changes -
          Labels upstream
          bar Alexander Barkov added a comment -

          Also repeatable in MySQL-5.7.8

          bar Alexander Barkov added a comment - Also repeatable in MySQL-5.7.8
          bar Alexander Barkov made changes -
          Fix Version/s 10.1.8 [ 19605 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 71846 ] MariaDB v4 [ 149670 ]

          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.