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

Unexpected error on CREATE..SELECT HEX(num)

Details

    Description

      SET sql_mode=STRICT_ALL_TABLES;
      		 
      DROP TABLE IF EXISTS t1, t2;
      		 
      CREATE TABLE t1 (a TINYINT);
      		 
      INSERT INTO t1 VALUES (-1);
      		 
      CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;


      ERROR 1406 (22001): Data too long for column 'h' at row 1

      Looks wrong.

      This script:

      CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1 LIMIT 0;
      		 
      SHOW CREATE TABLE t2;

      demonstrates that the data type for the column h is too short:

      +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                               |
      		 
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t2    | CREATE TABLE `t2` (
      		 
        `a` tinyint(4) DEFAULT NULL,
      		 
        `h` varchar(8) CHARACTER SET utf8 DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------+

      It should be varchar(16) to fit hex representation of positive completents for negative numbers:

      SELECT a, HEX(a) FROM t1;


      +------+------------------+
      		 
      | a    | HEX(a)           |
      		 
      +------+------------------+
      		 
      |   -1 | FFFFFFFFFFFFFFFF |
      		 
      +------+------------------+

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MCOL-3762 HEX(<decimal>) gets truncated

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MCOL-3475 HEX() returns incorrect results

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Rank Ranked higher
            bar Alexander Barkov made changes -
            Fix Version/s 10.4 [ 22408 ]
            bar Alexander Barkov made changes -
            Description {code:sql}
            SET sql_mode=STRICT_ALL_TABLES;
            DROP TABLE IF EXISTS t1, t2;
            CREATE TABLE t1 (a TINYINT);
            INSERT INTO t1 VALUES (-1);
            CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
            {code}
            {noformat}
            ERROR 1406 (22001): Data too long for column 'h' at row 1
            {noformat}
            Looks wrong.

            This script:
            {code:sql}
            CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1 LIMIT 0;
            SHOW CREATE TABLE t2;
            {code}
            demonstrates that the data type for the column h is too short:
            {noformat}
            +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `a` tinyint(4) DEFAULT NULL,
              `h` varchar(8) CHARACTER SET utf8 DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            It should be varchar(16) to fit negative numbers:
            {code:sql}
            SELECT a, HEX(a) FROM t1;
            {code}
            {noformat}
            +------+------------------+
            | a | HEX(a) |
            +------+------------------+
            | -1 | FFFFFFFFFFFFFFFF |
            +------+------------------+
            {noformat}


            		{code:sql}
            SET sql_mode=STRICT_ALL_TABLES;
            DROP TABLE IF EXISTS t1, t2;
            CREATE TABLE t1 (a TINYINT);
            INSERT INTO t1 VALUES (-1);
            CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
            {code}
            {noformat}
            ERROR 1406 (22001): Data too long for column 'h' at row 1
            {noformat}
            Looks wrong.

            This script:
            {code:sql}
            CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1 LIMIT 0;
            SHOW CREATE TABLE t2;
            {code}
            demonstrates that the data type for the column h is too short:
            {noformat}
            +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `a` tinyint(4) DEFAULT NULL,
              `h` varchar(8) CHARACTER SET utf8 DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}

            It should be varchar(16) to fit hex representation of positive completents for negative numbers:
            {code:sql}
            SELECT a, HEX(a) FROM t1;
            {code}
            {noformat}
            +------+------------------+
            | a | HEX(a) |
            +------+------------------+
            | -1 | FFFFFFFFFFFFFFFF |
            +------+------------------+
            {noformat}


            LinuxJedi Andrew Hutchings (Inactive) made changes -
            gdorman Gregory Dorman (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 99559 ] MariaDB v4 [ 141530 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.5.26 [ 29832 ]
            Fix Version/s 10.6.19 [ 29833 ]
            Fix Version/s 10.11.9 [ 29834 ]
            Fix Version/s 11.1.6 [ 29835 ]
            Fix Version/s 11.2.5 [ 29836 ]
            Fix Version/s 11.4.3 [ 29837 ]
            Fix Version/s 11.5.2 [ 29838 ]
            Fix Version/s 11.6.0 [ 29839 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.