[ https://issues.apache.org/jira/browse/PHOENIX-6224?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
chenglei updated PHOENIX-6224: ------------------------------ Description: 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: {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 Spark SQL was: 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 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 > Support Correlated-Subquery for In Clause > ------------------------------------------ > > Key: PHOENIX-6224 > URL: https://issues.apache.org/jira/browse/PHOENIX-6224 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.15.0 > Reporter: 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: > {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 Spark SQL -- This message was sent by Atlassian Jira (v8.3.4#803005)