[
https://issues.apache.org/jira/browse/PHOENIX-6498?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17436242#comment-17436242
]
Istvan Toth commented on PHOENIX-6498:
--------------------------------------
Backported to 5.1 branch.
> 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
> Assignee: chenglei
> Priority: Major
> Fix For: 4.17.0, 5.2.0, 5.1.3
>
>
> 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)
