[ https://issues.apache.org/jira/browse/CASSANDRA-8033?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tyler Hobbs updated CASSANDRA-8033: ----------------------------------- Component/s: Core Fix Version/s: 2.1.1 > 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 > Components: Core > Environment: ubuntu 14.04 lts, cassandra 2.1.0 > Reporter: Tuukka Mustonen > Assignee: Tyler Hobbs > Fix For: 2.1.1 > > > 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)