3

I have following table in Cassandra (CQL spec 3.3.0 ):-

Users: <a,b,c,d>, Primary key a Int, B: Map<Int,Date>

Where Users table has column names as a,b,c,d. Now I want to select all the columns for the rows where column b is null. I did following query:-

select from Users where b = null;

but this fails with error = "message="Invalid null value in condition"

  1. The answer here:- null doesn't exists clarifies the semantics of null but still its not clear how do I select the columns which are not present in a row?
  2. Here Nulls in Cassandra an answer claims null to be supported by latest cassandra, but looks like it doesn't work for maps.

Is there any way to solve it? Or should I select all the columns from all the rows and manually check null programmatically, which of course would be very inefficient for a large database.

Community
  • 1
  • 1
Mangat Rai Modi
  • 5,397
  • 8
  • 45
  • 75

1 Answers1

3

Cassandra doesn't support querying based on null, even for secondary indexes.

You could add another field to your table called something like "uninitialized" as a boolean that you set to true when you first insert the row, and you'd create a secondary index on the "uninitialized" field.

When you set the map to something, you would set "uninitialized" to false. Then to find the rows where the map is null, you'd query for rows where "uninitialized" is true.

Jim Meyer
  • 9,275
  • 1
  • 24
  • 49
  • 1
    A bug in my code entered nulls in the table and now I want to remove those lines, there is no way to handle that? Such lines are only 2% of the data. – Mangat Rai Modi Sep 16 '15 at 13:00
  • Then I think you are stuck reading out all the rows and looking for the nulls on the client side. Cassandra won't be able to query for them. To reduce the amount of data, just select the column you want to examine rather than all the fields. – Jim Meyer Sep 16 '15 at 13:10
  • Thanks for the help. I guess there is only dirty way to handle this. – Mangat Rai Modi Sep 17 '15 at 04:46