[ https://issues.apache.org/jira/browse/PHOENIX-6224?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
chenglei reassigned PHOENIX-6224: --------------------------------- Assignee: chenglei > 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 support Correlated In Subquery > . -- This message was sent by Atlassian Jira (v8.3.4#803005)