Formula evaluation errors

This guide describes various types of errors that occur while evaluating a formula. You may also encounter formula syntax errors that need to be debugged.

Missing columns

A common mistake to make in a formula is referencing a column that does not exist. In the following example, the formula attempts to evaluate owner.toLowerCase():

pqsl = db.liveTable("DbInternal", "PersistentQueryStateLog").where("Date=today()").update("owner=owner.toLowerCase()")

pqsl = (
    db.live_table("DbInternal", "PersistentQueryStateLog")
    .where("Date=today()")
    .update("owner=owner.toLowerCase()")
)

However, the PersistentQueryStateLog does not contain an owner column. The formula parser does not know that a particular name should be a column rather than a variable name, class name, or other identifier. In the error message, the parser simply reports that it Cannot find variable or class owner:

r-Scheduler-Serial-1 | .c.ConsoleServiceGrpcImpl | Error running script: java.lang.RuntimeException: Error in Python interpreter:
Type: <class 'deephaven.dherror.DHError'>
Value: table update operation failed. : Cannot find variable or class owner
Traceback (most recent call last):
  File "/usr/illumon/coreplus/venv/latest/lib/python3.10/site-packages/deephaven/table.py", line 994, in update
    return Table(j_table=self.j_table.update(*formulas))
RuntimeError: io.deephaven.engine.table.impl.select.FormulaCompilationException: Formula compilation error for: owner.toLowerCase()
...
caused by io.deephaven.engine.table.impl.lang.QueryLanguageParser$QueryLanguageParseException:

Having trouble with the following expression:
Full expression           : owner.toLowerCase()
Expression having trouble :
Exception type            : io.deephaven.engine.table.impl.lang.QueryLanguageParser$ParserResolutionFailure
Exception message         : Cannot find variable or class owner

You know, however, that you intended to reference a column, so you can query the table's definition to get a list of valid columns:

 pqsl = db.liveTable("DbInternal", "PersistentQueryStateLog").where("Date=today()").meta()

x = (
    db.live_table("DbInternal", "PersistentQueryStateLog")
    .where("Date=today()")
    .meta_table
)

As the actual name of the column is Owner, update the query accordingly:

pqsl = db.liveTable("DbInternal", "PersistentQueryStateLog").where("Date=today()").update("Owner=Owner.toLowerCase()")

pqsl = (
    db.live_table("DbInternal", "PersistentQueryStateLog")
    .where("Date=today()")
    .update("Owner=Owner.toLowerCase()")
)

If you intended to use a variable, you can get a list of variable names in the session as follows:

println(getBinding().getVariables().keySet())

print(globals().keys())

Null pointers

A common programming error is to reference a field or call a function on a null object. The following example normalizes the ServerHost column to lowercase values:

pqsl = db.liveTable("DbInternal", "PersistentQueryStateLog").where("Date=today()").update("ServerHost = ServerHost.toLowerCase()")

pqsl = (
    db.live_table("DbInternal", "PersistentQueryStateLog")
    .where("Date=today()")
    .update("ServerHost = ServerHost.toLowerCase()")
)

However, the ServerHost is not always set, which results in a NullPointerException:

r-Scheduler-Serial-1 | .c.ConsoleServiceGrpcImpl | Error running script: java.lang.RuntimeException: Error in Python interpreter:
Type: <class 'deephaven.dherror.DHError'>
Value: table update operation failed. : java.lang.NullPointerException: Cannot invoke "String.toLowerCase()" because "<parameter1>" is null
Traceback (most recent call last):
  File "/usr/illumon/coreplus/venv/latest/lib/python3.10/site-packages/deephaven/table.py", line 994, in update
    return Table(j_table=self.j_table.update(*formulas))
RuntimeError: io.deephaven.engine.exceptions.TableInitializationException: Error while initializing Update([ServerHost]): an exception occurred while performing the initial select or update
...
caused by io.deephaven.engine.table.impl.select.FormulaEvaluationException: In formula: ServerHost = ServerHost.toLowerCase()
...
caused by java.lang.NullPointerException: Cannot invoke "String.toLowerCase()" because "<parameter1>" is null
	at io.deephaven.temp.c_914d7370e887ba0f320fa6d77fa167632419bb643a30bf0bfcc088f3113a7043v61_0.Formula.applyFormulaPerItem(Formula.java:155)
	... 23 more

The simplest workaround in these cases is to check for null using a ternary operator and return null as follows:

pqsl = db.liveTable("DbInternal", "PersistentQueryStateLog").where("Date=today()").update("ServerHost = ServerHost == null ? null : ServerHost.toLowerCase()")

pqsl = (
    db.live_table("DbInternal", "PersistentQueryStateLog")
    .where("Date=today()")
    .update("ServerHost = ServerHost == null ? null : ServerHost.toLowerCase()")
)

Classes not in your Query Library

One of the most powerful features of Deephaven is its ability to call into arbitrary Java libraries. For example, we may want to use the Apache Commons Codec Hex class to decode hexadecimal numbers into byte arrays. The following example calls the Hex.decodeHex function on several values:

x = emptyTable(1).update("A=new String[]{`d00d`, `feed`, `cafe`}").ungroup()
y = x.update("Bytes=Hex.decodeHex(A)")

from deephaven import empty_table

x = empty_table(1).update("A=new String[]{`d00d`, `feed`, `cafe`}").ungroup()
y = x.update("Bytes=Hex.decodeHex(A)")

Instead of byte arrays, we get the following error:

