Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3892

Updating a decimal column with scale smaller than the source column returns an error

    XMLWordPrintable

Details

    Description

      Columnstore returns an error when a target decimal column has a scale smaller than the source column for the update. Innodb however updates the value and returns a warning message. sql_mode for the query is strict.

      Here are steps to reproduce:

      MariaDB [test]> create table c1 (a decimal(2,2), b decimal(4,4))engine=columnstore;
      Query OK, 0 rows affected (0.268 sec)
       
      MariaDB [test]> insert into c1 values (0.09, 0.0009);
      Query OK, 1 row affected (5.187 sec)
       
      MariaDB [test]> select * from c1;
      +------+--------+
      | a    | b      |
      +------+--------+
      | 0.09 | 0.0009 |
      +------+--------+
      1 row in set (0.099 sec)
       
      MariaDB [test]> UPDATE c1 SET a=b;
      ERROR 1264 (22003): CAL0002: IDB-2025: Data truncated for column 'a' 
      MariaDB [test]> select * from c1;
      +------+--------+
      | a    | b      |
      +------+--------+
      | 0.09 | 0.0009 |
      +------+--------+
      1 row in set (0.022 sec)
       
      MariaDB [test]> create table i1 as select * from c1;
      Query OK, 1 row affected (0.032 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> UPDATE i1 SET a=b;
      Query OK, 1 row affected, 1 warning (0.003 sec)
      Rows matched: 1  Changed: 1  Warnings: 1
       
      MariaDB [test]> show warnings;
      +-------+------+----------------------------------------+
      | Level | Code | Message                                |
      +-------+------+----------------------------------------+
      | Note  | 1265 | Data truncated for column 'a' at row 1 |
      +-------+------+----------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from i1;
      +------+--------+
      | a    | b      |
      +------+--------+
      | 0.00 | 0.0009 |
      +------+--------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> show variables like '%sql_mode%';
      +---------------+-------------------------------------------------------------------------------------------+
      | Variable_name | Value                                                                                     |
      +---------------+-------------------------------------------------------------------------------------------+
      | sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +---------------+-------------------------------------------------------------------------------------------+
      1 row in set (0.004 sec)
      
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            tntnatbry Gagan Goel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.