PRIMARY KEY part colname cannot be restricted by IN relation

Question

My CQL3 table is like this

  CREATE TABLE stringindice (
  id text,
  colname text,
  colvalue blob,
  PRIMARY KEY (id, colname, colvalue)
  ) WITH COMPACT STORAGE

and I have inserted some values in it. Now when I am trying to do something like this:

   QueryBuilder.select().all().from(keySpace, indTastringindice ble).where().and(QueryBuilder.eq("id", 'rowKey")).and(QueryBuilder.in("colname", "string1", "string2"));

which is essentially

select * from stringindice where id = "rowkey" and colname IN ("string1", "string2")

I am getting following exception:

com.datastax.driver.core.exceptions.InvalidQueryException: PRIMARY KEY part colname cannot be restricted by IN relation
at com.datastax.driver.core.exceptions.InvalidQueryException.copy(InvalidQueryException.java:35)
at com.datastax.driver.core.ResultSetFuture.extractCauseFromExecutionException(ResultSetFuture.java:214)
at com.datastax.driver.core.ResultSetFuture.getUninterruptibly(ResultSetFuture.java:169)
at com.datastax.driver.core.Session.execute(Session.java:110)

In the documentation of CQL3, it is written that

"Moreover, the IN relation is only allowed on the last column of the partition key and on the last column of the full primary key."

So it seems that it is not supported!! If yes, then what is the way if I have to use something like IN for equating many values at once?

score 2 · Answer 1 · answered Sep 26 '13 at 12:45

2

It is because you are using compact storage, so the composite column is colname:colvalue (and the value is empty). This means colname is not the last column of the full primary key.

If you don't use compact storage (which is recommended for all new data models), you have the equivalent schema:

CREATE TABLE stringindice (
  id text,
  colname text,
  colvalue blob,
  PRIMARY KEY (id, colname)
);

Then your IN query will work:

cqlsh:ks> insert into stringindice (id, colname, colvalue) VALUES ('rowkey', 'string1', '01');
cqlsh:ks> insert into stringindice (id, colname, colvalue) VALUES ('rowkey', 'string2', '02');
cqlsh:ks> insert into stringindice (id, colname, colvalue) VALUES ('rowkey', 'string3', '03');
cqlsh:ks> select * from stringindice where id = 'rowkey' and colname IN ('string1', 'string2');

 id     | colname | colvalue
--------+---------+----------
 rowkey | string1 |     0x01
 rowkey | string2 |     0x02

answered Sep 26 '13 at 12:45

Richard

11,050
2
46
33

Thanks Richard, but sadly we have to use Compact Storage for some other reasons..Any other alternative to us IN clause? – Easility Sep 26 '13 at 12:49
Why do you need compact storage? This schema stores colvalue within the column key so is limited to 64 KB. The non-compact storage is very similar except colvalue is the actual column value. – Richard Sep 26 '13 at 12:55
Well, that is the best way to preserve our wide rows pattern of thrift. You can see more reasons in the similar discussion here https://groups.google.com/a/lists.datastax.com/forum/#!topic/java-driver-user/hgZnyVJhWCw – Easility Sep 26 '13 at 13:09
1

Sorry, that schema I wrote isn't equivalent, because your schema allows for multiple colvalues per colname. Is that what you need? Instead of using `IN` you can get all the values for a colname by using `where id = 'rowkey' and colname = 'string1'` and executing for each value of your `IN` list. – Richard Sep 26 '13 at 13:24

PRIMARY KEY part colname cannot be restricted by IN relation

1 Answers1