[RESOLVED] Spring Data Cassandra : Cannot execute this query as it might involve data filtering

The Issue

Following error can be noticed while using the Spring Data Cassandra, if you need to search between a range on a column where secondary index is set on it.

org.springframework.data.cassandra.CassandraInvalidQueryException: Query;  CQL [SELECT * FROM **** WHERE *** > * AND *** < *** AND **** <  **** ;];  

Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. 

If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING;

Cause of the Issue

The reason here is, if you are searching for non equivalence, cassandra has to do a full table scan and run filtering on it to fetch the expected data as the data is sorted and stored to fetch on equivalence. So instead of performing the above action, Cassandra fails the request with warning and asks us to enforce above process if we really need to do this with the significant performance penalty.

However it is a question, that where we have to set the 'ALLOW FILTERING'. Here Spring Data Cassandra is smart enough to make the query in a repository interface without an implementation, it can't notice the requirement of data filtering. 

Fix/ Workaround

Here, we need to annotate and mark the interface method with @AllowFiltering to ping Spring Data Cassandra to send it with the query and allowing the database to perform it.

@AllowFiltering

Book findByPublishedYearGreaterThanAndPublishedYearLessThan(int startyear, int endYear)

Note

use of ALLOW FILTERING is not recommended unless that you are confident the table contains a very small set of data in it. Otherwise significant performance issues may appear due to full table scan & filtering. 

See this post for more details and optimisations on data filtering.

References:

1. https://www.datastax.com/dev/blog/allow-filtering-explained-2
2. https://pixabay.com/en/filter-mechanics-vehicle-640487/