spring-data-cassandra: InvalidQueryException: Cannot execute this query ... use ALLOW FILTERING

Question

I have the following code

  @Indexed
  @PrimaryKeyColumn(name = "x", ordinal = 1, type = PrimaryKeyType.PARTITIONED)
  @Column(value="x")
  private String x;

  @Indexed
  @PrimaryKeyColumn(name = "code", ordinal = 2, type = PrimaryKeyType.PARTITIONED)
  @Column(value="code")
  private String code;

@Query(value = "select * from customers where code = ?0")
Optional<Customer> findByCode(String code);

When this is executed, I get Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: 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.

Is there a way to avoid this just from spring-data-cassandra? I do not want to add ALLOW FILTERING in my query. I tried creating a separate index on the code column but this haven't solved the issue. I think it stops in the spring data configuration. If I execute the same query in cqlsh, it works.

score 3 · Answer 1 · answered Oct 04 '17 at 15:29

3

You must specify partition key on your query, unless you create index or use ALLOW FILTERING

Executing query with allow filtering might not be a good idea as it can use a lot of your computing resources and Might not return any result because of timeout. Don't use allow filtering in production Read the datastax doc about using ALLOW FILTERING

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/select_r.html?hl=allow,filter

answered Oct 04 '17 at 15:29

Ashraful Islam

12,470
3
32
53

1

what does it mean in practice "specify partition key on your query" ? how to do that in the code? – davey May 04 '18 at 19:39
1

For simple primary key, first field is the partition key. More on here https://stackoverflow.com/a/24953331/2320144 – Ashraful Islam May 06 '18 at 16:30

score 1 · Answer 2 · answered Feb 05 '20 at 07:41

When using a no-sql database, you need to properly design your data to avoid filtering. You can add a secondary index to optimize retrieval by a specific field. More details here: https://docs.datastax.com/en/archived/cql/3.3/cql/cql_using/useSecondaryIndex.html

If you are sure that the query is what you need, you can use the allowFiltering parameter on the @Query annotation to explicitly indicate that ALLOW FILTERING be used.

@Query(value = "select * from customers where code = ?0", allowFiltering = true)
Optional<Customer> findOneByCode(String code);

spring-data-cassandra: InvalidQueryException: Cannot execute this query ... use ALLOW FILTERING

2 Answers2