Genie Agent refuses to add scalar SQL function from Unity Catalog

[charl-p-botha]

We would like to add some scalar SQL functions to our Genie Agents, for context that can't be provided via other mechanisms. In testing this, we have discovered that scalar functions are not supported, although the documentation explicitly states that they are.

The documentation at https://learn.microsoft.com/en-us/azure/databricks/genie-agents/tune-quality#how-does-genie-use-sql-... states the following:

"Genie supports both scalar functions, which return a single value, and table-valued functions, which return a table of rows."

When we attempt to attach a test UC scalar function, SQL or Python, that returns the number 42, it errors out with: "You must select a function that returns a table.", see attached screenshot.

Are the docs incorrect, or should the Genie Agent in fact support scalar functions, which would be convenient. In the meantime, we'll work around this by wrapping the scalar in a table.

[ivanvyd]

@charl-p-botha I think your reading is right. The current docs say Genie supports both scalar and table-valued Unity Catalog functions, and the August 27 release notes explicitly say that both can be attached as Genie Agent tool.

Given the UI message, the table-valued wrapper looks like a reasonable workaround for now rather than an intended requirement.

If it persists, it may be worth capturing the workspace cloud/region, function definition and DESCRIBE FUNCTION EXTENDED output, then raising it with Support so they can check whether this is specific to the workspace or current UI behavior.

[ThomazNeto]

Hi,

The tune-quality page (updated Sep 11) says "Genie supports both scalar functions, which return a single value, and table-valued functions, which return a table of rows", and the Aug 27 release note is explicit: "Agent authors can now attach both scalar and table-valued Unity Catalog functions as tools for a Genie Agent." Nowhere in the docs is there a "must return a table" restriction. So the UI message you're getting is either a rollout lag in your workspace or a bug, not intended behavior.
https://docs.databricks.com/aws/en/genie-agents/tune-quality#how-does-genie-use-sql-functions
https://docs.databricks.com/aws/en/ai-bi/release-notes/2026

Two things worth checking before you file it. First, where you're adding it: the docs describe the flow as Configure, then the Examples tab, SQL functions. If your workspace still shows an older add-function dialog somewhere else, that may be the one that hasn't caught up. Second, the function needs a proper COMMENT, since Genie can't see the body and only reasons over the description (the Sep 3 note added showing that description in the agent). A scalar returning 42 with no description won't be much use to the agent even once it attaches.

Your workaround is fine in the meantime, a one-row table function wrapping the scalar. When you open the ticket, include cloud/region, the CREATE FUNCTION statement and DESCRIBE FUNCTION EXTENDED output as Ivan suggested, plus a link to that Aug 27 release note so support can't argue it isn't supposed to work.

Would be good to hear back on what they say, this affects more people than just you.

[charl-p-botha]

Thank you both!

I have just tried again: The flow is Genie Agent / Configure / Sources / Add / SQL function. It still refuses with the error (shown in the screenshot below) that "You must select a function that returns a table."

I will create a new Genie Agent and test again. If it still doesn't work, I'll take it further with our excellent account team.

[ivanvyd]

Thanks for reproducing this, @ThomazNeto. @charl-p-botha, I'd try Configure > Examples > Add on your existing agent before recreating it. That matches the Azure documentation's route for adding SQL functions.

One small testing detail: your screenshot's function description already includes 42, so Genie returning 42 wouldn't by itself confirm that it executed the function. After saving, check the function / query details too: Show code for a SQL response, or Show more for a trusted-function response in Chat mode.

If Examples still rejects it - or saves successfully but the function cannot be called - that's the specific distinction I'd take to your account team.

[ThomazNeto]

Good news, Charl: I think we found it. I reproduced your error on a separate Azure workspace today and then tried the other path, which works.

• Configure > Sources > Add > SQL function rejects scalar functions with "You must select a function that returns a table". I tried a scalar with no arguments and one with a DATE parameter, and both were blocked. A table function wrapping the same value saved without problems. This is the "provide verified answers" dialog, and it only accepts table functions.
• Configure > Examples > Add accepts the same scalar function. It shows the definition along with the Parameters and Usage Guidance sections, and saves fine.

This matches the docs, which only describe adding functions through the Examples tab: "Use the Examples tab to add... SQL functions". What makes it confusing is that the Sources menu also lists "SQL function", so it looks like the natural place to add one.
https://docs.databricks.com/aws/en/genie-agents/tune-quality#how-does-genie-use-sql-functions

So you don't need the table wrapper. Add the scalar through Examples and fill in the Usage Guidance so Genie knows when to call it. It may still be worth flagging to the account team that the Sources path rejects scalar functions with an error that suggests they aren't supported at all.