SASI index doesn't work with composite partition key

[makailol]

Hello,

We have a table with primary key : PRIMARY KEY ((user_id, app_id), id)

To query all records of user_id, we tried SASI index as below.
CREATE CUSTOM INDEX master_userid_sparse ON mykeyspace.master (user_id) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'SPARSE'};

Now when we execute query with "WHERE user_id = ?" it returns blank result(zero rows and no error).

If we use native secondary index, it works fine for above query (referenced from below link) but native index (applied on user_id) doesn't work with SASI indexes (applied on other columns).
https://docs.datastax.com/en/cql/3.3/cql/cql_using/useSecondaryIndex.html

Is this SASI behaviour by design or does it address any bug?

Thanks

[xedin]

@makailol can version of cassandra are you trying to use here? If it's 2.0 then we don't have support for clustering, only 3.x port does have that.

[makailol]

@xedin , I have been trying this on cassandra version 3.5.0 . Clustering works fine but applying SASI index on composite partition key column (user_id column in above case) doesn't work.

[xedin]

@makailol Ah, I think we just haven't considered that originally because things like statics since they are 3.x, it would be great if you contribute support for that...

[makailol]

I'm not a developer otherwise I would have contributed support for this already! Also our team has not past experience with Cassandra and planning to use it with new project launch so considering to use latest Cassandra (3.5.0+) with SASI support in production.

[xedin]

Ah, sorry about that. I will add it to my todo list on the same level as collections support and talk to other developers too.

[doanduyhai]

@xedin Adding support for partition key component is quite trivial and look very similar to support for static column isn't it ?

[xedin]

It might be, I haven't looked at that yet, probably has to be validated similar to clustering but otherwise shouldn't be that hard, do you want to take a stub at this?

[doanduyhai]

Ok, I'll bootstrap a PoC this weekend

[xedin]

Sounds good, thanks @doanduyhai! I'll review when ever you are ready, it is going to be up for inclusion in 3.8 since 3.6 is already frozen.

[doanduyhai]

Patch available: https://issues.apache.org/jira/browse/CASSANDRA-11734 @makailol

[vijayinjam]

HI, i'm having similar issues,consistency with SASI indexes for with composite Partition and Clustering keys,Does SASI indexes are supported for production usage?

[xedin]

@vijayinjam SAS might not be as stableI with composite keys/columns currently for variety of reasons related to underlaying storage inefficiencies (both C* and SASI itself), couple of people are working on improving that so hopefully we'll get this sorted out in future releases.

[vijayinjam]

Thanks @xedin. I will wait for new release and provide feedback from our tests.

[meox]

any news?