phoenix-user mailing list archives

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,