Support Questions

Find answers, ask questions, and share your expertise

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

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.

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.