You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "chenglei (Jira)" <ji...@apache.org> on 2021/06/25 06:45:00 UTC
[jira] [Updated] (PHOENIX-6498) Fix incorrect Correlated Exists
Subquery rewrite when Subquery is aggregate
[ 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)