You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Chunhui Liu <le...@gmail.com> on 2015/10/15 07:15:39 UTC
Row value constructors failed on the index, when len(table's pks) > 2
and table's 1st pk is index's last pk
Hi team,
When I try to use paged query on secondary index, I found a issue.
1. Table has more than 2 primary keys;
2. Table's 1st pk as index's last pk; eg. table's pks are (pk1, pk2, pk3),
the failed index's pks are (pk2, pk3, pk1); table's pks are (1, 2, 3, 4),
failed index's pks are (2, 3, 4, 5, 1);
3. Use row value constructors on index with another condition that use one
pks(not the table's 1st pk);
4. You will get "DEGENERATE SCAN OVER TABLE_NAME"
Here is the Test SQL
---------------------------------------------------------------------
DROP TABLE IF EXISTS T;
CREATE TABLE IF NOT EXISTS T (
PK1 VARCHAR not null,
PK2 VARCHAR not null,
PK3 VARCHAR not null,
V1 VARCHAR,
CONSTRAINT PK PRIMARY KEY (PK1, PK2, PK3)
);
CREATE INDEX IDX_T ON T
(
PK2, PK3, PK1
);
UPSERT INTO T VALUES('100', '200', '300', 'V');
UPSERT INTO T VALUES('101', '201', '301', 'V');
UPSERT INTO T VALUES('102', '202', '302', 'V');
UPSERT INTO T VALUES('103', '203', '303', 'V');
UPSERT INTO T VALUES('104', '204', '304', 'V');
SELECT * FROM T;
EXPLAIN
SELECT PK1, PK2, PK3 FROM T WHERE
(PK2, PK3, PK1) >= ('202', '302', '102')
AND PK2 < '204'
LIMIT 10;
---------------------------------------------------------------------
I've tried 3 primary key, here is the results.
1. table's pks are (pk1, pk2, pk3);
2. 132 means (pk1, pk3, pk2);
| index's pks order | result |
| --------------------- | ------------------------------------ |
| 132 | correct |
| 213 | correct |
| 231 | fail |
| 312 | correct |
| 321 | correct |
I've also test this on table with 4, 5 pks
| len(pks) | failed order |
| --------------------- | ------------------------------------ |
| 3 | 231 |
| 4 | 2341 |
| 5 | 23451 |
Thanks,
Chunhui
Re: Row value constructors failed on the index, when len(table's pks)
> 2 and table's 1st pk is index's last pk
Posted by Chunhui Liu <le...@gmail.com>.
https://issues.apache.org/jira/browse/PHOENIX-2327
2015-10-16 0:16 GMT+08:00 James Taylor <ja...@apache.org>:
> Please file a JIRA with a unit test case.
>
> @Samarth - any insights?
>
> On Thu, Oct 15, 2015 at 12:36 AM, Chunhui Liu <le...@gmail.com> wrote:
>
>> Test with patch for PHOENIX-2319, issue still happened.
>>
>> When PHOENIX-2319 was triggered, no index upsert into hbase.
>>
>> For this issue, UPSERT seems ok, "SELECT * FROM IDX_T" works fine.
>>
>> Thanks,
>> Chunhui
>>
>> 2015-10-15 14:26 GMT+08:00 James Taylor <ja...@apache.org>:
>>
>>> Any difference if you apply the patch for PHOENIX-2319?
>>> Thanks,
>>> James
>>>
>>> On Wed, Oct 14, 2015 at 10:15 PM, Chunhui Liu <le...@gmail.com>
>>> wrote:
>>>
>>>> Hi team,
>>>>
>>>> When I try to use paged query on secondary index, I found a issue.
>>>>
>>>> 1. Table has more than 2 primary keys;
>>>> 2. Table's 1st pk as index's last pk; eg. table's pks are (pk1, pk2,
>>>> pk3), the failed index's pks are (pk2, pk3, pk1); table's pks are (1, 2, 3,
>>>> 4), failed index's pks are (2, 3, 4, 5, 1);
>>>> 3. Use row value constructors on index with another condition that use
>>>> one pks(not the table's 1st pk);
>>>> 4. You will get "DEGENERATE SCAN OVER TABLE_NAME"
>>>>
>>>> Here is the Test SQL
>>>> ---------------------------------------------------------------------
>>>> DROP TABLE IF EXISTS T;
>>>>
>>>> CREATE TABLE IF NOT EXISTS T (
>>>> PK1 VARCHAR not null,
>>>> PK2 VARCHAR not null,
>>>> PK3 VARCHAR not null,
>>>> V1 VARCHAR,
>>>> CONSTRAINT PK PRIMARY KEY (PK1, PK2, PK3)
>>>> );
>>>>
>>>> CREATE INDEX IDX_T ON T
>>>> (
>>>> PK2, PK3, PK1
>>>> );
>>>>
>>>> UPSERT INTO T VALUES('100', '200', '300', 'V');
>>>> UPSERT INTO T VALUES('101', '201', '301', 'V');
>>>> UPSERT INTO T VALUES('102', '202', '302', 'V');
>>>> UPSERT INTO T VALUES('103', '203', '303', 'V');
>>>> UPSERT INTO T VALUES('104', '204', '304', 'V');
>>>>
>>>> SELECT * FROM T;
>>>>
>>>> EXPLAIN
>>>> SELECT PK1, PK2, PK3 FROM T WHERE
>>>> (PK2, PK3, PK1) >= ('202', '302', '102')
>>>> AND PK2 < '204'
>>>> LIMIT 10;
>>>> ---------------------------------------------------------------------
>>>>
>>>> I've tried 3 primary key, here is the results.
>>>> 1. table's pks are (pk1, pk2, pk3);
>>>> 2. 132 means (pk1, pk3, pk2);
>>>>
>>>> | index's pks order | result |
>>>> | --------------------- | ------------------------------------ |
>>>> | 132 | correct |
>>>> | 213 | correct |
>>>> | 231 | fail |
>>>> | 312 | correct |
>>>> | 321 | correct |
>>>>
>>>> I've also test this on table with 4, 5 pks
>>>> | len(pks) | failed order |
>>>> | --------------------- | ------------------------------------ |
>>>> | 3 | 231 |
>>>> | 4 | 2341 |
>>>> | 5 | 23451 |
>>>>
>>>>
>>>> Thanks,
>>>> Chunhui
>>>>
>>>>
>>>
>>
>
Re: Row value constructors failed on the index, when len(table's pks)
> 2 and table's 1st pk is index's last pk
Posted by James Taylor <ja...@apache.org>.
Please file a JIRA with a unit test case.
@Samarth - any insights?
On Thu, Oct 15, 2015 at 12:36 AM, Chunhui Liu <le...@gmail.com> wrote:
> Test with patch for PHOENIX-2319, issue still happened.
>
> When PHOENIX-2319 was triggered, no index upsert into hbase.
>
> For this issue, UPSERT seems ok, "SELECT * FROM IDX_T" works fine.
>
> Thanks,
> Chunhui
>
> 2015-10-15 14:26 GMT+08:00 James Taylor <ja...@apache.org>:
>
>> Any difference if you apply the patch for PHOENIX-2319?
>> Thanks,
>> James
>>
>> On Wed, Oct 14, 2015 at 10:15 PM, Chunhui Liu <le...@gmail.com> wrote:
>>
>>> Hi team,
>>>
>>> When I try to use paged query on secondary index, I found a issue.
>>>
>>> 1. Table has more than 2 primary keys;
>>> 2. Table's 1st pk as index's last pk; eg. table's pks are (pk1, pk2,
>>> pk3), the failed index's pks are (pk2, pk3, pk1); table's pks are (1, 2, 3,
>>> 4), failed index's pks are (2, 3, 4, 5, 1);
>>> 3. Use row value constructors on index with another condition that use
>>> one pks(not the table's 1st pk);
>>> 4. You will get "DEGENERATE SCAN OVER TABLE_NAME"
>>>
>>> Here is the Test SQL
>>> ---------------------------------------------------------------------
>>> DROP TABLE IF EXISTS T;
>>>
>>> CREATE TABLE IF NOT EXISTS T (
>>> PK1 VARCHAR not null,
>>> PK2 VARCHAR not null,
>>> PK3 VARCHAR not null,
>>> V1 VARCHAR,
>>> CONSTRAINT PK PRIMARY KEY (PK1, PK2, PK3)
>>> );
>>>
>>> CREATE INDEX IDX_T ON T
>>> (
>>> PK2, PK3, PK1
>>> );
>>>
>>> UPSERT INTO T VALUES('100', '200', '300', 'V');
>>> UPSERT INTO T VALUES('101', '201', '301', 'V');
>>> UPSERT INTO T VALUES('102', '202', '302', 'V');
>>> UPSERT INTO T VALUES('103', '203', '303', 'V');
>>> UPSERT INTO T VALUES('104', '204', '304', 'V');
>>>
>>> SELECT * FROM T;
>>>
>>> EXPLAIN
>>> SELECT PK1, PK2, PK3 FROM T WHERE
>>> (PK2, PK3, PK1) >= ('202', '302', '102')
>>> AND PK2 < '204'
>>> LIMIT 10;
>>> ---------------------------------------------------------------------
>>>
>>> I've tried 3 primary key, here is the results.
>>> 1. table's pks are (pk1, pk2, pk3);
>>> 2. 132 means (pk1, pk3, pk2);
>>>
>>> | index's pks order | result |
>>> | --------------------- | ------------------------------------ |
>>> | 132 | correct |
>>> | 213 | correct |
>>> | 231 | fail |
>>> | 312 | correct |
>>> | 321 | correct |
>>>
>>> I've also test this on table with 4, 5 pks
>>> | len(pks) | failed order |
>>> | --------------------- | ------------------------------------ |
>>> | 3 | 231 |
>>> | 4 | 2341 |
>>> | 5 | 23451 |
>>>
>>>
>>> Thanks,
>>> Chunhui
>>>
>>>
>>
>
Re: Row value constructors failed on the index, when len(table's pks)
> 2 and table's 1st pk is index's last pk
Posted by Chunhui Liu <le...@gmail.com>.
Test with patch for PHOENIX-2319, issue still happened.
When PHOENIX-2319 was triggered, no index upsert into hbase.
For this issue, UPSERT seems ok, "SELECT * FROM IDX_T" works fine.
Thanks,
Chunhui
2015-10-15 14:26 GMT+08:00 James Taylor <ja...@apache.org>:
> Any difference if you apply the patch for PHOENIX-2319?
> Thanks,
> James
>
> On Wed, Oct 14, 2015 at 10:15 PM, Chunhui Liu <le...@gmail.com> wrote:
>
>> Hi team,
>>
>> When I try to use paged query on secondary index, I found a issue.
>>
>> 1. Table has more than 2 primary keys;
>> 2. Table's 1st pk as index's last pk; eg. table's pks are (pk1, pk2,
>> pk3), the failed index's pks are (pk2, pk3, pk1); table's pks are (1, 2, 3,
>> 4), failed index's pks are (2, 3, 4, 5, 1);
>> 3. Use row value constructors on index with another condition that use
>> one pks(not the table's 1st pk);
>> 4. You will get "DEGENERATE SCAN OVER TABLE_NAME"
>>
>> Here is the Test SQL
>> ---------------------------------------------------------------------
>> DROP TABLE IF EXISTS T;
>>
>> CREATE TABLE IF NOT EXISTS T (
>> PK1 VARCHAR not null,
>> PK2 VARCHAR not null,
>> PK3 VARCHAR not null,
>> V1 VARCHAR,
>> CONSTRAINT PK PRIMARY KEY (PK1, PK2, PK3)
>> );
>>
>> CREATE INDEX IDX_T ON T
>> (
>> PK2, PK3, PK1
>> );
>>
>> UPSERT INTO T VALUES('100', '200', '300', 'V');
>> UPSERT INTO T VALUES('101', '201', '301', 'V');
>> UPSERT INTO T VALUES('102', '202', '302', 'V');
>> UPSERT INTO T VALUES('103', '203', '303', 'V');
>> UPSERT INTO T VALUES('104', '204', '304', 'V');
>>
>> SELECT * FROM T;
>>
>> EXPLAIN
>> SELECT PK1, PK2, PK3 FROM T WHERE
>> (PK2, PK3, PK1) >= ('202', '302', '102')
>> AND PK2 < '204'
>> LIMIT 10;
>> ---------------------------------------------------------------------
>>
>> I've tried 3 primary key, here is the results.
>> 1. table's pks are (pk1, pk2, pk3);
>> 2. 132 means (pk1, pk3, pk2);
>>
>> | index's pks order | result |
>> | --------------------- | ------------------------------------ |
>> | 132 | correct |
>> | 213 | correct |
>> | 231 | fail |
>> | 312 | correct |
>> | 321 | correct |
>>
>> I've also test this on table with 4, 5 pks
>> | len(pks) | failed order |
>> | --------------------- | ------------------------------------ |
>> | 3 | 231 |
>> | 4 | 2341 |
>> | 5 | 23451 |
>>
>>
>> Thanks,
>> Chunhui
>>
>>
>
Re: Row value constructors failed on the index, when len(table's pks)
> 2 and table's 1st pk is index's last pk
Posted by James Taylor <ja...@apache.org>.
Any difference if you apply the patch for PHOENIX-2319?
Thanks,
James
On Wed, Oct 14, 2015 at 10:15 PM, Chunhui Liu <le...@gmail.com> wrote:
> Hi team,
>
> When I try to use paged query on secondary index, I found a issue.
>
> 1. Table has more than 2 primary keys;
> 2. Table's 1st pk as index's last pk; eg. table's pks are (pk1, pk2, pk3),
> the failed index's pks are (pk2, pk3, pk1); table's pks are (1, 2, 3, 4),
> failed index's pks are (2, 3, 4, 5, 1);
> 3. Use row value constructors on index with another condition that use one
> pks(not the table's 1st pk);
> 4. You will get "DEGENERATE SCAN OVER TABLE_NAME"
>
> Here is the Test SQL
> ---------------------------------------------------------------------
> DROP TABLE IF EXISTS T;
>
> CREATE TABLE IF NOT EXISTS T (
> PK1 VARCHAR not null,
> PK2 VARCHAR not null,
> PK3 VARCHAR not null,
> V1 VARCHAR,
> CONSTRAINT PK PRIMARY KEY (PK1, PK2, PK3)
> );
>
> CREATE INDEX IDX_T ON T
> (
> PK2, PK3, PK1
> );
>
> UPSERT INTO T VALUES('100', '200', '300', 'V');
> UPSERT INTO T VALUES('101', '201', '301', 'V');
> UPSERT INTO T VALUES('102', '202', '302', 'V');
> UPSERT INTO T VALUES('103', '203', '303', 'V');
> UPSERT INTO T VALUES('104', '204', '304', 'V');
>
> SELECT * FROM T;
>
> EXPLAIN
> SELECT PK1, PK2, PK3 FROM T WHERE
> (PK2, PK3, PK1) >= ('202', '302', '102')
> AND PK2 < '204'
> LIMIT 10;
> ---------------------------------------------------------------------
>
> I've tried 3 primary key, here is the results.
> 1. table's pks are (pk1, pk2, pk3);
> 2. 132 means (pk1, pk3, pk2);
>
> | index's pks order | result |
> | --------------------- | ------------------------------------ |
> | 132 | correct |
> | 213 | correct |
> | 231 | fail |
> | 312 | correct |
> | 321 | correct |
>
> I've also test this on table with 4, 5 pks
> | len(pks) | failed order |
> | --------------------- | ------------------------------------ |
> | 3 | 231 |
> | 4 | 2341 |
> | 5 | 23451 |
>
>
> Thanks,
> Chunhui
>
>