phoenix-dev mailing list archives

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

     [ 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)

Mime
View raw message