Uploaded image for project: 'Cassandra'
  1. Cassandra
  2. CASSANDRA-12794

COPY FROM with NULL='' fails when inserting empty row in primary key

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Fix Version/s: 3.0.11, 3.10
    • Component/s: Legacy/Tools
    • Labels:
      None
    • Environment:

      Tested using C* 2.1.15

    • Severity:
      Normal

      Description

      Using this table:

      CREATE TABLE testtab (  a_id text,  b_id text,  c_id text,  d_id text,  order_id uuid,  acc_id bigint,  bucket bigint,  r_id text,  ts bigint,  PRIMARY KEY ((a_id, b_id, c_id, d_id), order_id));
      

      insert one row:

      INSERT INTO testtab (a_id, b_id , c_id , d_id , order_id, r_id ) VALUES ( '', '', '', 'a1', 645e7d3c-aef7-4e3c-b834-24b792cf2e55, 'r1');
      

      Use COPY to dump the row to temp.csv:

      copy testtab TO 'temp.csv';
      

      Which creates this file:

      $ cat temp.csv 
      ,,,a1,645e7d3c-aef7-4e3c-b834-24b792cf2e55,,,r1,
      

      Truncate the testtab table and then use copy from with NULL='' to insert the row:

      cqlsh:sbkeyspace> COPY testtab FROM 'temp.csv' with NULL='';
      Using 1 child processes
      
      Starting copy of sbkeyspace.testtab with columns ['a_id', 'b_id', 'c_id', 'd_id', 'order_id', 'acc_id', 'bucket', 'r_id', 'ts'].
      Failed to import 1 rows: ParseError - Cannot insert null value for primary key column 'a_id'. If you want to insert empty strings, consider using the WITH NULL=<marker> option for COPY.,  given up without retries
      Failed to process 1 rows; failed rows written to import_sbkeyspace_testtab.err
      Processed: 1 rows; Rate:       2 rows/s; Avg. rate:       3 rows/s
      1 rows imported from 1 files in 0.398 seconds (0 skipped).
      

      It shows 1 rows inserted, but the table is empty:

      select * from testtab ;
      
       a_id | b_id | c_id | d_id | order_id | acc_id | bucket | r_id | ts
      ------+------+------+------+----------+--------+--------+------+----
      
      (0 rows)
      

      The same error is returned even without the with NULL=''. Is it actually possible for copy from to insert an empty row into the primary key? The insert command shown above inserts the empty row for the primary key without any problems.

      Is this related to https://issues.apache.org/jira/browse/CASSANDRA-7792?

        Attachments

          Activity

            People

            • Assignee:
              stefania Stefania Alborghetti
              Reporter:
              sbassi Sucwinder Bassi
              Authors:
              Stefania Alborghetti
              Reviewers:
              Tom Hobbs
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: