CODEWITHSUNDEEP
springcassandraspring-dataspring-data-jpaspring-data-cassandra
tzortzik
tzortzik

Reputation: 5133

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

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.

Upvotes: 2

Views: 3658

Answers (2)

Omtara
Omtara

Reputation: 3001

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

Upvotes: 1

Ashraful Islam
Ashraful Islam

Reputation: 12830

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

Upvotes: 3

Related Questions