[jira] [Commented] (PHOENIX-6224) Support Correlated IN Subquery

["Geoffrey Jacoby (Jira)" <ji...@apache.org>]

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

Geoffrey Jacoby commented on PHOENIX-6224:
------------------------------------------

I'm not sure I'd classify this as a Bug, because I thought that we just didn't support correlated subqueries? Would be a good feature to have, of course, esp. since we already support joins and in-clause correlated subqueries are often equivalent to a join. 

> Support  Correlated IN Subquery
> -------------------------------
>
>                 Key: PHOENIX-6224
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-6224
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.15.0
>            Reporter: chenglei
>            Assignee: chenglei
>            Priority: Major
>             Fix For: 5.1.0, 4.16.0
>
>
> Given following tables :
> {code:java}
>      create table item
>        (item_id varchar not null primary key, 
>         name varchar, 
>         price integer, 
>         discount1 integer, 
>         discount2 integer, 
>         supplier_id varchar, 
>        description varchar)
>          
>         create table order
>          ( order_id varchar not null primary key, 
>            customer_id varchar, 
>            item_id varchar, 
>            price integer,
>            quantity integer, 
>            date timestamp)
> {code}
> for the  correlated in subquery:
> {code:java}
>  SELECT item_id, name FROM item i WHERE i.item_id IN 
>           (SELECT item_id FROM order o  where o.price = i.price) ORDER BY name
> {code} 
> Phoenix would throw following exception, that is because phoenix only support Non-Correlated In Subquery now:
> {code:java}
> org.apache.phoenix.schema.ColumnFamilyNotFoundException: ERROR 1001 (42I01): Undefined column family. familyName=I
> 	at org.apache.phoenix.schema.PTableImpl.getColumnFamily(PTableImpl.java:1363)
> 	at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.resolveColumn(FromCompiler.java:527)
> 	at org.apache.phoenix.compile.ExpressionCompiler.resolveColumn(ExpressionCompiler.java:368)
> 	at org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.resolveColumn(WhereCompiler.java:191)
> 	at org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.visit(WhereCompiler.java:177)
> 	at org.apache.phoenix.compile.ExpressionCompiler.visit(ExpressionCompiler.java:1)
> 	at org.apache.phoenix.parse.ColumnParseNode.accept(ColumnParseNode.java:56)
> 	at org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64)
> 	at org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:45)
> 	at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:138)
> 	at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:108)
> 	at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:629)
> 	at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:574)
> 	at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:203)
> 	at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
> 	at org.apache.phoenix.compile.QueryCompiler.compileSubquery(QueryCompiler.java:563)
> 	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:239)
> 	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:320)
> 	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:252)
> 	at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:201)
> 	at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
> 	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:497)
> 	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:1)
> 	at org.apache.phoenix.jdbc.PhoenixStatement.compileQuery(PhoenixStatement.java:1769)
> 	at org.apache.phoenix.jdbc.PhoenixStatement.compileQuery(PhoenixStatement.java:1762)
> 	at org.apache.phoenix.jdbc.PhoenixStatement.optimizeQuery(PhoenixStatement.java:1756)
> {code}
> But other SQL engines like Spark SQL and MySQL all support Correlated In Subquery .



--
This message was sent by Atlassian Jira
(v8.3.4#803005)