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

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: