[jira] [Comment Edited] (PHOENIX-3301) Row Value Constructors Against Indexes Don't Work Correctly

["Brian Esserlieu (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15505355#comment-15505355 ] 

Brian Esserlieu edited comment on PHOENIX-3301 at 9/20/16 2:29 AM:
-------------------------------------------------------------------

1) Not totally sure I understand your comment: Multitenant tables have field 1 (tenant ID) as an implied first field. Creating a secondary index on a multi-tenant table with pk1 would throw an exception. Eg:

CREATE INDEX TEST_INDEX ON TEST_TABLE (pk1, pk2, pk4, pk3, pk5); --throws:
Error: ERROR 514 (42892): A duplicate column name was detected in the object definition or ALTER TABLE statement. columnName=TEST_INDEX2.:PK1
SQLState:  42892
ErrorCode: 514

2) CREATE INDEX TEST_INDEX ON TEST_TABLE (pk2, pk4, pk3, pk5); 
has pk1 at the beginning (ordinal_position=1) of the index according to SYSTEM.CATALOG?



was (Author: rangent):
1) Not totally sure I understand your comment: Multitenant tables have field 1 (tenant ID) as an implied first field. Creating a secondary index on a multi-tenant table with pk1 would throw an exception. Eg:

CREATE INDEX TEST_INDEX ON TEST_TABLE (pk1, pk2, pk4, pk3, pk5); --throws:
Error: ERROR 514 (42892): A duplicate column name was detected in the object definition or ALTER TABLE statement. columnName=TEST_INDEX2.:PK1
SQLState:  42892
ErrorCode: 514

2) CREATE INDEX TEST_INDEX ON TEST_TABLE (pk2, pk4, pk3, pk5); 
has pk1 at the beginning (index 0) of the index according to SYSTEM.CATALOG?


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