Clustering key and secondary index behavior changed between 2.0.11 and 3.3.0

[julien muller <ju...@gmail.com>]

Hello,

I noticed the following change in behavior while migrating from 2.0.11:
Elements of the clustering key seems to not be secondary indexable anymore.
Anyone could give me an insight about this issue? Or point me to documents
relative to this evolution?

There is a sample table with some data:
CREATE TABLE table1 (
    name text,
    class int,
    inter text,
    power int,
    PRIMARY KEY (name, class, inter)
) WITH CLUSTERING ORDER BY (class DESC, inter ASC);
INSERT INTO table1 (name, class, inter, power) VALUES ('R1',1, 'int1',13);
INSERT INTO table1 (name, class, inter, power) VALUES ('R1',2, 'int1',18);
INSERT INTO table1 (name, class, inter, power) VALUES ('R1',3, 'int1',37);
INSERT INTO table1 (name, class, inter, power) VALUES ('R1',4, 'int1',49);

In version 2.0.11, I used to have a secondary index on inter, that allowed
me to make fast queries on this table:
CREATE INDEX table1_inter ON table1 (inter);
SELECT * FROM table1 where name='R1' AND class>0 AND class<4 AND inter='int1'
ALLOW FILTERING;

While testing on 3.3.0, I get the following message:
*Cluste*
*ring column "inter" cannot be restricted (preceding column "class" is
restricted by a non-EQ relation)*
It seems to only be considered as a key and the index and ALLOW FILTERING
are not taken into account anymore (as it was in 2.0.11).

Further tests confused me, as I found an ugly workaround: If I duplicate
the column inter as a regular column, I can simply query it with the
secondary index and no ALLOW FILTERING. It looks like the behavior I would
anticipate and do not understand why it does not work on inter only because
it is a clustering key.

Any insight highly appreciated!

Julien

Re: Clustering key and secondary index behavior changed between 2.0.11 and 3.3.0

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

I'm surprised this would have fall through the cracks but that certainly
look like a regression (a bug). If you can reproduce on 3.0.4 (just to make
sure we haven't fixed it recently), then please open a ticket in
https://issues.apache.org/jira/browse/CASSANDRA/ with your repro steps.

On Tue, Apr 5, 2016 at 10:47 AM, julien muller <ju...@gmail.com>
wrote:

> Hello,
>
> I noticed the following change in behavior while migrating from 2.0.11:
> Elements of the clustering key seems to not be secondary indexable anymore.
> Anyone could give me an insight about this issue? Or point me to documents
> relative to this evolution?
>
> There is a sample table with some data:
> CREATE TABLE table1 (
>     name text,
>     class int,
>     inter text,
>     power int,
>     PRIMARY KEY (name, class, inter)
> ) WITH CLUSTERING ORDER BY (class DESC, inter ASC);
> INSERT INTO table1 (name, class, inter, power) VALUES ('R1',1, 'int1',13);
> INSERT INTO table1 (name, class, inter, power) VALUES ('R1',2, 'int1',18);
> INSERT INTO table1 (name, class, inter, power) VALUES ('R1',3, 'int1',37);
> INSERT INTO table1 (name, class, inter, power) VALUES ('R1',4, 'int1',49);
>
> In version 2.0.11, I used to have a secondary index on inter, that allowed
> me to make fast queries on this table:
> CREATE INDEX table1_inter ON table1 (inter);
> SELECT * FROM table1 where name='R1' AND class>0 AND class<4 AND
> inter='int1' ALLOW FILTERING;
>
> While testing on 3.3.0, I get the following message:
> *Cluste*
> *ring column "inter" cannot be restricted (preceding column "class" is
> restricted by a non-EQ relation)*
> It seems to only be considered as a key and the index and ALLOW FILTERING
> are not taken into account anymore (as it was in 2.0.11).
>
> Further tests confused me, as I found an ugly workaround: If I duplicate
> the column inter as a regular column, I can simply query it with the
> secondary index and no ALLOW FILTERING. It looks like the behavior I would
> anticipate and do not understand why it does not work on inter only because
> it is a clustering key.
>
> Any insight highly appreciated!
>
> Julien
>