3

How does OR condition works in WHERE clause for SELECT statements in scylladb.

I have created secondary index on email. Below query works fine and return result.

cqlsh> select * from test.d_emp where email='robs@ex.com';

 id  | dept | email       | first_name | last_name | salary
-----+------+-------------+------------+-----------+--------
  10 |   10 | robs@ex.com |        Rob |     Stark |   1000
   1 |   10 | robs@ex.com |        Rob |     Stark |   1000
 101 |   10 | robs@ex.com |        Rob |     Stark |   1000

(3 rows)

However if I use two conditions using OR operator it is giving syntax error. Even a simple 1=1 is also giving same error.

cqlsh> select * from test.d_emp where email='robs@ex.com' or email='robs@ex';
SyntaxException: line 1:51  : syntax error...

cqlsh>

cqlsh> select * from test.d_emp where email='robs@ex.com' or 1=1;
SyntaxException: line 1:51  : syntax error...

cqlsh>

Please help me to understand how to combine multiple conditions in where clause in Scylladb.

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
Gopi
  • 689
  • 1
  • 8
  • 17

1 Answers1

4

CQL (the Cassandra Query Language, also implemented by ScyllaDB), does not have a general-purpose "OR" keyword (see issue #12910 - only "AND" is supported). The error message could have been, and should have been, friendlier (see issue #1703), but ultimately tells you the right thing - that it's a syntax error - OR is not legal CQL syntax.

In your use case, where email='robs@ex.com' or email='robs@ex', the solution is simple - just use the IN keyword: where email IN ('robs@ex.com', 'robs@ex').

By the way, the 1=1 part also doesn't work, not even without OR, because currently CQL expressions are rather limited in their scope, in this case column=value is a valid expression, but value=value or other stuff, is not. Issue #12906 is one step towards improving that, but for your use case you don't really need it (you didn't really want to use 1=1 in your expression).

Nadav Har'El
  • 11,785
  • 1
  • 24
  • 45