how to avoid full outer join in generated sql

[Kai]

We are using Spring Data/JPA with QueryDSL in our project. 
Everything works fine until we recently tried to migrate our database from postgres to mysql, which doesn't support full outer join.

So QueryDsl generates a query with full outer join in one use case, and causing problems.

Our data model is like this:

ERReport (1:m) <-> reportaccess (m:1) <-> unit (1:m)  <-> user
So basically between report and unit is (many to many) relationship.

The problematic user case is to find out all the report a specific user has access to.

So our code is like this:
eRReport.reportAccesses.any().unit.users.any().userNtId.equalsIgnoreCase("userNtId");

this statement will generate a few full out joins in real sql:

select count(erreport0_.rpt_id) as col_0_0_
from wer_report erreport0_
where lower(erreport0_.is_public)='y' or
exists (select 1 from wer_rpt_access reportacce1_
                 full outer join wer_unit erunit2_  on reportacce1_.unit_id=erunit2_.unit_id
                 full outer join wer_unit_membership_vw_s users3_  on erunit2_.unit_id=users3_.unit_id
                 full outer join wer_user eruser4_ on users3_.user_id=eruser4_.user_id
                where erreport0_.rpt_id=reportacce1_.rpt_id and lower(eruser4_.user_nt_id)='xxx')

In the query the full outer is not needed at all.

So I am wondering if the predicate above is the optimal, or is there any better way to do this.

If it is not possible, is that possible to hint querydsl at least not to generate "full outer join" so the query won't throw incorrect syntax exception on mysql.

Thanks a lot.

[timowest]

Could you also paste the resulting JPQL? Querydsl JPA renders only JPQL which is mapped by the JPA provider (e.g. Hibernate) to SQL.

Concerning the query you could also try to express it via explicit joins instead of any() usage.

[Kai]

Thanks for the suggestion.

Here is the JPQuery generated by queryDSL:  com.mysema.query.jpa.impl.JPAQuery -

select count(eRReport) from ERReport eRReport
where lower(eRReport.isPublic) = ?1 or
exists (select 1 from eRReport.reportAccesses as eRReport_reportAccessesbd7e7  
            full join eRReport_reportAccessesbd7e7.unit.users as eRReport_reportAccessesbd7e7_unit_usersd0073 where     
            lower(eRReport_reportAccessesbd7e7_unit_usersd0073.userNtId) = ?2)   

So it is querydsl asking for full outer join, and causing this problem.  Guess JPA can't override that even I provided mysql dialect in the JPA configuration.

As to the query via explicit joins that you referred to, do you mean the multi-sources querydsl mentioned in the doc http://www.querydsl.com/static/querydsl/4.0.1/reference/html_single/#d0e227:
--- quote ---
multiple sources you use the query like this:
QCustomer customer = QCustomer.customer;
QCompany company = QCompany.company;
query.from(customer, company);
--- end ---

I have a few questions about that feature:
1. How to exactly use that in where clause?  something like: query.from(report, user) where (report.reportaccess.unit=user.unit and user.userNTid=?) ?

2. We are using spring Data/JPA to generate the DSL entity path class for each entity bean, and use the findall(Predicate, Pageable) method in spring repository (extended from QueryDslPredicateExecutor) to do dynamic query.  So if mulitple source solution in 1) works, how do we integrate that into spring data model?

3. WIll MySqlTemplate in queryDSL solve this particular dialect problem so queryDSL will avoid using full join? or it is just an way to access specific MySql feature, but not as a hint to avoid the problem in my case?

Thanks again for any help.

[timowest]

Which Querydsl version do you use?

[Kai]

3.6.3

[Oliver Ferschke]

Hi there,

I know this is an old thread, but has there been a solution to this problem?

I have pretty much the same scenario as Kai and I am running into the same "full outer join" issue on MySQL. 

I am using two any() calls in my predicate that I pass to my Spring Data Repository:

 QContribution.contribution.contributionPartOfDiscourseParts.any().discoursePart.discourseToDiscourseParts.any().discourse.eq(discourse)

I could too investigate solving this with explicit joins, but I am wondering if there is a way around it.

My QueryDSL version is 3.7.0

Thanks so much in advance.

Oliver

[timowest]

Hi Oliver.

