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,
package com.jsteenkamp.phoenix;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.UUID;
/**
* Reproducing the eternal cursor issue.
*/
public class PhoenixEternalCursorTest
{
private final boolean CREATE_INDEX = true; //With false this works, with true it doesn't
private final String JDBC_URL = "jdbc:phoenix:localhost:63214";
private final java.sql.Connection conn;
private final Statement stmt;
public static void main(String... args) throws Exception
{
PhoenixEternalCursorTest ectest = new PhoenixEternalCursorTest();
ectest.testEternalCursor();
System.out.println("ALL GOOD - No Exception");
}
public PhoenixEternalCursorTest() throws Exception
{
System.out.println("Connecting To : " + JDBC_URL);
conn = DriverManager.getConnection(JDBC_URL, "sa", "");
stmt = conn.createStatement();
}
public void testEternalCursor() throws Exception
{
stmt.execute("DROP TABLE IF EXISTS SOME_NUMBERS");
String createTable = "CREATE TABLE IF NOT EXISTS SOME_NUMBERS\n" +
"( \n" +
" ID VARCHAR NOT NULL,\n" +
" NAME VARCHAR ,\n" +
" ANOTHER_VALUE VARCHAR ,\n" +
" TRANSACTION_TIME TIMESTAMP ,\n" +
" CONSTRAINT pk PRIMARY KEY(ID)\n" +
") " +
"IMMUTABLE_STORAGE_SCHEME=ONE_CELL_PER_COLUMN,\n" +
"UPDATE_CACHE_FREQUENCY=900000,\n" +
"COLUMN_ENCODED_BYTES=NONE,\n" +
"IMMUTABLE_ROWS=true";
String createIndex = "CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME, TRANSACTION_TIME DESC) INCLUDE(ANOTHER_VALUE)";
System.out.println(createTable);
stmt.execute(createTable);
if(CREATE_INDEX == true)
{
System.out.println("\nCreating Index: " + createIndex + "\n");
stmt.execute(createIndex);
}
else
{
System.out.println("\nNot Creating the Index\n");
}
//Insert Some Items.
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();
prstmt.close();
}
}
String countSQL = "SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS ";
System.out.println("\nDoing Count SQL : " + countSQL);
ResultSet rs = stmt.executeQuery(countSQL);
rs.next();
final int totalCount = rs.getInt("TOTAL_ITEMS");
System.out.println("TOTAL COUNT : " + totalCount + "\n");
rs.close();
//Now a Cursor
String cursorSQL = "DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM SOME_NUMBERS where NAME like 'ReferenceData.Country/%' ORDER BY TRANSACTION_TIME DESC";
System.out.println("\nCursor SQL : " + cursorSQL);
PreparedStatement cursorStatement = conn.prepareStatement(cursorSQL);
cursorStatement.execute();
PreparedStatement openCursorStatement = conn.prepareStatement("OPEN MyCursor");
openCursorStatement.execute();
PreparedStatement next10Rows = conn.prepareStatement("FETCH NEXT 10 ROWS FROM MyCursor");
int itemsReturnedByCursor = 0;
while(true)
{
ResultSet cursorRS = next10Rows.executeQuery();
int rowsReadBeforeEmpty = 0;
while(cursorRS.next())
{
itemsReturnedByCursor++;
rowsReadBeforeEmpty++;
}
if(rowsReadBeforeEmpty > 0 )
{
cursorRS.close();
}
else
{
System.out.println("CLOSING THE CURSOR");
int res = conn.prepareStatement("CLOSE MyCursor").executeUpdate();
System.out.println("Result : " + res);
break;
}
if(itemsReturnedByCursor > (totalCount * 3))
{
System.err.println("Aborting the Cursor, as it is more than the count!");
break;
}
}
System.out.println("ITEMS returned by count : " + totalCount + " | Items Returned by Cursor : " + itemsReturnedByCursor );
if(totalCount != itemsReturnedByCursor)
{
throw new RuntimeException("The cursor returned a different number of rows from the count !! ");
}
}
}
