[PHOENIX-6224] Support Correlated IN Subquery

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.15.0
• Fix Version/s: 5.1.0, 4.16.0
• Assignee: chenglei
• Reporter: chenglei
• Created: 13/Nov/20 11:21
• Updated: 01/Aug/23 15:10
• Resolved: 25/Nov/20 03:52

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 .

Issue Links

• links to: GitHub Pull Request #974