Search code examples
sqlapacheapache-phoenix

DELETE with having dynamic fields in condition clause seems not working for phoenix

I am trying to delete rows in apache phoenix by specifying dynamic fields. Here is an example: Say we create "test" table with schema below:

create table "test" ("id" INTEGER not null primary key, "staticColumn" VARCHAR);

and insert one row like this

upsert into "test" ("id", "staticColumn", "dynamicColumn" VARCHAR) values (0, 'static', 'dynamic');

Then according to the documentation, I do can search what by:

select * from "test"("dynamicColumn" VARCHAR)  where "dynamicColumn" = 'dynamic';

but I can only delete by:

delete from "test" where "staticColumn" = 'static';

NOT by:

delete from "test"("dynamicColumn" VARCHAR) where "dynamicColumn" = 'dynamic';

Error message I got:

Error: ERROR 602 (42P00): Syntax error. Missing "EOF" at line 1, column 19. (state=42P00,code=602)
org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "EOF" at line 1, column 19.
        at org.apache.phoenix.exception.PhoenixParserException.newException(PhoenixParserException.java:33)
        at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:111)
        at org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementParser.parseStatement(PhoenixStatement.java:1285)
        at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1366)
        at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1429)
        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)
Caused by: MissingTokenException(inserted [@-1,0:0='<missing EOF>',<-1>,1:18] at ()
        at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:350)
        at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
        at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:510)
        at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
        ... 9 more
Solution

  • You should file a bug report or feature request with them, but as a workaround:

    delete from test where id in (select id from test(dynamicColumn VARCHAR) where dynamicColumn = 'dynamic');

    It works with or without double quotations around the column name, which were unrelated to the error message.