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

Explicit and implicit CAST from string to integer work differently

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Data types
    • None

    Description

      Implicit cast:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES ('1.5'),('1e2');
      		 
      SELECT * FROM t1;

      returns:

      +------+
      		 
      | a    |
      		 
      +------+
      		 
      |    2 |
      		 
      |  100 |
      		 
      +------+

      I.e. it understands floating point formats.

      Explicit CAST works differently:

      SELECT CAST('1.5' AS SIGNED), CAST('1e2' AS SIGNED);
      		 
      SHOW WARNINGS;

      returns:

      +-----------------------+-----------------------+
      		 
      | CAST('1.5' AS SIGNED) | CAST('1e2' AS SIGNED) |
      		 
      +-----------------------+-----------------------+
      		 
      |                     1 |                     1 |
      		 
      +-----------------------+-----------------------+
      		 
      1 row in set, 2 warnings (0.00 sec)
      		 
       
      		 
      +---------+------+------------------------------------------+
      		 
      | Level   | Code | Message                                  |
      		 
      +---------+------+------------------------------------------+
      		 
      | Warning | 1292 | Truncated incorrect INTEGER value: '1.5' |
      		 
      | Warning | 1292 | Truncated incorrect INTEGER value: '1e2' |
      		 
      +---------+------+------------------------------------------+

      i.e. it does not understand floating point formats.

      Explicit CAST should be fixed to understand floating point format.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8514 Storing '1e-1000000000000' into an INT column produces a wrong result

          • Major - Major loss of function.
          • Open

          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 Implicit cast:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1.5'),('1e2');
            SELECT * FROM t1;
            {code}
            returns:
            {noformat}
            +------+
            | a |
            +------+
            | 2 |
            | 100 |
            +------+
            {noformat}
            I.e. it understands floating point formats.

            Explicit CAST works differently:
            {code}
            SELECT CAST('1.5' AS SIGNED), CAST('1e2' AS SIGNED);
            SHOW WARNINGS;
            {code}
            returns:
            {noformat}
            +-----------------------+-----------------------+
            | CAST('1.5' AS SIGNED) | CAST('1e2' AS SIGNED) |
            +-----------------------+-----------------------+
            | 1 | 1 |
            +-----------------------+-----------------------+
            1 row in set, 2 warnings (0.00 sec)

            +---------+------+------------------------------------------+
            | Level | Code | Message |
            +---------+------+------------------------------------------+
            | Warning | 1292 | Truncated incorrect INTEGER value: '1.5' |
            | Warning | 1292 | Truncated incorrect INTEGER value: '1e2' |
            +---------+------+------------------------------------------+
            {noformat}
            i.e. it does not understand the floating point formats.

            Explicit CAST should be fixed to understand the floating point format.
            		 Implicit cast:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1.5'),('1e2');
            SELECT * FROM t1;
            {code}
            returns:
            {noformat}
            +------+
            | a |
            +------+
            | 2 |
            | 100 |
            +------+
            {noformat}
            I.e. it understands floating point formats.

            Explicit CAST works differently:
            {code}
            SELECT CAST('1.5' AS SIGNED), CAST('1e2' AS SIGNED);
            SHOW WARNINGS;
            {code}
            returns:
            {noformat}
            +-----------------------+-----------------------+
            | CAST('1.5' AS SIGNED) | CAST('1e2' AS SIGNED) |
            +-----------------------+-----------------------+
            | 1 | 1 |
            +-----------------------+-----------------------+
            1 row in set, 2 warnings (0.00 sec)

            +---------+------+------------------------------------------+
            | Level | Code | Message |
            +---------+------+------------------------------------------+
            | Warning | 1292 | Truncated incorrect INTEGER value: '1.5' |
            | Warning | 1292 | Truncated incorrect INTEGER value: '1e2' |
            +---------+------+------------------------------------------+
            {noformat}
            i.e. it does not understand floating point formats.

            Explicit CAST should be fixed to understand the floating point format.
            bar Alexander Barkov made changes -
            Description Implicit cast:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1.5'),('1e2');
            SELECT * FROM t1;
            {code}
            returns:
            {noformat}
            +------+
            | a |
            +------+
            | 2 |
            | 100 |
            +------+
            {noformat}
            I.e. it understands floating point formats.

            Explicit CAST works differently:
            {code}
            SELECT CAST('1.5' AS SIGNED), CAST('1e2' AS SIGNED);
            SHOW WARNINGS;
            {code}
            returns:
            {noformat}
            +-----------------------+-----------------------+
            | CAST('1.5' AS SIGNED) | CAST('1e2' AS SIGNED) |
            +-----------------------+-----------------------+
            | 1 | 1 |
            +-----------------------+-----------------------+
            1 row in set, 2 warnings (0.00 sec)

            +---------+------+------------------------------------------+
            | Level | Code | Message |
            +---------+------+------------------------------------------+
            | Warning | 1292 | Truncated incorrect INTEGER value: '1.5' |
            | Warning | 1292 | Truncated incorrect INTEGER value: '1e2' |
            +---------+------+------------------------------------------+
            {noformat}
            i.e. it does not understand floating point formats.

            Explicit CAST should be fixed to understand the floating point format.
            		 Implicit cast:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES ('1.5'),('1e2');
            SELECT * FROM t1;
            {code}
            returns:
            {noformat}
            +------+
            | a |
            +------+
            | 2 |
            | 100 |
            +------+
            {noformat}
            I.e. it understands floating point formats.

            Explicit CAST works differently:
            {code}
            SELECT CAST('1.5' AS SIGNED), CAST('1e2' AS SIGNED);
            SHOW WARNINGS;
            {code}
            returns:
            {noformat}
            +-----------------------+-----------------------+
            | CAST('1.5' AS SIGNED) | CAST('1e2' AS SIGNED) |
            +-----------------------+-----------------------+
            | 1 | 1 |
            +-----------------------+-----------------------+
            1 row in set, 2 warnings (0.00 sec)

            +---------+------+------------------------------------------+
            | Level | Code | Message |
            +---------+------+------------------------------------------+
            | Warning | 1292 | Truncated incorrect INTEGER value: '1.5' |
            | Warning | 1292 | Truncated incorrect INTEGER value: '1e2' |
            +---------+------+------------------------------------------+
            {noformat}
            i.e. it does not understand floating point formats.

            Explicit CAST should be fixed to understand floating point format.
            bar Alexander Barkov made changes -
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 5.5 [ 15800 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.0 [ 16000 ]
            bar Alexander Barkov made changes -
            Component/s Data types [ 13906 ]
            Component/s OTHER [ 10125 ]
            julien.fritsch Julien Fritsch made changes -
            Epic Link MDEV-21071 [ 80504 ]
            julien.fritsch Julien Fritsch made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 70724 ] MariaDB v4 [ 139857 ]

            People

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

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.