[
https://issues.apache.org/jira/browse/CASSANDRA-11734?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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 '<term>%'
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 '<term>%'
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':
> '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
> io.netty.channel.AbstractChannelHandlerContext$7.run(AbstractChannelHandlerContext.java:283)
> [netty-all-4.0.36.Final.jar:4.0.36.Final]
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> [na:1.8.0_45]
> at
> org.apache.cassandra.concurrent.AbstractLocalAwareExecutorService$FutureTask.run(AbstractLocalAwareExecutorService.java:164)
> [main/:na]
> at org.apache.cassandra.concurrent.SEPWorker.run(SEPWorker.java:106)
> [main/:na]
> {noformat}
> Indeed, to allow the above queries we'll need to update the class
> {{StatementRestrictions}} and introduce a new class
> {{PartitionKeyMultipleRestrictionsSet}} and I don't feel like doing it,
> afraid of breaking existing code.
> WDYT [~xedin] [~jrwest] [~blerer] ?
> I attach a patch for this JIRA.
> I have added new tests to {{OperationTest}} and {{SASIIndexTest}} with
> flushing & non-flushing before queries to test in-memory & on disk read paths.
> To enable operators other than {{=}} and {{IN}} on the partition key
> component, I modified the {{SingleColumnRelation}} to add this code:
> {code:java}
> else if (isSlice() || isLIKE())
> {
> // Non EQ relation is not supported without token(), unless SASI
> index is used
> final boolean hasSASIIndex = metaData.getLiveIndices()
> .stream()
> .filter(index ->
> index.dependsOn(columnDef))
> .anyMatch(index -> index instanceof
> SASIIndex);
> checkFalse(columnDef.isPartitionKey() && !hasSASIIndex, "Only EQ
> and IN relation are supported on the partition key (unless you use the
> token() function or SASI index)");
> }
> {code}
> This implies adding the method {{getLiveIndices()}} on {{CFMetaData}} which
> accesses the index manager by creating an instance of {{ColumnFamilyStore}},
> don't know whether it is expensive or not, please advise
> {code:java}
> //CFMetaData
> public Collection<Index> getLiveIndices()
> {
> return Keyspace.openAndGetStore(this).indexManager.listIndexes();
> }
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
