[MDEV-8862] Wrong field type for MAX(COALESCE(datetime_column)) Created: 2015-09-28  Updated: 2017-11-07  Resolved: 2017-11-07

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Duplicate Votes: 0
Labels: upstream

Issue Links:
Duplicate
duplicates MDEV-8910 Wrong metadata or field type for MAX(... Closed
Relates
relates to MDEV-8863 Wrong result for MAX(COALESCE(time_co... Closed

 Description   

If I start mysql --column-type-info and run this script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATETIME);
 
INSERT INTO t1 VALUES ('2001-01-01 10:20:30'),('2001-01-02 10:20:30');
 
SELECT MAX(a), MAX(COALESCE(a)) FROM t1;

it returns a correct result set:

+---------------------+---------------------+
 
| MAX(a)              | MAX(COALESCE(a))    |
 
+---------------------+---------------------+
 
| 2001-01-02 10:20:30 | 2001-01-02 10:20:30 |
 
+---------------------+---------------------+

but the metadata is wrong:

Field   1:  `MAX(a)`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       DATETIME
 
Collation:  binary (63)
 
Length:     19
 
Max_length: 19
 
Decimals:   0
 
Flags:      BINARY 
 
 
Field   2:  `MAX(COALESCE(a))`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       VAR_STRING
 
Collation:  utf8_general_ci (33)
 
Length:     57
 
Max_length: 19
 
Decimals:   0
 
Flags:

Notice, metadata for MAX(COALESCE(a)) says it's a VAR_STRING column.
The correct result is to return the DATETIME data type, as for the first column.

If I change the column data type from DATETIME to TIME:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a TIME);
 
INSERT INTO t1 VALUES ('10:20:30'),('10:20:30');
 
SELECT MAX(a), MAX(COALESCE(a)) FROM t1;

it also returns VAR_STRING for the second column.
Generated at Thu Feb 08 07:30:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.