cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From DuyHai Doan <doanduy...@gmail.com>
Subject Re: IN clause with composite primary key?
Date Fri, 25 Jul 2014 21:29:46 GMT
Below are the rules for IN clause

a. composite partition keys: the IN clause only applies to the last
composite component
b. clustering keys: the IN clause only applies to the last clustering key

Contrived example:

CREATE TABLE test(
   pk1 int,
   pk2 int,
   clust1 int,
   clust2 int,
   clust3 int,
   PRIMARY KEY ((pk1,pk2), clust1, clust2, clust3));

Possible queries

SELECT * FROM test WHERE pk1=1 AND pk2 IN (1,2,3);
SELECT * FROM test WHERE pk1=1 AND pk2 IN (1,2,3) AND col1=1 AND col2=2 AND
col3 IN (3,4,5);

Theoretically there should be possible to do   SELECT * FROM test WHERE pk1
IN(1,2)  AND pk2 =3;  or SELECT * FROM test WHERE pk1 IN(1,2)  AND pk2 IN
(3,4) because the values in the IN() clause are just expanded to all linear
combinations with other composites of the partiton key. But for some reason
it's not allowed.

However the restriction of IN clause for the clustering keys some how makes
sense. Having multiple clustering keys, if you allow using IN clause for
the first or any clustering key that is not the last one, C* would have to
do a very large slice to pick some discrete values matching the IN() clause
...







On Fri, Jul 25, 2014 at 11:17 PM, Kevin Burton <burton@spinn3r.com> wrote:

> How the heck would you build an IN clause with a primary key which is
> composite?
>
> so say columns foo and bar are the primary key.
>
> if you just had foo as your column name, you can do
>
> where foo in ()
>
> … but with two keys I don't see how it's possible.
>
> specifying both actually builds a cartesian product.  which is kind of
> cool but not what I want :)
>
> --
>
> Founder/CEO Spinn3r.com
> Location: *San Francisco, CA*
> blog: http://burtonator.wordpress.com
> … or check out my Google+ profile
> <https://plus.google.com/102718274791889610666/posts>
> <http://spinn3r.com>
>
>

Mime
View raw message