[
https://issues.apache.org/jira/browse/PHOENIX-4740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Valliet updated PHOENIX-4740:
-
Description:
Hi,
so I'm running phoenix over a 1.2 Hbase, and found this gem:
First, without salt_buckets, everything works fine:
{code:java}
create table emp (
emp_code VARCHAR not null,
bu_code VARCHAR not null,
territory_codes VARCHAR,
salary DOUBLE,
CONSTRAINT pk PRIMARY KEY (emp_code, bu_code));
upsert into emp values('emp1', 'bu1', 'FR', 1000);
upsert into emp values('emp1', 'bu2', 'EN', 1000);
upsert into emp values('emp2', 'bu1', 'US', 1000);
upsert into emp values('emp2', 'bu2', 'DE', 1000);
upsert into emp values('emp2', 'bu3', 'AF', 1000);
SELECT emp_code, first_values(territory_codes, 10) within group (order by
territory_codes asc), sum(salary) as total from emp group by emp_code order by
total desc limit 100;
+---+---+-+
| EMP_CODE | FIRST_VALUES(TERRITORY_CODES, true, TERRITORY_CODES, 10) | TOTAL |
+---+---+-+
| emp2 | [AF, DE, US] | 3000.0 |
| emp1 | [EN, FR] | 2000.0 |
+---+---+-+
{code}
Then I add SALT_BUCKETS on the table, and if I use the 'order by total',
FIRST_VALUES results are empty:
{code:java}
drop table emp;
create table emp (
emp_code VARCHAR not null,
bu_code VARCHAR not null,
territory_codes VARCHAR,
salary DOUBLE,
CONSTRAINT pk PRIMARY KEY (emp_code, bu_code)) SALT_BUCKETS=10;
upsert into emp values('emp1', 'bu1', 'FR', 1000);
upsert into emp values('emp1', 'bu2', 'EN', 1000);
upsert into emp values('emp2', 'bu1', 'US', 1000);
upsert into emp values('emp2', 'bu2', 'DE', 1000);
upsert into emp values('emp2', 'bu3', 'AF', 1000);
SELECT emp_code, first_values(territory_codes, 10) within group (order by
territory_codes asc), sum(salary) as total from emp group by emp_code order by
total desc limit 100;
+---+---+-+
| EMP_CODE | FIRST_VALUES(TERRITORY_CODES, true, TERRITORY_CODES, 10) | TOTAL |
+---+---+-+
| emp2 | | 3000.0 |
| emp1 | | 2000.0 |
+---+---+-+
2
SELECT emp_code, first_values(territory_codes, 10) within group (order by
territory_codes asc), sum(salary) as total from emp group by emp_code limit 100;
+---+---+-+
| EMP_CODE | FIRST_VALUES(TERRITORY_CODES, true, TERRITORY_CODES, 10) | TOTAL |
+---+---+-+
| emp1 | [EN, FR] | 2000.0 |
| emp2 | [AF, DE, US] | 3000.0 |
+---+---+-+
{code}
Cheers,
-manu
was:
Hi,
so I'm running phoenix over a 1.2 Hbase, and found this gem:
First, without salt_buckets, everything works fine:
{code:java}
create table emp (
emp_code VARCHAR not null,
bu_code VARCHAR not null,
territory_codes VARCHAR,
salary DOUBLE,
CONSTRAINT pk PRIMARY KEY (emp_code, bu_code));
upsert into emp values('emp1', 'bu1', 'FR', 1000);
upsert into emp values('emp1', 'bu2', 'EN', 1000);
upsert into emp values('emp2', 'bu1', 'US', 1000);
upsert into emp values('emp2', 'bu2', 'DE', 1000);
upsert into emp values('emp2', 'bu3', 'AF', 1000);
SELECT emp_code, first_values(territory_codes, 10) within group (order by
territory_codes asc), sum(salary) as total from emp group by emp_code order by
total desc limit 100;
+---+---+-+
| EMP_CODE | FIRST_VALUES(TERRITORY_CODES, true, TERRITORY_CODES, 10) | TOTAL |
+---+---+-+
| emp2 | [AF, DE, US] | 3000.0 |
| emp1 | [EN, FR] | 2000.0 |
+---+---+-+
{code}
Then I add SALT_BUCKETS on the table, and if I use the 'order by total',
FIRST_VALUES results are empty:
{code:java}
drop table emp;
create table emp (
emp_code VARCHAR not null,
bu_code VARCHAR not null,
territory_codes VARCHAR,
salary DOUBLE,
CONSTRAINT pk PRIMARY KEY (emp_code, bu_code)) SALT_BUCKETS=10;
upsert into emp values('emp1', 'bu1', 'FR', 1000);
upsert into emp values('emp1', 'bu2', 'EN', 1000);
upsert into emp values('emp2', 'bu1', 'US', 1000);
upsert into emp values('emp2', 'bu2', 'DE', 1000);
upsert into emp values('emp2', 'bu3', 'AF', 1000);
SELECT emp_code, first_values(territory_codes, 10) within group (order by
territory_codes asc), sum(salary) as total from emp group by emp_code order by
total desc limit 100;
