[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Alex Petrov (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Alex Petrov updated CASSANDRA-12573:

Assignee: (was: Alex Petrov)

> SASI index. Incorrect results for '%foo%bar%'-like search pattern. 
> ---
>
> Key: CASSANDRA-12573
> URL: https://issues.apache.org/jira/browse/CASSANDRA-12573
> Project: Cassandra
>  Issue Type: Bug
>Reporter: Mikhail Krupitskiy
>Priority: Critical
>  Labels: sasi
>
> We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests 
> with "LIKE '%foo%bar%'" constraints on a column with SASI index.
> Below are few experiments that show this behaviour.
> Experiment 1:
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: no rows.
> Experiment 2 (NOTE: definition of index is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: asdqwe, qweasd, qwea1.
> Experiment 3 (NOTE: primary key is compound now and inserted data was 
> changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: qwe, qweasd, qwea1, 1qwe, asdqwe.
> Experiment 4 (NOTE: search criteria is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w22%a%';
> {noformat}
> Expected result: no rows.
> Actual result: qweasd, qwea1, asdqwe.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Alex Petrov (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Alex Petrov updated CASSANDRA-12573:

Labels: sasi  (was: )

> SASI index. Incorrect results for '%foo%bar%'-like search pattern. 
> ---
>
> Key: CASSANDRA-12573
> URL: https://issues.apache.org/jira/browse/CASSANDRA-12573
> Project: Cassandra
>  Issue Type: Bug
>Reporter: Mikhail Krupitskiy
>Assignee: Alex Petrov
>Priority: Critical
>  Labels: sasi
>
> We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests 
> with "LIKE '%foo%bar%'" constraints on a column with SASI index.
> Below are few experiments that show this behaviour.
> Experiment 1:
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: no rows.
> Experiment 2 (NOTE: definition of index is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: asdqwe, qweasd, qwea1.
> Experiment 3 (NOTE: primary key is compound now and inserted data was 
> changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: qwe, qweasd, qwea1, 1qwe, asdqwe.
> Experiment 4 (NOTE: search criteria is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w22%a%';
> {noformat}
> Expected result: no rows.
> Actual result: qweasd, qwea1, asdqwe.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Arunkumar M (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Arunkumar M updated CASSANDRA-12573:

Assignee: (was: Arunkumar M)

> SASI index. Incorrect results for '%foo%bar%'-like search pattern. 
> ---
>
> Key: CASSANDRA-12573
> URL: https://issues.apache.org/jira/browse/CASSANDRA-12573
> Project: Cassandra
>  Issue Type: Bug
>Reporter: Mikhail Krupitskiy
>Priority: Critical
>
> We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests 
> with "LIKE '%foo%bar%'" constraints on a column with SASI index.
> Below are few experiments that show this behaviour.
> Experiment 1:
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: no rows.
> Experiment 2 (NOTE: definition of index is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: asdqwe, qweasd, qwea1.
> Experiment 3 (NOTE: primary key is compound now and inserted data was 
> changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: qwe, qweasd, qwea1, 1qwe, asdqwe.
> Experiment 4 (NOTE: search criteria is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;
> select c2 from kmv.kmv where c2 like '%w22%a%';
> {noformat}
> Expected result: no rows.
> Actual result: qweasd, qwea1, asdqwe.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Description: 
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
"LIKE '%foo%bar%'" constraints on a column with SASI index.
Below are few experiments that show this behaviour.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2 (NOTE: definition of index is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (2, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (3, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (4, 'f24', '1qwe') ;
insert into kmv (id, c1, c2) values (5, 'f25', 'asdqwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: asdqwe, qweasd, qwea1.

Experiment 3 (NOTE: primary key is compound now and inserted data was changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: qwe, qweasd, qwea1, 1qwe, asdqwe.

Experiment 4 (NOTE: search criteria is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
insert into kmv (id, c1, c2) values (1, 'f25', 'asdqwe') ;

select c2 from kmv.kmv where c2 like '%w22%a%';
{noformat}
Expected result: no rows.
Actual result: qweasd, qwea1, asdqwe.

  was:
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
"LIKE '%foo%bar%'" constraints on a column with SASI index.
Below are few experiments that show this behaviour.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no 

[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Priority: Critical  (was: Major)

> SASI index. Incorrect results for '%foo%bar%'-like search pattern. 
> ---
>
> Key: CASSANDRA-12573
> URL: https://issues.apache.org/jira/browse/CASSANDRA-12573
> Project: Cassandra
>  Issue Type: Bug
>Reporter: Mikhail Krupitskiy
>Assignee: Arunkumar M
>Priority: Critical
>
> We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests 
> with 'LIKE '%foo%bar%'' constraints on a column with SASI index.
> Below are few experiments that shows this behaviour.
> Experiment 1:
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: no rows.
> Experiment 2 (NOTE: definition of index is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: 1qwe.
> Experiment 3 (NOTE: primary key is compound now):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: qwe, qweasd, qwea1, 1qwe.
> Experiment 4 (NOTE: search criteria is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w22%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: qweasd, qwea1.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Description: 
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
"LIKE '%foo%bar%'" constraints on a column with SASI index.
Below are few experiments that show this behaviour.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2 (NOTE: definition of index is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: 1qwe.

Experiment 3 (NOTE: primary key is compound now):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: qwe, qweasd, qwea1, 1qwe.

Experiment 4 (NOTE: search criteria is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w22%a%';
{noformat}
Expected result: no rows.
Actual result: qweasd, qwea1.

  was:
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
'LIKE '%foo%bar%'' constraints on a column with SASI index.
Below are few experiments that shows this behaviour.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2 (NOTE: definition of index is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Description: 
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
'LIKE '%foo%bar%'' constraints on a column with SASI index.
Below are few experiments that shows this behaviour.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2 (NOTE: definition of index is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: 1qwe.

Experiment 3 (NOTE: primary key is compound now):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: qwe, qweasd, qwea1, 1qwe.

Experiment 4 (NOTE: search criteria is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w22%a%';
{noformat}
Expected result: no rows.
Actual result: qweasd, qwea1.

  was:
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
'LIKE '%foo%bar%'' constraints on a column with SASI index.
Below are few experiments that shows this behaviour.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2 (NOTE: definition of index is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

[jira] [Updated] (CASSANDRA-12573) SASI index. No results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Description: 
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
'LIKE '%foo%bar%'' constraints on a column with SASI index.
Below are few experiments that shows this behaviour.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2 (NOTE: definition of index is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: 1qwe.

Experiment 3 (NOTE: primary key is compound now):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: qwe, qweasd, qwea1, 1qwe.

Experiment 4 (NOTE: search criteria is changed):
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w22%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: qweasd, qwea1.

  was:
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
'LIKE '%foo%bar%'' constraints on a column with SASI index.
Below are few experiments 

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 

[jira] [Updated] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Summary: SASI index. Incorrect results for '%foo%bar%'-like search pattern. 
  (was: SASI index. No results for '%foo%bar%'-like search pattern. )

> SASI index. Incorrect results for '%foo%bar%'-like search pattern. 
> ---
>
> Key: CASSANDRA-12573
> URL: https://issues.apache.org/jira/browse/CASSANDRA-12573
> Project: Cassandra
>  Issue Type: Bug
>Reporter: Mikhail Krupitskiy
>Assignee: Arunkumar M
>
> We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests 
> with 'LIKE '%foo%bar%'' constraints on a column with SASI index.
> Below are few experiments that shows this behaviour.
> Experiment 1:
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: no rows.
> Experiment 2 (NOTE: definition of index is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: 1qwe.
> Experiment 3 (NOTE: primary key is compound now):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: qwe, qweasd, qwea1, 1qwe.
> Experiment 4 (NOTE: search criteria is changed):
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, 
> c1));
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w22%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: qweasd, qwea1.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Updated] (CASSANDRA-12573) SASI index. No results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Description: 
We use Cassandra 3.7 and have faced a strange behaviour of SELECT requests with 
'LIKE '%foo%bar%'' constraints on a column with SASI index.
Below are few experiments 

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: 1qwe.

Experiment 3:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: qwe, qweasd, qwea1, 1qwe.

Experiment 4:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w22%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: qweasd, qwea1.

  was:
Cassandra 3.7.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;

[jira] [Updated] (CASSANDRA-12573) SASI index. No results for '%foo%bar%'-like search pattern.

[Mikhail Krupitskiy (JIRA)]

 [ 
https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mikhail Krupitskiy updated CASSANDRA-12573:
---
Description: 
Cassandra 3.7.

Experiment 1:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: no rows.


Experiment 2:
{noformat}
drop keyspace if exists kmv;
create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
'SimpleStrategy', 'replication_factor':'1'} ;

use kmv;

CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);

CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
 'mode': 'CONTAINS',
 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
 'analyzed': 'true'
};

insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;

select c2 from kmv.kmv where c2 like '%w%a%';
{noformat}
Expected result: qweasd, qwea1.
Actual result: 1qwe.

  was:
Cassandra 3.7.

1) Create index for String column:
{noformat}
CREATE CUSTOM INDEX index_name ON Table (Column) USING 
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS'};
{noformat}
2) Insert some rows where Column contains 'foo' and 'bar'. E.g. '1foo2bar3'.

3) Try to query rows where Column contains 'foo' and 'bar'.
Request {noformat}select * from Table where Column like '%foo%bar%'{noformat} 
returns *no* rows. 


> SASI index. No results for '%foo%bar%'-like search pattern. 
> 
>
> Key: CASSANDRA-12573
> URL: https://issues.apache.org/jira/browse/CASSANDRA-12573
> Project: Cassandra
>  Issue Type: Bug
>Reporter: Mikhail Krupitskiy
>Assignee: Arunkumar M
>
> Cassandra 3.7.
> Experiment 1:
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: no rows.
> Experiment 2:
> {noformat}
> drop keyspace if exists kmv;
> create keyspace if not exists kmv WITH REPLICATION = { 'class' : 
> 'SimpleStrategy', 'replication_factor':'1'} ;
> use kmv;
> CREATE TABLE if not exists kmv (id int primary key, c1 text, c2 text);
> CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
>  'mode': 'CONTAINS',
>  'analyzer_class': 
> 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
>  'analyzed': 'true'
> };
> insert into kmv (id, c1, c2) values (1, 'f21', 'qwe') ;
> insert into kmv (id, c1, c2) values (1, 'f22', 'qweasd') ;
> insert into kmv (id, c1, c2) values (1, 'f23', 'qwea1') ;
> insert into kmv (id, c1, c2) values (1, 'f24', '1qwe') ;
> select c2 from kmv.kmv where c2 like '%w%a%';
> {noformat}
> Expected result: qweasd, qwea1.
> Actual result: 1qwe.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)