cql 3 qualification failing?

[Greg Fausak <gr...@named.com>]

I have playing around with composite CFs, I have one declared:

create columnfamily
    at_event_ac_c
(
    ac_event_id int,
    ac_creation timestamp,
    ac_action text,
    ac_addr text,
    ac_advisory_id text,
    ac_c text,
...
    ev_sev text,
...
    ev_total text,
    ev_url text,
    ev_used text,
    toast text,
    fw text,
    name text,
    resp text,
    size text,
    PRIMARY KEY (ac_c, ac_creation)
) with compression_parameters:sstable_compression = '';

So, my main primary key is on the ac_c column, text, and
the secondary composite key is on ac_creation, which is a date.  These
queries perform correctly:

select * from at_event_ac_c where ac_c = '1234';

select * from at_event_ac_c where ac_c = '1234' and ac_creation >
'2012-07-15' and ac_creation < '2012-07-18';

What's weird is I can't qualify on a non-indexed column, like:

select * from at_event_ac_c where ac_c = '1234' and ac_creation >
'2012-07-15' and ac_creation < '2012-07-18'
   and ev_sev = 2;

I get an error:
Bad Request: No indexed columns present in by-columns clause with Equal operator

But, I just attended a class on this.  I thought that once I used my
indices the remaining qualifications would be satisfied via a filtering
method.  Obviously this is incorrect.  Is there a way to 'filter' results?

-g

Re: cql 3 qualification failing?

[Sylvain Lebresne <sy...@datastax.com>]

On Thu, Jun 14, 2012 at 7:04 PM, Greg Fausak <gr...@named.com> wrote:
> But, I just attended a class on this.  I thought that once I used my
> indices the remaining qualifications would be satisfied via a filtering
> method.

The actual rule is that you need to at least qualify one of the indexed column
with an EQUAL. Then, if that is satisfied, you can indeed add filtering on
non-indexed columns. In you example, the EQUAL is on a non-indexed
column, so this won't work. For the request to work, you would need to
index ac_c (but then you wouldn't need to index ac_creation for that specific
request).

--
Sylvain

Re: cql 3 qualification failing?

[aaron morton <aa...@thelastpickle.com>]

> So, my main primary key is on the ac_c column, text, and
> the secondary composite key is on ac_creation, which is a date.  These
> queries perform correctly:
In your CF there is only one primary key (aka row key), it is a composite of ac_c and ac_creation.

> select * from at_event_ac_c where ac_c = '1234' and ac_creation >
> '2012-07-15' and ac_creation < '2012-07-18'
>   and ev_sev = 2;

See 
"WHERE clauses can include greater-than and less-than comparisons on columns other than the first. As long as all previous key-component columns have already been identified with strict = comparisons, the last given key component column can be any sort of comparison." 
http://www.datastax.com/docs/1.1/references/cql/SELECT

You need to specify both the ac_c and ac_creation values using equality, or specify neither. 

Hope that helps. 


-----------------
Aaron Morton
Freelance Developer
@aaronmorton
http://www.thelastpickle.com

On 15/06/2012, at 5:04 AM, Greg Fausak wrote:

> I have playing around with composite CFs, I have one declared:
> 
> create columnfamily
>    at_event_ac_c
> (
>    ac_event_id int,
>    ac_creation timestamp,
>    ac_action text,
>    ac_addr text,
>    ac_advisory_id text,
>    ac_c text,
> ...
>    ev_sev text,
> ...
>    ev_total text,
>    ev_url text,
>    ev_used text,
>    toast text,
>    fw text,
>    name text,
>    resp text,
>    size text,
>    PRIMARY KEY (ac_c, ac_creation)
> ) with compression_parameters:sstable_compression = '';
> 
> So, my main primary key is on the ac_c column, text, and
> the secondary composite key is on ac_creation, which is a date.  These
> queries perform correctly:
> 
> select * from at_event_ac_c where ac_c = '1234';
> 
> select * from at_event_ac_c where ac_c = '1234' and ac_creation >
> '2012-07-15' and ac_creation < '2012-07-18';
> 
> What's weird is I can't qualify on a non-indexed column, like:
> 
> select * from at_event_ac_c where ac_c = '1234' and ac_creation >
> '2012-07-15' and ac_creation < '2012-07-18'
>   and ev_sev = 2;
> 
> I get an error:
> Bad Request: No indexed columns present in by-columns clause with Equal operator
> 
> But, I just attended a class on this.  I thought that once I used my
> indices the remaining qualifications would be satisfied via a filtering
> method.  Obviously this is incorrect.  Is there a way to 'filter' results?
> 
> -g