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='[email protected]';
id | dept | email | first_name | last_name | salary
-----+------+-------------+------------+-----------+--------
10 | 10 | [email protected] | Rob | Stark | 1000
1 | 10 | [email protected] | Rob | Stark | 1000
101 | 10 | [email protected] | 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='[email protected]' or email='robs@ex';
SyntaxException: line 1:51 : syntax error...
cqlsh>
cqlsh> select * from test.d_emp where email='[email protected]' 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.
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='[email protected]' or email='robs@ex', the solution is simple - just use the IN keyword: where email IN ('[email protected]', '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).