NullPointerException on evaluate with Conditional Aggregation

#1

Trying to execute an evaluate with a conditional aggregate (Expression: sum(col1=="X"?col2:0), SQL: sum(case when col1='X' then col2 else 0 end) fails with a NullPointerException when the conditional result (col2) is a column rather than a constant, even though the SQL that gets generated appears to be valid.

Consider: var shouldWork = CountryCode.evaluate({projection: 'sum(alphaTwoCode=="US"?number(numericCode):0)', explain:true, forceDbEngineEval: true})
This specific example has no business value, but uses standard types so it is easy to reproduce. This should work, but if you try it, you will get Error: wrapped NullPointerException.

It appears that the correct SQL is being generated and executed, but the error happening somewhere else. To verify, change the above slightly in order to product invalid SQL.
var willFail = CountryCode.evaluate({projection: 'sum(alphaTwoCode==0?number(numericCode):0)', explain:true, forceDbEngineEval: true})

That will rightfully fail, due to a SQL error, but the benefit is it will return the actual SQL query in the error. Change the parameters from that query to match the shouldWork query above. (replace tenantTagId with one appropriate for your environment)
var q = "SELECT (SUM(CASE WHEN T.ALPHATWOCODE_S='US' THEN CAST(T.NUMERICCODE_S AS DECIMAL) ELSE 0 END)) P1 FROM C3_2_GEO_CNTRYCODE T WHERE T.TENANT_TAG_ID=9 AND ((NOT ((T.HIDDEN_B IS NOT NULL)) OR (T.HIDDEN_B = 'false' OR T.HIDDEN_B IS NULL))) LIMIT 2001"
And execute it directly (bad practice, I know, wouldn’t ever do for an actual app):
c3Grid(DbAdmin.executeQuery(q).results)

And we get the expected results.

This leads me to believe that the original evaluate should work, as it is capable of generating an appropriate query, but a bug or something somewhere is preventing it from running.

Is this a known issue and are there plans to resolve it?
‘base’ version: 7.8.69.

Also note, this seems to only happen when the conditional results in a column. If you change the shouldWork query to use 1 instead of number(numericCode), it works.

#2

You should never get a NullPointerException. Things should either “work” or throw an appropriate error message if they are invalid. Please file a ticket.