[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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 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(103 line):
{code:borderStyle=solid}
iterator = new OrderedResultIterator(iterator,
keyExpressionOrderBy, thresholdBytes, limit,
projector.getEstimatedRowByteSize());
{code}
was:
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);
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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 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):
{code:borderStyle=solid}
iterator = new OrderedResultIterator(iterator, keyExpressionOrderBy,
thresholdBytes, limit, projector.getEstimatedRowByteSize());
{code}
was:
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);
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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 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 agree the cause of this issue is following code in
was:
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.
> ClientAggregatePlan
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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 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.
was:
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 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>
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.
> ClientAggregatePlan incorrectly using limit in aggregate may cause sql with
> limit
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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 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 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>
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.
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>
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.
> ClientAggregatePlan incorrectly using limit in aggregate may cause sql with
> limit clause to
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
>
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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:
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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:
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
> -
>
>
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
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);
}
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
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>
result=this.jdbcTemplate.queryForList(sqlWithLimit, new Object[0]);
assertTrue(result.size()==0);
}
{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
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
result=this.jdbcTemplate.queryForList(sqlWithLimit, new Object[0]);
assertTrue(result.size()==0);
}
{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>
result=this.jdbcTemplate.queryForList(sqlWithLimit, new Object[0]);
assertTrue(result.size()==0);
}
{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,"+
>
[jira] [Updated] (PHOENIX-2753) ClientAggregatePlan incorrectly using limit in aggregate may cause sql with limit clause to misbehave
[
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>
result=this.jdbcTemplate.queryForList(sqlWithLimit, new Object[0]);
assertTrue(result.size()==0);
}
{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}
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);
> 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 "+
> "("+
>
