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

INSERT and UPDATE with out-of-range values caused saturation error

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 1.4.2
    • Icebox
    • DMLProc
    • None

    Description

      Build tested: 1.4.2-1 (first combined build from Azura)
      OS: centos 7
      Stack: single server

      DML (insert and update) with out-of-range values, as well as empty and null marker values (two reserved values in ColumnStore), caused errors:

      MariaDB [mytest]> create table t1 (snBIGINT bigint) engine=columnstore;
      Query OK, 0 rows affected (0.203 sec)

      MariaDB [mytest]> insert into t1 values (10);
      Query OK, 1 row affected (0.172 sec)

      MariaDB [mytest]> update t1 set snBIGINT = -9223372036854775807;
      ERROR 1264 (22003): CAL0002: IDB-2025: Data truncated for column 'snBIGINT'

      MariaDB [mytest]> insert into t1 values (-92233720368547758070);
      ERROR 1264 (22003): Out of range value for column 'snBIGINT' at row 1

      MariaDB [mytest]> select * from t1;
      ----------

      snBIGINT

      ----------

      10

      ----------
      1 row in set (0.018 sec)

      In 1.4.1-1, the same statements would cause value saturation with a warnings:

      MariaDB [mytest]> create table t1 (snBIGINT bigint) engine=columnstore;
      Query OK, 0 rows affected (0.319 sec)

      MariaDB [mytest]> insert into t1 values (10);
      Query OK, 1 row affected (0.188 sec)

      MariaDB [mytest]> update t1 set snBIGINT = -9223372036854775807;
      Query OK, 0 rows affected, 1 warning (0.360 sec)
      Rows matched: 0 Changed: 0 Warnings: 1

      MariaDB [mytest]> show warnings;
      ---------------------------------------------------------------------

      Level Code Message

      ---------------------------------------------------------------------

      Warning 1264 CAL0002: IDB-2025: Data truncated for column 'snBIGINT'

      ---------------------------------------------------------------------
      1 row in set (0.000 sec)

      MariaDB [mytest]> select * from t1;
      ----------------------

      snBIGINT

      ----------------------

      -9223372036854775806

      ----------------------
      1 row in set (0.017 sec)

      The same error occurred for all supported data types:

      testcase: autopilot.systemTest.datatypes

      [root@localhost datatypes]# mysql mytest -f < update.emptyMarkers.sql
      ERROR 1264 (22003) at line 2: CAL0002: IDB-2025: Data truncated for column 'snBIGINT'
      ERROR 1264 (22003) at line 3: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL1'
      ERROR 1264 (22003) at line 4: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL4'
      ERROR 1264 (22003) at line 5: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL4_2'
      ERROR 1264 (22003) at line 6: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL5'
      ERROR 1264 (22003) at line 7: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL9'
      ERROR 1264 (22003) at line 8: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL9_2'
      ERROR 1264 (22003) at line 9: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL10'
      ERROR 1264 (22003) at line 10: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL18'
      ERROR 1264 (22003) at line 11: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL18_2'
      ERROR 1264 (22003) at line 12: CAL0002: IDB-2025: Data truncated for column 'snINTEGER'
      ERROR 1264 (22003) at line 13: CAL0002: IDB-2025: Data truncated for column 'snSMALLINT'
      ERROR 1264 (22003) at line 14: CAL0002: IDB-2025: Data truncated for column 'snTINYINT'
      ERROR 1264 (22003) at line 15: CAL0002: IDB-2025: Data truncated for column 'dDATE'
      ERROR 1264 (22003) at line 16: CAL0002: IDB-2025: Data truncated for column 'dDATETIME'
      ERROR 1264 (22003) at line 17: CAL0002: IDB-2025: Data truncated for column 'dTIME'
      ERROR 1264 (22003) at line 37: CAL0002: IDB-2025: Data truncated for column 'unBIGINT'
      ERROR 1264 (22003) at line 38: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL1'
      ERROR 1264 (22003) at line 39: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL4'
      ERROR 1264 (22003) at line 40: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL4_2'
      ERROR 1264 (22003) at line 41: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL5'
      ERROR 1264 (22003) at line 42: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL9'
      ERROR 1264 (22003) at line 43: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL9_2'
      ERROR 1264 (22003) at line 44: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL10'
      ERROR 1264 (22003) at line 45: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL18'
      ERROR 1264 (22003) at line 46: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL18_2'
      ERROR 1264 (22003) at line 47: CAL0002: IDB-2025: Data truncated for column 'unINTEGER'
      ERROR 1264 (22003) at line 48: CAL0002: IDB-2025: Data truncated for column 'unSMALLINT'
      ERROR 1264 (22003) at line 49: CAL0002: IDB-2025: Data truncated for column 'unTINYINT'

      The same test case worked in 1.4.1-1

      Attachments

        Activity

          People

            Unassigned Unassigned
            dleeyh Daniel Lee (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.