[ 
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)

Reply via email to