OQL Troubleshooting Guide 🛠️

This guide helps you resolve the most common OQL errors seen in dashboards and OQL Tester. If your issue isn’t listed here, ask Forge or contact your customer support manager.

How to Use This Guide

  1. Execute your query in OQL Tester
  2. Look for the error in your logs or query output. It usually looks like:
    • error <> query: code: <CODE>, message: <MESSAGE>
    • rpc error: code = <CODE> desc = <MESSAGE>
  3. Search for the code or the message below.
  4. Apply the suggested fix / workaround.
  5. Ask Forge to troubleshoot the query.

1. Join Errors

Missing Columns

What you’ll see

code: 47, message: Missing columns: 'current.abc'

Why it happens: The query references a column (current.abc) without joining the current table/CTE.

Fix / Workaround: Add the missing join, for example:

...
join current using (line.name)
...

Missing Join Key

What you’ll see

rpc error: could not find join key <> in main query

Why it happens: The join key listed isn’t available in your main query.

Fix / Workaround: Make sure the join key is included in your aggregates or group by section:

aggregate line.name as 'Line'
join other_cte using ('Line')
group by line.name as linename

2. Cache Errors

What you’ll see

rpc error: error getting cache blob

code: 43 / 386 — aggregate type mismatch

Why it happens: The cache may have incompatible or outdated data.

Fix / Workaround: Run the query with cache disabled:

...  // your full query
...
settings disable_cache=true

3. Sorting Issues

What you’ll see Sorting by multiple columns doesn’t work.

Why it happens: Sort order isn’t clear if asc or desc aren’t specified.

Fix / Workaround:

...
order by 'Line' asc, 'Test' asc, 'Head #' asc
...

Duplicate Rows After Join

Why it happens: A join without any keys creates a cross join.

Fix / Workaround:

...
join xyz using (line.name)
...

5. Common Syntax Errors

Duplicate Aliases

What you’ll see

code: 179, message: Different expressions with the same alias product.name

Fix: Rename columns so each has a unique alias.

CTE Override Mismatch

What you’ll see

expected N CTE column overrides, got M

Fix: The number of overrides must match the number of aggregates + group-bys.

Invalid multiIf

What you’ll see

Invalid number of arguments for function multiIf

Fix: Use the correct pattern:

...
multiIf(condition1, value1, condition2, value2, ..., default_value)
...

HAVING Clause with String Literals

String literals in a having clause may be either a reference to a target alias or a plain string value used in a comparison. A quoted string is resolved as an alias reference when it matches a name in aggregate, and is otherwise emitted as a SQL string literal.

HAVING filters on aggregate-derived values, so a string-valued column on the LHS must come from an aggregate (e.g. via multiIf bucketing) — not from a group by column.

-- alias reference
aggregate avg(metric_group('uuid2')) as 'Uptime Hours'
...
having 'Uptime Hours' > 0

-- string value on RHS (aggregate-derived bucket)
aggregate
    avg(metric_group('uuid2')) as avg_speed,
    multiIf(avg_speed > 200, 'Fast', avg_speed > 100, 'Medium', 'Slow') as speed_band
...
group by line.name as linename as linename
having speed_band = 'Fast'

6. Tips & Best Practices

  • Start small: test a minimal query, then add clauses one by one.
  • Always make sure your join keys exist in both the main query and the CTEs.
  • UUIDs in examples (uuid1, uuid2) are placeholders — replace with your actual IDs.
  • Use explicit cast() when mixing different number types.