Tuukka Mustonen created CASSANDRA-8033:
------------------------------------------
Summary: CONTAINS clause is ignored when index for compound
partition key part is in query
Key: CASSANDRA-8033
URL: https://issues.apache.org/jira/browse/CASSANDRA-8033
Project: Cassandra
Issue Type: Bug
Environment: ubuntu 14.04 lts, cassandra 2.1.0
Reporter: Tuukka Mustonen
With compound partition key, when you add index for one part and query by that
AND with CONTAINS, the CONTAINS clause does nothing.
Steps to reproduce:
{code}
-- drop existing data
DROP TABLE IF EXISTS test;
-- create data
CREATE TABLE test (id1 int, id2 int, tag text, items set<text>, PRIMARY KEY
((id1, id2), tag));
INSERT INTO test (id1, id2, tag, items) VALUES (1, 1, 'cars', {'ford',
'toyota'});
INSERT INTO test (id1, id2, tag, items) VALUES (1, 2, 'planes', {'airbus',
'boeing'});
INSERT INTO test (id1, id2, tag, items) VALUES (2, 1, 'cars', {'bmw', 'ford'});
-- if we create INDEX for items, query works ok
CREATE INDEX test_items ON test(items);
SELECT * FROM test WHERE items CONTAINS 'ford'; -- returns 2 rows
-- even this works now (but won't work later)
SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'ford' ALLOW FILTERING; --
returns 1 row
-- let's create the index on id1 instead
DROP INDEX test_items;
CREATE INDEX test_id1s ON test(id1);
-- these return all rows of id1 = 1 now, CONTAINS clause does nothing
SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'ford' ALLOW FILTERING; --
should return 1 row but returns 2
SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'doesnotexist' ALLOW
FILTERING; -- should return 0 rows but returns 2
-- add index back
CREATE INDEX test_items ON test(items);
-- no effect, same as before
SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'ford' ALLOW FILTERING; --
should return 1 row but returns 2
SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'doesnotexist' ALLOW
FILTERING; -- should return 0 rows but returns 2
{code}
Sample output:
{code}
cqlsh:stable> -- drop existing data
cqlsh:stable> DROP TABLE IF EXISTS test;
cqlsh:stable>
cqlsh:stable> -- create data
cqlsh:stable> CREATE TABLE test (id1 int, id2 int, tag text, items set<text>,
PRIMARY KEY ((id1, id2), tag));
cqlsh:stable> INSERT INTO test (id1, id2, tag, items) VALUES (1, 1, 'cars',
{'ford', 'toyota'});
cqlsh:stable> INSERT INTO test (id1, id2, tag, items) VALUES (1, 2, 'planes',
{'airbus', 'boeing'});
cqlsh:stable> INSERT INTO test (id1, id2, tag, items) VALUES (2, 1, 'cars',
{'bmw', 'ford'});
cqlsh:stable>
cqlsh:stable> -- if we create INDEX for items, query works ok
cqlsh:stable> CREATE INDEX test_items ON test(items);
cqlsh:stable> SELECT * FROM test WHERE items CONTAINS 'ford'; -- returns 2 rows
id1 | id2 | tag | items
-----+-----+------+--------------------
2 | 1 | cars | {'bmw', 'ford'}
1 | 1 | cars | {'ford', 'toyota'}
(2 rows)
cqlsh:stable>
cqlsh:stable> -- even this works now (but won't work later)
cqlsh:stable> SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'ford' ALLOW
FILTERING; -- returns 1 row
id1 | id2 | tag | items
-----+-----+------+--------------------
1 | 1 | cars | {'ford', 'toyota'}
(1 rows)
cqlsh:stable>
cqlsh:stable> -- let's create the index on id1 instead
cqlsh:stable> DROP INDEX test_items;
cqlsh:stable> CREATE INDEX test_id1s ON test(id1);
cqlsh:stable>
cqlsh:stable> -- these return all rows of id1 = 1 now, CONTAINS clause does
nothing
cqlsh:stable> SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'ford' ALLOW
FILTERING; -- should return 1 row but returns 2
id1 | id2 | tag | items
-----+-----+--------+----------------------
1 | 2 | planes | {'airbus', 'boeing'}
1 | 1 | cars | {'ford', 'toyota'}
(2 rows)
cqlsh:stable> SELECT * FROM test WHERE id1 = 1 AND items CONTAINS
'doesnotexist' ALLOW FILTERING; -- should return 0 rows but returns 2
id1 | id2 | tag | items
-----+-----+--------+----------------------
1 | 2 | planes | {'airbus', 'boeing'}
1 | 1 | cars | {'ford', 'toyota'}
(2 rows)
cqlsh:stable>
cqlsh:stable> -- add index back
cqlsh:stable> CREATE INDEX test_items ON test(items);
cqlsh:stable>
cqlsh:stable> -- no effect, same as before
cqlsh:stable> SELECT * FROM test WHERE id1 = 1 AND items CONTAINS 'ford' ALLOW
FILTERING; -- should return 1 row but returns 2
id1 | id2 | tag | items
-----+-----+--------+----------------------
1 | 2 | planes | {'airbus', 'boeing'}
1 | 1 | cars | {'ford', 'toyota'}
(2 rows)
cqlsh:stable> SELECT * FROM test WHERE id1 = 1 AND items CONTAINS
'doesnotexist' ALLOW FILTERING; -- should return 0 rows but returns 2
id1 | id2 | tag | items
-----+-----+--------+----------------------
1 | 2 | planes | {'airbus', 'boeing'}
1 | 1 | cars | {'ford', 'toyota'}
(2 rows)
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
