[jira] [Created] (PHOENIX-5072) Cursor Query Loops Eternally with Local Index, Returns Fine Without It

["Jack Steenkamp (JIRA)" <ji...@apache.org>]

Jack Steenkamp created PHOENIX-5072:
---------------------------------------

             Summary: 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
            Reporter: Jack Steenkamp
         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
(v7.6.3#76005)