Triage errors in queries

Everyone makes mistakes when writing queries in the Deephaven Query Language (DQL), as in any programming language. Mistakes often cause queries to fail to start properly or to crash during runtime. This guide shows you how to:

  • Read a stack trace to find the root cause.
  • Recognize and fix common mistakes, including problems that produce wrong results or pauses instead of an exception.
  • Check your query strings for errors.
  • Get help, including what to put in a bug report, when you can't find the cause.

Read a stack trace

At this point, you have written some Deephaven queries that should produce some interesting tables and visualizations, but when you attempt to run the query, it fails.

The first step is to find the error (an exception) and isolate its root cause. When you run code in the IDE console, the error appears in the console output. The Log panel shows the same error with its log header.

The log displays a large block of text that describes the exception and a chain of further exceptions that leads to the root of the problem.

Tip

You don't need to read the whole trace. The rules of thumb at the end of this section list which lines to read and which to skip.

For example, the following code creates a function that raises an error and a table that calls it, passing the row index special variable i:

The query fails with the following error:

Deephaven stack traces tend to be very large, but they open with a summary of the failing operation. Look at the first few lines of the above stack trace:

The highlighted RuntimeError line shows that the error happened during an update operation, while Deephaven computed column X.

Scroll past the first block of indented lines that start with at. The next block, which starts with caused by, has more important information:

The last two lines point to exactly where the error happens:

  • File "<string>", line 5, in throw_func shows that the error happened on line 5, where throw_func raises the RuntimeError.
  • RuntimeError: Error: 0 gives the error type and message.

The _wrapper frame is code that Deephaven generates to call your function, so skip it.

Some rules of thumb for triaging errors in Deephaven Python queries:

  • Read the first few lines for a summary of the error.
  • Follow the caused by blocks to the last one, which names the root cause.
  • Skip the lines that start with at, which are Java stack frames.
  • Keep the indented File ... lines of a Python traceback. The File "<string>" lines point to code you ran in the console, except _wrapper, which Deephaven generates to call your function.

This is a very simple example of triaging an equally simple query with a deliberate error. In practice, queries are much more sophisticated and contain many interdependencies between tables. The recipe for triage, however, is the same.

The Console in the Deephaven IDE is a critical tool during triage. After the stack trace isolates the failing operation, use it to execute parts of the query bit by bit, analyze the inputs to the failing method, and experiment with changes to solve the problem.

For a fuller walkthrough of Python error output, including errors from ticking tables and query strings that fail to compile, see Decipher Python errors.

Common mistakes

Once you've fixed the problems that stop a query from starting, it starts serving up tables and plots. The following sections show common mistakes, how to recognize them, and how to fix them.

Some queries fail as soon as they run. Others fail later, when new data brings an unexpected value. These runtime failures can be trickier to triage because they often depend on unexpected patterns in streaming data.

To triage either kind, use the rules of thumb at the end of Read a stack trace: read the first few lines, skip the lines that start with at, and follow the caused by blocks to the last one.

Two of the problems below, Non-deterministic formulas and Garbage collection (GC) pauses, show up as wrong results or pauses rather than an exception. Those sections describe what to look for instead.

Unexpected null values

It's easy to forget that columns can contain null values. Calling a Java method on a null value, such as Items.charAt(0) when Items is null, fails with a NullPointerException.

The following query creates a table with a string column that contains a null value. It then uses the charAt method to keep strings that start with B.

In this example, the last caused by line of the stack trace contains the cause:

<local7> is the JVM's name for a variable in the filter code that Deephaven generates. The number can differ.

To fix the filter, check whether the value is null before you call a method on it:

Note

Adding !isNull(Items) to the start of the filter works because of short-circuit evaluation. When !isNull(Items) is false, the engine skips the rest of the where clause, so Items.charAt(0) == 'B' never runs on a null value.

String and array access

Reading past the end of a string, a vector, or a Java array behaves differently for each type:

The following query creates a table with a string column that holds strings of varying length. It then keeps the strings whose second character is i.

As with the null-value error in Unexpected null values, the last caused by line contains the cause:

As in Read a stack trace, the RuntimeError line near the top of the stack trace names the TableInitializationException. It also shows the query string that failed, where([Items.charAt(1) == 'i']). The filter tried to read index 1 of a string that has only one character.

