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

Inconsistent value saturation behavior in DML commands and cpimport for numeric data types

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.3
    • Fix Version/s: 1.0.3
    • Component/s: cpimport, DMLProc
    • Labels:
      None

      Description

      Build tested:

      mscadmin> getsoft
      getsoftwareinfo Thu Sep 15 12:06:24 2016

      Name : mariadb-columnstore-platform
      Version : 1.0.3
      Release : 1
      Architecture: x86_64
      Install Date: Thu 15 Sep 2016 11:39:01 AM CDT
      Group : Applications
      Size : 25431329
      License : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
      Signature : (none)
      Source RPM : mariadb-columnstore-1.0.3-1.src.rpm
      Build Date : Thu 15 Sep 2016 09:56:11 AM CDT

      For numeric columns, INSERT, LDI saturates empty ("") string values to 0, as MariaDB would do. But UPDATE and cpimport setup values to NULL instead. They should also saturate values to 0s.

      MariaDB [mytest]> create table t2 (c1 tinyint, c2 smallint, c3 int, c4 bigint) engine=columnstore;
      Query OK, 0 rows affected (0.54 sec)

      MariaDB [mytest]> insert into t2 values ('','','','');
      Query OK, 1 row affected, 4 warnings (0.48 sec)

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

      Level Code Message

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

      Warning 1366 Incorrect integer value: '' for column 'c1' at row 1
      Warning 1366 Incorrect integer value: '' for column 'c2' at row 1
      Warning 1366 Incorrect integer value: '' for column 'c3' at row 1
      Warning 1366 Incorrect integer value: '' for column 'c4' at row 1

      -----------------------------------------------------------------
      4 rows in set (0.00 sec)

      MariaDB [mytest]> select * from t2;
      ------------------+

      c1 c2 c3 c4

      ------------------+

      0 0 0 0

      ------------------+
      1 row in set (0.07 sec)

      MariaDB [mytest]> update t2 set c1=1, c2=2, c3=3, c4=4;
      Query OK, 1 row affected (0.17 sec)
      Rows matched: 0 Changed: 0 Warnings: 0

      MariaDB [mytest]> select * from t2;
      ------------------+

      c1 c2 c3 c4

      ------------------+

      1 2 3 4

      ------------------+
      1 row in set (0.02 sec)

      MariaDB [mytest]> update t2 set c1='', c2='', c3='', c4='';
      Query OK, 1 row affected (0.16 sec)
      Rows matched: 0 Changed: 0 Warnings: 0

      MariaDB [mytest]> select * from t2;
      ------------------+

      c1 c2 c3 c4

      ------------------+

      NULL NULL NULL NULL

      ------------------+
      1 row in set (0.04 sec)

      I have a /tmp/t2.txt file that contains one single line as the following:

      The | character is the delimiter, with 4 empty values.

      MariaDB [mytest]> load data infile '/tmp/t2.txt' into table t2 fields terminated by "|";
      Query OK, 1 row affected, 4 warnings (1.91 sec)
      Records: 1 Deleted: 0 Skipped: 0 Warnings: 4

      MariaDB [mytest]> select * from t2;
      ------------------+

      c1 c2 c3 c4

      ------------------+

      1 2 3 4
      0 0 0 0

      ------------------+
      2 rows in set (0.06 sec)

      MariaDB [mytest]> set infinidb_use_import_for_batchinsert=0;
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [mytest]> load data infile '/tmp/t2.txt' into table t2 fields terminated by "|";
      Query OK, 1 row affected, 4 warnings (0.21 sec)
      Records: 1 Deleted: 0 Skipped: 0 Warnings: 4

      MariaDB [mytest]> select * from t2;
      ------------------+

      c1 c2 c3 c4

      ------------------+

      1 2 3 4
      0 0 0 0
      0 0 0 0

      ------------------+
      3 rows in set (0.02 sec)

      MariaDB [mytest]> quit
      Bye
      [root@mcsce7 datatypes]# /usr/local/mariadb/columnstore/bin/cpimport mytest t2 /tmp/t2.txt
      No support for any checked transport domain
      snmpd: create_trap_session:
      Locale is : C

      Using table OID 62940 as the default JOB ID
      Input file(s) will be read from : /root/tests/systemTest/datatypes
      Job description file : /usr/local/mariadb/columnstore/data/bulk/tmpjob/62940_D20160915_T134325_S100014_Job_62940.xml
      Log file for this job: /usr/local/mariadb/columnstore/data/bulk/log/Job_62940.log
      2016-09-15 13:43:25 (26746) INFO : successfully loaded job file /usr/local/mariadb/columnstore/data/bulk/tmpjob/62940_D20160915_T134325_S100014_Job_62940.xml
      2016-09-15 13:43:25 (26746) INFO : Job file loaded, run time for this step : 0.168156 seconds
      2016-09-15 13:43:25 (26746) INFO : PreProcessing check starts
      2016-09-15 13:43:25 (26746) INFO : input data file /tmp/t2.txt
      2016-09-15 13:43:25 (26746) INFO : PreProcessing check completed
      2016-09-15 13:43:25 (26746) INFO : preProcess completed, run time for this step : 0.018373 seconds
      2016-09-15 13:43:25 (26746) INFO : No of Read Threads Spawned = 1
      2016-09-15 13:43:25 (26746) INFO : No of Parse Threads Spawned = 3
      2016-09-15 13:43:25 (26746) INFO : For table mytest.t2: 1 rows processed and 1 rows inserted.
      2016-09-15 13:43:26 (26746) INFO : Bulk load completed, total run time : 1.20223 seconds

      [root@mcsce7 datatypes]# mcsmysql mytest
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 873
      Server version: 10.1.17-MariaDB Columnstore 1.0.3-1

      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

      MariaDB [mytest]> select * from t2;
      ------------------+

      c1 c2 c3 c4

      ------------------+

      1 2 3 4
      0 0 0 0
      0 0 0 0
      NULL NULL NULL NULL

      ------------------+
      4 rows in set (0.08 sec)

        Attachments

          Activity

            People

            Assignee:
            dthompson David Thompson (Inactive)
            Reporter:
            dleeyh Daniel Lee
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: