Hi

I would report this to jira, but jira is not working.

My mapping is composed of 3 classes SystemUsersEntity, SystemObjectLocksEntity, SystemObjectsEntity. My mapping looks like that:

SystemUsersEntity.hbm.xml
<import class="SystemUsersDTO, Data" />
<class name="SystemUsersEntity, Data"
table="SystemUsers">
<id name="Id" column="IdSystemUser" type="Int32" unsaved-value="null">
<generator class="native" />
</id>
...
</class>

SystemObjectLocksEntity.hbm.xml
<class name="SystemObjectLocksEntity, Data"
table="SystemObjectLocks">
<id name="Id" column="IdSystemObjectLock" type="Int32" unsaved-value="null">
<generator class="native" />
</id>

...
<many-to-one name="SystemUser" column="IdSystemUser" not-null="true" outer-join="auto"></many-to-one>
<many-to-one name="SystemObject" column="IdSystemObject" not-null="true" outer-join="auto"></many-to-one>
...
</class>

SystemObjectsEntity.xbm.xml
<class name="SystemObjectsEntity, Data"
table="SystemObjects">

<id name="Id" column="IdSystemObject" type="Int32" unsaved-value="null">
<generator class="native" />
</id>

...
<set name="SystemObjectLocksSet"
cascade="all-delete-orphan"
inverse="true"
lazy="true">
<key column="IdSystemObject"/>
<one-to-many class="SystemObjectLocksEntity, Data"/>
</set>
</class>


I created the following HQL

SELECT Count(*)
FROM SystemObjectLocksEntity eentity
LEFT JOIN eentity.SystemUser SystemUser
LEFT JOIN eentity.SystemObject SystemObject
And what get's generated by hibernate is:

NHibernate:
select count(*) as x0_0_
(from SystemObjectLocks systemobje0_
left outer join SystemUsers systemuser1_ on systemobje0_.IdSystemUser=systemuser1_.IdSystemUser)
left outer join SystemObjects systemobje2_ on systemobje0_.IdSystemObject=systemobje2_.IdSystemObject

[4] 2007-06-17 22:37:10,129 [13] WARN - System.Data.OleDb.OleDbException: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

So this is a bug and the result must be:

select count(*) as x0_0_ from
(SystemObjectLocks systemobje0_ left outer join SystemUsers systemuser1_
on systemobje0_.IdSystemUser=systemuser1_.IdSystemUser)
left outer join SystemObjects systemobje2_ on systemobje0_.IdSystemObject=systemobje2_.IdSystemObject;

So left paranthesis is on a wrong place. And the paranthesis are necessary.

Note:
If there is more then one join it must be even more paranthesis.
Last part (after last join) does not have parenthesis.
First part has (n-1), where n is number of joins.

Let's see, if I would have 3 joins. It must be like this for example:

SELECT Count(*) AS x0_0_
FROM
((SystemObjectLocks AS systemobje0_ LEFT JOIN SystemUsers AS systemuser1_ ON systemobje0_.IdSystemUser = systemuser1_.IdSystemUser)
LEFT JOIN SystemObjects AS systemobje2_ ON systemobje0_.IdSystemObject = systemobje2_.IdSystemObject)
LEFT JOIN SystemObjects ON systemobje0_.IdSystemObject = SystemObjects.IdSystemObject;

Can this be fixed?

Lp
Sebastijan

Hi

I looked at the code in class JetDriver.

I found the method:



So I made a few corrections, and it works now. Since it is possible, that correction should be made somewhere else....I am just submiting this code for you to check.

The new method should be:



Please let me know, if you feel ok with this.

Lp
Pistotnik

Jet/Access doesn't do outer joins -- or am I missing something?

I think you're missing something. Jet does indeed do outer joins:

Here's a simple example:



This will give all rows from TableA and any rows from TableB where the RowA columns match.

That's not an outer join. That's an innerleft join.

Ja...this bug is already reported....but jira did not work, so I coud not see.

http://jira.nhibernate.org/browse/NH-1006

Sory...

Lp
Sebastijan