SQL Server query causes MSSQLSERVER_4104

[NecatiMeral]

Error message: The multi-part identifier "%.*ls" could not be bound.

I'll collect more details and create a test ficture to reproduce the issue.
Just taking a Issue id and notes for fixture here.

Generated SQL in our environment

SELECT contentite0_.formcaption as col_0_0_
FROM ContentItem contentite0_, tapplikation category1_ 
left OUTER JOIN ContentItem contentite2_ ON contentite0_.Category=contentite2_.Id
WHERE contentite0_.Category=category1_.Id
AND category1_.Id=?
AND (contentite2_.Id is null OR contentite2_.Id=0)

Generated SQL in testing fixture (looks good)

select contentite0_.Name as col_0_0_
from ContentItem contentite0_ 
inner join Category category1_ on contentite0_.Category=category1_.Id 
left outer join ContentItem contentite2_ on contentite0_.Parent=contentite2_.Id
where category1_.Id=1
and (contentite2_.Id is null or contentite2_.Id=0)

[NecatiMeral]

The fixture works as intended (on master branch).
I think this could be a duplicate of #2990

[bahusoid]

The fixture works as intended (on master branch).

Please share it anyway. I want to check if it's easy fixable on 5.3 branch.

[NecatiMeral]

Hi @bahusoid,

thanks for reaching out.
I've pushed the fixture on my nh-core fork at NecatiMeral/nhibernate-core/tree/nm/implicit-explicit-joins-3185.

Is this ok or should I open a PR against the 5.3.x branch on upstream?

EDIT: While cleaning up the fixture I observed that the issue only occurs when the many-to-one relation is mapped as NotFoundMode.Ignore. Could be also related to #2988.

[bahusoid]

It's fine as is...

We mainly fix regressions in releases and it's not a regression so no promises from me. Only if it's something really easy to fix.

Also I believe your query would work with 5.3 if you move null check as first check in your query

var query = session.Query<ContentItem>()
.Where(x => x.Parent == null || x.Parent.Id == 0)  //<- null check that triggers left join moved as first check in query
.Where(x => x.Category.Id == _categoryId)
...

So you can use it as a workaround in 5.3 (if it works)

[fredericDelaporte]

Maybe we should consider finishing a few more 5.4 PR then release 5.4 anyway, in such case it would be less useful to do another 5.3 patch.

[NecatiMeral]

In our case it's totally fine to wait for 5.4 final. I've worked around the issue but will try the different query approach tomorrow (and will report back of course).