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

Wrong metadata or type for @c:=string_or_blob_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

      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

            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: