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

Wrong field type for MAX(COALESCE(datetime_column))

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: None
        • Component/s: Temporal Types
        • Labels:

          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8863 Wrong result for MAX(COALESCE(time_column))

                • Major - Major loss of function.
                • Closed

                  Activity

                  There are no comments yet on this issue.

                    People

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

                      Dates

                      • Created:
                        Updated: