Inner Join Cursor Query fails with NullPointerException - JoinCompiler.java:187

[Jack Steenkamp]

Hi All,

Using Phoenix 4.14.1, I have come across an inner join query in my
application that fails with the NullPointerException if executed as part of
a Cursor, but executes fine if done without it.

To reproduce this issue, you can run the attached program (assuming you
update the JDBC_URL to point to an instance you have running) or you can
follow the steps below:

Create the Table:

CREATE TABLE IF NOT EXISTS MY_STATS
(
   ID                  VARCHAR    NOT NULL,
   ENTRY_NAME                     VARCHAR    ,
   ENTRY_VALUE           DOUBLE     ,
   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

Execute a normal query (this works fine):

SELECT * FROM MY_STATS
   INNER JOIN
   (
    SELECT ENTRY_NAME, MAX(TRANSACTION_TIME) AS TRANSACTION_TIME
FROM MY_STATS
     GROUP BY ENTRY_NAME
   ) sub
   ON MY_STATS.ENTRY_NAME = sub.ENTRY_NAME AND MY_STATS.TRANSACTION_TIME =
sub.TRANSACTION_TIME
ORDER BY MY_STATS.TRANSACTION_TIME DESC

Now if you execute the same query, but with the cursor declaration at the
top -

DECLARE MyCursor CURSOR FOR

It produces the following exception:

Exception in thread "main" java.lang.NullPointerException
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.resolveTable(JoinCompiler.java:187)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:224)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:181)
at
org.apache.phoenix.parse.DerivedTableNode.accept(DerivedTableNode.java:49)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:201)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:181)
at org.apache.phoenix.parse.JoinTableNode.accept(JoinTableNode.java:81)
at org.apache.phoenix.compile.JoinCompiler.compile(JoinCompiler.java:138)
at
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:190)
at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:153)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:490)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeclareCursorStatement.compilePlan(PhoenixStatement.java:950)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeclareCursorStatement.compilePlan(PhoenixStatement.java:941)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:401)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)
at
com.jsteenkamp.phoenix.PhoenixInnerJoinCursorTest.testCursorQuery(PhoenixInnerJoinCursorTest.java:68)
at
com.jsteenkamp.phoenix.PhoenixInnerJoinCursorTest.main(PhoenixInnerJoinCursorTest.java:20)

I think this may be a bug  - though perhaps this is something you might
have come across before?

Thanks,

package com.jsteenkamp.phoenix;

import java.sql.DriverManager;
import java.sql.Statement;

/**
 *  Reproducing the inner join cursor issue
 */
public class PhoenixInnerJoinCursorTest
{
	private final boolean RUN_WITH_CURSOR = 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 
	{
		PhoenixInnerJoinCursorTest innerJoinTest = new PhoenixInnerJoinCursorTest();
		innerJoinTest.testCursorQuery();
		System.out.println("ALL GOOD - No Exception");
	}
	
	public PhoenixInnerJoinCursorTest() throws Exception
	{
		System.out.println("Connecting To : " + JDBC_URL);
		conn = DriverManager.getConnection(JDBC_URL, "sa", "");
		stmt = conn.createStatement();
	}
	
	public void testCursorQuery() throws Exception
	{
		stmt.execute("DROP TABLE IF EXISTS MY_STATS");
		String createTable = "CREATE TABLE IF NOT EXISTS MY_STATS\n" + 
				"(	\n" + 
				"   ID        		                     VARCHAR    NOT NULL,\n" + 
				"   ENTRY_NAME                           VARCHAR    ,\n" +
				"   ENTRY_VALUE		                     DOUBLE     ,\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";
		
		System.out.println(createTable);
	
		stmt.execute(createTable);
 
		StringBuilder sql = new StringBuilder();
		if(RUN_WITH_CURSOR == true)
		{
			sql.append(" DECLARE MyCursor CURSOR FOR \n");
		}
		sql.append("SELECT * FROM MY_STATS\n" + 
				"   INNER JOIN \n" + 
				"   (\n" + 
				"   	SELECT ENTRY_NAME, MAX(TRANSACTION_TIME) AS TRANSACTION_TIME \n" + 
				"			 FROM MY_STATS \n" + 
				"     GROUP BY ENTRY_NAME\n" + 
				"   ) sub\n" + 
				"   ON MY_STATS.ENTRY_NAME = sub.ENTRY_NAME AND MY_STATS.TRANSACTION_TIME = sub.TRANSACTION_TIME \n" + 
				"ORDER BY MY_STATS.TRANSACTION_TIME DESC ");
		
		System.out.println("Executing: \n" + sql);
		
		stmt.execute(sql.toString());
	}
	
}