[ 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 agree the cause of this issue is following code in 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. > 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 agree the cause of this issue is following code in -- This message was sent by Atlassian JIRA (v6.3.4#6332)