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

Wrong metadata or type for @c:=string_or_blob_field

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.1.8
    • OTHER
    • None
    • 10.1.8-4

    Description

      This script:

      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
        @c1:=c1 AS c1,
        @c2:=c2 AS c2,
        @c3:=c3 AS c3,
        @c4:=c4 AS c4,
        @c5:=c5 AS c5,
        @c6:=c6 AS c6,
        @c7:=c7 AS c7 FROM t1;
      SHOW CREATE TABLE t2;

      returns

      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `c1` longblob,
        `c2` longblob,
        `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 run the same SELECT query that was used in CREATE TABLE t2:

      SELECT
        @c1:=c1 AS c1,
        @c2:=c2 AS c2,
        @c3:=c3 AS c3,
        @c4:=c4 AS c4,
        @c5:=c5 AS c5,
        @c6:=c6 AS c6,
        @c7:=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`
      Catalog:    `def`
      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 column type in metadata does not match the column type in t2.

      Attachments

        Activity

          There are no comments yet on this issue.

          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.