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

MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16

    XMLWordPrintable

    Details

      Description

      MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16.

      Consider the example.

      MariaDB [test]> CREATE TABLE `t44` (`d1` decimal(38,0) DEFAULT NULL);
      ERROR 1050 (42S01): Table 't44' already exists
      MariaDB [test]> drop table t44;
      Query OK, 0 rows affected (0.008 sec)
       
      MariaDB [test]> CREATE TABLE `t44` (`d1` decimal(38,0) DEFAULT NULL);
      Query OK, 0 rows affected (0.014 sec)
       
      MariaDB [test]> create table t45 as select floor(d1) from t44;
      Query OK, 0 rows affected (0.017 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table t45;
      +-------+------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                         |
      +-------+------------------------------------------------------------------------------------------------------+
      | t45   | CREATE TABLE `t45` (
        `floor(d1)` decimal(16,0) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> insert into t44 values (12345678901234567890123456789012345678);
      Query OK, 1 row affected (0.006 sec)
       
      MariaDB [test]> select floor(d1) from t44;
      +----------------------------------------+
      | floor(d1)                              |
      +----------------------------------------+
      | 12345678901234567890123456789012345678 |
      +----------------------------------------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> drop table t45;
      Query OK, 0 rows affected (0.009 sec)
       
      MariaDB [test]> create table t45 as select floor(d1) from t44;
      ERROR 1264 (22003): Out of range value for column 'floor(d1)' at row 1
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              drrtuy Roman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: