[ https://issues.apache.org/jira/browse/PHOENIX-2753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15185116#comment-15185116 ]
chenglei edited comment on PHOENIX-2753 at 3/8/16 3:52 PM: ----------------------------------------------------------- sorry,I found the 2279 fixed this problem was (Author: comnetwork): sorry,I found the 2279 fixed this problem > ClientAggregatePlan incorrectly uses limit in aggregation 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 think the cause of this issue is the following code in > ClientAggregatePlan(around the 103 line),the limit parameter should be null: > {code:borderStyle=solid} > iterator = new OrderedResultIterator(iterator, keyExpressionOrderBy, > thresholdBytes, limit, projector.getEstimatedRowByteSize()); > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)