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

Wrong metadata or field type for MAX(COALESCE(string_field))

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.0
    • Fix Version/s: 10.1.8
    • Component/s: OTHER
    • Labels:
      None
    • Sprint:
      10.1.8-4

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
        c1 LONGBLOB,
        c2 MEDIUMBLOB,
        c3 BLOB,
        c4 TINYBLOB,
        c5 VARCHAR(64),
        c6 VARCHAR(4000),
        c7 CHAR(64));
      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 AS SELECT
        MAX(COALESCE(c1)) AS c1,
        MAX(COALESCE(c2)) AS c2,
        MAX(COALESCE(c3)) AS c3,
        MAX(COALESCE(c4)) AS c4,
        MAX(COALESCE(c5)) AS c5,
        MAX(COALESCE(c6)) AS c6,
        MAX(COALESCE(c7)) AS c7 FROM t1;
      SHOW CREATE TABLE t2;

      returns

      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                  |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `c1` longblob,
        `c2` mediumblob,
        `c3` blob,
        `c4` varbinary(255) DEFAULT NULL,
        `c5` varchar(64) DEFAULT NULL,
        `c6` text,
        `c7` varchar(64) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Now if I run "mysql --column-type-info test" and execute this query:

      SELECT
        MAX(COALESCE(c1)) AS c1,
        MAX(COALESCE(c2)) AS c2,
        MAX(COALESCE(c3)) AS c3,
        MAX(COALESCE(c4)) AS c4,
        MAX(COALESCE(c5)) AS c5,
        MAX(COALESCE(c6)) AS c6,
        MAX(COALESCE(c7)) AS c7 FROM t1;

      it returns the following metadata:

      Field   1:  `c1`
      Type:       LONG_BLOB
      Collation:  binary (63)
      Length:     4294967295
       
      Field   2:  `c2`
      Type:       MEDIUM_BLOB
      Collation:  binary (63)
      Length:     16777215
       
      Field   3:  `c3`
      Type:       VAR_STRING
      Collation:  binary (63)
      Length:     65535
       
      Field   4:  `c4`
      Type:       VAR_STRING
      Collation:  binary (63)
      Length:     255
       
      Field   5:  `c5`
      Type:       VAR_STRING
      Collation:  utf8_general_ci (33)
      Length:     192
       
      Field   6:  `c6`
      Type:       VAR_STRING
      Collation:  utf8_general_ci (33)
      Length:     12000
       
      Field   7:  `c7`
      Type:       VAR_STRING
      Collation:  utf8_general_ci (33)
      Length:     192

      Notice, for some columns the type of the column created in CREATE TABLE...SELECT does not match the type of the column returned in the metadata for the same SELECT query.

        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: