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

FORMAT(d1,dec) where dec=0/38 and d1 is DECIMAL(38,38) gives incorrect results

    XMLWordPrintable

    Details

      Description

      Here are steps to reproduce:

      MariaDB [test]> drop table if exists i1, i2, i3;
      Query OK, 0 rows affected (0.035 sec)
       
      MariaDB [test]> create table i1 (a decimal(38,38));
      Query OK, 0 rows affected (0.014 sec)
       
      MariaDB [test]> insert into i1 values (-0.9999999999999999999999999999999999999), (0.9999999999999999999999999999999999999);
      Query OK, 2 rows affected (0.004 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select format(a,0), format(a,38) from i1;
      +-------------+-----------------------------------+
      | format(a,0) | format(a,38)                      |
      +-------------+-----------------------------------+
      | -1          | -1.000000000000000000000000000000 |
      | 1           | 1.000000000000000000000000000000  |
      +-------------+-----------------------------------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> create table i2 as select format(a,0) from i1;
      ERROR 1406 (22001): Data too long for column 'format(a,0)' at row 1
      MariaDB [test]> create table i3 as select format(a,38) from i1;
      ERROR 1406 (22001): Data too long for column 'format(a,38)' at row 1
      MariaDB [test]> delete from i1;
      Query OK, 2 rows affected (0.003 sec)
       
      MariaDB [test]> create table i2 as select format(a,0) from i1;
      Query OK, 0 rows affected (0.016 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table i2;
      +-------+-----------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                          |
      +-------+-----------------------------------------------------------------------------------------------------------------------+
      | i2    | CREATE TABLE `i2` (
        `format(a,0)` varchar(1) CHARACTER SET utf8 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-----------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> create table i3 as select format(a,38) from i1;
      Query OK, 0 rows affected (0.017 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table i3;
      +-------+-------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                            |
      +-------+-------------------------------------------------------------------------------------------------------------------------+
      | i3    | CREATE TABLE `i3` (
        `format(a,38)` varchar(32) CHARACTER SET utf8 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
      

      As can be seen, when i1 contains some records, "create table as select" does not work. When i1 is empty, "create table as select" works but the varchar field in i2 (should be varchar(2)) and i3 (should be varchar(41)) is incorrect.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              tntnatbry Gagan Goel
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: