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

["Swaroopa Kadam (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-5072?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Swaroopa Kadam reassigned PHOENIX-5072:
---------------------------------------

    Assignee: Swaroopa Kadam

> 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
>            Assignee: Swaroopa Kadam
>            Priority: Major
>         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)