r-Scheduler-Serial-1 | .c.ConsoleServiceGrpcImpl | Error running script: java.lang.RuntimeException: Error in Python interpreter:
Type: <class 'deephaven.dherror.DHError'>
Value: table update operation failed. : Cannot find variable or class Hex
Traceback (most recent call last):
  File "/usr/illumon/coreplus/venv/latest/lib/python3.10/site-packages/deephaven/table.py", line 994, in update
    return Table(j_table=self.j_table.update(*formulas))
RuntimeError: io.deephaven.engine.table.impl.select.FormulaCompilationException: Formula compilation error for: Hex.decodeHex(A)
...
caused by io.deephaven.engine.table.impl.lang.QueryLanguageParser$QueryLanguageParseException:

Having trouble with the following expression:
Full expression           : Hex.decodeHex(A)
Expression having trouble :
Exception type            : io.deephaven.engine.table.impl.lang.QueryLanguageParser$ParserResolutionFailure
Exception message         : Cannot find variable or class Hex
...

Deephaven's formula parser does not consider all classes on the classpath when parsing a formula. You must add the formula to the query library in the current execution context. Manually adding classes to the query library prevents most changes in worker dependencies from affecting the behavior of queries and also permits better isolation between different queries. So, you must add the Hex class to the query library to get the desired result:

import io.deephaven.engine.context.ExecutionContext

ExecutionContext.getContext().getQueryLibrary().importClass(org.apache.commons.codec.binary.Hex.class)

x = emptyTable(1).update("A=new String[]{`d00d`, `feed`, `cafe`}").ungroup()
y = x.update("Bytes=Hex.decodeHex(A)")

from deephaven import empty_table
from deephaven import query_library

query_library.import_class("org.apache.commons.codec.binary.Hex")

x = empty_table(1).update("A=new String[]{`d00d`, `feed`, `cafe`}").ungroup()
y = x.update("Bytes=Hex.decodeHex(A)")

Errors/Exceptions from called functions

Deephaven permits calling arbitrary functions, and as such, the engine does not know the requirements for the function inputs. In this example, the Hex.decodeHex function is used on the string zazu, which is not a valid hexadecimal number:

import io.deephaven.engine.context.ExecutionContext

ExecutionContext.getContext().getQueryLibrary().importClass(org.apache.commons.codec.binary.Hex.class)

x = emptyTable(1).update("A=new String[]{`d00d`, `feed`, `zazu`}").ungroup()
y = x.update("Bytes=Hex.decodeHex(A)")

from deephaven import empty_table
from deephaven import query_library

query_library.import_class("org.apache.commons.codec.binary.Hex")

x = empty_table(1).update("A=new String[]{`d00d`, `feed`, `zazu`}").ungroup()
y = x.update("Bytes=Hex.decodeHex(A)")

The engine reports the following exception:

r-Scheduler-Serial-1 | .c.ConsoleServiceGrpcImpl | Error running script: java.lang.RuntimeException: Error in Python interpreter:
Type: <class 'deephaven.dherror.DHError'>
Value: table update operation failed. : org.apache.commons.codec.DecoderException: Illegal hexadecimal character z at index 0
Traceback (most recent call last):
  File "/usr/illumon/coreplus/venv/latest/lib/python3.10/site-packages/deephaven/table.py", line 994, in update
    return Table(j_table=self.j_table.update(*formulas))
RuntimeError: io.deephaven.engine.exceptions.TableInitializationException: Error while initializing Update([Bytes]): an exception occurred while performing the initial select or update
...
caused by io.deephaven.engine.table.impl.select.FormulaEvaluationException: In formula: Bytes = Hex.decodeHex(A)
...
caused by org.apache.commons.codec.DecoderException: Illegal hexadecimal character z at index 0
	at org.apache.commons.codec.binary.Hex.toDigit(Hex.java:371)
	at org.apache.commons.codec.binary.Hex.decodeHex(Hex.java:109)
	at org.apache.commons.codec.binary.Hex.decodeHex(Hex.java:79)
	at org.apache.commons.codec.binary.Hex.decodeHex(Hex.java:130)
	at io.deephaven.temp.c_16c55d27f8bcee334df501cc290dd21091f1bce6413a4cff9603588b4d565b20v61_0.Formula.applyFormulaPerItem(Formula.java:156)
	... 23 more

In these cases, you must examine the exception message produced by the function, the documentation of the function you are calling, and the input data to determine why it failed. In some cases, using a debugger is necessary to examine the state of the Java Virtual Machine (JVM).

Unsafe refreshing uses of ii/i/k

The Deephaven engine exposes virtual row variables for row position (i and ii) and row key (k). These variables can only be used in append-only ticking tables. The query engine cannot determine correct propagation and, therefore, fails. The following example references the prior row in an update formula:

x = db.liveTable("DbInternal", "ProcessEventLog").where("Date=today()").update("SameProc=Process_[-1 + i] == Process")

x = (
    db.live_table("DbInternal", "ProcessEventLog")
    .where("Date=today()")
    .update("SameProc=Process_[-1 + i] == Process")
)

It fails with the following exception:

RuntimeError: java.lang.IllegalArgumentException: Formula 'eq(Process_.get(longCast(plus(negate(1), i))), Process)' uses i, ii, k, or column array variables, and is not safe to refresh. Note that some usages, such as on an append-only table are safe. To allow unsafe refreshing formulas, set the system property io.deephaven.engine.table.impl.select.AbstractFormulaColumn.allowUnsafeRefreshingFormulas.

When this error occurs, you must use alternative query logic to achieve your desired result. For example, you may be able to group your data, apply a transformation, and then ungroup the data.