[
https://issues.apache.org/jira/browse/PHOENIX-2753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chenglei updated PHOENIX-2753:
------------------------------
Description:
When we execute a sql with subquery and aggregation,Phoenix may compile the sql
to get a ClientAggregatePlan.However,if the sql also has a limit
clause,ClientAggregatePlan may incorrectly use the limit clause to create a
AggregatingResultIterator under some conditions,leading the sql can't get the
correct result,just as the following unit test:
{code:borderStyle=solid}
@Test
public void testLimit() throws Exception
{
this.jdbcTemplate.update("drop table if exists limit_test ");
this.jdbcTemplate.update(
"create table limit_test "+
"("+
"account_id INTEGER not null,"+
"buy_key VARCHAR not null,"+
"group_id INTEGER,"+
"cost INTEGER,"+
"CONSTRAINT pk PRIMARY KEY (account_id,buy_key) "+
")");
this.jdbcTemplate.update("upsert into
LIMIT_TEST(account_id,buy_key,group_id,cost) values(1000,'key1',1,10)");
this.jdbcTemplate.update("upsert into
LIMIT_TEST(account_id,buy_key,group_id,cost) values(1001,'key2',2,20)");
this.jdbcTemplate.update("upsert into
LIMIT_TEST(account_id,buy_key,group_id,cost) values(1002,'key1',3,30)");
String sqlWithLimit="select bk,sum(sc) "+
"from (select buy_key as bk,group_id as
gid,sum(cost) as sc "+
"from limit_test group by buy_key,group_id
order by group_id)"+
"group by bk having count(*) > 1 limit 1";
List<Map<String,Object>>
result=this.jdbcTemplate.queryForList(sqlWithLimit, new Object[0]);
assertTrue(result.size()==0);
String sqlNoLimit="select bk,sum(sc) "+
"from (select buy_key as bk,group_id as
gid,sum(cost) as sc "+
"from limit_test group by buy_key,group_id
order by group_id)"+
"group by bk having count(*) > 1";
result=this.jdbcTemplate.queryForList(sqlNoLimit, new Object[0]);
assertTrue(result.size()==1);
}
{code}
As above unit test explained,the sqlWithLimit gets no result at all,but it
should get the "key1" as the result,just like the sqlNoLimit does.
I think the cause of this issue is the following code in
ClientAggregatePlan(around the 103 line):
{code:borderStyle=solid}
iterator = new OrderedResultIterator(iterator, keyExpressionOrderBy,
thresholdBytes, limit, projector.getEstimatedRowByteSize());
{code}
was:
When we execute a sql with subquery and aggregation,Phoenix may compile the sql
to get a ClientAggregatePlan.However,if the sql also has a limit
clause,ClientAggregatePlan may incorrectly use the limit clause to create a
AggregatingResultIterator under some conditions,leading the sql can't get the
correct result,just as the following unit test:
{code:borderStyle=solid}
@Test
public void testLimit() throws Exception
{
this.jdbcTemplate.update("drop table if exists limit_test ");
this.jdbcTemplate.update(
"create table limit_test "+
"("+
"account_id INTEGER not null,"+
"buy_key VARCHAR not null,"+
"group_id INTEGER,"+
"cost INTEGER,"+
"CONSTRAINT pk PRIMARY KEY (account_id,buy_key) "+
")");
this.jdbcTemplate.update("upsert into
LIMIT_TEST(account_id,buy_key,group_id,cost) values(1000,'key1',1,10)");
this.jdbcTemplate.update("upsert into
LIMIT_TEST(account_id,buy_key,group_id,cost) values(1001,'key2',2,20)");
this.jdbcTemplate.update("upsert into
LIMIT_TEST(account_id,buy_key,group_id,cost) values(1002,'key1',3,30)");
String sqlWithLimit="select bk,sum(sc) "+
"from (select buy_key as bk,group_id as
gid,sum(cost) as sc "+
"from limit_test group by buy_key,group_id
order by group_id)"+
"group by bk having count(*) > 1 limit 1";
List<Map<String,Object>>
result=this.jdbcTemplate.queryForList(sqlWithLimit, new Object[0]);
assertTrue(result.size()==0);
String sqlNoLimit="select bk,sum(sc) "+
"from (select buy_key as bk,group_id as
gid,sum(cost) as sc "+
"from limit_test group by buy_key,group_id
order by group_id)"+
"group by bk having count(*) > 1";
result=this.jdbcTemplate.queryForList(sqlNoLimit, new Object[0]);
assertTrue(result.size()==1);
}
{code}
As above unit test explained,the sqlWithLimit gets no result at all,but it
should get the "key1" as the result,just like the sqlNoLimit does.
I think the cause of this issue is the following code in
ClientAggregatePlan(103 line):
{code:borderStyle=solid}
iterator = new OrderedResultIterator(iterator,
keyExpressionOrderBy, thresholdBytes, limit,
projector.getEstimatedRowByteSize());
{code}
> ClientAggregatePlan incorrectly using limit in aggregate may cause sql with
> limit clause to misbehave
> -----------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-2753
> URL: https://issues.apache.org/jira/browse/PHOENIX-2753
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.6.0
> Environment: HBase 0.98.6-cdh5.3.2, Phoenix 4.6.0-HBase-0.98
> Reporter: chenglei
>
> When we execute a sql with subquery and aggregation,Phoenix may compile the
> sql to get a ClientAggregatePlan.However,if the sql also has a limit
> clause,ClientAggregatePlan may incorrectly use the limit clause to create a
> AggregatingResultIterator under some conditions,leading the sql can't get the
> correct result,just as the following unit test:
> {code:borderStyle=solid}
> @Test
> public void testLimit() throws Exception
> {
> this.jdbcTemplate.update("drop table if exists limit_test ");
> this.jdbcTemplate.update(
> "create table limit_test "+
> "("+
> "account_id INTEGER not null,"+
> "buy_key VARCHAR not null,"+
> "group_id INTEGER,"+
> "cost INTEGER,"+
> "CONSTRAINT pk PRIMARY KEY (account_id,buy_key) "+
> ")");
> this.jdbcTemplate.update("upsert into
> LIMIT_TEST(account_id,buy_key,group_id,cost) values(1000,'key1',1,10)");
> this.jdbcTemplate.update("upsert into
> LIMIT_TEST(account_id,buy_key,group_id,cost) values(1001,'key2',2,20)");
> this.jdbcTemplate.update("upsert into
> LIMIT_TEST(account_id,buy_key,group_id,cost) values(1002,'key1',3,30)");
>
> String sqlWithLimit="select bk,sum(sc) "+
> "from (select buy_key as bk,group_id as
> gid,sum(cost) as sc "+
> "from limit_test group by buy_key,group_id
> order by group_id)"+
> "group by bk having count(*) > 1 limit 1";
> List<Map<String,Object>>
> result=this.jdbcTemplate.queryForList(sqlWithLimit, new Object[0]);
> assertTrue(result.size()==0);
>
> String sqlNoLimit="select bk,sum(sc) "+
> "from (select buy_key as bk,group_id as
> gid,sum(cost) as sc "+
> "from limit_test group by buy_key,group_id
> order by group_id)"+
> "group by bk having count(*) > 1";
> result=this.jdbcTemplate.queryForList(sqlNoLimit, new Object[0]);
> assertTrue(result.size()==1);
>
>
>
> }
> {code}
> As above unit test explained,the sqlWithLimit gets no result at all,but it
> should get the "key1" as the result,just like the sqlNoLimit does.
> I think the cause of this issue is the following code in
> ClientAggregatePlan(around the 103 line):
> {code:borderStyle=solid}
> iterator = new OrderedResultIterator(iterator, keyExpressionOrderBy,
> thresholdBytes, limit, projector.getEstimatedRowByteSize());
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
