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

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

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0
        • Fix Version/s: 10.1.8
        • Component/s: Temporal Types
        • Labels:
        • Sprint:
          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.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Also repeatable in MySQL-5.7.8

                Show
                bar Alexander Barkov added a comment - Also repeatable in MySQL-5.7.8

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    1 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Agile