So, dunno really how to begin...

there is this call-center, people (Customer) calling in and ordering something (ServiceTask). Call center agents (User) taking their calls. Customers are grouped by regions (SalesorganisationCustomerData) where one customer can belong to more than one region ((List) Customer.salesorganisationCustomerData). Users have rights ((List) User.roles).

ServiceTask has reference to User who created it and Customer to whom it belongs to.

Customer and User are JOINED SUBCLASSES of an abstract Actor (in DB there are three tables for Actor, User and Customer where Actor exports primary key to both other tables, so that we have common and non-overlapping ids for Customers and Users).

Classes:
----------------------------------------------------------------
public class ServiceTask {
// PK
private long tbServiceTaskID;
// many to one
private Customer customer;
private User user;
}

public abstract class Actor
{
private int actorID;
}

public class Customer extends Actor
{
private boolean isDealer;
private List salesorganisationCustomerData = new LinkedList();
}

public class User extends Actor {
private String loginName;
private List roles = new LinkedList();
}

Mapings:
-----------------------------------------------------------------------------
<hibernate-mapping>
<class name="eg.Actor" table="tbActor">
<!-- PK -->
<id name="actorID" column="actorID" type="int" unsaved-value="0">
<generator class="identity"/>
</id>
<!-- subclasses -->
<!-- User class -->
<joined-subclass name="eg.User" table="tbUser">
<key column="userActorID"/>
<property name="loginName" column="loginName" type="string"/>
<bag name="roles" table="tbUser2Role" lazy="true">
<key column="userActorID"/>
<many-to-many class="eg.Role" column="tbRoleID"/>
</bag>
</joined-subclass>
<!-- Customer class -->
<joined-subclass name="eg.Customer" table="tbCustomer">
<!-- PK -->
<key column="customerActorID"/>
<!-- local attributes -->
<property name="isDealer" column="isDealer" type="boolean"/>
<bag name="salesorganisationCustomerData" table="tbSalesorganisationCustomerData" inverse="true" lazy="true" cascade="save-update">
<key column="customerActorID"/>
<one-to-many class="eg.SalesorganisationCustomerData"/>
</bag>
</joined-subclass>
</class>
</hibernate-mapping>
----------------------------------------------------------------------------------
<hibernate-mapping>
<class name="eg.ServiceTask" table="tbServiceTask">
<!-- PK -->
<id name="tbServiceTaskID" column="tbServiceTaskID" type="long" unsaved-value="0">
<generator class="identity"/>
</id>
<many-to-one name="customer" class="eg.Customer" column="customerActorID" cascade="save-update"/>
<many-to-one name="user" class="eg.User" column="userActorID" not-null="true"/>
</class>
</hibernate-mapping>

DB:
----------------------------------------------------------------------------------
CREATE TABLE [dbo].[tbActor] (
[actorID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tbCustomer] (
[isDealer] [bit] NOT NULL ,
[customerActorID] [int] NOT NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[tbServiceTask] (
[tbServiceTaskID] [bigint] IDENTITY (1, 1) NOT NULL ,
[userActorID] [int] NULL ,
[customerActorID] [int] NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[tbUser] (
[userActorID] [int] NOT NULL ,
[loginName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tbRoleID] [int] NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[tbActor] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
([actorID]) ON [PRIMARY]

ALTER TABLE [dbo].[tbCustomer] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
([customerActorID]) ON [PRIMARY]

ALTER TABLE [dbo].[tbServiceTask] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
([tbServiceTaskID]) ON [PRIMARY]

ALTER TABLE [dbo].[tbUser] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
([userActorID]) ON [PRIMARY]

CREATE UNIQUE INDEX [XAK1tbServiceTask] ON [dbo].[tbServiceTask]([serviceTaskID], [tbSalesorganisationID]) ON [PRIMARY]

CREATE UNIQUE INDEX [XAK1tbUser] ON [dbo].[tbUser]([loginName]) ON [PRIMARY]

ALTER TABLE [dbo].[tbCustomer] ADD
FOREIGN KEY ([customerActorID]) REFERENCES [dbo].[tbActor] ([actorID])

ALTER TABLE [dbo].[tbServiceTask] ADD
FOREIGN KEY ([customerActorID]) REFERENCES [dbo].[tbCustomer] ([customerActorID]),
FOREIGN KEY( [userActorID]) REFERENCES [dbo].[tbUser] ([userActorID])

ALTER TABLE [dbo].[tbUser] ADD
FOREIGN KEY ([tbRoleID]) REFERENCES [dbo].[tbRole] ([tbRoleID]),
FOREIGN KEY ([userActorID]) REFERENCES [dbo].[tbActor] ([actorID])

-----------------------------------------------------------------------------------

NOW THE PROBLEM

-----------------------------------------------------------------------------------

if I perform query like this:
select servicetask from ServiceTask servicetask where servicetask.user.loginName like 'Me%'
everything works fine, but query like this:
select servicetask from ServiceTask servicetask left join servicetask.user.roles where servicetask.user.loginName like 'Me%'
throws exception: 1 errors occurred while listing (and calling getPathNames).
Could not execute query: Line 1: Incorrect syntax near ')'.

and sql looks quite weird:
select servicet0_.tbServiceTaskID as tbServiceTaskID, ...
from tbServiceTask servicet0_ left outer join tbActor user1__1 on user1_.userActorID=user1__1.actorID left outer join tbUser user1_ on servicet0_.userActorID=user1_.userActorID left outer join tbUser2Role roles2_ on user1_.userActorID=roles2_.userActorID left outer join tbRole role3_ on roles2_.tbRoleID=role3_.tbRoleID where (user1_.loginName like 'Me%' and )
(pay attention to last line there is and in where clause WITHOUT conditional)

query like this:
select servicetask from ServiceTask servicetask join servicetask.user.roles where servicetask.user.loginName like 'Me%'
throws exception: 1 errors occurred while listing (and calling getPathNames).
Could not execute query: The column prefix 'user1_' does not match with a table name or alias name used in the query.

and sql looks weird again:
select servicet0_.tbServiceTaskID as tbServiceTaskID, ...
from tbServiceTask servicet0_
left outer join tbActor user1__1 on user1_.userActorID=user1__1.actorID, tbUser user1_, tbUser2Role roles2_, tbRole role3_
where servicet0_.userActorID=user1_.userActorID and user1_.userActorID=roles2_.userActorID and roles2_.tbRoleID=role3_.tbRoleID and ((user1_.loginName like 'Me%' and servicet0_.userActorID=user1_.userActorID))
(pay attention to the part in bold, where the join sequence is wrong)

First thought was "error in mappings", but I get exactlly the same errors if I do querying with customer:
select servicetask from ServiceTask servicetask left join servicetask.customer.salesorganisationCustomerData where servicetask.customer.isDealer = 1
or
select servicetask from ServiceTask servicetask join servicetask.customer.salesorganisationCustomerData where servicetask.customer.isDealer = 1

I think it has something to do with the fact that user and customer are both JOINED-SUBCLASSES of Actor, but this problem is too tough for me :((((((

guys, anyone knows what is here wrong?

kind regards
reinis.

Get the latest version.