MapPath problem with JPA / Hibernate in v4.1.3

[m4urer]

I have a strange problem with a MapPath mapping. I don't know if it's something on my side, so I'm posting it here (sorry for using quotes instead of code, it was not formatting as it should for some reason!)

I have this kind of mapping:

@ElementCollection
@MapKeyJoinColumn(name = "workMode")
@CollectionTable(name = "WorkByMode", joinColumns = { @joincolumn(name = "session_id") })
@column(name = "workCount")
protected Map<WorkMode, Integer> workByMode

When I try to filter a query with the map keys, allowing to have one of multiple values using a boolean builder:

BooleanBuilder where = new BooleanBuilder();
for (WorkMode mode : modes) {
where.or(myEntity.workByMode.containsKey(mode));
}

The SQL that ends up being generated has a left outer join with WorkByMode (which is great) but then an inner join with WorkMode

left outer join
WorkShiftStatistics_workByMode workbymode2_
on workshifts0_.id=workbymode2_.session_id
and (
workbymode2_.workMode=?
)
inner join
WorkMode workmode3_
on workbymode2_.workMode=workmode3_.code
left outer join
WorkShiftStatistics_workByMode workbymode4_
on workshifts0_.id=workbymode4_.session_id
and (
workbymode4_.workMode=?
)
inner join
WorkMode workmode5_
on workbymode4_.workMode=workmode5_.code
where
(
workbymode2_.count is not null
or workbymode4_.count is not null
)

So if I only have one of the given keys, the second inner join makes the result empty... if it were a left join it wouldn't.

I don't know if other sql engines use another precedence, but I'm using sqlserver and this is not working.
Is it me? Can I avoid the second inner join somehow?

[Shredder121]

Generated SQL is a tough issue in Querydsl JPA, but we'll see if it's something on our end, on Hibernate's end or your query.

Could you maybe post our generated JPQL?

Did you already make aliases when joining?
Maybe you need to be explicit with what path you want.

For containsKey we join to the property's parent

BooleanBuilder where = new BooleanBuilder();
for (WorkMode mode : modes) {
    where.or(myEntity.workByMode.containsKey(mode));
    //      ^  ^  ^  ^  ^  ^  ^
    // maybe use an alias here?
}

Maybe we need to see more of the query, is that possible?
An example project that has the issue is also good (would also help if you need to take the issue somewhere else).

[m4urer]

Here's more detail

the WorkShiftStatistics Entity

@Entity
public class WorkShiftStatistics {
    @Id
    protected long id;

    private boolean active;

    @ElementCollection
    @MapKeyJoinColumn(name = "workMode")
    @CollectionTable(name = "WorkShiftStatistics_workByMode", joinColumns = { @JoinColumn(name = "statistics_id") })
    @Column(name = "count")
    protected Map<WorkMode, Integer> workByMode = Maps.newHashMap();

    ...
}

the query builder

@Override
public HibernateQuery createQuery(List<WorkMode> modes) {
    BooleanBuilder whereBuilder = new BooleanBuilder();
    for (WorkMode mode : modes) {
        whereBuilder.or(workShiftStatistics.workModes.contains(mode.getCode()));
    }
    return newQuery()
            .from(workShiftStatistics)
            .leftJoin(workShiftStatistics.user, user)
            .where(whereBuilder);
}

the output sql

Hibernate: 
    /* select
        count(workShiftStatistics) 
    from
        WorkShiftStatistics workShiftStatistics   
    left join
        workShiftStatistics.user as user   
    left join
        workShiftStatistics.workByMode as workShiftStatistics_workByMode_1992797668 with key(workShiftStatistics_workByMode_1992797668) = ?1   
    left join
        workShiftStatistics.workByMode as workShiftStatistics_workByMode_25899661 with key(workShiftStatistics_workByMode_25899661) = ?2 
    where
        (
            workShiftStatistics_workByMode_1992797668 is not null 
            or workShiftStatistics_workByMode_25899661 is not null
        ) 
    */ 


    select
            count(workshifts0_.id) as col_0_0_ 
        from
            WorkShiftStatistics workshifts0_ 
        left outer join
            EoxUser user1_ 
                on workshifts0_.user_id=user1_.id 
        left outer join
            WorkShiftStatistics_workByMode workbymode2_ 
                on workshifts0_.id=workbymode2_.statistics_id 
                and (
                    workbymode2_.workMode=?
                ) 
        inner join
            WorkMode workmode3_ 
                on workbymode2_.workMode=workmode3_.code 
        left outer join
            WorkShiftStatistics_workByMode workbymode4_ 
                on workshifts0_.id=workbymode4_.statistics_id 
                and (
                    workbymode4_.workMode=?
                ) 
        inner join
            WorkMode workmode5_ 
                on workbymode4_.workMode=workmode5_.code 
        where
            (
                workbymode2_.count is not null 
                or workbymode4_.count is not null
            ) 

If you need to take the issue somewhere, I could try to set up a test project but probably not this week...

[Shredder121]

the output sql

Could you please post the JPQL?
The SQL is not our output in this case.

I think I see what is wrong (we might not reuse generated aliases correctly?), but to confirm my theory I need the JPQL that we generate.

[m4urer]

This is the log output, is this what you need?

2016-08-25 10:43:41,589 [http-nio-8080-exec-3] DEBUG com.querydsl.jpa.hibernate.HibernateQuery - select count(workShiftStatistics) from WorkShiftStatistics workShiftStatistics left join workShiftStatistics.user as user where (?1 member of workShiftStatistics.workModes or ?2 member of workShiftStatistics.workModes)
130476 [WARN] deprecation: HHH90000016: Found use of deprecated 'collection property' syntax in HQL/JPQL query [null.elements]; use collection function syntax instead [elements(null)].
130479 [WARN] deprecation: HHH90000016: Found use of deprecated 'collection property' syntax in HQL/JPQL query [null.elements]; use collection function syntax instead [elements(null)].
2016-08-25 10:43:41,626 [http-nio-8080-exec-3] DEBUG com.querydsl.jpa.hibernate.HibernateQuery - select workShiftStatistics.id, user.id, user.fullName, workShiftStatistics.workStartDate, workShiftStatistics.workEndDate, workShiftStatistics.netWorkTime, workShiftStatistics.active, workShiftStatistics.deviceId, workShiftStatistics.lastStartedWorkId, workShiftStatistics.lastStartedWorkType, workShiftStatistics.pickCount, workShiftStatistics.totalWeight, workShiftStatistics.lastStartedWorkDate, workShiftStatistics.lastFinishedWorkDate, workShiftStatistics.lastMessage, workShiftStatistics.lastMessageDate, workShiftStatistics.lastWorkMode.code, '' as workByMode from WorkShiftStatistics workShiftStatistics left join workShiftStatistics.user as user where (?1 member of workShiftStatistics.workModes or ?2 member of workShiftStatistics.workModes) order by workShiftStatistics.lastMessageDate desc

[timowest]

Querydsl doesn't provide DSL alternatives for the JPQL KEY operator, but you can use it manually like this instead of map.containsKey(...):

query.from(myEntity)
    .leftJoin(myEntity.workByMode, workByMode)
    .on(Expressions.operation(WorkMode.class, JPQLOps.KEY, workByMode).in(key1, key2))
    .where(...)