Query issue

[asishPatnaik2000]

Divison operation is not working in oracle query after upgradation from superset 4.1,4 to v 6.0.0 what could be the issue?

DB engine Error
Could not convert string '0' to numeric
This may be triggered by:

Issue 1011 - Superset encountered an unexpected error.

Please reach out to the Chart Owner for assistance.
Chart Owner: Admin User

[asishPatnaik2000]

@dosu any update on this?

[dosubot[bot]]

Hi @asishPatnaik2000 — I looked into this further. Your original issue ("Could not convert string '0' to numeric" during division operations on Oracle) is separate from the positional GROUP BY issue discussed earlier in this thread, so upgrading sqlglot alone wouldn't fix it.

There's no exact match for this error in existing issues, but here are some likely causes and things to check:

1. Check the generated SQL — In SQL Lab or the chart's "View Query" option, look at the actual query Superset generates. Oracle handles type coercion differently than other databases, and a division operation where one operand is implicitly treated as a string (e.g., from a VARCHAR2 column or a calculated field) would cause this error.

2. Explicit CAST in your metric/column — If your division involves columns that Oracle might interpret as strings, try wrapping them with CAST(column_name AS NUMBER) or TO_NUMBER(column_name) in your metric definition.

3. Type casting changes in recent versions — Superset has undergone changes to how Oracle type casting works in time grain expressions and the type system [1]. If your chart uses temporal columns or epoch conversions, these changes could be contributing.

4. Oracle driver — Make sure you're using the recommended oracledb driver rather than the legacy cx_Oracle [2], and check your SQLAlchemy version compatibility.

To help narrow this down further, could you share:

• The full stack trace from the Superset logs (not just the UI error message)?
• The generated SQL query from the chart (via "View Query")?
• Whether the division works fine when run directly in Oracle SQL Developer or similar?

That would help identify whether the issue is in Superset's query generation or in Oracle's handling of the generated SQL.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}
   

[asishPatnaik2000]

@dosu cant share generated query for company policies I ca give an example:

ROUND(
 MAX(CASE WHEN some_type = 'X' THEN TO_NUMBER(some_value) END)
  / NULLIF((SELECT TO_NUMBER(pref_value) FROM some_preferences WHERE pref_key = 'FACTOR'), 0),
  2
)

ethe divison works when run directly through sql lab

[dosubot[bot]]

Thanks for the example and the clarification — that's very helpful. The fact that the division works in SQL Lab but fails in a chart is a strong clue.

The root cause is likely how Superset wraps chart queries differently from SQL Lab. SQL Lab executes your SQL directly against the database [1], but when rendering a chart, Superset compiles your metric expression into a SQLAlchemy query object, then wraps it in subqueries for limits, filtering, and post-processing [2]. This wrapping can alter how Oracle interprets type conversions — your TO_NUMBER() calls and the division may lose their context when nested inside Superset's generated subquery layers, causing Oracle to treat a numeric result as a string.

Since Oracle's engine spec doesn't define custom type mutators [3], the error is almost certainly happening during query generation/transformation, not result processing.

Workarounds to try:

1. Wrap the entire expression in an explicit CAST in your metric definition:

   CAST(
     ROUND(
       MAX(CASE WHEN some_type = 'X' THEN TO_NUMBER(some_value) END)
         / NULLIF((SELECT TO_NUMBER(pref_value) FROM some_preferences WHERE pref_key = 'FACTOR'), 0),
       2
     ) AS NUMBER
   )

2. Use a virtual dataset — create a SQL query in SQL Lab that performs the division, save it as a virtual dataset, and build your chart on that instead. This avoids the metric compilation layer entirely.

3. Check if the subquery in your metric is the issue — the SELECT ... FROM some_preferences subquery inside the metric might not survive Superset's query wrapping correctly. Try pre-computing the factor value in a virtual dataset or using a JOIN instead of a subquery within the metric.

Could you also share:

• The Superset server logs (Python traceback) when the chart fails? That would pinpoint exactly where the type conversion breaks.
• Whether you're using this as a custom SQL metric in the chart or as part of a virtual dataset?

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}
   

[asishPatnaik2000]

@dosu we already tried this did not work
CAST(
ROUND(
MAX(CASE WHEN some_type = 'X' THEN TO_NUMBER(some_value) END)
/ NULLIF((SELECT TO_NUMBER(pref_value) FROM some_preferences WHERE pref_key = 'FACTOR'), 0),
2
) AS NUMBER
)

and we checked all the data are valid it is working/charts are coming through superset admin but when rendered through iframe user fetting this issue

[dosubot[bot]]

This is a really important detail — it works as admin in Superset but fails when rendered via iframe. That changes the diagnosis significantly. This is likely not a pure Oracle division issue but rather a problem with how Superset generates queries in embedded/guest token mode.

Here are the most probable causes:

1. Double RLS application bug (Superset 6.0.0 regression)
If you're using Row Level Security rules in your guest token, there's a known regression in 6.0.0 where RLS filters get applied twice — once wrapping the query in a subquery and again as a WHERE clause on the outer query. This double-wrapping can break type context for your division expression, causing Oracle to misinterpret numeric values as strings. This was introduced by PR #36061.

Workaround: Use physical datasets instead of virtual datasets, or check if PR #37395 has been backported to your version.

2. Guest token RLS configuration issues
Guest token RLS rules are only applied when [1]:

• The EMBEDDED_SUPERSET feature flag is enabled
• The user.username field in the guest token is non-empty

If the RLS rules aren't applying correctly, the query context can differ from what admin sees, potentially triggering different query paths.

3. Missing query_context on charts
Charts that lack a properly populated query_context field can fail specifically in embedded mode with guest tokens while working fine for admin users [2]. Try opening and re-saving each affected chart in the Superset UI to regenerate the query context.

To narrow this down, could you check:

• Are you using RLS rules in your guest token?
• Is this a virtual dataset (SQL query-based) or a physical dataset (direct table)?
• What does the Superset server log show when the iframe chart fails? The full Python traceback would pinpoint whether it's the double-RLS bug or something else.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}
   

[asishPatnaik2000]

@dosu i am using these proporties

SESSION_REFRESH_EACH_REQUEST = False
SESSION_SERVER_SIDE = True
SESSION_TYPE = "sqlalchemy"
SESSION_PERMANENT = True
SESSION_USE_SIGNER = True
SESSION_SQLALCHEMY_TABLE = "flask_session"
SESSION_COOKIE_NAME = "session"
SESSION_COOKIE_PATH = "/"

replase sqlalchemy in session_type to filesystem for superseet upgrade from 4.1.4 to 6.0.0

[dosubot[bot]]

You've reached the anonymous usage limit of 10 interactions per month. Your usage will reset on 2026-05-01. If you'd like to use Dosu before then, please register to increase your limit to 100 interactions per month for free.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}