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

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;
-----------------------------------------------------------------

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

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

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

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

------------------+
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;
------------------+

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

------------------+
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;
------------------+

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

------------------+
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;
------------------+

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

------------------+
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;
------------------+

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

------------------+
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;
------------------+

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

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