6

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

Nishant
  • 54,584
  • 13
  • 112
  • 127
Snake
  • 785
  • 1
  • 5
  • 13

2 Answers2

6

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

the paul
  • 8,972
  • 1
  • 36
  • 53
  • so if I upgrade to Cassandra 1.2 and CQL 3.0, will this solve my issue? – Snake Aug 06 '12 at 06:04
  • Can't say for sure. Cassandra 1.2 doesn't exist yet, so there are no guarantees about what tickets will be implemented for it. At any rate, it shouldn't be *necessary* to upgrade to 1.2 to solve your problem. – the paul Aug 07 '12 at 07:09
  • sorry, i ment Cassandra 1.1.2. They are on 1.1.3 as I look today. I will update Cassandra and try it. – Snake Aug 09 '12 at 16:07
  • Upgrading to 1.1.2 or 1.1.3 definitely will not solve your problem. You need to change your queries. – the paul Aug 10 '12 at 17:26
  • thanks paul, i just want to check if data was inserted by the app by checking for nulls. if you can point me to the syntax, it would be helpful. i already did searches here and on google... =( – Snake Aug 10 '12 at 19:45
  • Like I said, there is no syntax for that yet; CQL doesn't have the concept of NULL. Whatever problem you're trying to solve is probably still solvable without using NULL, but I can't tell you exactly how without knowing what the situation is. You should probably ask a new toplevel question and state what you're trying to do (instead of how you're trying to do it). – the paul Aug 11 '12 at 22:55
  • 1
    It works now, CASSANDRA-3783 was resolved and newer Cassandra releases accept `addr1 = null` . – Georg Jun 18 '15 at 21:46
0

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

Rahul R
  • 1
  • 1