Phoenix doesn't support 'like' in correlated-subqueries

[khaleel mershad <kh...@gmail.com>]

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: kwm03@aub.edu.lb