[
https://issues.apache.org/jira/browse/PHOENIX-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15505379#comment-15505379
]
Brian Esserlieu commented on PHOENIX-3301:
------------------------------------------
Just reread the first bullet. Good catch on the SELECT *. I'm seeing an issue
locally with our full table, but this repro no longer works. Let me try to get
another repro and I'll update the JIRA's description (or close if I'm unable
to).
> Row Value Constructors Against Indexes Don't Work Correctly
> -----------------------------------------------------------
>
> Key: PHOENIX-3301
> URL: https://issues.apache.org/jira/browse/PHOENIX-3301
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.8.0
> Reporter: Brian Esserlieu
>
> I was trying to run a delete statement using a row value constructor against
> a table's secondary index, and noticed no data was being deleted. Digging
> into the problem a bit more, I cant get any queries using a row value
> constructor against the secondary index to work at all. I've included repro
> steps below:
> *Repro*
> {code:title=repro.sql|borderStyle=solid}
> DROP INDEX IF EXISTS TEST_INDEX ON TEST_TABLE;
> DROP TABLE IF EXISTS TEST_TABLE;
> CREATE TABLE IF NOT EXISTS TEST_TABLE (
> pk1 VARCHAR NOT NULL,
> pk2 VARCHAR NOT NULL,
> pk3 VARCHAR NOT NULL,
> pk4 DATE NOT NULL,
> pk5 VARCHAR NOT NULL,
> v1 VARCHAR
> CONSTRAINT PK PRIMARY KEY
> (
> pk1,
> pk2,
> pk3,
> pk4 DESC,
> pk5
> )
> ) MULTI_TENANT=true,IMMUTABLE_ROWS=true;
> CREATE INDEX TEST_INDEX ON TEST_TABLE (pk2, pk4, pk3, pk5);
> upsert into TEST_TABLE (pk1, pk2, pk3, pk4, pk5, v1) values ('a', '001', '1',
> TO_DATE('2000-01-01'), 'A', 'value');
> upsert into TEST_TABLE (pk1, pk2, pk3, pk4, pk5, v1) values ('a', '001', '1',
> TO_DATE('2000-01-01'), 'B', 'value');
> -- THIS IS THIE ORDERING USING A DEFAULT ROW VALUE CONSTRUCTOR, USING
> -- VALUES FOR THE ROW VALUE CONSTRUCTOR SUCH THAT BOTH TEST ROWS
> -- SHOULD BE RETURNED
> SELECT *
> FROM TEST_TABLE
> WHERE
> pk1 = 'a' AND pk2 = '001' AND
> (pk1, pk2, pk3, pk4, pk5) >= ('a', '001', '0', TO_DATE('1999-01-01'), 'A')
> -- HERE ARE THE EXACT SAME VALUES, WITH FIELDS pk3 AND pk4 TRANSPOSED
> SELECT *
> FROM TEST_TABLE
> WHERE
> pk1 = 'a' AND pk2 = '001' AND
> (pk1, pk2, pk4, pk3, pk5) >= ('a', '001', TO_DATE('1999-01-01'), '0', 'A')
> -- NOTE, THAT NOTHING IS RETURNED BY THIS QUERY, BUT THE TWO TEST ROWS SHOULD
> BE.
> -- THIS SHOULD BE USING THE INDEX, BUT ISN'T:
> EXPLAIN SELECT * FROM TEST_TABLE WHERE pk1 = 'a' AND pk2 = '001' AND
> (pk1, pk2, pk4, pk3, pk5) >= ('a', '001', TO_DATE('1999-01-01'), '0', 'A')
> -- IF WE CHANGE THE INEQUALITY TO BE LESS THAN OR EQUALS WITH THE TRANSPOSED
> ROW
> -- KEY, NOTE THAT THE TEST ROWS ARE RETURNED (THEY SHOULDN'T BE):
> SELECT *
> FROM TEST_TABLE
> WHERE
> pk1 = 'a' AND pk2 = '001' AND
> (pk1, pk2, pk4, pk3, pk5) <= ('a', '001', TO_DATE('1999-01-01'), '0', 'A')
> -- HINTING TO USE AN INDEX DOESN'T WORK:
> SELECT /*+ INDEX(TEST_TABLE TEST_INDEX) */ *
> FROM TEST_TABLE
> WHERE
> pk1 = 'a' AND pk2 = '001' AND
> (pk1, pk2, pk4, pk3, pk5) >= ('a', '001', TO_DATE('1999-01-01'), '0', 'A')
> -- FORCING AN ORDERING THAT WOULD CAUSE THE INDEX TO BE USED DOESN'T WORK
> EITHER:
> SELECT *
> FROM TEST_TABLE
> WHERE
> pk1 = 'a' AND pk2 = '001' AND
> (pk1, pk2, pk4, pk3, pk5) >= ('a', '001', TO_DATE('1999-01-01'), '0', 'A')
> ORDER BY pk1, pk2, pk4, pk3, pk5
> -- THIS IS THE ORIGINAL DELETE STATEMENT I TRIED TO RUN, BUT IS FAILING
> -- LIKELY FOR THE SAME REASON AS SELECTS ARE FAILING ABOVE
> DELETE
> FROM TEST_TABLE
> WHERE
> pk1 = 'a' AND pk2 = '001' AND
> (pk1, pk2, pk4, pk3, pk5) >= ('a', '001', TO_DATE('1999-01-01'), '0', 'A')
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
