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,
|