Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
-
2024-1
Description
cpimport is not able to load BLOB values. No data gets loaded into BLOB column and the value is left NULL.
Repro
------
1. create schema
CREATE TABLE `lte_call_leg_by_mesh_2_IMSI` ( `MeshID` bigint(20) DEFAULT NULL, `IMSI` blob DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
2. create text data and import
cat data.csv
1111111,oneoneoneoneoneoneoneone
cpimport -s ',' test lte_call_leg_by_mesh_2_IMSI data.csv
MariaDB [test]> select * from lte_call_leg_by_mesh_2_IMSI;
-------------+
MeshID | IMSI |
-------------+
1111111 | NULL |
-------------+
1 rows in set (0.030 sec)
convert the above csv file into a binary file data.bin as follows,
3. create binary data and import
python program to create binary file for the above data.csv file>
import csv
with open('data.csv', 'r') as csvfile, open('data.bin', 'wb') as binfile:
reader = csv.reader(csvfile)
for row in reader:
binfile.write(bytes(','.join(row), 'utf-8'))
cpimport -s ',' test lte_call_leg_by_mesh_2_IMSI data.bin
MariaDB [test]> select * from lte_call_leg_by_mesh_2_IMSI;
-------------+
MeshID | IMSI |
-------------+
1111111 | NULL |
1111111 | NULL |
-------------+
2 rows in set (0.034 sec)
Performed different types of queries on blob column. I'm seeing the following mismatches (from InnoDB) when the values contain NULL and/or empty string,
create table t1 (t text,c char(10),b blob) engine = columnstore;
insert into t1 values (NULL,NULL,NULL);
insert into t1 values ("","","");
insert into t1 values ("hello","hello","hello");
insert into t1 values ("HELLO","HELLO","HELLO");
insert into t1 values ("HELLO MY","HELLO MY","HELLO MY");
insert into t1 values ("a","a","a");
insert into t1 values (1,1,1);
insert into t1 values (NULL,NULL,NULL);
update t1 set c="",b=null where c="1";
update t1 set b="NewBlob" where c="a";
update t1 set t="newhello", c="newhello" where b="hello";
select * from t1;
---------
--------------------------
--------------------------
-----------------8 rows in set (0.000 sec)
Mismatch results:
1. – empty string is not considered as distinct value
InnoDB> select distinct b from t1;
----------
----------
----------
6 rows in set (0.001 sec)
Columnstore> select distinct b from t1;
----------
----------
----------
5 rows in set (0.008 sec)
2. – not NULL values are missing
InnoDB> select b from cst1 group by b;
----------
----------
----------
6 rows in set (0.001 sec)
Columnstore> select b from cst1 group by b;
------
------
------
1 row in set (0.047 sec)
denis0x0D Please see if we've a problem here.