[ https://issues.apache.org/jira/browse/PHOENIX-6498?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
chenglei updated PHOENIX-6498: ------------------------------ 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 exists subquery: {code:java} 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 {code} Phoenix would throw following exception: {code:java} 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) {code} That is because Phoenix incorrectly rewrite the subquery as: {code:java} SELECT DISTINCT 1 $3,O.ITEM_ID $2 FROM ORDER_TABLE O GROUP BY CUSTOMER_ID HAVING COUNT(ORDER_ID) > 1 {code} > Fix incorrect Correlated Exists Subquery rewrite when Subquery is aggregate > --------------------------------------------------------------------------- > > Key: PHOENIX-6498 > URL: https://issues.apache.org/jira/browse/PHOENIX-6498 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.16.1, 5.1.2 > Reporter: chenglei > Priority: Major > Fix For: 4.17.0, 5.2.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 exists subquery: > {code:java} > 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 > {code} > Phoenix would throw following exception: > {code:java} > 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) > {code} > That is because Phoenix incorrectly rewrite the subquery as: > {code:java} > SELECT DISTINCT 1 $3,O.ITEM_ID $2 FROM ORDER_TABLE O GROUP BY CUSTOMER_ID > HAVING COUNT(ORDER_ID) > 1 > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)