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

Wrong data type for CAST(@a AS BINARY) for a numeric variable

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.5
    • Fix Version/s: 10.5.0
    • Component/s: Data types
    • Labels:
      None

      Description

      I run this script:

      SET NAMES latin1;
      SET @a=2;
      CREATE OR REPLACE TABLE t1 AS SELECT CAST(1 AS BINARY), CAST(@a AS BINARY), CAST(@b:=3 AS BINARY);
      SHOW CREATE TABLE t1;
      

      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                       |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `CAST(1 AS BINARY)` varbinary(1) DEFAULT NULL,
        `CAST(@a AS BINARY)` varbinary(20) DEFAULT NULL,
        `CAST(@b:=3 AS BINARY)` varbinary(1) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Looks fine so far.

      Now I change the character set to utf8:

      SET NAMES utf8;
      SET @a=2;
      CREATE OR REPLACE TABLE t1 AS SELECT CAST(1 AS BINARY), CAST(@a AS BINARY), CAST(@b:=3 AS BINARY);
      SHOW CREATE TABLE t1;
      

      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                       |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `CAST(1 AS BINARY)` varbinary(1) DEFAULT NULL,
        `CAST(@a AS BINARY)` varbinary(60) DEFAULT NULL,
        `CAST(@b:=3 AS BINARY)` varbinary(3) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Notice, the data type has changed for the second and the third columns.
      Looks wrong. The behaviour of numeric user variables should not depend on the character set.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: