---
title: Unexpected results
sidebar_label: Unexpected results
---

Unexpected results occur when Deephaven queries execute successfully but produce output that differs from what you anticipated. These issues can manifest as missing data, incorrect calculations, unexpected null values, or filtered results that don't match your intended criteria.

Debugging data issues is critical for ensuring the accuracy and reliability of your analytics. Even when queries don't throw errors, incorrect results can lead to flawed analysis and decision-making if not properly investigated and resolved.

## Debugging strategies

### Breaking down complex queries

The Deephaven query engine's composable nature provides an effective approach for troubleshooting unexpected results. By breaking complex queries into smaller operations and assigning intermediate results to variables, you can systematically identify exactly where your data transforms in unexpected ways.

### Visualizing intermediate results

After breaking down a complex query into smaller operations, examine each intermediate result to pinpoint where the data diverges from your expectations. This isolation technique helps you identify the specific operation causing the issue.

### Verifying data assumptions

Many unexpected results stem from incorrect assumptions about the underlying data. Always verify your expectations about data completeness, column types, and value ranges when troubleshooting results.

## Common types of unexpected results

### Missing or null values

Unexpected null values often appear when joined tables don't contain matching records or when data sources are incomplete. Consider this example that attempts to show the in-use heap for each Persistent Query (PQ) at a specific timestamp:

#### Step 1: Initial query that produces unexpected null values

```groovy
stateLog = db.liveTable("DbInternal", "PersistentQueryStateLog").lastBy("SerialNumber")
withState = stateLog.where("Status in `Running`, `Initializing`, `AcquiringWorker`, `Connecting`, `Stopping`, `Executing`").view("SerialNumber", "Name", "VersionNumber", "Status")
withConfiguration = withState.naturalJoin(db.liveTable("DbInternal", "PersistentQueryConfigurationLogV2").lastBy("SerialNumber", "VersionNumber"), "SerialNumber,VersionNumber", "HeapSizeInGB")
```

```python
state_log = db.live_table("DbInternal", "PersistentQueryStateLog").last_by(
    "SerialNumber"
)
with_state = state_log.where(
    "Status in `Running`, `Initializing`, `AcquiringWorker`, `Connecting`, `Stopping`, `Executing`"
).view(["SerialNumber", "Name", "VersionNumber", "Status"])
with_configuration = with_state.natural_join(
    db.live_table("DbInternal", "PersistentQueryConfigurationLogV2").last_by(
        ["SerialNumber", "VersionNumber"]
    ),
    "SerialNumber,VersionNumber",
    "HeapSizeInGB",
)
```

This produces the following table in a Code Studio:

![No Heap size is present for the running query](../assets/debugging/nodata-try1.png)

#### Step 2: Identifying the problem

Notice that the `HeapSizeInGB` column contains a `null` value for a running query. We expect that every running query has a heap size.

#### Step 3: Breaking down the query

To debug this issue, we break the query into constituent operations to find the first operation that does not contain our expected data. Here is an equivalent query that splits out the `PersistentQueryConfigurationLogV2` into a separately named variable called `config_log`:

```python
state_log = db.live_table("DbInternal", "PersistentQueryStateLog").last_by(
    "SerialNumber"
)
with_state = state_log.where(
    "Status in `Running`, `Initializing`, `AcquiringWorker`, `Connecting`, `Stopping`, `Executing`"
).view(["SerialNumber", "Name", "VersionNumber", "Status"])
config_log = db.live_table("DbInternal", "PersistentQueryConfigurationLogV2").last_by(
    ["SerialNumber", "VersionNumber"]
)
with_configuration = with_state.natural_join(
    config_log, "SerialNumber,VersionNumber", "HeapSizeInGB"
)
```

```groovy
stateLog = db.liveTable("DbInternal", "PersistentQueryStateLog").lastBy("SerialNumber")
withState = stateLog.where("Status in `Running`, `Initializing`, `AcquiringWorker`, `Connecting`, `Stopping`, `Executing`").view("SerialNumber", "Name", "VersionNumber", "Status")
configLog = db.liveTable("DbInternal", "PersistentQueryConfigurationLogV2").lastBy("SerialNumber", "VersionNumber")
withConfiguration = withState.naturalJoin(configLog, "SerialNumber,VersionNumber", "HeapSizeInGB")
```

![Sleep on Startup is not present](../assets/debugging/nodata-try2.png)

#### Step 4: Analyzing intermediate results

From this table, we can see that "Sleep on Startup" is not present, so the error is that `config_log` does not have the desired data. There is no filter for the table, but only data for today's date is present.

#### Step 5: Using UI tools to verify data assumptions

The easiest way to see that there is only one value is to hover over the Date column header in the UI to display the statistics, which confirms there is only one unique value:

![Sleep on Startup is not present](../assets/debugging/nodata-columnstats.png)

#### Step 6: Understanding the root cause

The `live_table` method we used for retrieving the `DbInternal.PersistentQueryConfigurationLogV2` table only contains data served by the Data Import Server, not data that has been [merged](../data-guide/merging.md) to historical storage.

#### Step 7: Implementing the solution

If we combine the historical configuration data with live data, then we achieve the desired result:

```python
from deephaven import merge

state_log = db.live_table("DbInternal", "PersistentQueryStateLog").last_by(
    "SerialNumber"
)
with_state = state_log.where(
    "Status in `Running`, `Initializing`, `AcquiringWorker`, `Connecting`, `Stopping`, `Executing`"
).view(["SerialNumber", "Name", "VersionNumber", "Status"])
config_log = merge(
    [
        db.historical_table("DbInternal", "PersistentQueryConfigurationLogV2"),
        db.live_table("DbInternal", "PersistentQueryConfigurationLogV2"),
    ]
).last_by(["SerialNumber", "VersionNumber"])
with_configuration = with_state.natural_join(
    config_log, "SerialNumber,VersionNumber", "HeapSizeInGB"
)
```

```groovy
stateLog = db.liveTable("DbInternal", "PersistentQueryStateLog").lastBy("SerialNumber")
withState = stateLog.where("Status in `Running`, `Initializing`, `AcquiringWorker`, `Connecting`, `Stopping`, `Executing`").view("SerialNumber", "Name", "VersionNumber", "Status")
configLog =merge(db.historicalTable("DbInternal", "PersistentQueryConfigurationLogV2"), db.liveTable("DbInternal", "PersistentQueryConfigurationLogV2")).lastBy("SerialNumber", "VersionNumber")
withConfiguration = withState.naturalJoin(configLog, "SerialNumber,VersionNumber", "HeapSizeInGB")
```

#### Step 8: Verifying the solution

Rerunning the query with the historical data included results in a value for our heap size.

![We see our desired heap usage](../assets/debugging/nodata-try3.png)

## Summary

When debugging unexpected results in Deephaven:

1. Break complex queries into smaller components with assigned variables.
2. Examine intermediate results at each step.
3. Verify data types, formats, and completeness.
4. Consider whether the issue is with your expectations rather than the data.
5. Check for historical data when working with time-based tables.

## Related documentation

- [Merging data](../data-guide/merging.md)
- [PersistentQueryConfigurationLogV2](../sys-admin/internal-tables/persistent-query-configuration-log.md)
- [Table types](/core/docs/conceptual/table-types/)