cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Laing, Michael" <michael.la...@nytimes.com>
Subject Re: IN clause with composite primary key?
Date Fri, 25 Jul 2014 22:45:15 GMT
You may also want to use tuples for the clustering columns:

The tuple notation may also be used for IN clauses on CLUSTERING COLUMNS:
>
> SELECT * FROM posts WHERE userid='john doe' AND (blog_title, posted_at) IN (('John''s
Blog', '2012-01-01), ('Extreme Chess', '2014-06-01'))
>
>
> from https://cassandra.apache.org/doc/cql3/CQL.html#selectStmt


On Fri, Jul 25, 2014 at 2:29 PM, DuyHai Doan <doanduyhai@gmail.com> wrote:

> 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