You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Clint Kelly <cl...@gmail.com> on 2014/02/28 06:57:30 UTC
CQL: Any way to have inequalities on multiple clustering columns in a
WHERE clause?
All,
Is there any way to have inequalities comparisons on multiple clustering
columns in a WHERE clause in CQL? For example, I'd like to do:
select * from foo where fam = 'Info' and qual > 'A' and qual < 'D' and
version > 2013 ALLOW FILTERING;
I get an error:
Bad Request: PRIMARY KEY part version cannot be restricted (preceding
part qual is either not restricted or by a non-EQ relation)
when I try this. Is there any way to make a query like this work? I
understand that this query will not be a nice, continuous scan of data, but
I'd rather have a slower query than have to do all of this filtering on the
client side. Any other suggestions?
BTW my table looks like this:
CREATE TABLE foo (
key text,
fam text,
qual text,
version int,
val text,
PRIMARY KEY (key, fam, qual, version)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};
Best regards,
Clint
Re: CQL: Any way to have inequalities on multiple clustering columns
in a WHERE clause?
Posted by Clint Kelly <cl...@gmail.com>.
Yes, thank you!!!!!!!!!!!!!
On Thu, Feb 27, 2014 at 10:26 PM, DuyHai Doan <do...@gmail.com> wrote:
> Clint, what you want is this :
> https://issues.apache.org/jira/browse/CASSANDRA-4851
>
> select * from foo where key=something and fam = 'Info' and (qual,version)
> > ('A',2013) and qual < 'D' ALLOW FILTERING
>
>
> On Fri, Feb 28, 2014 at 6:57 AM, Clint Kelly <cl...@gmail.com>wrote:
>
>> All,
>>
>> Is there any way to have inequalities comparisons on multiple clustering
>> columns in a WHERE clause in CQL? For example, I'd like to do:
>>
>> select * from foo where fam = 'Info' and qual > 'A' and qual < 'D' and
>> version > 2013 ALLOW FILTERING;
>>
>> I get an error:
>>
>> Bad Request: PRIMARY KEY part version cannot be restricted (preceding
>> part qual is either not restricted or by a non-EQ relation)
>>
>> when I try this. Is there any way to make a query like this work? I
>> understand that this query will not be a nice, continuous scan of data, but
>> I'd rather have a slower query than have to do all of this filtering on the
>> client side. Any other suggestions?
>>
>> BTW my table looks like this:
>>
>> CREATE TABLE foo (
>> key text,
>> fam text,
>> qual text,
>> version int,
>> val text,
>> PRIMARY KEY (key, fam, qual, version)
>> ) WITH
>> bloom_filter_fp_chance=0.010000 AND
>> caching='KEYS_ONLY' AND
>> comment='' AND
>> dclocal_read_repair_chance=0.000000 AND
>> gc_grace_seconds=864000 AND
>> index_interval=128 AND
>> read_repair_chance=0.100000 AND
>> replicate_on_write='true' AND
>> populate_io_cache_on_flush='false' AND
>> default_time_to_live=0 AND
>> speculative_retry='99.0PERCENTILE' AND
>> memtable_flush_period_in_ms=0 AND
>> compaction={'class': 'SizeTieredCompactionStrategy'} AND
>> compression={'sstable_compression': 'LZ4Compressor'};
>>
>> Best regards,
>> Clint
>>
>
>
Re: CQL: Any way to have inequalities on multiple clustering columns
in a WHERE clause?
Posted by DuyHai Doan <do...@gmail.com>.
Clint, what you want is this :
https://issues.apache.org/jira/browse/CASSANDRA-4851
select * from foo where key=something and fam = 'Info' and (qual,version) >
('A',2013) and qual < 'D' ALLOW FILTERING
On Fri, Feb 28, 2014 at 6:57 AM, Clint Kelly <cl...@gmail.com> wrote:
> All,
>
> Is there any way to have inequalities comparisons on multiple clustering
> columns in a WHERE clause in CQL? For example, I'd like to do:
>
> select * from foo where fam = 'Info' and qual > 'A' and qual < 'D' and
> version > 2013 ALLOW FILTERING;
>
> I get an error:
>
> Bad Request: PRIMARY KEY part version cannot be restricted (preceding
> part qual is either not restricted or by a non-EQ relation)
>
> when I try this. Is there any way to make a query like this work? I
> understand that this query will not be a nice, continuous scan of data, but
> I'd rather have a slower query than have to do all of this filtering on the
> client side. Any other suggestions?
>
> BTW my table looks like this:
>
> CREATE TABLE foo (
> key text,
> fam text,
> qual text,
> version int,
> val text,
> PRIMARY KEY (key, fam, qual, version)
> ) WITH
> bloom_filter_fp_chance=0.010000 AND
> caching='KEYS_ONLY' AND
> comment='' AND
> dclocal_read_repair_chance=0.000000 AND
> gc_grace_seconds=864000 AND
> index_interval=128 AND
> read_repair_chance=0.100000 AND
> replicate_on_write='true' AND
> populate_io_cache_on_flush='false' AND
> default_time_to_live=0 AND
> speculative_retry='99.0PERCENTILE' AND
> memtable_flush_period_in_ms=0 AND
> compaction={'class': 'SizeTieredCompactionStrategy'} AND
> compression={'sstable_compression': 'LZ4Compressor'};
>
> Best regards,
> Clint
>