Given this sample table schema:
CREATE TABLE foo (
pk1 text,
pk2 text,
pk3 text,
pk4 text,
data map<text, text>,
PRIMARY KEY ((pk1, pk2, pk3), pk4)
);
I wonder if it is possible to have a query that selects different combinations of pk2, pk3, pk4 with a fixed pk1. Something like:
SELECT * FROM foo WHERE pk1 = 'abc' AND
((pk2 = 'x' AND pk3 = 'y' AND pk4 = 'z') OR ((pk2 = 'd' AND pk3 = 'e' AND pk4 = 'f'));
I don't get this working. I have a set of pk2, pk3, pk4 tuples and a fixed pk1 and want to select all matching rows using a single query if possible (Cassandra 2.2.x).
I wonder if it is possible to have a query that selects different combinations of pk2, pk3, pk4 with a fixed pk1.
No, it is not.
As Doan pointed out, OR is not supported (yet). This leaves you with AND which makes nesting WHERE conditions superfluous.
Also, adding parens around your AND parts is not supported either. Without parens this works, but not with them:
aploetz@cqlsh:stackoverflow> SELECT * FROM row_historical_game_outcome_data
WHERE customer_id=123123 AND (game_id=673 AND game_time = '2015-07-01 05:01:42+0000');
SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query] message="line 1:89 missing ')' at 'AND' (...=123123 AND (game_id=673 [AND] game_time...)">
That being said, the IN predicate will work on either the last partition or last clustering key. But using it on a partition key is considered to be an anti-pattern. In any case, in your case this would work (syntactically):
SELECT * FROM foo WHERE pk1 = 'abc' AND pk2 = 'x' AND pk3 = 'y' AND pk4 IN ('z','f');