[ https://issues.apache.org/jira/browse/PHOENIX-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Brian Esserlieu resolved PHOENIX-3301. -------------------------------------- Resolution: Cannot Reproduce > 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)