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

BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL

Details

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

    Description

      • BINARY 

        SET NAMES utf8;
        		 
        DROP TABLE IF EXISTS t1;
        		 
        CREATE TABLE t1 (a BINARY(30));
        		 
        INSERT INTO t1 VALUES ('1äÖüß@µ*$');
        		 
        SELECT CAST(a AS DECIMAL) FROM t1;
        		 
        SHOW WARNINGS;
        		 

        +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
        		 
        | Level   | Code | Message                                                                                                                                           |
        		 
        +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
        		 
        | Warning | 1292 | Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' |
        		 
        +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+

      • VARBINARY 

        SET NAMES utf8;
        		 
        DROP TABLE IF EXISTS t1;
        		 
        CREATE TABLE t1 (a VARBINARY(30));
        		 
        INSERT INTO t1 VALUES ('1äÖüß@µ*$');
        		 
        SELECT CAST(a AS DECIMAL) FROM t1;
        		 
        SHOW WARNINGS;
        		 

        +---------+------+-----------------------------------------------------+
        		 
        | Level   | Code | Message                                             |
        		 
        +---------+------+-----------------------------------------------------+
        		 
        | Warning | 1292 | Truncated incorrect DECIMAL value: '1äÖüß@µ*$'      |
        		 
        +---------+------+-----------------------------------------------------+

      • BLOB 

        SET NAMES utf8;
        		 
        DROP TABLE IF EXISTS t1;
        		 
        CREATE TABLE t1 (a BLOB);
        		 
        INSERT INTO t1 VALUES ('1äÖüß@µ*$');
        		 
        SELECT CAST(a AS DECIMAL) FROM t1;
        		 
        SHOW WARNINGS;
        		 

        Empty set (0.00 sec)

      BINARY looks the most correct.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description - BINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}

          {noformat}
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Level | Code | Message |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          {noformat}

          - VARBINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a VARBINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          +---------+------+-----------------------------------------------------+
          | Level | Code | Message |
          +---------+------+-----------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1äÖüß@µ*$' |
          +---------+------+-----------------------------------------------------+
          {noformat}

          - BLOB
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BLOB);
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          Empty set (0.00 sec)
          {noformat}
          		 - BINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Level | Code | Message |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          {noformat}

          - VARBINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a VARBINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          +---------+------+-----------------------------------------------------+
          | Level | Code | Message |
          +---------+------+-----------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1äÖüß@µ*$' |
          +---------+------+-----------------------------------------------------+
          {noformat}

          - BLOB
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BLOB);
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          Empty set (0.00 sec)
          {noformat}
          bar Alexander Barkov made changes -
          Description - BINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Level | Code | Message |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          {noformat}

          - VARBINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a VARBINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          +---------+------+-----------------------------------------------------+
          | Level | Code | Message |
          +---------+------+-----------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1äÖüß@µ*$' |
          +---------+------+-----------------------------------------------------+
          {noformat}

          - BLOB
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BLOB);
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          Empty set (0.00 sec)
          {noformat}
          		 - BINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Level | Code | Message |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' |
          +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
          {noformat}

          - VARBINARY
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a VARBINARY(30));
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          +---------+------+-----------------------------------------------------+
          | Level | Code | Message |
          +---------+------+-----------------------------------------------------+
          | Warning | 1292 | Truncated incorrect DECIMAL value: '1äÖüß@µ*$' |
          +---------+------+-----------------------------------------------------+
          {noformat}

          - BLOB
          {code}
          SET NAMES utf8;
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a BLOB);
          INSERT INTO t1 VALUES ('1äÖüß@µ*$');
          SELECT CAST(a AS DECIMAL) FROM t1;
          SHOW WARNINGS;
          {code}
          {noformat}
          Empty set (0.00 sec)
          {noformat}

          BINARY looks the most correct.
          bar Alexander Barkov made changes -
          Fix Version/s 10.0.21 [ 19406 ]
          Fix Version/s 10.0 [ 16000 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 70644 ] MariaDB v4 [ 149374 ]

          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.