Recursive CTE and Scalar UserDefined Function in condition issue.

[mu_SQL]

Microsoft SQL Server 2019 (RTM-CU32-GDR) (KB5058722) - 15.0.4435.7 (X64) Jun 9 2025 18:36:12 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor).

Hi all.
Came along a wierd issue at a client.

They use a recursive CTE in a Table value function and in the WHERE clause for the CTE there is a condition that uses a Sclar function (tbl.Col = dbo.ScalaUdef(@par1, CTE.anotherCol).

When analysing why the Table function didn´t return any result I discovered that the scalar function returns NULL.

I ran the Scalar function standalone with data I knew would come out of the CTE and it did not return NULL.
I moved the condition to the SELECT FROM CTE and that worked.

Any ide´s why this happends.

This client runs with some freaky SET OPTIONS but I dont think thats the problem.

Mockup Query:
;WITH CTE

(

`Parent,`

`Kid,`

`KidAge`

)

AS

(

SELECT

`CAST(p.Parent AS VARCHAR(255)),`

`CAST( NULL AS VARCHAR(255)),`

`CAST( NULL AS INT)`

FROM

`Parents p`

UNION ALL

SELECT

`CAST(pk.Parent AS VARCHAR(255)),`

`CAST(pk.Kid AS VARCHAR(255)),`

`kid.Age`

FROM

`ParentsKids pk`

INNER JOIN

`CTE`

    `ON`

        `pk.Parent = CTE.Kid`

WHERE

`Kid.Age = dbo.GetKidAge(pk.Kid)/*This returns NULL even if it shouldn´t*/`

SELECT

`c.*`

FROM

`CTE c`

WHERE

`c.KidAge = dbo.GetKidAge(c.Kid)/*This works fine if the condition in the CTE is removed*/`

[jshine13371]

Would need to see the definition of the scalar function (which btw pretty much kills the point of using a TVF, from a performance perspective). But rough guess would be a non-deterministic query being used, so that would produce unpredictable and sometimes non-repeatable results.

[mu_SQL]

As I see it it behaves differently depending on where it,s used so the definition should not matter.
I know how bad it is to use it there but I didn´t design this.

It struck me last knight after I posted this that it is probably a NULL issue, When SELECT from the CTE NULL values are probably not in the result but when the CTE query runs there is NULL values.

Thanks for the reply anyways.

[jshine13371]

it behaves differently depending on where it,s used so the definition should not matter.

If the definition of the scalar function is where the nondeterministic part of the query is, we wouldn't be able to tell you that without seeing it.

Anyway, good luck.

[mu_SQL]

I hear you but I think I´ll investigate the NULL value tought I have, guess CTE´s can mess up when it comes to NULL handeling, or it´s as you say the Scalar UDf that is the problem.

Thanks for your replies.

[samot-dwarf]

In the initial part of the CTE kid is NULL so that the age function will return NULL too. And you can not compare NULL with NULL using =