How to delete from apache phoenix querying by fields not in every index

Question

I try to execute statement

DELETE FROM statistics WHERE statistic_id is null

and geting error:

java.sql.SQLException: ERROR 1027 (42Y86): All columns referenced in a WHERE clause must be available in every index for a table with immutable rows. tableName=STATISTICS
at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:386)
at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at org.apache.phoenix.compile.DeleteCompiler.compile(DeleteCompiler.java:389)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:553)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:541)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:303)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:296)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:294)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1254)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)

my primary key is on field ID and I've secondary key on STATISTIC_ID

Answer 1

Phoenix require [1] that when you delete something from table with immutable rows, rows to delete should be filtered by all indexed columns. The one way to do so is to disable offending indices by

ALTER INDEX index_name ON table_name DISABLE;
DELETE FROM table_name WHERE condition;

And afterwards rebuild disabled indices:

ALTER INDEX index_name ON table_name REBUILD;

However, keep in mind that this operation takes significant amount of time and resources.

[1] https://phoenix.apache.org/secondary_indexing.html#Immutable_Tables