Duplicate table alias

[Daryl Stultz]

Hello, the following query:
Query<RoleTimeCreditType> subQuery =
Ebean.find(RoleTimeCreditType.class);
subQuery.where()
.eq("roleTimeCredits.timeTrackingEntry.scheduledAssignment.scheduleDay.date",
cal.getTime())
.eq("roleTimeCredits.timeTrackingEntry.scheduledAssignment.user.userType.id",
1);

produces this SQL:


select distinct r.roletimecredittypeid as c0, r.name as c1, r.descr as
c2, r.printorder as c3
from public.roletimecredittypes r
join public.roletimecredits xr on xr.roletimecredittypeid =
r.roletimecredittypeid
join public.timetrackingentries xt on xt.timetrackingentryid =
xr.timetrackingentryid
join public.scheduledassignments xs on xs.scheduledassignmentid =
xt.scheduledassignmentid
join public.scheduledays xs on xs.scheduledayid = xs.scheduledayid
join public.roletimecredits rr on rr.roletimecredittypeid =
r.roletimecredittypeid
left outer join public.timetrackingentries rrt on
rrt.timetrackingentryid = rr.timetrackingentryid
left outer join public.scheduledassignments rrts on
rrts.scheduledassignmentid = rrt.scheduledassignmentid
left outer join public.users rrtsu on rrtsu.userid = rrts.userid
where xs.scheduledate = ? and rrtsu.usertypeid = ?

Notice the table alias "xs" is duplicated. It also seems the tables
roletimecredittypes and timetrackingentries are joined in twice,
perhaps once for each path being tested in the where clause? It seems
it could be more efficient. Also since I am requiring equality for the
usertypeid, the joins could be inner rather than left.

Could I be doing something wrong or is this a bug? Obviously the model
is fairly complex, but I could try to create a unit test if nothing
looks suspicious.

Thanks,

/Daryl

[Rob Bygrave]

>> Notice the table alias "xs" is duplicated.

That looks like a bug.

>> Also since I am requiring equality for the usertypeid, the joins could be inner rather than left.

Ebean doesn't take the expression into account in determining the join type. So you get the outer based on cardinality (Many) or optionality (can be null) and that cascades to any subsequent relationships in the path.

The DB could take this into account depending on how smart the cost based optimiser is though.

>>
are joined in twice, perhaps once for each path being tested in the where clause?
It seems it could be more efficient.
<<

(for each path) Yes. In terms of efficiency that could be the case. It would be interesting to get the execution plans for the 2 statements to compare.

[Daryl Stultz]

On Sep 4, 6:26 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> >> Notice the table alias "xs" is duplicated.
>
> That looks like a bug.

Updated unitest.jar in files section.

I'm not saying this has anything to do with the bug, just curious, why
don't you alias tables the way you do columns, t1, t2, etc?

Thanks.

/Daryl

[Rob Bygrave]

>> Updated unitest.jar in files section.

Ok, ta.

>> why don't you alias tables the way you do columns, t1, t2, etc?

Actually considering changing to do that - which might be simplify a few things

[Rob Bygrave]

Logged as BUG 319 : SQL exception - Duplicate table alias ...

Fixed in HEAD.

[Daryl Stultz]

On Sep 17, 5:27 am, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> Logged as BUG 319 : SQL exception - Duplicate table alias ...

Confirmed, thanks!

/Daryl