CQL: Unable to Null Check in "Where" Clause

6
votes

Try to return all rows where a specific field is null or not null. 

select * from ADDRESS where addr1 = null;

or 

select * from ADDRESS where addr1 = 'NULL';

addr1 can be a boolean or text field.

I've tried != null, is null, <> null, and isnull(addr1, 'NULL')

but I get 

no viable alternative at input '='

or 

no index columns present in by-columns clause with "equals' operator"

Using Cassandra 1.1.1 & Java 1.7_05

2
cassandracql

2 Answers

6
votes

CQL doesn't have the concept of NULL (yet- see CASSANDRA-3783). The right thing to do instead depends on your particular situation.

0
votes

One way is to use cqlsh to select the required columns - normally the primary key and the column of interest using -e option and use grep for identifying null entries. Set "paging off" also in the query to get all the results otherwise by default only 100 rows will be returned.

cqlsh <ip> -u <user name> -p <password> -e "paging off;select * from ADDRESS where addr1 = null;" | grep null