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

Unexpected modification of value and warning about out of range value upon ALTER

Details

    Description

      Consider the following example:

      CREATE TABLE t1 (
      f FLOAT,
      d10_10 DOUBLE PRECISION (10,10),
      d53_10 DOUBLE(53,10) 
      ) ENGINE=MyISAM;
       
      INSERT INTO t1 (f,d10_10,d53_10) VALUES (
        -9999999999999999999999999999999999999999999.9999999999,
        -9999999999999999999999999999999999999999999.9999999999,
        -9999999999999999999999999999999999999999999.9999999999
      );
      SELECT * FROM t1 \G
      INSERT INTO t1 (f,d10_10,d53_10) SELECT d53_10, d53_10, d53_10 FROM t1;
      SELECT * FROM t1 \G
      ALTER TABLE t1 ADD COLUMN i INT;
      SELECT * FROM t1 \G
      

      The first INSERT inserts out of range values into `f` and `d10_10` columns. The warnings are issued, and values are adjusted (whether they are adjusted correctly is another question, but it's not the point of this report):

      Warnings:
      Warning 1264    Out of range value for column 'f' at row 1
      Warning 1264    Out of range value for column 'd10_10' at row 1
      SELECT * FROM t1;
      f       -3.40282e38
      d10_10  -0.9999999999
      d53_10  -10000000000000000000000000000000000000000000.0000000000
      

      The second INSERT also inserts out of range values into these columns, but somehow the server only complains about the float column, but not about `d10_10` (and it was so in previous versions):

      INSERT INTO t1 (f,d10_10,d53_10) SELECT d53_10, d53_10, d53_10 FROM t1;
      Warnings:
      Warning 1264    Out of range value for column 'f' at row 1
      SELECT * FROM t1;
      f       -3.40282e38
      d10_10  -0.9999999999
      d53_10  -10000000000000000000000000000000000000000000.0000000000
      f       -3.40282e38
      d10_10  -10000000000000000000000000000000000000000000.0000000000
      d53_10  -10000000000000000000000000000000000000000000.0000000000
      

      But when we are adding a new unrelated column to the table, the server suddenly realizes that the value was wrong, issues a warning and amends the value:

      10.2

      ALTER TABLE t1 ADD COLUMN i INT;
      Warnings:
      Warning 1264    Out of range value for column 'd10_10' at row 2
      SELECT * FROM t1;
      f       -3.40282e38
      d10_10  -0.9999999999
      d53_10  -10000000000000000000000000000000000000000000.0000000000
      i       NULL
      f       -3.40282e38
      d10_10  -0.9999999999
      d53_10  -10000000000000000000000000000000000000000000.0000000000
      i       NULL
      

      It is not so in 10.1 and MySQL 5.7, both versions stick to the inserted value:

      MySQL 5.7

      ALTER TABLE t1 ADD COLUMN i INT;
      SELECT * FROM t1;
      f       -3.40282e38
      d10_10  -0.9999999999
      d53_10  -10000000000000000000000000000000000000000000.0000000000
      i       NULL
      f       -3.40282e38
      d10_10  -10000000000000000000000000000000000000000000.0000000000
      d53_10  -10000000000000000000000000000000000000000000.0000000000
      i       NULL
      

      It does not seem right to change the value upon ALTER – if there is a problem with the value, it should be noticed and dealt with upon INSERT.

      Attachments

        Issue Links

          Activity

            bar Alexander Barkov added a comment - - edited

            A smaller script demonstrating the same problem:

            CREATE OR REPLACE TABLE t1 (d10_10 DOUBLE (10,10));
            CREATE OR REPLACE TABLE t2 (d53_10 DOUBLE (53,10));
            INSERT INTO t2 VALUES (-9999999999999999999999999999999999999999999.9999999999);
            INSERT INTO t1 (d10_10) SELECT d53_10 FROM t2;
            SELECT * FROM t1;
            

            +----------------------------------------------------------+
            | d10_10                                                   |
            +----------------------------------------------------------+
            | -10000000000000000000000000000000000000000000.0000000000 |
            +----------------------------------------------------------+
            

            The expected result is -0.9999999999.

            This is repeatable in 10.0, 10.1, 10.2.

            bar Alexander Barkov added a comment - - edited A smaller script demonstrating the same problem: CREATE OR REPLACE TABLE t1 (d10_10 DOUBLE (10,10)); CREATE OR REPLACE TABLE t2 (d53_10 DOUBLE (53,10)); INSERT INTO t2 VALUES (-9999999999999999999999999999999999999999999.9999999999); INSERT INTO t1 (d10_10) SELECT d53_10 FROM t2; SELECT * FROM t1; +----------------------------------------------------------+ | d10_10 | +----------------------------------------------------------+ | -10000000000000000000000000000000000000000000.0000000000 | +----------------------------------------------------------+ The expected result is -0.9999999999. This is repeatable in 10.0, 10.1, 10.2.

            The same problem is repeatable with FLOAT:

            CREATE OR REPLACE TABLE t1 (d2_2 FLOAT (2,2));
            CREATE OR REPLACE TABLE t2 (d4_2 FLOAT (4,2));
            INSERT INTO t2 VALUES (99.99);
            INSERT INTO t1 (d2_2) SELECT d4_2 FROM t2;
            SELECT * FROM t1;
            

            +-------+
            | d2_2  |
            +-------+
            | 99.99 |
            +-------+
            

            The expected result is 0.99.

            bar Alexander Barkov added a comment - The same problem is repeatable with FLOAT: CREATE OR REPLACE TABLE t1 (d2_2 FLOAT (2,2)); CREATE OR REPLACE TABLE t2 (d4_2 FLOAT (4,2)); INSERT INTO t2 VALUES (99.99); INSERT INTO t1 (d2_2) SELECT d4_2 FROM t2; SELECT * FROM t1; +-------+ | d2_2 | +-------+ | 99.99 | +-------+ The expected result is 0.99.

            People

              bar Alexander Barkov
              elenst Elena Stepanova
              Votes:
              0 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.