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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1
    • 10.1.8
    • OTHER
    • None
    • 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

              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.