I've noticed that in certain situations NHibernate will create a cross join (by using a comma in the from clause) and then filter the join in the where clause. This does seem defensible.
Lately I have seen errors (with certain generated queries) where I receive an error stating "unable to bind multi-part alias..." referring to an alias an id which is complete valid in the SQL statement (or at least appears to be to my eyes). I was able to play with the HQL in such a way that the generated SQL changed and started working.
It now appears that SQL Server 2000/2005 have a bug where if you use a comma in the join clause you can not separate an aliased table and another table from each other with a comma joined table in between. So for example
Code:
SELECT * FROM A, B INNER JOIN C ON A.Id = C.Id WHERE B.Id = A.SubId
would be an invalid SQL statement according to SQL Server whereas
Code:
SELECT * FROM A INNER JOIN C ON A.Id = C.Id, B WHERE B.Id = A.SubId
would be totally valid. Upon further investigation it turns out that the comma is in fact the root cause of the issue. Simply changing the first query to the following resolved the issue:
Code:
SELECT * FROM A CROSS JOIN B INNER JOIN C ON A.Id = C.Id WHERE B.Id = A.SubId
So in short isn't it a good idea to use the cross join syntax instead of the comma, or at least do this for Sql Server 2000/2005? I took a look at the NHibernate source code and it looks like it is a super easy change. I would be willing to make the change and submit it if that is desired.
Login
FAQ
Search
