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