[CASSANDRA-12785] LIKE query with partition key restriction gives unexpected results

• Type: Bug
• Status: Resolved
• Priority: Low
• Resolution: Duplicate
• Component/s: Feature/SASI
• Severity: Low
• Assignee: Unassigned
• Reporter: Kelsey McKenna
• Created: 13/Oct/16 15:50
• Updated: 16/Apr/19 09:30
• Resolved: 13/Oct/16 15:57

Description

When trying to query a table using the LIKE keyword and a partial restriction of the partition key, unexpected results are given.

Either the query is returning the wrong data, or the query shouldn't be allowed since the partition key is being partially restricted. Please see the demonstration below:

DROP TABLE IF EXISTS example;
 
CREATE TABLE example (
    indextype text,
    indexref text,
    phrase text,
    PRIMARY KEY ((indextype, indexref), phrase)
);
 
CREATE CUSTOM INDEX example_contains ON example(phrase) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'CONTAINS' };
 
INSERT INTO example(indextype, indexref, phrase) VALUES('A', 'a1', 'potato');
INSERT INTO example(indextype, indexref, phrase) VALUES('A', 'a2', 'rubato');
INSERT INTO example(indextype, indexref, phrase) VALUES('B', 'a3', 'tomato');
 
SELECT * FROM example
 WHERE indextype='A'
  AND phrase LIKE '%ato%'
  allow filtering;
 

The expected output is

| indextype | indexref | phrase |
| A         | a2       | rubato |
| A         | a1       | potato |

The actual output is

| indextype | indexref | phrase |
| B         | a3       | tomato |
| A         | a2       | rubato |
| A         | a1       | potato |

Issue Links

• duplicates: CASSANDRA-12746 Wrong search results if clustering column has SASI index