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