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

INSERTIONS are lost, no ERROR reported for out-of-range values in STRICT mode

    XMLWordPrintable

Details

    • 2016-21, 2016-22, 2016-23

    Description

      insertion of -128 works for MyISAM

      mysql> create table t2(c1 tinyint) ENGINE=MYISAM;
      Query OK, 0 rows affected (0.01 sec)

      mysql> insert into t2 values(-128);
      Query OK, 1 row affected (0.01 sec)

      mysql> select * from t2;
      ------

      c1

      ------

      -128

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

      mysql> drop table t2;
      Query OK, 0 rows affected (0.00 sec)

      value is "conformed" to smallest possible value on ColumnStore:

      mysql> create table t2(c1 tinyint) engine=columnstore;
      Query OK, 0 rows affected (0.39 sec)

      mysql> insert into t2 values(-128);
      Query OK, 1 row affected, 1 warning (0.17 sec)

      mysql> show warnings;
      -----------------------------------------------------------------

      Level Code Message

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

      Warning 9999 CAL0001: IDB-2025: Data truncated for column 'c1'

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

      Note above error does not have properly error code or error message, it should be

      error=1264, error message=Out of range value for column 'c1' at row 1

      mysql> select * from t2;
      ------

      c1

      ------

      -126

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

      STRICT_MODE should result in ERROR and no row insertion

      mysql> set sql_mode=STRICT_ALL_TABLES;
      Query OK, 0 rows affected (0.00 sec)

      Columnstore says one row was inserted, but raises warning:

      mysql> insert into t2 values(-128);
      Query OK, 1 row affected, 1 warning (0.06 sec)
      mysql> show warnings;
      ---------------------------------------------------------------

      Level Code Message

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

      Error 9999 CAL0001: IDB-2025: Data truncated for column 'c1'

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

      Again, note above error does not have properly error code or error message, it should be

      error=1264, error message=Out of range value for column 'c1' at row 1

      INSERTION FAILED BUT QUERY RETURNED 1 ROW INSERTED!
      mysql> select * from t2;
      ------

      c1

      ------

      -126

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

      Attachments

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            jswanhart Justin Swanhart (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.