[
https://issues.apache.org/jira/browse/CASSANDRA-9631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Benjamin Lerer updated CASSANDRA-9631:
--------------------------------------
Component/s: CQL
> Unnecessary required filtering for query on indexed clustering key
> ------------------------------------------------------------------
>
> Key: CASSANDRA-9631
> URL: https://issues.apache.org/jira/browse/CASSANDRA-9631
> Project: Cassandra
> Issue Type: Bug
> Components: CQL
> Environment: Cassandra 2.1.6 vanilla; 3-node local cluster; OSX
> Yosemite 10.10.3; Installed with CCM.
> Reporter: Kevin Deldycke
> Assignee: Benjamin Lerer
> Labels: CQL, query, secondaryIndex
> Fix For: 2.0.17, 2.1.8, 2.2.0 rc2
>
> Attachments: 9631-2.0.txt, 9631-2.1.txt, 9631-2.2.txt
>
>
> Let's create and populate a simple table composed of one partition key {{a}},
> two clustering keys {{b}} & {{c}}, and one secondary index on a standard
> column {{e}}:
> {code:sql}
> $ cqlsh 127.0.0.1
> Connected to test21 at 127.0.0.1:9160.
> [cqlsh 4.1.1 | Cassandra 2.1.6-SNAPSHOT | CQL spec 3.1.1 | Thrift protocol
> 19.39.0]
> Use HELP for help.
> cqlsh> CREATE KEYSPACE test WITH REPLICATION={'class': 'SimpleStrategy',
> 'replication_factor': 3};
> cqlsh> CREATE TABLE test.table1 (
> ... a int,
> ... b int,
> ... c int,
> ... d int,
> ... e int,
> ... PRIMARY KEY (a, b, c)
> ... );
> cqlsh> CREATE INDEX table1_e ON test.table1 (e);
> cqlsh> INSERT INTO test.table1 (a, b, c, d, e) VALUES (1, 1, 1, 1, 1);
> (...)
> cqlsh> SELECT * FROM test.table1;
> a | b | c | d | e
> ---+---+---+---+---
> 1 | 1 | 1 | 1 | 1
> 1 | 1 | 2 | 2 | 2
> 1 | 1 | 3 | 3 | 3
> 1 | 2 | 1 | 1 | 3
> 1 | 3 | 1 | 1 | 1
> 2 | 4 | 1 | 1 | 1
> (6 rows)
> {code}
> With such a schema, I am allowed to query on the indexed column without
> filtering by providing the first two elements of the primary key:
> {code:sql}
> cqlsh> SELECT * FROM test.table1 WHERE a=1 AND b=1 AND e=3;
> a | b | c | d | e
> ---+---+---+---+---
> 1 | 1 | 3 | 3 | 3
> (1 rows)
> {code}
> Let's now introduce an index on the first clustering key:
> {code:sql}
> cqlsh> CREATE INDEX table1_b ON test.table1 (b);
> {code}
> Now, I expect the same query as above to work without filtering, but it's not:
> {code:sql}
> cqlsh> SELECT * FROM test.table1 WHERE a=1 AND b=1 AND e=3;
> Bad Request: Cannot execute this query as it might involve data filtering and
> thus may have unpredictable performance. If you want to execute this query
> despite the performance unpredictability, use ALLOW FILTERING
> {code}
> I think this is a bug on the way secondary indexes are accounted for when
> checking for unfiltered queries.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
