[jira] [Comment Edited] (PHOENIX-2746) Delete on the table with immutable rows may fail with INVALID_FILTER_ON_IMMUTABLE_ROWS error code.

["Rajeshbabu Chintaguntla (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-2746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15193143#comment-15193143 ] 

Rajeshbabu Chintaguntla edited comment on PHOENIX-2746 at 3/14/16 11:44 AM:
----------------------------------------------------------------------------

Here is the patch fixes the issues with deletes when there are multiple indexes on table with immutable rows. [~jamestaylor] please review.


was (Author: rajeshbabu):
Here is the patch fixes the issues with deletes when there are multiple indexes on table with immutable rows.

> Delete on the table with immutable rows may fail with INVALID_FILTER_ON_IMMUTABLE_ROWS error code.
> --------------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-2746
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2746
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.7.0
>            Reporter: Rajeshbabu Chintaguntla
>            Assignee: Rajeshbabu Chintaguntla
>             Fix For: 4.8.0
>
>         Attachments: PHOENIX-2746.patch
>
>
> Some times delete on table with immutable rows is failing with below error even all the indexes are having the column in where condition. If we have condition on primary key columns it's always failing.
> {noformat}
> 0: jdbc:phoenix:localhost> delete from t2 where a='raj1';
> Error: ERROR 1027 (42Y86): All columns referenced in a WHERE clause must be available in every index for a table with immutable rows. tableName=T2 (state=42Y86,code=1027)
> 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=T2
> 	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:390)
> 	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:546)
> 	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:534)
> 	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:302)
> 	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:295)
> 	at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> 	at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:293)
> 	at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1247)
> 	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
> {noformat}
> The reason is we are collecting nondisable indexes and adding to list. 
> 1) Once after resolving the table data table.
> 2) One after running select with where condition from delete. 
> So the references of index table objects will be different two times if cache updated again 2nd time.
> {noformat}
>                 immutableIndex = getNonDisabledImmutableIndexes(tableRefToBe);
> {noformat}
> So here when remove a table from immutableIndex list we should compare references because PTable doesn't have equal or hashCode implementations which will not remove any index from the list and we throw SQLException.
> {noformat}
>             while (plans.hasNext()) {
>                 QueryPlan plan = plans.next();
>                 PTable table = plan.getTableRef().getTable();
>                 if (table.getType() == PTableType.INDEX) { // index plans
>                     tableRefs[i++] = plan.getTableRef();
>                     immutableIndex.remove(table);
>                 } else { // data plan
>                     /*
>                      * If we have immutable indexes that we need to maintain, don't execute the data plan
>                      * as we can save a query by piggy-backing on any of the other index queries, since the
>                      * PK columns that we need are always in each index row.
>                      */
>                     plans.remove();
>                 }
> {noformat}
> If the where condition is PK column then the plans returned by compiler is only one because we are passing USE_DATA_OVER_INDEX_TABLE hint. Then also the immutableIndex list is not empty. Then also we through exception.
> {noformat}
>                 noQueryReqd = !hasLimit;
>                 // Can't run on same server for transactional data, as we need the row keys for the data
>                 // that is being upserted for conflict detection purposes.
>                 runOnServer = isAutoCommit && noQueryReqd && !table.isTransactional();
>                 HintNode hint = delete.getHint();
>                 if (runOnServer && !delete.getHint().hasHint(Hint.USE_INDEX_OVER_DATA_TABLE)) {
>                     hint = HintNode.create(hint, Hint.USE_DATA_OVER_INDEX_TABLE);
>                 }
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)