[ 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; +-----------+-----------------------------------------------------------+---------+ | 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} > 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 > Priority: Major > 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} > > Cheers, > > -manu > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)