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

Wrong field type for MAX(COALESCE(datetime_column))

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • N/A
    • Temporal Types

    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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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