phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dhiraj Hegde (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-2961) select on a composite primary key fails when DESC is set in the table
Date Wed, 08 Jun 2016 05:58:21 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-2961?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dhiraj Hegde updated PHOENIX-2961:
----------------------------------
    Description: 
I created a table with a primary key composed of 4 fields. The table specifies DESC for one
of those fields (please see the test script provided). When the table is queried using any
combination of 3 of the primary key fields, it selects rows correctly. However, when all 4
fields are specified, no rows are selected.  Here is the sql script that reproduces this issue:

----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS  tests1 (
   ACCOUNT_ID  VARCHAR NOT NULL,
  BUCKET_ID     VARCHAR NOT NULL,
  OBJECT_ID      VARCHAR NOT NULL,
  OBJECT_VERSION   VARCHAR NOT NULL,
      LOC           VARCHAR,
CONSTRAINT PK PRIMARY KEY (ACCOUNT_ID, BUCKET_ID, OBJECT_ID, OBJECT_VERSION DESC));

UPSERT INTO tests1  (ACCOUNT_ID, BUCKET_ID, OBJECT_ID, OBJECT_VERSION, LOC) VALUES ('acct1',
'bucket1', 'obj1', '1111', 'loc1');


select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1' and
BUCKET_ID = 'bucket1' and OBJECT_VERSION = '1111';

select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1' and
BUCKET_ID = 'bucket1' and OBJECT_ID = 'obj1';

select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1' and
BUCKET_ID = 'bucket1' and OBJECT_VERSION = '1111'  and OBJECT_ID = 'obj1';

----------------------------------------------

The first two select statements selects the row that was inserted, whereas the third one fails
to select any rows.







  was:
I created a table with a primary key composed of 4 fields. The table specifies DESC for one
of those fields (please see the test script provided). When the table is queried using any
combination of 3 of the primary key fields, it selects rows correctly. However, when all 4
fields are specified, no rows are selected.  Here is the sql script that reproduces this issue:

----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS  tests1 (
   ACCOUNT_ID  VARCHAR NOT NULL,
  BUCKET_ID     VARCHAR NOT NULL,
  OBJECT_ID      VARCHAR NOT NULL,
  OBJECT_VERSION   VARCHAR NOT NULL,
      LOC           VARCHAR,
CONSTRAINT PK PRIMARY KEY (ACCOUNT_ID, BUCKET_ID, OBJECT_ID, OBJECT_VERSION DESC));

UPSERT INTO tests1  (ACCOUNT_ID, BUCKET_ID, OBJECT_ID, OBJECT_VERSION, LOC) VALUES ('acct1',
'bucket1', 'obj1', '1111', 'loc1');


select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1' and
BUCKET_ID = 'bucket1' and OBJECT_VERSION = '1111';

select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1' and
BUCKET_ID = 'bucket1' and OBJECT_ID = 'obj1';

select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1' and
BUCKET_ID = 'bucket1' and OBJECT_VERSION = '1111'  and OBJECT_ID = 'obj1';

----------------------------------------------

The first two select statements selects the row that was inserted, whereas the third one fails
to select any rows.




> select on a composite primary key fails when DESC is set in the table
> ---------------------------------------------------------------------
>
>                 Key: PHOENIX-2961
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2961
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.7.0
>         Environment: linux ubuntu 14.0.4.4   
> phoenix-4.7.0-HBase-0.98-client
> HBase Version	0.98.19
>            Reporter: Dhiraj Hegde
>            Assignee: Samarth Jain
>              Labels: SFDC
>             Fix For: 4.8.0
>
>         Attachments: test1.sql
>
>
> I created a table with a primary key composed of 4 fields. The table specifies DESC for
one of those fields (please see the test script provided). When the table is queried using
any combination of 3 of the primary key fields, it selects rows correctly. However, when all
4 fields are specified, no rows are selected.  Here is the sql script that reproduces this
issue:
> ----------------------------------------------------------------
> CREATE TABLE IF NOT EXISTS  tests1 (
>    ACCOUNT_ID  VARCHAR NOT NULL,
>   BUCKET_ID     VARCHAR NOT NULL,
>   OBJECT_ID      VARCHAR NOT NULL,
>   OBJECT_VERSION   VARCHAR NOT NULL,
>       LOC           VARCHAR,
> CONSTRAINT PK PRIMARY KEY (ACCOUNT_ID, BUCKET_ID, OBJECT_ID, OBJECT_VERSION DESC));
> UPSERT INTO tests1  (ACCOUNT_ID, BUCKET_ID, OBJECT_ID, OBJECT_VERSION, LOC) VALUES ('acct1',
'bucket1', 'obj1', '1111', 'loc1');
> select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1'
and BUCKET_ID = 'bucket1' and OBJECT_VERSION = '1111';
> select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1'
and BUCKET_ID = 'bucket1' and OBJECT_ID = 'obj1';
> select ACCOUNT_ID, BUCKET_ID, OBJECT_VERSION  from tests1  WHERE ACCOUNT_ID = 'acct1'
and BUCKET_ID = 'bucket1' and OBJECT_VERSION = '1111'  and OBJECT_ID = 'obj1';
> ----------------------------------------------
> The first two select statements selects the row that was inserted, whereas the third
one fails to select any rows.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message