[jira] [Commented] (CASSANDRA-13130) Strange result of several list updates in a single request
[ https://issues.apache.org/jira/browse/CASSANDRA-13130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15907903#comment-15907903 ] Mikhail Krupitskiy commented on CASSANDRA-13130: Benjamin, thank you for the clarifications. Will try to follow your recommendations. > Strange result of several list updates in a single request > -- > > Key: CASSANDRA-13130 > URL: https://issues.apache.org/jira/browse/CASSANDRA-13130 > Project: Cassandra > Issue Type: Bug >Reporter: Mikhail Krupitskiy >Assignee: Benjamin Lerer >Priority: Trivial > Fix For: 2.2.x, 3.0.x, 3.11.x, 4.x > > > Let's assume that we have a row with the 'listColumn' column and value > \{1,2,3,4\}. > For me it looks logical to expect that the following two pieces of code will > ends up with the same result but it isn't so. > Code1: > {code} > UPDATE t SET listColumn[2] = 7, listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,7,8,4\} > Code2: > {code} > UPDATE t SET listColumn[2] = 7 WHERE id = 1; > UPDATE t SET listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,8,4\} > So the question is why Code1 and Code2 give different results? > Looks like Code1 should give the same result as Code2. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Commented] (CASSANDRA-13130) Strange result of several list updates in a single request
[ https://issues.apache.org/jira/browse/CASSANDRA-13130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15871978#comment-15871978 ] Mikhail Krupitskiy commented on CASSANDRA-13130: Thank you for the clarification! Such behaviour looks like not intuitive - will be very useful to have the description somewhere in Cassandra's docs. 1) Are there any plans to make the behaviour more intuitive like "the second value will win because it comes last"? 2) How the following query will work (does an order matter?)? {code}UPDATE t SET listColumn[2] = 8, listColumn = 7 + listColumn WHERE id = 1;{code} 3) Is there a general recommendation not to combine several updates to one column in the single query? > Strange result of several list updates in a single request > -- > > Key: CASSANDRA-13130 > URL: https://issues.apache.org/jira/browse/CASSANDRA-13130 > Project: Cassandra > Issue Type: Bug >Reporter: Mikhail Krupitskiy >Assignee: Benjamin Lerer >Priority: Trivial > > Let's assume that we have a row with the 'listColumn' column and value > \{1,2,3,4\}. > For me it looks logical to expect that the following two pieces of code will > ends up with the same result but it isn't so. > Code1: > {code} > UPDATE t SET listColumn[2] = 7, listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,7,8,4\} > Code2: > {code} > UPDATE t SET listColumn[2] = 7 WHERE id = 1; > UPDATE t SET listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,8,4\} > So the question is why Code1 and Code2 give different results? > Looks like Code1 should give the same result as Code2. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Comment Edited] (CASSANDRA-13130) Strange result of several list updates in a single request
[ https://issues.apache.org/jira/browse/CASSANDRA-13130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15870591#comment-15870591 ] Mikhail Krupitskiy edited comment on CASSANDRA-13130 at 2/16/17 7:46 PM: - {quote}When you issue a single query with two updates, the two updates are actually having the same timestamp which means that the update with the higher value will win.{quote} Could you please clarify what does "the higher value will win" mean? Does it mean that it's not defined which of two updates will be actually applied? was (Author: mkrupits_jb): >> When you issue a single query with two updates, the two updates are >> actually having the same timestamp which means that the update with the >> higher value will win. Could you please clarify what does "the higher value will win" mean? Does it mean that it's not defined which of two updates will be actually applied? > Strange result of several list updates in a single request > -- > > Key: CASSANDRA-13130 > URL: https://issues.apache.org/jira/browse/CASSANDRA-13130 > Project: Cassandra > Issue Type: Bug >Reporter: Mikhail Krupitskiy >Assignee: Benjamin Lerer >Priority: Trivial > > Let's assume that we have a row with the 'listColumn' column and value > \{1,2,3,4\}. > For me it looks logical to expect that the following two pieces of code will > ends up with the same result but it isn't so. > Code1: > {code} > UPDATE t SET listColumn[2] = 7, listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,7,8,4\} > Code2: > {code} > UPDATE t SET listColumn[2] = 7 WHERE id = 1; > UPDATE t SET listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,8,4\} > So the question is why Code1 and Code2 give different results? > Looks like Code1 should give the same result as Code2. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Commented] (CASSANDRA-13130) Strange result of several list updates in a single request
[ https://issues.apache.org/jira/browse/CASSANDRA-13130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15870591#comment-15870591 ] Mikhail Krupitskiy commented on CASSANDRA-13130: >> When you issue a single query with two updates, the two updates are >> actually having the same timestamp which means that the update with the >> higher value will win. Could you please clarify what does "the higher value will win" mean? Does it mean that it's not defined which of two updates will be actually applied? > Strange result of several list updates in a single request > -- > > Key: CASSANDRA-13130 > URL: https://issues.apache.org/jira/browse/CASSANDRA-13130 > Project: Cassandra > Issue Type: Bug >Reporter: Mikhail Krupitskiy >Assignee: Benjamin Lerer >Priority: Trivial > > Let's assume that we have a row with the 'listColumn' column and value > \{1,2,3,4\}. > For me it looks logical to expect that the following two pieces of code will > ends up with the same result but it isn't so. > Code1: > {code} > UPDATE t SET listColumn[2] = 7, listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,7,8,4\} > Code2: > {code} > UPDATE t SET listColumn[2] = 7 WHERE id = 1; > UPDATE t SET listColumn[2] = 8 WHERE id = 1; > {code} > Expected result: listColumn=\{1,2,8,4\} > Actual result: listColumn=\{1,2,8,4\} > So the question is why Code1 and Code2 give different results? > Looks like Code1 should give the same result as Code2. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Created] (CASSANDRA-13130) Strange result of several list updates in a single request
Mikhail Krupitskiy created CASSANDRA-13130: -- Summary: Strange result of several list updates in a single request Key: CASSANDRA-13130 URL: https://issues.apache.org/jira/browse/CASSANDRA-13130 Project: Cassandra Issue Type: Bug Reporter: Mikhail Krupitskiy Priority: Critical Let's assume that we have a row with the 'listColumn' column and value \{1,2,3,4\}. For me it looks logical to expect that the following two pieces of code will ends up with the same result but it isn't so. Code1: {code} UPDATE t SET listColumn[2] = 7, listColumn[2] = 8 WHERE id = 1; {code} Expected result: listColumn=\{1,2,8,4\} Actual result: listColumn=\{1,2,7,8,4\} Code2: {code} UPDATE t SET listColumn[2] = 7 WHERE id = 1; UPDATE t SET listColumn[2] = 8 WHERE id = 1; {code} Expected result: listColumn=\{1,2,8,4\} Actual result: listColumn=\{1,2,8,4\} So the question is why Code1 and Code2 give different results? Looks like Code1 should give the same result as Code2. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (CASSANDRA-12873) Cassandra can't restart after set NULL to a frozen list
Mikhail Krupitskiy created CASSANDRA-12873: -- Summary: Cassandra can't restart after set NULL to a frozen list Key: CASSANDRA-12873 URL: https://issues.apache.org/jira/browse/CASSANDRA-12873 Project: Cassandra Issue Type: Bug Reporter: Mikhail Krupitskiy Priority: Critical Cassandra 3.5. 1) Create a table with frozen list as one of columns. 2) Add a row where the column is NULL. 3) Stop Cassandra. 4) Run Cassandra. Cassandra unable to start with the following exception: {noformat} ERROR o.a.c.utils.JVMStabilityInspector - Exiting due to error while processing commit log during initialization. org.apache.cassandra.db.commitlog.CommitLogReplayer$CommitLogReplayException: Unexpected error deserializing mutation; saved to /var/folders/gl/bvj71v5d39339dlr8yf08drcgq/T/mutation5963614818028050337dat. This may be caused by replaying a mutation against a table with the same name but incompatible schema. Exception follows: org.apache.cassandra.serializers.MarshalException: Not enough bytes to read a list at org.apache.cassandra.db.commitlog.CommitLogReplayer.handleReplayError(CommitLogReplayer.java:611) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.db.commitlog.CommitLogReplayer.replayMutation(CommitLogReplayer.java:568) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.db.commitlog.CommitLogReplayer.replaySyncSection(CommitLogReplayer.java:521) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.db.commitlog.CommitLogReplayer.recover(CommitLogReplayer.java:407) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.db.commitlog.CommitLogReplayer.recover(CommitLogReplayer.java:236) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.db.commitlog.CommitLog.recover(CommitLog.java:192) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.db.commitlog.CommitLog.recover(CommitLog.java:172) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.service.CassandraDaemon.setup(CassandraDaemon.java:283) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.service.CassandraDaemon.activate(CassandraDaemon.java:551) [apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.service.CassandraDaemon.main(CassandraDaemon.java:680) [apache-cassandra-3.5.jar:3.5] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_71] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_71] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_71] at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_71] {noformat} Below is a script for steps #1, #2: {code} CREATE keyspace if not exists kmv WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor':'1'} ; USE kmv; CREATE TABLE if not exists kmv (id int, l frozen, PRIMARY KEY(id)); INSERT into kmv (id, l) values (1, null) ; {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-12662) OOM when using SASI index
[ https://issues.apache.org/jira/browse/CASSANDRA-12662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546363#comment-15546363 ] Mikhail Krupitskiy commented on CASSANDRA-12662: We've done a parallel experiment on another cluster (please let me know if it would be better to move it to a separate issue): Cassandra 3.5 cluster with 3 nodes and replication factor = 3. Each node: HDD, 4 CPU cores, 16g RAM, Xmx=4g. During a period of intensive write load nodes failed with OOM described in the issue. As a try to solve it we decreased 'max_compaction_flush_memory_in_mb' for our SASI indexes to 512Mb. After that nodes started to fail with the following: {noformat} org.apache.cassandra.io.FSReadError: java.io.IOException: Map failed at org.apache.cassandra.io.util.ChannelProxy.map(ChannelProxy.java:156) ~[apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.index.sasi.utils.MappedBuffer.(MappedBuffer.java:78) ~[apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.index.sasi.utils.MappedBuffer.(MappedBuffer.java:57) ~[apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.index.sasi.disk.OnDiskIndex.(OnDiskIndex.java:142) ~[apache-cassandra-3.5.jar:3.5] at org.apache.cassandra.index.sasi.disk.PerSSTableIndexWriter$Index.lambda$scheduleSegmentFlush$261(PerSSTableIndexWriter.java:262) ~[apache-cassandra-3.5.jar:3.5] at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_91] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[na:1.8.0_91] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[na:1.8.0_91] at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_91] Caused by: java.io.IOException: Map failed at sun.nio.ch.FileChannelImpl.map(FileChannelImpl.java:940) ~[na:1.8.0_91] at org.apache.cassandra.io.util.ChannelProxy.map(ChannelProxy.java:152) ~[apache-cassandra-3.5.jar:3.5] ... 8 common frames omitted Caused by: java.lang.OutOfMemoryError: Map failed at sun.nio.ch.FileChannelImpl.map0(Native Method) ~[na:1.8.0_91] at sun.nio.ch.FileChannelImpl.map(FileChannelImpl.java:937) ~[na:1.8.0_91] ... 9 common frames omitted {noformat} > OOM when using SASI index > - > > Key: CASSANDRA-12662 > URL: https://issues.apache.org/jira/browse/CASSANDRA-12662 > Project: Cassandra > Issue Type: Bug > Environment: Linux, 4 CPU cores, 16Gb RAM, Cassandra process utilizes > ~8Gb, of which ~4Gb is Java heap >Reporter: Maxim Podkolzine >Priority: Critical > Fix For: 3.x > > Attachments: memory-dump.png > > > 2.8Gb of the heap is taken by the index data, pending for flush (see the > screenshot). As a result the node fails with OOM. > Questions: > - Why can't Cassandra keep up with the inserted data and flush it? > - What resources/configuration should be changed to improve the performance? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (CASSANDRA-12746) Wrong search results if clustering column has SASI index
Mikhail Krupitskiy created CASSANDRA-12746: -- Summary: Wrong search results if clustering column has SASI index Key: CASSANDRA-12746 URL: https://issues.apache.org/jira/browse/CASSANDRA-12746 Project: Cassandra Issue Type: Bug Reporter: Mikhail Krupitskiy Priority: Critical Cassandra 3.5. Code snippet to reproduce the issue: {code} 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, PRIMARY KEY(id,c1)); CREATE CUSTOM INDEX ON kmv.kmv ( c1 ) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = { 'analyzed' : 'true', 'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false', 'mode' : 'CONTAINS' }; INSERT into kmv (id, c1) values (1, 'qweasd') ; INSERT into kmv (id, c1) values (1, 'uuu') ; INSERT into kmv (id, c1) values (2, 'qwe') ; {code} c1 is a clustering column with SASI index. An attempt to add a restriction for partition key AND for the clustering column leads to incorrect results: {code} SELECT c1 from kmv.kmv where id=1 and c1 like '%qwe%'; {code} Expected results: qweasd Actual results: qweasd, *uuu* Looks like a restriction for c1 was ignored. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Resolved] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mikhail Krupitskiy resolved CASSANDRA-12573. Resolution: Duplicate > 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] [Comment Edited] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15506944#comment-15506944 ] Mikhail Krupitskiy edited comment on CASSANDRA-12573 at 9/20/16 3:55 PM: - Thank you for the investigation. I've created an improvement request - CASSANDRA-12675. Closing 12573 as a duplicate. was (Author: mkrupits_jb): I've created an improvement request - CASSANDRA-12675. Closing 12573 as a duplicate. > 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
[jira] [Commented] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15506944#comment-15506944 ] Mikhail Krupitskiy commented on CASSANDRA-12573: I've created an improvement request - CASSANDRA-12675. Closing 12573 as a duplicate. > 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] [Created] (CASSANDRA-12675) SASI index. Support for '%' as a wildcard in the middle of LIKE pattern string.
Mikhail Krupitskiy created CASSANDRA-12675: -- Summary: SASI index. Support for '%' as a wildcard in the middle of LIKE pattern string. Key: CASSANDRA-12675 URL: https://issues.apache.org/jira/browse/CASSANDRA-12675 Project: Cassandra Issue Type: Improvement Reporter: Mikhail Krupitskiy The improvement is filed based on a discussion from https://issues.apache.org/jira/browse/CASSANDRA-12573. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15496031#comment-15496031 ] Mikhail Krupitskiy commented on CASSANDRA-12573: I agree that it could be applicable for NonTokeninzingAnalyzer only. --> why should we change it ? To let users to query strings by more flexible patterns. E.g. to find '123foo456bar789' by the '%foo%bar%' 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 >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] [Commented] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15496013#comment-15496013 ] Mikhail Krupitskiy commented on CASSANDRA-12573: Ok, thanks for clarification...I need some time to realize it.. Am I right that '%' interprets as a wildcard only when it is a first or last character and it is an expected behaviour? If so then are there any plans to change it (interpret it as a wildcard in the middle of a string too)? > 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
[jira] [Commented] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15493766#comment-15493766 ] Mikhail Krupitskiy commented on CASSANDRA-12573: Let's try to clarify things. As I understand there are two different issues: 1) Incorrect processing of escaped '%'. 2) Incorrect processing of %foo%bar% patterns without any escaping. This issue (12573) is not about escaping and all requests from the experiments meaningly don't do any escaping. > 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
[jira] [Commented] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15493690#comment-15493690 ] Mikhail Krupitskiy commented on CASSANDRA-12573: Yes, but in experiments 2,3,4 we still have non-empty results. > 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] [Commented] (CASSANDRA-12573) SASI index. Incorrect results for '%foo%bar%'-like search pattern.
[ https://issues.apache.org/jira/browse/CASSANDRA-12573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15493518#comment-15493518 ] Mikhail Krupitskiy commented on CASSANDRA-12573: As I see it doesn't explain results of experiments described in the description. E.g. A request for '%w%a%' returns several results without '%' at all. > 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] [Created] (CASSANDRA-12621) How to query '%' character using LIKE operator in Cassandra 3.7?
Mikhail Krupitskiy created CASSANDRA-12621: -- Summary: How to query '%' character using LIKE operator in Cassandra 3.7? Key: CASSANDRA-12621 URL: https://issues.apache.org/jira/browse/CASSANDRA-12621 Project: Cassandra Issue Type: Bug Reporter: Mikhail Krupitskiy Priority: Critical I use Cassandra 3.7 and have a text column with SASI index. Let's assume that I want to find column values that contain '%' character somewhere in the middle. The problem is that '%' is a command char for LIKE clauses. How to escape '%' char in a query like LIKE '%%%'? Here is a test script: 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 = { 'analyzed' : 'true', 'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false', 'mode' : 'CONTAINS' }; INSERT into kmv (id, c1, c2) values (1, 'f22', 'qwe%asd'); SELECT c2 from kmv.kmv where c2 like '%$$%$$%'; The select query returns nothing. -- 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.
[ 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.
[ 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.
[ 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.
[ 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.
[ 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.
[ 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.
[ 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.
[ 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)
[jira] [Created] (CASSANDRA-12573) SASI index. No results for '%foo%bar%'-like search pattern.
Mikhail Krupitskiy created CASSANDRA-12573: -- Summary: 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 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. -- This message was sent by Atlassian JIRA (v6.3.4#6332)