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

No data gets loaded into BLOB column and the value is left NULL

Details

    • Task
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • 23.02.9, 23.10.2
    • cpimport
    • 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)

      Attachments

        Activity

          susil.behera Susil Behera added a comment -

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

          t c b

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

          NULL NULL NULL
               
          newhello newhello hello
          HELLO HELLO HELLO
          HELLO MY HELLO MY HELLO MY
          a a NewBlob
          1   NULL
          NULL NULL NULL

          --------------------------
          8 rows in set (0.000 sec)

          Mismatch results:

          1. – empty string is not considered as distinct value
          InnoDB> select distinct b from t1;
          ----------

          b

          ----------

          NULL
           
          hello
          HELLO
          HELLO MY
          NewBlob

          ----------
          6 rows in set (0.001 sec)

          Columnstore> select distinct b from t1;
          ----------

          b

          ----------

          NULL
          hello
          HELLO
          HELLO MY
          NewBlob

          ----------
          5 rows in set (0.008 sec)

          2. – not NULL values are missing
          InnoDB> select b from cst1 group by b;
          ----------

          b

          ----------

          NULL
           
          HELLO
          HELLO MY
          NewBlob
          hello

          ----------
          6 rows in set (0.001 sec)

          Columnstore> select b from cst1 group by b;
          ------

          b

          ------

          NULL

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

          denis0x0D Please see if we've a problem here.

          susil.behera Susil Behera added a comment - 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; --------- -------- --------- t c b --------- -------- --------- NULL NULL NULL       newhello newhello hello HELLO HELLO HELLO HELLO MY HELLO MY HELLO MY a a NewBlob 1   NULL NULL NULL NULL --------- -------- --------- 8 rows in set (0.000 sec) Mismatch results: 1. – empty string is not considered as distinct value InnoDB> select distinct b from t1; ---------- b ---------- NULL   hello HELLO HELLO MY NewBlob ---------- 6 rows in set (0.001 sec) Columnstore> select distinct b from t1; ---------- b ---------- NULL hello HELLO HELLO MY NewBlob ---------- 5 rows in set (0.008 sec) 2. – not NULL values are missing InnoDB> select b from cst1 group by b; ---------- b ---------- NULL   HELLO HELLO MY NewBlob hello ---------- 6 rows in set (0.001 sec) Columnstore> select b from cst1 group by b; ------ b ------ NULL ------ 1 row in set (0.047 sec) denis0x0D Please see if we've a problem here.
          susil.behera Susil Behera added a comment -

          The above mismatches aren't related to cpimport. I've filed separate tickets MCOL-5754 and MCOL-5755 for the same. Otherwise cpimport is now handling blob columns correctly.

          Verified on https://ci.columnstore.mariadb.net/mariadb-corporation/mariadb-columnstore-engine/10089.

          susil.behera Susil Behera added a comment - The above mismatches aren't related to cpimport. I've filed separate tickets MCOL-5754 and MCOL-5755 for the same. Otherwise cpimport is now handling blob columns correctly. Verified on https://ci.columnstore.mariadb.net/mariadb-corporation/mariadb-columnstore-engine/10089 .

          People

            denis0x0D Denis Khalikov (Inactive)
            susil.behera Susil Behera
            Roman Roman
            Susil Behera Susil Behera
            Votes:
            1 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.