Hello, It seems that Phoenix still doesn't support using LIKE in correlated-subqueries. I tried the following query:
select W."rowId" from "Wiki" AS W where NOT EXISTS ( select * from "History" AS H where (H."rowId" like W."rowId" || '%') AND (H."status" = 'pending'); and it gave me the error: Error: Does not support non-standard or non-equi correlated-subquery conditions. (state=,code=0) java.sql.SQLFeatureNotSupportedException: Does not support non-standard or non-equi correlated-subquery conditions. at org.apache.phoenix.compile.SubqueryRewriter$JoinConditionExtractor.leaveBooleanNode(SubqueryRewriter.java:469) at org.apache.phoenix.compile.SubqueryRewriter$JoinConditionExtractor.leaveBooleanNode(SubqueryRewriter.java:395) at org.apache.phoenix.parse.BooleanParseNodeVisitor.visitLeave(BooleanParseNodeVisitor.java:37) at org.apache.phoenix.parse.LikeParseNode.accept(LikeParseNode.java:62) at org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64) at org.apache.phoenix.parse.AndParseNode.accept(AndParseNode.java:47) at org.apache.phoenix.compile.SubqueryRewriter.visitLeave(SubqueryRewriter.java:166) at org.apache.phoenix.compile.SubqueryRewriter.visitLeave(SubqueryRewriter.java:68) at org.apache.phoenix.parse.ExistsParseNode.accept(ExistsParseNode.java:53) at org.apache.phoenix.parse.ParseNodeRewriter.rewrite(ParseNodeRewriter.java:48) at org.apache.phoenix.compile.SubqueryRewriter.transform(SubqueryRewriter.java:82) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:342) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:330) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:240) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:235) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:234) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1100) However if I replace 'like' by '=' so the query becomes: select W."rowId" from "Wiki" AS W where NOT EXISTS ( select * from "History" AS H where (H."rowId" = W."rowId") AND (H."status" = 'pending'); the query works fine. Is there a possibility that 'like' will be supported in correlated-subqueries in the future release? Best Regards, Khaleel Mershad, Ph.D. Research Associate American University of Beirut Department of Electrical and Computer Engineering Bliss Street, Beirut, Lebanon email: kw...@aub.edu.lb
