[MDEV-8875] Wrong metadata for MAX(CAST(time_column AS DATETIME)) - Jira

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.

Also repeatable in MySQL-5.7.8

Alexander Barkov added a comment - 2015-09-30 13:24 Also repeatable in MySQL-5.7.8

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server