No indexed columns present in by-columns clause with Equal operator

I'm running CQL3 with Cassandra 2.1.2, and here's what happens (I have a keyspace called default):

cqlsh> CREATE TABLE default.test (x int, y int) PRIMARY KEY (x);
cqlsh> CREATE TABLE default.test (x int PRIMARY KEY, y int);
cqlsh> INSERT INTO default.test (x, y) VALUES (1, 2);
cqlsh> INSERT INTO default.test (x, y) VALUES (1, 0);
cqlsh> SELECT * FROM default.test WHERE x=1 AND y > 1;
code=2200 [Invalid query] message="No indexed columns present in by-columns clause with Equal operator"

What happened? I read the related questions on here and they said I could have a > filter on the non-primary key as long as I had an = filter on the primary key.

Solution

Ok, there are a few things going on here, so I'll tackle them one at a time.

Your first CREATE TABLE statement is not syntactically correct.

CREATE TABLE default.test (x int, y int) PRIMARY KEY (x);

Your PRIMARY KEY definition needs to be inside of your column definition, like this:

CREATE TABLE default.test (x int, y int, PRIMARY KEY (x));

Ironically, this is close to what you need to support your query.

PRIMARY KEYS in Cassandra are unique. This becomes apparent when I follow your steps above and INSERT two rows:

aploetz@cqlsh:stackoverflow> INSERT INTO default.test (x, y) VALUES (1, 2);
aploetz@cqlsh:stackoverflow> INSERT INTO default.test (x, y) VALUES (1, 0);
aploetz@cqlsh:stackoverflow> SELECT * FROm test;
x | y
---+---
1 | 0

(1 rows)

As x is your only PRIMARY KEY, the values of x = 1 and y = 2 are INSERTed first...and then next INSERT promptly overwrites y with the value of 0. And not only are PRIMARY KEYS unique, INSERTs and UPDATEs are treated the same by Cassandra.

Ironically, the solution to support your query is the same as the solution to support both rows.

I read the related questions on here and they said I could have a > filter on the non-primary key as long as I had an = filter on the primary key.

Not entirely true. You are allowed to filter by > or < only on clustering columns, and then only if the partition key is restricted by equals. As you have a single PRIMARY KEY, x is your partition key, and you do not have a clustering column defined. Therefore to support this query, y must also be defined as part of the PRIMARY KEY, like this:

CREATE TABLE default.test (x int, y int, PRIMARY KEY (x,y));

Including y as a part of your PRIMARY KEY also helps to ensure uniqueness, which will allow your table to contain two rows:

aploetz@cqlsh:stackoverflow> INSERT INTO default.test (x, y) VALUES (1, 2);
aploetz@cqlsh:stackoverflow> INSERT INTO default.test (x, y) VALUES (1, 0);
aploetz@cqlsh:stackoverflow> SELECT * FROm test;
x | y
---+---
1 | 0
1 | 2

(2 rows)

With all of that done, this will now work:

aploetz@cqlsh:stackoverflow> SELECT * FROM test WHERE x=1 AND y > 1;

 x | y
---+---
 1 | 2

(1 rows)

Here is a link to the most-recent doc detailing the CQL SELECT statement. You should definitely give that a read, a well as this one that explains Compound Keys and Clustering.