null safe is not working in databricks
# gooddata-cloud
k
null safe is not working in databricks
k
Thinking completed NULL-Safe Joins on Databricks For Databricks specifically, GoodData uses a native null-safe equality operator and does not use
nullValue
in the generated query. This is by design — Databricks is on the list of databases where GoodData skips the `COALESCE`/`nullValue` approach and relies on the database's own null-safe comparison instead. [NULL Joins docs] Here are the things to verify if null-safe joins are still not working as expected: 1. Feature is enabled — Go to Settings > SQL generation > Null-safe joins and confirm the toggle is on. NULL joins are disabled by default. [NULL Joins docs] 2.
isNullable
is set correctly
— For null-safe comparison to kick in, both columns involved in the join must have
isNullable
set to
true
(or left as `null`/omitted). If either column has
isNullable: false
, GoodData falls back to the standard
=
operator regardless of the setting. [NULL Joins docs]Example:{ "id": "order_status", "type": "attribute", "sourceColumn": "order_status", "isNullable": true } 3.
nullValue
is not required for Databricks
— Since Databricks uses a native null-safe operator, you don't need to configure
nullValue
. The value is stored in the model but not used in the generated SQL for Databricks. If you've confirmed all of the above and null-safe joins are still not behaving correctly, this likely requires a deeper look at your specific model configuration. To help the Support team investigate, please add the following details to this thread: • The specific behavior you're seeing (e.g., separate rows for NULL values instead of grouped results) • Any error messages or query trace IDs • The affected workspace or organization • The LDM object types involved (fact, attribute, label, etc.) and their
isNullable
settings Community support (Startup & Professional) You're already in the right place — the GoodData Support team monitors this Slack and a team member will join the conversation when human review is needed. You can also visit the Community Forums for additional help. 24/7 Support (Enterprise plans) If this is a Severity 1 (L1) critical issue or your account includes 24/7 Support, you can also: • Submit a ticket: support.gooddata.com • Email: support@gooddata.com • Call: +1 (415) 200-0194 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
k
@kapa.ai _ I checked first 2 condition and null Safe is not working as expected. How to check 3rd condtion
k
Thinking completed Checking the Generated SQL for Databricks The best way to verify what null-safe operator GoodData is actually sending to Databricks is to inspect the generated SQL query using the debug feature in Analytical Designer: 1. Open the affected visualization in Analyze (Analytical Designer) — not from the dashboard. 2. In the URL, the last segment should be
/edit
(e.g.
.../analyze/#/<workspace>/<insight-id>/edit
). 3. Replace
/edit
with
/debug
→
.../analyze/#/<workspace>/<insight-id>/debug
. 4. This triggers a file download (no new tab opens) — check your browser's Downloads folder for a ZIP file named
explainAfm.zip
. 5. Inside the ZIP, look for the file
report.json.qt.opt.sql
— this contains the actual SQL query sent to Databricks. [Check query debug] > Tip: If
/debug
seems to do nothing, the file may have downloaded silently. Also try
.../debug?explainType=SQL
if the plain
/debug
call fails (useful for large/complex models). [Debug workaround] Once you have the SQL, look for Databricks' native null-safe equality operator (
<=>
) in the join conditions. If you see standard
=
instead, it would confirm that null-safe comparison is not being applied — likely because
isNullable
is not set to
true
on both joined columns. If the SQL looks correct but results are still wrong, please share the relevant SQL snippet and your LDM configuration in this thread so the Support team can investigate further. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
k
sql generate = sign
@kapa.ai SQL generate '=' sign on region join
k
Thinking completed Standard = Operator in the Generated SQL If the generated SQL shows
=
instead of Databricks' native null-safe operator (
<=>
), the most likely cause is that
isNullable
is not set to
true
on both columns
involved in the region join. As documented, if either compared column has
isNullable
set to
false
(or if one side is
false
while the other is
true
), GoodData falls back to the standard
=
operator regardless of the NULL joins setting. [NULL Joins docs] What to check Look at both sides of the region join in your LDM — for example, the
region
attribute on the fact/dataset side and the corresponding attribute on the dimension/lookup side. Both must have `isNullable: true`:
Copy code
{
  "id": "customer_region",
  "type": "attribute",
  "sourceColumn": "region",
  "isNullable": true
}
If either one is set to
false
or is missing (which defaults to unknown but may still trigger standard equality in some cases), update it to
true
and re-run the query to check if
<=>
appears in the SQL. If after setting
isNullable: true
on both sides the SQL still generates
=
, this needs a deeper look. To help the Support team investigate, please add the following to this thread: • The relevant portion of the generated SQL (the join condition on
region
) • The LDM definitions of both attributes involved in the join (including their
isNullable
values) • The affected workspace or organization Community support (Startup & Professional) You're already in the right place — the GoodData Support team monitors this Slack and will join the thread when human review is needed. You can also visit the Community Forums. 24/7 Support (Enterprise plans) • Submit a ticket: support.gooddata.com • Email: support@gooddata.com • Call: +1 (415) 200-0194 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
k
@kapa.ai - Can I set in UI.
Copy code
"isNullable": true
k
Thinking completed The knowledge sources do not explicitly mention whether
isNullable
can be set directly through the GoodData UI modeler. What the documentation does confirm is that: • The
nullValue
field is editable in the modeler
(UI). [NULL Joins docs] •
isNullable
is described in the context of the API/JSON model definition, but no explicit mention is made of a UI toggle for it. Recommended approach in the meantime You can set
isNullable: true
via the API using a
PUT
request to update your logical model:
Copy code
PUT /api/v1/layout/workspaces/{workspaceId}/logicalModel
Retrieve your current model first with a
GET
, update the relevant attribute definitions to include
"isNullable": true
, then
PUT
the updated model back. To get a definitive answer on UI support for
isNullable
, please add the following to this thread so the Support team can confirm: • Whether your modeler version exposes a nullability toggle in the attribute/label settings panel • The affected workspace, so the team can check if there are any UI options available for your setup Community support (Startup & Professional) You're already in the right place — the GoodData Support team monitors this Slack and will join when human review is needed. You can also visit the Community Forums. 24/7 Support (Enterprise plans) • Submit a ticket: support.gooddata.com • Email: support@gooddata.com • Call: +1 (415) 200-0194 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
k
@kapa.ai team it seems This one is not working in our environment. SQL generation Configure how SQL queries are generated. Null-safe joins ------------------ It is on databricks. I enabled Null-safe join on analytics.dev.elasticsuite.com
k
Thinking completed Based on our conversation so far, you have confirmed: 1. ✅ Null-safe joins is enabled in Settings 2. ✅
isNullable
is set correctly on both columns 3. ✅ The generated SQL still shows
=
instead of
<=>
on the region join This goes beyond what the documentation can help resolve — it needs a human review of your specific environment. To help the Support team investigate, please add the following details to this thread: • The relevant portion of the generated SQL showing the region join condition • The LDM definitions (JSON) of both attributes involved in the join, including their
isNullable
values • Confirmation of which Databricks version/runtime you are using • Any error or trace IDs from the query execution • The affected workspace name Community support (Startup & Professional) You're already in the right place — the GoodData Support team monitors this Slack and a team member will join the conversation when human review is needed. You can also visit the Community Forums. 24/7 Support (Enterprise plans) If this is a Severity 1 (L1) critical issue or your account includes 24/7 Support: • Submit a ticket: support.gooddata.com • Email: support@gooddata.com • Call: +1 (415) 200-0194 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
j
Hi Krishti, could you please provide the following: • The relevant portion of the generated SQL showing the region join condition • The LDM definitions (JSON) of both attributes involved in the join, including their
isNullable
values • Confirmation of which Databricks version/runtime you are using • Any error or trace IDs from the query execution • The affected workspace name you can DM if you prefer.
k
You can see all details here analytics.dev.elasticsuite.com/workspace/elastic_order_EBA-8308/analyze#/…/edit Yes isNullable is set True
It seems like setting looks enabled, but on backened it is not. Could you please check from your side that it really work. May be bug on product side
j
Hi Krishti, I see that it is enabled, but I don't see how it is not working. Can you explain a bit in detail as to what you are expecting to see in that table?
k
That is why I gave you link of Report - analytics.dev.elasticsuite.com/workspace/elastic_order_EBA-8308/analyze#/…/edit In SQL you can see join on "region" is with "=" sign. It should be with "<=>" this sign (with Null-safe join, isnullable = true in databricks). Could you please see why it is not happening.
Copy code
SELECT * FROM all_rows) AS `__sql_6e1c3811ddafbc8ad8030164f3a70417`) `__sql_6e1c3811ddafbc8ad8030164f3a70417`
ON `mv_orders`.`product_number` = `__sql_6e1c3811ddafbc8ad8030164f3a70417`.`product_number`
AND `mv_orders`.`region` = `__sql_6e1c3811ddafbc8ad8030164f3a70417`.`region`
AND `mv_orders`.`supplier_code` = `__sql_6e1c3811ddafbc8ad8030164f3a70417`.`supplier_code`
INNER JOIN `gold_mvp`.`v_fx_rates` ON `mv_orders`.`currency_code` = `v_fx_rates`.`from_currency`
AND `mv_orders`.`order_dt_month` = `v_fx_rates`.`month` AND `mv_orders`.`order_dt_year` = `v_fx_rates`.`year`
WHERE `mv_orders`.`supplier_code` = '47brand' AND `v_fx_rates`.`to_currency` IS NOT NULL
GROUP BY `__sql_6e1c3811ddafbc8ad8030164f3a70417`.`tag_name`) `t3`
WHERE `t3`.`def_m_156873d3b4bc7f6a979355df7dc54d3a`;
I added a support ticket, because it remain unresolved over here. support.gooddata.com/hc/en-us/requests/135048
j
Thanks Krishti, we will follow up in the ticket
k
Do we have any update on this? support.gooddata.com/hc/en-us/requests/135048
m
Hi Krishti, as mentioned in the support ticket by my colleague from Technical Support, we will provide an update as soon as we have one. Thank you for your understanding.