cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tuukka Mustonen (JIRA)" <j...@apache.org>
Subject [jira] [Created] (CASSANDRA-8033) CONTAINS clause is ignored when index for compound partition key part is in query
Date Wed, 01 Oct 2014 13:07:34 GMT
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)

Mime
View raw message