phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Valliet (JIRA)" <j...@apache.org>
Subject [jira] [Created] (PHOENIX-4740) FIRST_VALUES fails when using salt_buckets and order by
Date Thu, 17 May 2018 09:38:00 GMT
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)

Mime
View raw message