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

["Vlad Krava (Jira)" <ji...@apache.org>]

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

Vlad Krava commented on PHOENIX-5072:
-------------------------------------

[~swaroopa] is there any info on your investigation, since your last comment there were no updates provided on this ticket. Has this bug been confirmed, if so, does it also affect Phoenix 5.0.0 (and HBase 2.0)?

 

> 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
>            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
(v8.3.4#803005)