Non-equality operator is not allowed when filtering results

[smalyshev]

For some reason, non-equality operation (!=) is not allowed in non-key filters, while other operations ('>', '<', '=') are allowed. Example:

create table test2 (a int primary key, b int, c int);
insert into test2 values(1,2,3);

This works:

> select * from test2 where b <0 ;

 a | b | c
---+---+---

(0 rows)

> select * from test2 where b >0 ;

 a | b | c
---+---+---
 1 | 2 | 3

(1 rows)

> select * from test2 where b =0 ;

 a | b | c
---+---+---

(0 rows)

But this fails:

> select * from test2 where b !=0 ;
SyntaxException: Invalid CQL Statement. This operator is not allowed in where clause
select * from test2 where b !=0 ;
                          ^
 (ql error -12)

I see no reason why all other operations work but != is excluded. Note this is not about performance - all of these would make a full table scan before applying the filter and that's ok with me (in fact, that's what I want to happen). But for some reason only one of the operators fails.

[smalyshev]

Additional weirdness:

select * from test2 where b > 1;

 a | b | c
---+---+---
 1 | 2 | 3
> select * from test2 where b > 1 and b < 2;

 a | b | c
---+---+---
> select * from test2 where b > 1 or b < 2;
SyntaxException: Invalid CQL Statement. This operator is not allowed in where clause
select * from test2 where b > 1 or b < 2;
                          ^^^^^
 (ql error -12)

I'm not sure whether or operator is banned completely or just in this case. But the error points to b > 1 for some reason.

[m-iancu]

@smalyshev The WHERE clause has those restrictions (mostly for CQL compatibility reasons). To avoid that you can use the IF clause which does not have those restrictions (and we also allow in SELECT statements also in addition to UPDATE and DELETE) For instance, with your example:

cqlsh:foo> select * from test2 if b !=0 ;

 a | b | c
---+---+---
 1 | 2 | 3

Also, you can have both WHERE and IF clause (though IF clause is only useful to avoid these operator restrictions on filtering queries):

create table test(h int, r int, v int, primary key (h,r));
insert into test(h,r,v) values (1,1,10);
insert into test(h,r,v) values (1,2,15);
insert into test(h,r,v) values (1,3,0);
insert into test(h,r,v) values (1,4,20);
insert into test(h,r,v) values (1,5,0);
select * from test;

 h | r | v
---+---+----
 1 | 1 | 10
 1 | 2 | 15
 1 | 3 |  0
 1 | 4 | 20
 1 | 5 |  0

(5 rows)

select * from test where h = 1 if v != 0;

 h | r | v
---+---+----
 1 | 1 | 10
 1 | 2 | 15
 1 | 4 | 20

(3 rows)

Hope this helps.