How to fix CQL syntax for multiple criteria?

Question

I have below query, where I want to filter out records using multiple criteria. But I am getting below syntax error.

Query

SELECT * FROM mydb.test 
where org=123 
AND (status = 'over' AND ecode = 196) 
OR (status = 'start' AND ecode = 195) 
ALLOW FILTERING;

Syntax error

SyntaxException: <Error from server: code=2000 [Syntax error in CQL query] 
message="line 1:88 mismatched input 'AND' expecting ')' (... AND (status = 'over' [AND]...)">

How can I fix this syntax error?

Syntax error aside, you probably have a logical error with crit1 AND crit2 OR crit3. Maybe you meant crit1 AND (crit2 or crit3)? — ernest_k
@ernest_k So my objective is to fetch the records who has org=123 and status = 'over' and ecode = 196 or status = 'start' and ecode = 195 — Sangram Badi
That's exactly the problem. Both OR and AND are commutative and associative. So a AND b OR c can validly be interpreted as (a AND b) OR c) or as a AND (b OR c). I think you mean the latter, but your logic's syntax is ambiguous and probably buggy. — ernest_k

Aaron Aaron · Accepted Answer · 2021-02-09T16:08:54

OR is not supported by Cassandra...

Alex is correct. Cassandra does not support the OR keyword. It's one of the differences between CQL and SQL. In fact, given Cassandra's storage model, an OR construct is particularly problematic.

How can I achieve this scenario?

I can think of a few ways.

With Cassandra, the general idea with data modeling is to build your tables to suit your queries. So the first, would be to apply the logic on the data load, but your logic may be too complex for that.

You could also split this query into two queries (based on your AND conditions) and process the result sets on the application side. Not optimal, but it might be the only way to get the fine-grained control you need.

The other approach, would be to try using IN to get around the absence of OR. Just be careful not to restrict your partition key with IN, and always specify your partition key (with an = operator) when you do. That way you'll limit your query to processing on a single node. In fact, using IN on a clustering key (again, with = on your partition key) is really the only way I would recommend its use in a production system.

How to fix CQL syntax for multiple criteria?

1 Answers