[jira] [Comment Edited] (PHOENIX-2753) ClientAggregatePlan incorrectly uses limit in aggregation may cause sql with limit clause to misbehave
[
https://issues.apache.org/jira/browse/PHOENIX-2753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15185116#comment-15185116
]
chenglei edited comment on PHOENIX-2753 at 3/8/16 3:59 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>
> 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)
[jira] [Comment Edited] (PHOENIX-2753) ClientAggregatePlan incorrectly uses limit in aggregation may cause sql with limit clause to misbehave
[
https://issues.apache.org/jira/browse/PHOENIX-2753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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>
> 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)
