You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "chenglei (JIRA)" <ji...@apache.org> on 2016/03/08 16:06:40 UTC

[jira] [Created] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave

chenglei created PHOENIX-2753:
---------------------------------

             Summary: 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}

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





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)