[ 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)