How can I have null column value for a composite key column in CQL3

Question

This may sound silly as there are no null values in SQL's composite primary key. But just want to confirm if we can have the same in CQL3?
So, we have a table like this to store wide rows:

CREATE TABLE keyspace12.colFamily1 
(
  id text, 
  colname text,
  colvalue blob,
  PRIMARY KEY (id,colname, colvalue)
) WITH COMPACT STORAGE

And we have some cases where colname is null. Can I do that? If yes, then how? If NO, then what are the ways to store wide columns rows where we can have some null in first part of composite column of cassandra(As per Thrift's convention)?

The related questions are: CQL3 and millions of columns composite key use case and Cassandra -How to create composite column name (not key) using cqlsh

Cogitate

Answer 1

Say i have a column family

CREATE TABLE cnt_test (time_slot text , comp1 text, comp2 text, field1 counter, field2 counter, PRIMARY KEY(time_slot,comp1, comp2));

Now i am trying to insert some data in it

UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='' AND comp2='XYZ';
UPDATE cnt_test SET field1=field1+1, field2=field2+2 WHERE time_slot='20130924' AND comp1='PQR' AND comp2='';

As you can see in the above statements i have inserted some empty values in the compound key part, just instead of null i am putting the blank character.

I can even query on the same

 SELECT * FROM cnt_test WHERE time_slot='20130924' AND comp1='' AND comp2='ABC';

 time_slot | comp1 | comp2 | field1 | field2
-----------+-------+-------+--------+--------
  20130924 |       |   ABC |      4 |      8

(1 rows)

SELECT * FROM cnt_test WHERE time_slot='20130924' AND comp1='PQR' AND comp2='';

 time_slot | comp1 | comp2 | field1 | field2
-----------+-------+-------+--------+--------
  20130924 |   PQR |       |      1 |      2

(1 rows)

So to summarize every thing just replace the null column value with empty column i.e ''

Alric

Thanks Abhi, That worked in some of our usecases. What is colname in our case is not text but int? How we can insert null or blank for that? – Cogitate

hmmm... interesting... well in that case i will consider any integer value, may be 0, i.e the default integer value. And while querying on that data i will remember the assumption & will handle accordingly. – Alric

@Alric yes, how to do this for the varint type? – Margay

@DeanHiller For varint, i belief the replacement should be 0 only. – Alric

@Alric I was thinking about a DUMMY value to represent null but it's not so obvious and is a big code smell. If your data type is String, you can store "" to represent null. But what about Integer, Long, UUID, Date type ? There is no sensible default integer value to represent the null state. Same for Long, Double, UUID ... – Magnify

yeah that i know and for that only i have mentioned that, it need to be handled carefully if done. – Alric

uhm, I use private Integer golfScore; which I can set to null. int is not nullable while Integer is. In thrift, we represented null with empty value or 0 length byte array. String is way more tricky as you can't represent "" and null both...which one is more important to represent...usually null as that is more valuable. – Margay

obviously using 0 is a bad idea as well. – Margay

Answer 2

And we have some cases where colname is null. Can I do that?

Response is no, as expected

If NO, then what are the ways to store wide columns rows where we can have some null in first part of composite column of cassandra(As per Thrift's convention)?

You can do in in Thrift with cassandra-cli. It's not possible with CQL3 to set a primary key component to null

Magnify

That we are already doing. Basically in thrift we have composite column like Decimal.Bytes and there we can have Decimal part as null..Wanted to know how to achieve that in CQL3 – Cogitate

We have the same issue here as how do we port to CQL3 then? – Margay

I think that technically it is possible but semantically Datastax forbid to have null value for primary key components (which make sense). If you have component1, component2 and component3 as composites, setting null for component2 will make you have a "hole" in the byte array. For slice query and column sorting, you may have weird behaviors – Magnify

@Magnify It turns out you can do a 0 length value to represent null(see cassandra email list).....still trying to figure it out though...if you get something working, let me know – Margay

Answer 3

I needed to do the same in order to model parent/child relationships in a single table. So when I want to insert a parent then 'id' is populated and your 'colname' would be set to nil.

To do this the solution was really simple literally insert the 'colname' as empty string. I can see my select return null returned.

Crwth