Valliet created PHOENIX-4740: -------------------------------- Summary: FIRST_VALUES fails when using salt_buckets and order by Key: PHOENIX-4740 URL: https://issues.apache.org/jira/browse/PHOENIX-4740 Project: Phoenix Issue Type: Bug Affects Versions: 4.13.1 Environment: BigInsight 4.2.0.0
HBase 1.2 phoenix-4.13.1-HBase-1.2 Reporter: Valliet Attachments: emp.sql 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} -- This message was sent by Atlassian JIRA (v7.6.3#76005)