You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Jack Steenkamp <st...@gmail.com> on 2018/12/14 10:23:13 UTC
Cursor Query Loops Eternally with Local Index, Returns Fine Without It
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,
Re: Cursor Query Loops Eternally with Local Index, Returns Fine
Without It
Posted by Jack Steenkamp <st...@gmail.com>.
Hi Tomas,
Sure - I've created https://issues.apache.org/jira/browse/PHOENIX-5072 with
all the details.
Thanks,
On Tue, 18 Dec 2018 at 01:50, Thomas D'Silva <td...@salesforce.com> wrote:
> Jack,
>
> Can you please file a JIRA that includes you repro steps?
>
> On Fri, Dec 14, 2018 at 2:33 AM Jack Steenkamp <st...@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,
>>
>
Re: Cursor Query Loops Eternally with Local Index, Returns Fine
Without It
Posted by Thomas D'Silva <td...@salesforce.com>.
Jack,
Can you please file a JIRA that includes you repro steps?
On Fri, Dec 14, 2018 at 2:33 AM Jack Steenkamp <st...@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,
>