You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Sergei Petrunia <ps...@askmonty.org> on 2012/09/22 20:58:26 UTC
Varchar indexed column and IN(...)
Hello,
Does CQL's IN(...) predicate have the same meaning as SQL's IN(...)? I'm
asking this, because I get results that I cannot explain:
cqlsh:xpl1> select * from t1 where col2='bar1';
pk | col1 | col2
------+------+------
pk1b | foo1 | bar1
pk1 | foo1 | bar1
pk1a | foo1 | bar1
pk1c | foo1 | bar1
cqlsh:xpl1> select * from t1 where col2 in ('bar1', 'bar2') ;
cqlsh:xpl1>
The first query shows there are records with col2='bar1'. I would expect the
second query return a superset of what the first query returned. However, it
returns nothing!
If this is intended behavior, could somebody please point me to where this is
documented?
== Complete example ==
# Repeatable on Cassandra 1.1.4 or 1.1.5:
[cqlsh 2.2.0 | Cassandra 1.1.5 | CQL spec 3.0.0 | Thrift protocol 19.32.0]
Use HELP for help.
cqlsh>
cqlsh> create keyspace xpl1 WITH strategy_class ='SimpleStrategy' and strategy_options:replication_factor=1;
cqlsh> use xpl1;
cqlsh:xpl1> create table t1 (pk varchar primary key, col1 varchar, col2 varchar);
cqlsh:xpl1> create index t1_c1 on t1(col1);
cqlsh:xpl1> create index t1_c2 on t1(col2);
cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1','foo1','bar1');
cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1a','foo1','bar1');
cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1b','foo1','bar1');
cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1c','foo1','bar1');
cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk2','foo2','bar2');
cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk3','foo3','bar3');
cqlsh:xpl1> select * from t1 where col2='bar1';
pk | col1 | col2
------+------+------
pk1b | foo1 | bar1
pk1 | foo1 | bar1
pk1a | foo1 | bar1
pk1c | foo1 | bar1
cqlsh:xpl1> select * from t1 where col2 in ('bar1', 'bar2') ;
cqlsh:xpl1>
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
Re: Varchar indexed column and IN(...)
Posted by Sylvain Lebresne <sy...@datastax.com>.
On Sun, Sep 23, 2012 at 11:30 PM, aaron morton <aa...@thelastpickle.com> wrote:
> If this is intended behavior, could somebody please point me to where this
> is
> documented?
>
> It is intended.
It is not in fact. We should either refuse the query as "yet
unsupported" or we should do the right thing, but returning nothing
silently is wrong. I've created
https://issues.apache.org/jira/browse/CASSANDRA-4709 to fix that.
--
Sylvain
Re: Varchar indexed column and IN(...)
Posted by aaron morton <aa...@thelastpickle.com>.
> If this is intended behavior, could somebody please point me to where this is
> documented?
It is intended.
The docs don't make it totally clear though:
<clause> syntax is:
<primary key name> { = | < | > | <= | >= } <key_value>
<primary key name> IN (<key_value> [,...])
http://www.datastax.com/docs/1.1/references/cql/SELECT
the key_value means only the primary key field.
Cheers
-----------------
Aaron Morton
Freelance Developer
@aaronmorton
http://www.thelastpickle.com
On 23/09/2012, at 6:58 AM, Sergei Petrunia <ps...@askmonty.org> wrote:
> Hello,
>
> Does CQL's IN(...) predicate have the same meaning as SQL's IN(...)? I'm
> asking this, because I get results that I cannot explain:
>
> cqlsh:xpl1> select * from t1 where col2='bar1';
> pk | col1 | col2
> ------+------+------
> pk1b | foo1 | bar1
> pk1 | foo1 | bar1
> pk1a | foo1 | bar1
> pk1c | foo1 | bar1
>
> cqlsh:xpl1> select * from t1 where col2 in ('bar1', 'bar2') ;
> cqlsh:xpl1>
>
> The first query shows there are records with col2='bar1'. I would expect the
> second query return a superset of what the first query returned. However, it
> returns nothing!
>
> If this is intended behavior, could somebody please point me to where this is
> documented?
>
> == Complete example ==
> # Repeatable on Cassandra 1.1.4 or 1.1.5:
>
> [cqlsh 2.2.0 | Cassandra 1.1.5 | CQL spec 3.0.0 | Thrift protocol 19.32.0]
> Use HELP for help.
> cqlsh>
> cqlsh> create keyspace xpl1 WITH strategy_class ='SimpleStrategy' and strategy_options:replication_factor=1;
> cqlsh> use xpl1;
> cqlsh:xpl1> create table t1 (pk varchar primary key, col1 varchar, col2 varchar);
> cqlsh:xpl1> create index t1_c1 on t1(col1);
> cqlsh:xpl1> create index t1_c2 on t1(col2);
> cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1','foo1','bar1');
> cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1a','foo1','bar1');
> cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1b','foo1','bar1');
> cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk1c','foo1','bar1');
> cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk2','foo2','bar2');
> cqlsh:xpl1> insert into t1 (pk, col1, col2) values ('pk3','foo3','bar3');
> cqlsh:xpl1> select * from t1 where col2='bar1';
> pk | col1 | col2
> ------+------+------
> pk1b | foo1 | bar1
> pk1 | foo1 | bar1
> pk1a | foo1 | bar1
> pk1c | foo1 | bar1
>
> cqlsh:xpl1> select * from t1 where col2 in ('bar1', 'bar2') ;
> cqlsh:xpl1>
>
> BR
> Sergei
> --
> Sergei Petrunia, Software Developer
> Monty Program AB, http://askmonty.org
> Blog: http://s.petrunia.net/blog