phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "chenglei (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
Date Tue, 08 Mar 2016 15:17:40 GMT

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

Mime
View raw message