cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Benjamin Lerer (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CASSANDRA-8033) Filtering for CONTAINS on sets is broken
Date Mon, 06 Oct 2014 10:11:33 GMT

    [ https://issues.apache.org/jira/browse/CASSANDRA-8033?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14160154#comment-14160154
] 

Benjamin Lerer commented on CASSANDRA-8033:
-------------------------------------------

+1

> Filtering for CONTAINS on sets is broken
> ----------------------------------------
>
>                 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
>
>         Attachments: 8033-2.1.txt
>
>
> 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)

Mime
View raw message