[
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 aggregate,Phoenix may compile the sql
to a ClientAggregatePlan.However,if the sql also has limit
statement,ClientAggregatePlan may incorrectly use limit statement to create
AggregatingResultIterator under some condition,leading the sql can't get
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}
was:
When we execute a sql with subquery and aggregate,Phoenix may compile the sql
to a ClientAggregatePlan.However,if the sql also has limit
statement,ClientAggregatePlan may incorrectly use limit statement to create
AggregatingResultIterator under some condition,leading the sql can't get
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}
> 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 aggregate,Phoenix may compile the sql
> to a ClientAggregatePlan.However,if the sql also has limit
> statement,ClientAggregatePlan may incorrectly use limit statement to create
> AggregatingResultIterator under some condition,leading the sql can't get
> 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}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