We can inspect the table by adding a new column ItemsLength = Items.length():

To see the rows where the string is less than two characters long, right-click the ItemsLength column header, open the Advanced Filters panel, and filter to values less than 2:

The Advanced Filters panel, set to filter ItemsLength to less than 2

Guard the charAt call with a length check, the same way Unexpected null values guards it with a null check:

This works because of short-circuit evaluation. When Items.length() >= 2 is false, the engine skips Items.charAt(1), so it never reads past the end of a short string.

If you only care whether the string contains the i character, a better solution is:

As always, use the Deephaven IDE to validate that this change works and produces the result you expect!

Vectors behave differently. The following query uses group_by to collect the Value column into a vector for each Key, then reads the third element of each vector:

The vector for Key 1 has only two elements, so ThirdValue is null in that row. The query doesn't fail. When a query reads vector elements by position, look downstream for unexpected null values.

Join key problems

Deephaven's join operations are one of its most powerful features. They combine tables by matching values in key columns to produce new tables.

Some joins allow any number of matching rows on each side, but others do not. By default, natural_join allows at most one right-table row for each key that a left row matches. If a left row's key matches more than one right-table row, the join fails. The following query shows this:

Because 31 appears twice in the DeptID column in the right table, the query fails with the following error:

If the right table can contain duplicate keys, pass NaturalJoinType.FIRST_MATCH or NaturalJoinType.LAST_MATCH as the type argument of natural_join to choose which match to keep. Import NaturalJoinType from deephaven.table. The following query keeps the last match:

The join now succeeds. LAST_MATCH keeps the last right-table row for key 31, so DeptName is Marketing.

Non-deterministic formulas

Not all errors have stack traces. Some queries run without an error but produce wrong results.

A deterministic function is one that produces the exact same result every single time when given the same input parameters. A non-deterministic function, such as a random number generator, does not. In a formula column, a non-deterministic function can return a different value each time the column is read. Consider the following query:

The NumberTimesTwo values are all even, but most are not twice the value in Number. Here is why:

  • update_view creates a formula column. A formula column stores the formula, not the values, and calculates each value on demand.
  • Each time something reads Number, randomInt runs again and returns a new value.
  • NumberTimesTwo reads Number again, so it doubles a different random number than the one Number shows.

When a formula calls a non-deterministic function, create its column with update instead of update_view, so that Deephaven calculates and stores each value once:

Now every NumberTimesTwo value is exactly twice Number.

Errors like these are trickier to diagnose than an exception, so pay attention to which kind of column you create when a formula calls a non-deterministic function.

Out of memory

A common problem is running out of heap memory as data volume grows during the day. It happens when your Deephaven instance has too little heap. Unexpected data patterns, such as unusually large Kafka input, make it worse.

The most common out-of-memory error looks like this:

Start by increasing the heap. If that isn't enough, review the query for changes that reduce heap usage. In practice, the fix often combines several of the techniques below.

To increase the heap allocated to the Java Virtual Machine (JVM), set -Xmx. Where you set it depends on how you run Deephaven:

  • Docker or the start script: set it in START_OPTS, for example START_OPTS=-Xmx8g.
  • pip-installed server: pass it in jvm_args, for example Server(jvm_args=["-Xmx8g"]). With the deephaven server command, pass it with --jvm-args, for example --jvm-args "-Xmx8g". See Install and run Deephaven with pip.

If you run Deephaven with Docker, as in the Docker install guide, make sure Docker itself has enough memory for that heap. See Configure and adjust heap size.

Increasing the heap is enough for many workloads. Be careful when you cross 32GB:

  • Below about 32GB, the JVM uses compressed object pointers, which make each object reference smaller.
  • At about 32GB and above, the JVM turns compressed object pointers off so it can address the whole heap.
  • As a result, raising the heap from 30GB to 32GB leaves less usable heap for your query.

Tip

If you must cross the 32GB boundary, increase the heap well past it, because uncompressed object pointers use more of the heap.

