Support Questions
Find answers, ask questions, and share your expertise

Phoenix upsert gives null pointer without an arbitrary limit

Phoenix upsert gives null pointer without an arbitrary limit

Explorer

Phoenix can't do a bunch of upserts without a limit. This is really weird since the number of rows updated are less than the limit.

UPSERT INTO ASSETS(ACCOUNT_ID, EFFECTIVE_DATE, SRC, SECURITY, AVG_VOL)
SELECT a.ACCOUNT_ID,a.EFFECTIVE_DATE, a.SRC,a.SECURITY,
ROUND(CASE WHEN a.SHARES_OUTSTANDING IS NOT NULL THEN a.SHARES_OUTSTANDING * RAND(5) * 0.1 ELSE 0.00 END,2)
FROM ASSETS a
WHERE a.ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM AL_MAP WHERE LIST_ID = 'MYLIST')
AND a.EFFECTIVE_DATE = TO_DATE('2017-03-02','yyyy-MM-dd') 

You will get the following exception:

Query execution failed. 
Reason: SQL Error[101][08000]: java.lang.NullPointerException

Now, run the same query with a LIMIT and it works. The funny things is the total number of rows updated are less than the LIMIT . You can set the limit to a billion rows and it doesn't matter.

UPSERT INTO ASSETS(ACCOUNT_ID,EFFECTIVE_DATE,SRC,SECURITY,AVG_VOL)
SELECT a.ACCOUNT_ID,a.EFFECTIVE_DATE, a.SRC,a.SECURITY,
ROUND(CASE WHEN a.SHARES_OUTSTANDING IS NOT NULL THEN a.SHARES_OUTSTANDING * RAND(5) * 0.1 ELSE 0.00 END,2)
FROM ASSETS a
WHERE a.ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM AL_MAP WHERE LIST_ID = 'MYLIST')
AND a.EFFECTIVE_DATE = TO_DATE('2017-03-02','yyyy-MM-dd')
LIMIT 1000000;

This works.

Does anyone know why phoenix does this? I'm on HDP 2.6.2

1 REPLY 1

Re: Phoenix upsert gives null pointer without an arbitrary limit

Super Collaborator

Could you please provide the full exception? Only a clue I have at the moment that it's related to the partial result set (PHOENIX-3112) which is fixed in HDP 2.6.3. But to say for sure it would be nice to have the full exception as well as the explain plans for both queries.