Could you post the resulting JPQL query? Querydsl uses inner joins for the any() transformations, so I wonder where the full joins come from.

Timo

[Oliver Ferschke]

Hi Timo, 

thanks for getting back to me.

First of all, by using a full QueryDSL query rather than just the predicate, I could make this particular case work now. 
But for future reference, I would still be interested in the reason why the original predicate did not work for me.

Here's the generated query

select

        contributi0_.id_contribution as id_contr1_11_,

        contributi0_.fk_annotation as fk_annot8_11_,

        contributi0_.created as created2_11_,

        contributi0_.end_time as end_time3_11_,

        contributi0_.start_time as start_ti4_11_,

        contributi0_.version as version5_11_,

        contributi0_.fk_data_sources as fk_data_9_11_,

        contributi0_.fk_current_revision as fk_curr10_11_,

        contributi0_.downvotes as downvote6_11_,

        contributi0_.fk_first_revision as fk_firs11_11_,

        contributi0_.fk_contribution_type as fk_cont12_11_,

        contributi0_.upvotes as upvotes7_11_ 

    from

        contribution contributi0_ 

    where

        exists (

            select

                1 

            from

                contribution_partof_discourse_part contributi1_ full 

            outer join

                discourse_part discoursep2_ 

                    on contributi1_.fk_discourse_part=discoursep2_.id_discourse_part full 

            outer join

                discourse_has_discourse_part discourset3_ 

                    on discoursep2_.id_discourse_part=discourset3_.fk_discourse_part 

            where

                contributi0_.id_contribution=contributi1_.fk_contribution 

                and discourset3_.fk_discourse=?

        )

Thanks again,

Oliver

[Timo Westkämper]

And how does the JPQL query look like?

You can get it for example via toString() of the Querydsl query.

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Oliver Ferschke]

Sorry, my bad.

Here's the JPQL:

select contribution

from Contribution contribution

where exists (select 1

from contribution.contributionPartOfDiscourseParts as contribution_contributionPartOfDiscourseParts_d8c06

full join contribution_contributionPartOfDiscourseParts_d8c06.discoursePart.discourseToDiscourseParts as contribution_contributionPartOfDiscourseParts_d8c06_discoursePart_discourseToDiscourseParts_a23f6

where contribution_contributionPartOfDiscourseParts_d8c06_discoursePart_discourseToDiscourseParts_a23f6.discourse = ?1)

On Friday, January 15, 2016 at 3:39:10 PM UTC-5, timowest wrote:

And how does the JPQL query look like?

You can get it for example via toString() of the Querydsl query.

[timowest]

Could you try with 3.7.0?

[Oliver Ferschke]

Hi Timo,

this was already 3.7.0

-Oliver

From: Querydsl on behalf of timowest <quer...@googlegroups.com> on behalf of timowest via Querydsl <querydsl+APn2wQefkk7kI02e0xPnNp6...@googlegroups.com>
Reply-To: Querydsl on behalf of timowest <quer...@googlegroups.com>
Date: Sunday, January 17, 2016 at 3:13 PM
To: Querydsl <quer...@googlegroups.com>
Subject: Re: how to avoid full outer join in generated sql

You received this message because you are subscribed to a topic in the Google Groups "Querydsl" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/querydsl/uoJ63ZdXPog/unsubscribe.
To unsubscribe from this group and all its topics, send an email to querydsl+u...@googlegroups.com.

[timowest]

There was indeed a bug in the collection any handling. I've pushed a fix in this PR https://github.com/querydsl/querydsl/pull/1754

On Tuesday, January 19, 2016 at 4:39:38 PM UTC+2, Oliver Ferschke wrote:

Hi Timo,

this was already 3.7.0

-Oliver

From: Querydsl on behalf of timowest <quer...@googlegroups.com> on behalf of timowest via Querydsl <querydsl+APn2wQefkk7kI02e0xPnNp6yrNOiXUEZ44_gIyQiFakJHtII_UP_1p5@googlegroups.com>

To unsubscribe from this group and all its topics, send an email to querydsl+unsubscribe@googlegroups.com.

[Oliver Ferschke]

Hi Timo,

Thank you so much for looking into this.

Great work!

Best,

Oliver

To unsubscribe from this group and all its topics, send an email to querydsl+u...@googlegroups.com.