[PHOENIX-6498] Fix incorrect Correlated Exists Subquery rewrite when Subquery is aggregate

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.16.1, 5.1.2
• Fix Version/s: 5.2.0, 5.1.3
• Assignee: chenglei
• Reporter: chenglei
• Created: 25/Jun/21 06:33
• Updated: 25/Apr/23 12:17
• Resolved: 06/Jun/22 19:40

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 exists subquery:

SELECT item_id, name FROM  item i WHERE exists
                    (SELECT 1 FROM  order o  where o.item_id = i.item_id group by customer_id having count(order_id) > 1) ORDER BY name

Phoenix would throw following exception:

java.sql.SQLException: ERROR 1018 (42Y27): Aggregate may not contain columns not in GROUP BY. ITEM_ID
	at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:606)
	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:217)
	at org.apache.phoenix.compile.ExpressionCompiler.throwNonAggExpressionInAggException(ExpressionCompiler.java:1090)
	at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:445)
	at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:755)
	at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:674)
	at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:251)
	at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:178)
	at org.apache.phoenix.compile.QueryCompiler.compileSubquery(QueryCompiler.java:661)
	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:289)
	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:370)
	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:302)
	at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:249)
	at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:178)
	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:672)
	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:1)
	at org.apache.phoenix.jdbc.PhoenixStatement.compileQuery(PhoenixStatement.java:2011)
	at org.apache.phoenix.jdbc.PhoenixStatement.compileQuery(PhoenixStatement.java:2004)
	at org.apache.phoenix.jdbc.PhoenixStatement.optimizeQuery(PhoenixStatement.java:1998)

That is because Phoenix incorrectly rewrite the subquery as:

   SELECT DISTINCT 1 $3,O.ITEM_ID $2 FROM ORDER_TABLE O  GROUP BY CUSTOMER_ID HAVING  COUNT(ORDER_ID) > 1

Issue Links

• links to: GitHub Pull Request #1268