[ https://issues.apache.org/jira/browse/PHOENIX-5072?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Richárd Antal resolved PHOENIX-5072. ------------------------------------ Fix Version/s: 5.1.3 4.16.2 5.2.0 4.17.0 Assignee: Richárd Antal Resolution: Fixed Committed to all active branches. Thank you for the reviews [~stoty] [~dbwong] [~ankit] > Cursor Query Loops Eternally with Local Index, Returns Fine Without It > ---------------------------------------------------------------------- > > Key: PHOENIX-5072 > URL: https://issues.apache.org/jira/browse/PHOENIX-5072 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.14.1, 5.2.0, 5.1.2 > Reporter: Jack Steenkamp > Assignee: Richárd Antal > Priority: Major > Fix For: 4.17.0, 5.2.0, 4.16.2, 5.1.3 > > Attachments: PhoenixEternalCursorTest.java > > > > I have come across a case where a particular cursor query would carry on > looping forever if executed when a local index is present. If however, I > execute the same query without a local index on the table, then it works as > expected. > You can reproduce this by executing the attached standalone test case. You > only need to modify the JDBC_URL constant (by default it tries to connect to > localhost) and then you compare the outputs between hving CREATE_INDEX = true > or CREATE_INDEX = false. > Here is an example of the output: > *1) Connect to an environment and create a simple table:* > {code:java} > Connecting To : jdbc:phoenix:localhost:63214{code} > {code:java} > CREATE TABLE IF NOT EXISTS SOME_NUMBERS > ( > ID VARCHAR NOT NULL, > NAME VARCHAR , > ANOTHER_VALUE VARCHAR , > TRANSACTION_TIME TIMESTAMP , > CONSTRAINT pk PRIMARY KEY(ID) > ) IMMUTABLE_STORAGE_SCHEME=ONE_CELL_PER_COLUMN, > UPDATE_CACHE_FREQUENCY=900000, > COLUMN_ENCODED_BYTES=NONE, > IMMUTABLE_ROWS=true{code} > *2) Optionally create a local index:* > > If you want to reproduce the failure, create an index: > {code:java} > CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME, TRANSACTION_TIME DESC) > INCLUDE(ANOTHER_VALUE){code} > Otherwise, skip this. > *3) Insert a number of objects and verify their count* > {code:java} > System.out.println("\nInserting Some Items"); > DecimalFormat dmf = new DecimalFormat("0000"); > final String prefix = "ReferenceData.Country/"; > for (int i = 0; i < 5; i++) > { > for (int j = 0; j < 2; j++) > { > PreparedStatement prstmt = conn.prepareStatement("UPSERT INTO > SOME_NUMBERS VALUES(?,?,?,?)"); > prstmt.setString(1,UUID.randomUUID().toString()); > prstmt.setString(2,prefix + dmf.format(i)); > prstmt.setString(3,UUID.randomUUID().toString()); > prstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis())); > prstmt.execute(); > conn.commit();3) Insert a number of objects and verify their count_ > prstmt.close(); > } > }{code} > Verify the count afterwards with: > {code:java} > SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS {code} > *5) Run a Cursor Query* > Run a cursor using the standard sequence of commands as appropriate: > {code:java} > DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM SOME_NUMBERS where > NAME like 'ReferenceData.Country/%' ORDER BY TRANSACTION_TIME DESC{code} > {code:java} > OPEN MyCursor{code} > {code:java} > FETCH NEXT 10 ROWS FROM MyCursor{code} > * Without an index it will return the correct number of rows > {code:java} > Cursor SQL : DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM > SOME_NUMBERS where NAME like 'ReferenceData.Country/%' ORDER BY > TRANSACTION_TIME DESC > CLOSING THE CURSOR > Result : 0 > ITEMS returned by count : 10 | Items Returned by Cursor : 10 > ALL GOOD - No Exception{code} > * With an index it will return far more than the number of rows (it appears > to be erroneously looping for ever - hence the test-case terminates it). > {code:java} > Cursor SQL : DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM > SOME_NUMBERS where NAME like 'ReferenceData.Country/%' ORDER BY > TRANSACTION_TIME DESC > ITEMS returned by count : 10 | Items Returned by Cursor : 40 > Aborting the Cursor, as it is more than the count! > Exception in thread "main" java.lang.RuntimeException: The cursor returned a > different number of rows from the count !! {code} > > -- This message was sent by Atlassian Jira (v8.3.4#803005)