CQL syntax error: "mismatched input 'and' expecting ')' "

Question

I am executing the below query :

SELECT * FROM test
WHERE (
    Column11 in ('Value1','Value2','Vlaue3')
    AND Column12 in ('Value11','Value22','Vlaue32')
    AND Column13 = 'Value99'
);

This is giving below error :

mismatched input 'and' expecting ')' (...,'Value3')  [and]...)

But when I am executing above query with out external braces, its working fine.

SELECT * FROM test 
WHERE Column11 in ('Value1','Value2','Vlaue3')  
    AND Column12 in ('Value11','Value22','Vlaue32') 
    AND Column13 = 'Value99' ;

Is there any way to execute first query ? Because I want to add multiple clauses, OR separated, and prepare a big query.

https://stackoverflow.com/questions/49872417/cassandra-subquery-alternate Cassandra doesnt support this sub queries. — Rafee, Mar 29 '21 at 07:16
But that's not the same problem, @Rafee. This one is just parentheses enclosing conditions forming the where clause. It's not a sub-query. — ernest_k, Mar 29 '21 at 07:24
@R_S_C It could be because of clustering of column https://docs.datastax.com/en/cql-oss/3.x/cql/cql_reference/cqlSelect.html#cqlSelect__comparing-clustering-columns — Rafee, Mar 29 '21 at 07:37
Depending on the size of the expected data payload and number of nodes in the cluster, unless `column13` is a partition key, this query will probably time-out. — Aaron, Mar 29 '21 at 13:03
May be you are using reserved keyword as column name. My reply on same kind of exception at https://stackoverflow.com/a/71903497/556540 — Shams, Apr 17 '22 at 16:27

score 1 · Accepted Answer · answered Mar 29 '21 at 13:00

This one is just parentheses enclosing conditions forming the where clause.

Subquery or not, the CQL parser does not allow for extra parens.

I want to add multiple clauses, OR separated, and prepare a big query.

Due to Cassandra's underlying engineering choices with regard to data distribution and read path, OR is not a valid CQL keyword.

Cassandra requires you to model tables based on the anticipated query patterns. When you run a query, the goal should be to ensure that it can be served by a single node in the cluster. OR based logic tends to be more open-ended, and not focused on precise key values.

tl;dr;

CQL != SQL. This sounds to me like more of a use case for Postgres or MariaDB.

CQL syntax error: "mismatched input 'and' expecting ')' "

1 Answers1