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

Wrong field type for MAX(COALESCE(datetime_column))

    XMLWordPrintable

    Details

      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.

        Attachments

          Issue Links

            Activity

              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:

                  Git Integration