[jira] [Updated] (PHOENIX-4740) FIRST_VALUES fails when using salt_buckets and order by
[ https://issues.apache.org/jira/browse/PHOENIX-4740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Valliet updated PHOENIX-4740: - Affects Version/s: 4.14.0 > 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.14.0, 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)
[jira] [Updated] (PHOENIX-4740) FIRST_VALUES fails when using salt_buckets and order by
[ 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;
[jira] [Created] (PHOENIX-4740) FIRST_VALUES fails when using salt_buckets and order by
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)