Description
Given following tables :
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)
for the correlated in subquery:
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
Phoenix would throw following exception, that is because phoenix only support Non-Correlated In Subquery now:
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)
But other SQL engines like Spark SQL and MySQL all support Correlated In Subquery .
Attachments
Attachments
Issue Links
- links to