phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Esserlieu (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-2920) Incorrect Queries on Multi-tenant tables with WHERE clause containing Row Value Constructor
Date Fri, 03 Jun 2016 22:35:59 GMT

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

Brian Esserlieu updated PHOENIX-2920:
-------------------------------------
    Description: 
Nothing returns from query against multi-tenant table where the SELECT statement contains
a WHERE condition filtering on the multi-tenant field and also contains a row value constructor.

REPRO:
Run the statement below and see that no records are returned (the second upserted record should
be returned).  Notice that you can either remove the multi-tenancy of the table, or the "pk1
= 1 AND" WHERE condition and the query returns the second record fine, but does not return
the second record as it is.

DROP TABLE IF EXISTS TEST_TABLE;
CREATE TABLE IF NOT EXISTS TEST_TABLE (
    pk1 VARCHAR NOT NULL,
    pk2 DECIMAL NOT NULL,
    v1 VARCHAR
    CONSTRAINT PK PRIMARY KEY 
    (
        pk1,
        pk2
    )
) MULTI_TENANT=true,IMMUTABLE_ROWS=true;
CREATE INDEX TEST_INDEX ON TEST_TABLE (v1);

upsert into TEST_TABLE (pk1, pk2, v1) values ('a', 1, 'value');
upsert into TEST_TABLE (pk1, pk2, v1) values ('a', 2, 'value');

SELECT pk1, pk2, v1 
FROM TEST_TABLE
WHERE 
-- notice that if you comment/remove the "pk1=1 AND " statment below the query returns the
second record correctly
pk1 = 1 AND
(pk1, pk2) > (1, 1)
ORDER BY PK1, PK2
LIMIT 2;

  was:
Nothing returns from query against multi-tenant table where the SELECT statement contains
a WHERE condition filtering on the multi-tenant field and also contains a row value constructor.

REPRO:
Run the statement below and see that no records are returned (the second upserted record should
be returned).  Notice that you can either remove the multi-tenancy of the table, or the "pk1
= 1 AND" WHERE condition and the query returns the second record fine, but does not return
the second record as it is.

DROP TABLE IF EXISTS TEST_TABLE;
CREATE TABLE IF NOT EXISTS TEST_TABLE (
    pk1 DECIMAL NOT NULL,
    pk2 DECIMAL NOT NULL,
    v1 VARCHAR
    CONSTRAINT PK PRIMARY KEY 
    (
        pk1,
        pk2
    )
) MULTI_TENANT=true,IMMUTABLE_ROWS=true;
CREATE INDEX TEST_INDEX ON TEST_TABLE (v1);

upsert into TEST_TABLE (pk1, pk2, v1) values (1, 1, 'value');
upsert into TEST_TABLE (pk1, pk2, v1) values (1, 2, 'value');

SELECT pk1, pk2, v1 
FROM TEST_TABLE
WHERE 
-- notice that if you comment/remove the "pk1=1 AND " statment below the query returns the
second record correctly
pk1 = 1 AND
(pk1, pk2) > (1, 1)
ORDER BY PK1, PK2
LIMIT 2;


> Incorrect Queries on Multi-tenant tables with WHERE clause containing Row Value Constructor
> -------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-2920
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2920
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.7.0
>            Reporter: Brian Esserlieu
>            Assignee: Samarth Jain
>            Priority: Minor
>              Labels: SFDC
>             Fix For: 4.8.0
>
>
> Nothing returns from query against multi-tenant table where the SELECT statement contains
a WHERE condition filtering on the multi-tenant field and also contains a row value constructor.
> REPRO:
> Run the statement below and see that no records are returned (the second upserted record
should be returned).  Notice that you can either remove the multi-tenancy of the table, or
the "pk1 = 1 AND" WHERE condition and the query returns the second record fine, but does not
return the second record as it is.
> DROP TABLE IF EXISTS TEST_TABLE;
> CREATE TABLE IF NOT EXISTS TEST_TABLE (
>     pk1 VARCHAR NOT NULL,
>     pk2 DECIMAL NOT NULL,
>     v1 VARCHAR
>     CONSTRAINT PK PRIMARY KEY 
>     (
>         pk1,
>         pk2
>     )
> ) MULTI_TENANT=true,IMMUTABLE_ROWS=true;
> CREATE INDEX TEST_INDEX ON TEST_TABLE (v1);
> upsert into TEST_TABLE (pk1, pk2, v1) values ('a', 1, 'value');
> upsert into TEST_TABLE (pk1, pk2, v1) values ('a', 2, 'value');
> SELECT pk1, pk2, v1 
> FROM TEST_TABLE
> WHERE 
> -- notice that if you comment/remove the "pk1=1 AND " statment below the query returns
the second record correctly
> pk1 = 1 AND
> (pk1, pk2) > (1, 1)
> ORDER BY PK1, PK2
> LIMIT 2;



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

Mime
View raw message