[MCOL-1881] NULL is imported as 0 from csv file by cpimport Created: 2018-11-13  Updated: 2018-11-14  Resolved: 2018-11-14

Status: Closed
Project: MariaDB ColumnStore
Component/s: cpimport
Affects Version/s: 1.2.1
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Zdravelina Sokolovska (Inactive) Assignee: Andrew Hutchings (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

NULL is imported as 0 from csv file by cpimport

[root@um1 mcsimport]# cat /root/t8.csv
 
|||||||NULL
 
|||||||NULL|
 
||||||||
 
|||||||
 
NULL|NULL||||||
 



[root@um1 ~]# cpimport tt t7 t8.csv
 
2018-11-13 17:35:34 (15525) INFO : Running distributed import (mode 1) on all PMs...
 
2018-11-13 17:35:35 (15525) INFO : For table tt.t7: 5 rows processed and 5 rows inserted.
 
2018-11-13 17:35:35 (15525) INFO : Bulk load completed, total run time : 0.626437 seconds
 
[root@um1 ~]# ./mcsimport  tt t7 t8.csv -c /usr/local/mariadb/columnstore/etc/Columnstore.xml^C
 
[root@um1 ~]# mcsmysql -u root -p1
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 133191
 
Server version: 10.3.10-MariaDB-log Columnstore 1.2.1-1
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> select * from tt.t7 ;
 
+------+------+------+------+------+------+------+-------+
 
| a    | b    | c    | d    | e    | f    | g    | h     |
 
+------+------+------+------+------+------+------+-------+
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0.000 |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0.000 |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL |  NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL |  NULL |
 
|    0 |    0 | NULL | NULL | NULL | NULL | NULL |  NULL |
 
+------+------+------+------+------+------+------+-------+
 
5 rows in set, 1 warning (0.104 sec)
 
 
 
MariaDB [(none)]>
 
 
 
 
 
 
 
 
 
MariaDB [(none)]> show create table tt.t7 ;
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                                                                                   |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t7    | CREATE TABLE `t7` (
 
  `a` tinyint(1) DEFAULT NULL,
 
  `b` tinyint(4) DEFAULT NULL,
 
  `c` smallint(6) DEFAULT NULL,
 
  `d` int(11) DEFAULT NULL,
 
  `e` bigint(20) DEFAULT NULL,
 
  `f` float DEFAULT NULL,
 
  `g` double DEFAULT NULL,
 
  `h` decimal(10,3) DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-11-14 ]

winstone Please try again with the option -n1. This converts the word 'NULL' in a CSV file to a NULL.

Comment by Zdravelina Sokolovska (Inactive) [ 2018-11-14 ]

thank you LinuxJedi, with explicitly set option -n 1 the 'NULL' strings are imported from csv file as NULL values

[root@um1 ~]#  cpimport -n 1 tt t7 t8.csv
 
2018-11-14 13:05:10 (6424) INFO : Running distributed import (mode 1) on all PMs...
 
2018-11-14 13:05:11 (6424) INFO : For table tt.t7: 5 rows processed and 5 rows inserted.
 
2018-11-14 13:05:11 (6424) INFO : Bulk load completed, total run time : 0.345266 seconds
 
[root@um1 ~]#  cpimport  tt t7 t8.csv -n1
 
2018-11-14 13:05:49 (6744) INFO : Running distributed import (mode 1) on all PMs...
 
2018-11-14 13:05:50 (6744) INFO : For table tt.t7: 5 rows processed and 5 rows inserted.
 
2018-11-14 13:05:50 (6744) INFO : Bulk load completed, total run time : 0.32871 seconds
 
[root@um1 ~]#
 
 
 
MariaDB [tt]> select * from t7 ;
 
+------+------+------+------+------+------+------+------+
 
| a    | b    | c    | d    | e    | f    | g    | h    |
 
+------+------+------+------+------+------+------+------+
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
+------+------+------+------+------+------+------+------+
 
10 rows in set, 1 warning (0.048 sec)
 
 
 
MariaDB [tt]>

juan.vera I tried to recreate the issue https://support.mariadb.com/view.php?id=23736 , but did not reproduce it on v1.2.1-1

cpimport -n 1 tt football football.csv
 
2018-11-14 13:32:59 (18574) INFO : Running distributed import (mode 1) on all PMs...
 
2018-11-14 13:32:59 (18574) INFO : For table tt.football: 16 rows processed and 16 rows inserted.
 
2018-11-14 13:32:59 (18574) INFO : Bulk load completed, total run time : 0.317507 seconds
 
MariaDB [tt]> select * from  football ;
 
+------+------+-------------+
 
| num  | name | roster_size |
 
+------+------+-------------+
 
|  185 | test |        NULL |
 
|  186 | test |        NULL |
 
|  187 | test |        NULL |
 
|  188 | test |        NULL |
 
|  189 | test |        NULL |
 
|  190 | test |        NULL |
 
|  191 | test |        NULL |
 
|  192 | test |        NULL |
 
|  193 | test |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
+------+------+-------------+
 
16 rows in set, 1 warning (0.101 sec)
 
 
 
 
 
MariaDB [tt]> truncate table  football ;
 
Query OK, 0 rows affected (0.235 sec)
 
 
 
 
 
[root@um1 ~]# cpimport  tt football football.csv
 
2018-11-14 13:33:24 (18777) INFO : Running distributed import (mode 1) on all PMs...
 
2018-11-14 13:33:24 (18777) INFO : For table tt.football: 16 rows processed and 16 rows inserted.
 
2018-11-14 13:33:24 (18777) INFO : Bulk load completed, total run time : 0.360159 seconds
 
 
 
 
 
 
 
MariaDB [tt]> select * from  football ;
 
+------+------+-------------+
 
| num  | name | roster_size |
 
+------+------+-------------+
 
|  185 | test |        NULL |
 
|  186 | test |        NULL |
 
|  187 | test |        NULL |
 
|  188 | test |        NULL |
 
|  189 | test |        NULL |
 
|  190 | test |        NULL |
 
|  191 | test |        NULL |
 
|  192 | test |        NULL |
 
|  193 | test |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
| NULL | NULL |        NULL |
 
+------+------+-------------+
 
16 rows in set, 1 warning (0.110 sec)
 
 

Comment by Zdravelina Sokolovska (Inactive) [ 2018-11-14 ]

tried also the NULL insertion from csv file with empty fields and 'NULL' strings to different mcs data types
NULL (s) are inserted correctly

MariaDB [tt]> show create table ff10 ;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| ff10  | CREATE TABLE `ff10` (
 
  `a` tinyint(1) DEFAULT NULL,
 
  `b` tinyint(4) DEFAULT NULL,
 
  `c` smallint(6) DEFAULT NULL,
 
  `d` int(11) DEFAULT NULL,
 
  `e` int(11) DEFAULT NULL,
 
  `f` bigint(20) DEFAULT NULL,
 
  `g` decimal(10,0) DEFAULT NULL,
 
  `h` decimal(10,0) DEFAULT NULL,
 
  `i` float DEFAULT NULL,
 
  `j` double DEFAULT NULL,
 
  `k` double DEFAULT NULL,
 
  `l` char(1) DEFAULT NULL,
 
  `m` varchar(700) DEFAULT NULL,
 
  `n` tinytext DEFAULT NULL,
 
  `p` tinyblob DEFAULT NULL,
 
  `q` text DEFAULT NULL,
 
  `r` blob DEFAULT NULL,
 
  `s` mediumtext DEFAULT NULL,
 
  `t` mediumblob DEFAULT NULL,
 
  `u` longtext DEFAULT NULL,
 
  `v` longblob DEFAULT NULL,
 
  `w` date DEFAULT NULL,
 
  `z` datetime DEFAULT NULL,
 
  `z1` time DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [tt]>
 
 
 
 
 
[root@um1 ~]# cat types_null.csv
 
|||||||||||||||||||||||
 
NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL
 
 
 
[root@um1 ~]# cpimport  tt ff10 types_null.csv -n1
 
2018-11-14 13:50:08 (26069) INFO : Running distributed import (mode 1) on all PMs...
 
2018-11-14 13:50:11 (26069) INFO : For table tt.ff10: 2 rows processed and 2 rows inserted.
 
2018-11-14 13:50:11 (26069) INFO : Bulk load completed, total run time : 2.89314 seconds
 
 
 
MariaDB [tt]> select * from ff10 ;
 
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
 
| a    | b    | c    | d    | e    | f    | g    | h    | i    | j    | k    | l    | m    | n    | p    | q    | r    | s    | t    | u    | v    | w    | z    | z1   |
 
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
 
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
 
2 rows in set, 1 warning (0.066 sec)
 
 
 
 

Generated at Thu Feb 08 02:32:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.