The next step is to review your query code.

  • Where you use update, consider update_view. It stores the formula instead of the values, which trades CPU time for memory. For simple computations, this is often a good trade. See Choose the right column selection method.
  • Reuse tables instead of recomputing them. Look for duplicate tables, or tables that differ only in which columns they keep, and derive later tables from one shared table. Deephaven reuses the result of many repeated operations on the same table, but derive from a shared table explicitly rather than rely on that.
  • Watch the number of unique keys in tables you pass to key-based operations such as partition_by, group_by, and join. If keys are mostly unique within a table, such as a SaleID column, partition_by can produce millions of single-row tables, each with some heap overhead. In that case, consider different keys or different operations.
  • Consider the order of your query operations. Filter data before you join tables.
    • A very simple example is applying a join and a where condition. In derived = my_table.join(my_other_table, "JoinColumn").where("FilterColumn = `FilterValue`"), the join matches rows from my_table that the where expression later filters out, so it uses much more heap than it needs to.
    • Filter first instead: derived = my_table.where("FilterColumn = `FilterValue`").join(my_other_table, "JoinColumn"). This uses less heap and reduces how many rows downstream tables process each time the left or right table updates.

A query can use more heap than expected for many other reasons. Finding them requires a deep analysis of how the query performs at runtime. You may need to use a Java profiling tool (such as JProfiler) to identify poorly performing sections of your code.

Garbage collection (GC) pauses

GC pauses are another heap-related problem. They happen when your query has enough memory but processes a lot of data on each tick. A tick is one update cycle of a ticking table, which can add, modify, or remove rows.

  • A long series of query operations allocates a lot of temporary heap on each tick.
  • When those computations finish, the JVM garbage collector reclaims that memory.
  • Garbage collection uses CPU time. Long collections pause query processing, and computations back up.

The symptom is periodic long pauses while the JVM frees memory. When you see this, reduce how often your tables tick.

Checklist for query string errors

Before you file a bug, check your query strings against this list.

  1. Is the query string itself properly quoted as a Python string?

    • Query strings in operations such as where or update must be Python strings, in single or double quotes. For example, my_table.where(filters=["StringColumn = `TheValue`"]).
    • If you pasted an expression from another app, such as Slack, check for Unicode curly quotation marks (U+201C and U+201D). Some apps substitute them for the ASCII quotation mark (U+0022).
  2. Do you have matching parentheses?

    • Make sure every opening parenthesis has a matching closing parenthesis.
  3. If you are referring to a column in a Deephaven query expression:

    • Is the column name spelled and capitalized correctly?
      • A formula or a range filter, such as Price > 5, that references a column that doesn't exist fails with a FormulaCompilationException, because the engine cannot compile it.
      • A simple match filter, such as StringColumn = `TheValue`, fails instead with a message that the column doesn't exist in this table, followed by the table's available columns.
    • Is the type of the column what you expect it to be?
    • Did you account for null values?
  4. If your query string contains string literals, did you quote them properly?

    • Inside a query string, quote string literals with backticks (`) or Java double quotes ("), not single quotes ('), which Deephaven uses for character and date-time literals.
    • Do you have matching close quotes for your open quotes?
  5. If you are using date-times in your expressions:

    • Did you use single quotes ('), not double quotes (") or backticks (`)?
    • Did you use the proper format? The most common date-time format is yyyy-MM-ddTHH:mm:ss[.SSSSSSSSS] TZ. The fractional seconds are optional and can have up to nine digits. For example, '2024-01-01T09:30:00 ET' or '2024-01-01T09:30:00.123 ET'.
  6. Are all classes that you are trying to use properly imported?

    • A query string can use a class by its simple name only if the query library imports it. The query library imports a set of defaults, and server configuration and installed extensions can add more.
    • To use any other Java class, write its fully-qualified name, such as java.net.URI.create(`https://deephaven.io`). Or import it with query_library.import_class or query_library.import_package, and then use its simple name. For example, after query_library.import_class("java.net.URI"), a query string can call URI.create(`https://deephaven.io`). See Java objects and classes in query strings.

Get help

If you've gone through all of the steps to identify the root of a problem, but can't find a cause in your query, you may have uncovered a bug. In this case, you should file a bug report. Be sure to include the following with your bug report:

  • The Deephaven server and web UI versions. See Get version information.
  • The complete stack trace, not just the list of caused by blocks.
  • A code snippet or a description of the query.
  • Any logs from your user console, or from your terminal if you run Deephaven in Docker.
  • Support logs exported from the browser. Open User Settings and click Export Logs to download a zip with the browser logs and the current state of the application. See Export support logs.

You can also get help by asking questions in our GitHub Discussions forum.