phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas D'Silva" <tdsi...@salesforce.com>
Subject Re: Cursor Query Loops Eternally with Local Index, Returns Fine Without It
Date Tue, 18 Dec 2018 01:50:40 GMT
Jack,

Can you please file a JIRA that includes you repro steps?

On Fri, Dec 14, 2018 at 2:33 AM Jack Steenkamp <steenkamp.jack@gmail.com>
wrote:

> Hi All,
>
> I have come across a curious case with Phoenix (4.14.1) cursors where a
> particular 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.
>
> Please find attached a standalone test case that you should be able to run
> to reproduce this problem (though you may need to modify the JDBC_URL
> constant). You can run it either with CREATE_INDEX = true or CREATE_INDEX =
> false. Below are the outputs
>
> With : CREATE_INDEX = true;
>
> Connecting To : jdbc:phoenix:localhost:63214
> 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
>
> Creating Index: CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME,
> TRANSACTION_TIME DESC) INCLUDE(ANOTHER_VALUE)
>
> Inserting Some Items
>
> Doing Count SQL : SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS
> TOTAL COUNT : 10
>
> 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 !!
>
> With : CREATE_INDEX = false;
>
> Connecting To : jdbc:phoenix:localhost:63214
> 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
>
> Not Creating the Index
>
> Inserting Some Items
>
> Doing Count SQL : SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS
> TOTAL COUNT : 10
>
> 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
>
> Any idea what might be going on here?
>
> Regards,
>

Mime
View raw message