[jira] [Commented] (CASSANDRA-11734) Enable partition component index for SASI
[
https://issues.apache.org/jira/browse/CASSANDRA-11734?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15310235#comment-15310235
]
Maciej Iwanowski commented on CASSANDRA-11734:
--
I'm facing similar issue with Cassandra 3.5. It is possible to create SASI
index on a field that is a part of composite primary key. Consider following
table:
{code:sql}
CREATE TABLE db.broken_table (
text_field text,
integer_field int,
another_text_field text,
date timestamp,
some_random_text text,
PRIMARY KEY ((text_field, integer_field, another_text_field), date)
) WITH CLUSTERING ORDER BY (date ASC)
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND comment = ''
AND compaction = {'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
'max_threshold': '32', 'min_threshold': '4'}
AND compression = {'chunk_length_in_kb': '64', 'class':
'org.apache.cassandra.io.compress.LZ4Compressor'}
AND crc_check_chance = 1.0
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = '99PERCENTILE';
CREATE CUSTOM INDEX broken ON db.broken_table (text_field) USING
'org.apache.cassandra.index.sasi.SASIIndex';
CREATE CUSTOM INDEX working ON db.broken_table (some_random_text) USING
'org.apache.cassandra.index.sasi.SASIIndex';
{code}
That has some data inserted:
{code:sql}
INSERT INTO broken_table (text_field, integer_field , another_text_field , date
, some_random_text ) VALUES ('Lorem ipsum dolor sit amet, consectetur
adipiscing elit. Vivamus a hendrerit enim. Aenean ultricies ex orci, vitae
pellentesque leo consequat eget.', 303, 'Lorem ipsum dolor sit amet,
consectetur adipiscing elit. Vivamus a hendrerit enim. Aenean ultricies ex
orci, vitae pellentesque leo consequat eget.', toTimestamp(now()), 'Lorem ipsum
dolor sit amet, consectetur adipiscing elit. Vivamus a hendrerit enim. Aenean
ultricies ex orci, vitae pellentesque leo consequat eget.');
{code}
Following query returns expected results:
{code:sql}
select integer_field from broken_table where some_random_text like 'Lorem%';
{code}
Following queries return empty results:
{code:sql}
select integer_field from broken_table where text_field like 'Lorem%';
select integer_field from broken_table where text_field like 'Lorem%' allow
filtering;
select integer_field from broken_table where text_field = 'Lorem ipsum dolor
sit amet, consectetur adipiscing elit. Vivamus a hendrerit enim. Aenean
ultricies ex orci, vitae pellentesque leo consequat eget.';
{code}
But when I drop the broken index:
{code:sql}
drop index broken;
{code}
The, as expected, following queries fail:
{noformat}
cqlsh:db> select integer_field from broken_table where text_field like 'Lorem%'
allow filtering;
InvalidRequest: code=2200 [Invalid query] message="text_field LIKE '%'
restriction is only supported on properly indexed columns"
cqlsh:db> select integer_field from broken_table where text_field like
'Lorem%';
InvalidRequest: code=2200 [Invalid query] message="text_field LIKE '%'
restriction is only supported on properly indexed columns"
cqlsh:snap> select integer_field from broken_table where text_field = 'Lorem
ipsum dolor sit amet, consectetur adipiscing elit. Vivamus a hendrerit enim.
Aenean ultricies ex orci, vitae pellentesque leo consequat eget.';
InvalidRequest: code=2200 [Invalid query] message="Partition key parts:
integer_field, another_text_field must be restricted as other parts are"
{noformat}
I guess that problem originally described in this issue is closely related to
the one that I'm facing so instead of reporting a brand new issue I decided to
comment on this one.
> Enable partition component index for SASI
> -
>
> Key: CASSANDRA-11734
> URL: https://issues.apache.org/jira/browse/CASSANDRA-11734
> Project: Cassandra
> Issue Type: Improvement
> Components: CQL
>Reporter: DOAN DuyHai
>Assignee: DOAN DuyHai
> Labels: doc-impacting, sasi, secondaryIndex
> Fix For: 3.8
>
> Attachments: patch.txt
>
>
> Enable partition component index for SASI
> For the given schema:
> {code:sql}
> CREATE TABLE test.comp (
> pk1 int,
> pk2 text,
> val text,
> PRIMARY KEY ((pk1, pk2))
> );
> CREATE CUSTOM INDEX comp_val_idx ON test.comp (val) USING
> 'org.apache.cassandra.index.sasi.SASIIndex';
> CREATE CUSTOM INDEX comp_pk2_idx ON test.comp (pk2) USING
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'PREFIX',
> 'analyzer_class':
>
[jira] [Commented] (CASSANDRA-11734) Enable partition component index for SASI
[
https://issues.apache.org/jira/browse/CASSANDRA-11734?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15278394#comment-15278394
]
DOAN DuyHai commented on CASSANDRA-11734:
-
bq. {{QueryController#hasIndexFor(ColumnDefinition)}} on every run of the
results checking logic is very inefficient
Agree
So we'll postpone this JIRA until [CASSANDRA-10765] is done then
> Enable partition component index for SASI
> -
>
> Key: CASSANDRA-11734
> URL: https://issues.apache.org/jira/browse/CASSANDRA-11734
> Project: Cassandra
> Issue Type: Improvement
> Components: CQL
>Reporter: DOAN DuyHai
>Assignee: DOAN DuyHai
> Labels: doc-impacting, sasi, secondaryIndex
> Fix For: 3.8
>
> Attachments: patch.txt
>
>
> Enable partition component index for SASI
> For the given schema:
> {code:sql}
> CREATE TABLE test.comp (
> pk1 int,
> pk2 text,
> val text,
> PRIMARY KEY ((pk1, pk2))
> );
> CREATE CUSTOM INDEX comp_val_idx ON test.comp (val) USING
> 'org.apache.cassandra.index.sasi.SASIIndex';
> CREATE CUSTOM INDEX comp_pk2_idx ON test.comp (pk2) USING
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'PREFIX',
> 'analyzer_class':
> 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
> 'case_sensitive': 'false'};
> CREATE CUSTOM INDEX comp_pk1_idx ON test.comp (pk1) USING
> 'org.apache.cassandra.index.sasi.SASIIndex';
> {code}
> The following queries are possible:
> {code:sql}
> SELECT * FROM test.comp WHERE pk1=1;
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5;
> SELECT * FROM test.comp WHERE pk1=1 AND val='xxx' ALLOW FILTERING;
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5 AND val='xxx' ALLOW FILTERING;
> SELECT * FROM test.comp WHERE pk2='some text';
> SELECT * FROM test.comp WHERE pk2 LIKE 'prefix%';
> SELECT * FROM test.comp WHERE pk2='some text' AND val='xxx' ALLOW FILTERING;
> SELECT * FROM test.comp WHERE pk2 LIKE 'prefix%' AND val='xxx' ALLOW
> FILTERING;
> //Without using SASI
> SELECT * FROM test.comp WHERE pk1 = 1 AND pk2='some text';
> SELECT * FROM test.comp WHERE pk1 IN(1,2,3) AND pk2='some text';
> SELECT * FROM test.comp WHERE pk1 = 1 AND pk2 IN ('text1','text2');
> SELECT * FROM test.comp WHERE pk1 IN(1,2,3) AND pk2 IN ('text1','text2');
> {code}
> However, the following queries *are not possible*
> {code:sql}
> SELECT * FROM test.comp WHERE pk1=1 AND pk2 LIKE 'prefix%';
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5 AND pk2 = 'some text';
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5 AND pk2 LIKE 'prefix%';
> {code}
> All of them are throwing the following exception
> {noformat}
> ava.lang.UnsupportedOperationException: null
> at
> org.apache.cassandra.cql3.restrictions.SingleColumnRestriction$LikeRestriction.appendTo(SingleColumnRestriction.java:715)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.restrictions.PartitionKeySingleRestrictionSet.values(PartitionKeySingleRestrictionSet.java:86)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.restrictions.StatementRestrictions.getPartitionKeys(StatementRestrictions.java:585)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.getSliceCommands(SelectStatement.java:473)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.getQuery(SelectStatement.java:265)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.execute(SelectStatement.java:230)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.execute(SelectStatement.java:79)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.QueryProcessor.processStatement(QueryProcessor.java:208)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.QueryProcessor.process(QueryProcessor.java:239)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.QueryProcessor.process(QueryProcessor.java:224)
> ~[main/:na]
> at
> org.apache.cassandra.transport.messages.QueryMessage.execute(QueryMessage.java:115)
> ~[main/:na]
> at
> org.apache.cassandra.transport.Message$Dispatcher.channelRead0(Message.java:507)
> [main/:na]
> at
> org.apache.cassandra.transport.Message$Dispatcher.channelRead0(Message.java:401)
> [main/:na]
> at
> io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105)
> [netty-all-4.0.36.Final.jar:4.0.36.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:292)
> [netty-all-4.0.36.Final.jar:4.0.36.Final]
> at
> io.netty.channel.AbstractChannelHandlerContext.access$600(AbstractChannelHandlerContext.java:32)
> [netty-all-4.0.36.Final.jar:4.0.36.Final]
> at
>
[jira] [Commented] (CASSANDRA-11734) Enable partition component index for SASI
[
https://issues.apache.org/jira/browse/CASSANDRA-11734?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15276894#comment-15276894
]
Pavel Yaskevich commented on CASSANDRA-11734:
-
Thanks for taking a stub at this, [~doanduyhai]! By the nature of changes it
looks like we will have to postpone this until I'm done with QueryPlan porting
(CASSANDRA-10765) which is going to make it more sane to have indexed
restrictions on partitions with(-out) ranges. From the patch I see couple of
things right away: CFMetaData.getLiveIndices() you mentioned goes against the
fact that some of the queries don't even allow usage of the indexes, which
there is no way (currently) to check from inside of the
SingleColumnRestrictions, checking
{{QueryController#hasIndexFor(ColumnDefinition)}} on every run of the results
checking logic is very inefficient and I think instead of using DecoratedKey
separately we might be better off providing {{Operation.satisfiedBy}} methods
with {{UnfilteredRowIterator}} and let it iterate it if needed instead of
involving {{QueryPlan}}. So I would rather have this after CASSANDRA-10765,
looks like it would make everybody's life a bit easier :)
> Enable partition component index for SASI
> -
>
> Key: CASSANDRA-11734
> URL: https://issues.apache.org/jira/browse/CASSANDRA-11734
> Project: Cassandra
> Issue Type: Improvement
> Components: CQL
>Reporter: DOAN DuyHai
>Assignee: DOAN DuyHai
> Labels: doc-impacting, sasi, secondaryIndex
> Fix For: 3.8
>
> Attachments: patch.txt
>
>
> Enable partition component index for SASI
> For the given schema:
> {code:sql}
> CREATE TABLE test.comp (
> pk1 int,
> pk2 text,
> val text,
> PRIMARY KEY ((pk1, pk2))
> );
> CREATE CUSTOM INDEX comp_val_idx ON test.comp (val) USING
> 'org.apache.cassandra.index.sasi.SASIIndex';
> CREATE CUSTOM INDEX comp_pk2_idx ON test.comp (pk2) USING
> 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'PREFIX',
> 'analyzer_class':
> 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
> 'case_sensitive': 'false'};
> CREATE CUSTOM INDEX comp_pk1_idx ON test.comp (pk1) USING
> 'org.apache.cassandra.index.sasi.SASIIndex';
> {code}
> The following queries are possible:
> {code:sql}
> SELECT * FROM test.comp WHERE pk1=1;
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5;
> SELECT * FROM test.comp WHERE pk1=1 AND val='xxx' ALLOW FILTERING;
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5 AND val='xxx' ALLOW FILTERING;
> SELECT * FROM test.comp WHERE pk2='some text';
> SELECT * FROM test.comp WHERE pk2 LIKE 'prefix%';
> SELECT * FROM test.comp WHERE pk2='some text' AND val='xxx' ALLOW FILTERING;
> SELECT * FROM test.comp WHERE pk2 LIKE 'prefix%' AND val='xxx' ALLOW
> FILTERING;
> //Without using SASI
> SELECT * FROM test.comp WHERE pk1 = 1 AND pk2='some text';
> SELECT * FROM test.comp WHERE pk1 IN(1,2,3) AND pk2='some text';
> SELECT * FROM test.comp WHERE pk1 = 1 AND pk2 IN ('text1','text2');
> SELECT * FROM test.comp WHERE pk1 IN(1,2,3) AND pk2 IN ('text1','text2');
> {code}
> However, the following queries *are not possible*
> {code:sql}
> SELECT * FROM test.comp WHERE pk1=1 AND pk2 LIKE 'prefix%';
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5 AND pk2 = 'some text';
> SELECT * FROM test.comp WHERE pk1>=1 AND pk1<=5 AND pk2 LIKE 'prefix%';
> {code}
> All of them are throwing the following exception
> {noformat}
> ava.lang.UnsupportedOperationException: null
> at
> org.apache.cassandra.cql3.restrictions.SingleColumnRestriction$LikeRestriction.appendTo(SingleColumnRestriction.java:715)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.restrictions.PartitionKeySingleRestrictionSet.values(PartitionKeySingleRestrictionSet.java:86)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.restrictions.StatementRestrictions.getPartitionKeys(StatementRestrictions.java:585)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.getSliceCommands(SelectStatement.java:473)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.getQuery(SelectStatement.java:265)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.execute(SelectStatement.java:230)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.statements.SelectStatement.execute(SelectStatement.java:79)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.QueryProcessor.processStatement(QueryProcessor.java:208)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.QueryProcessor.process(QueryProcessor.java:239)
> ~[main/:na]
> at
> org.apache.cassandra.cql3.QueryProcessor.process(QueryProcessor.java:224)
> ~[main/:na]
> at
>
