New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Invalid JPQL generated on IN clauses #72

Closed
luisfpg opened this Issue Dec 19, 2011 · 12 comments

Comments

Projects
None yet
4 participants
@luisfpg

luisfpg commented Dec 19, 2011

QueryDSL JPA 2.3.0 is generating invalid queries when using IN clauses.
The problem is a missing parenthesis.

Generated query:
and x.y in :a
When should be
and x.y in (:a)

The fix is trivial: JPQLTemplates has this:
add(Ops.IN, "{0} in {1}");
It should be:
add(Ops.IN, "{0} in ({1})");

As a workaround, I've created a custom templates (extending HQLTemplates) which invokes the add(Ops.IN, "{0} in ({1})"); on the constructor.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Dec 20, 2011

Member

Here is the related ticket
#60

Which JPA provider are you using?

According to the JPA specs, the current behaviour should be ok

in_expression ::=
{state_field_path_expression | type_discriminator} [NOT] IN
{ ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }
in_item ::= literal | single_valued_input_parameter
Member

timowest commented Dec 20, 2011

Here is the related ticket
#60

Which JPA provider are you using?

According to the JPA specs, the current behaviour should be ok

in_expression ::=
{state_field_path_expression | type_discriminator} [NOT] IN
{ ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }
in_item ::= literal | single_valued_input_parameter
@luisfpg

This comment has been minimized.

Show comment
Hide comment
@luisfpg

luisfpg Dec 20, 2011

I'm using Hibernate 3.6.1.
Sorry, I didn't see the related ticket.

luisfpg commented Dec 20, 2011

I'm using Hibernate 3.6.1.
Sorry, I didn't see the related ticket.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Dec 20, 2011

Member

The Querydsl test suite uses Hibernate 3.6.8

Here is an a test example

query().from(cat).where(cat.name.in("A","B","C")).count();
-->
select count(cat) from Cat cat where cat.name in :a1

Could you try if upgrading Hibernate helps? 3.6.8 seems to work with both syntax variants.

Member

timowest commented Dec 20, 2011

The Querydsl test suite uses Hibernate 3.6.8

Here is an a test example

query().from(cat).where(cat.name.in("A","B","C")).count();
-->
select count(cat) from Cat cat where cat.name in :a1

Could you try if upgrading Hibernate helps? 3.6.8 seems to work with both syntax variants.

@luisfpg

This comment has been minimized.

Show comment
Hide comment
@luisfpg

luisfpg Dec 21, 2011

After upgrading to Hibernate 3.6.9, I still had the same problem.
Simple queries work, but I've managed to isolate the problem, and it seems like a bug in Hibernate.

    QPerson p = QPerson.person;
    JPAQuery query = new JPAQuery(em)
        .from(p)
        .where(
            p.id.isNotNull(), 
            p.category.isNull().or(p.category.in("A", "B")));
        .list(p);

generates the following JPQL:

from Person person
where person.id is not null and (person.category is null or person.category in :a1)

However, I get this error in Hibernate:

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: {vector} [select person
from entities.Person person
where person.id is not null and (person.category is null or person.category in :a10_, :a11_)]

The interesting part ist that if I remove the first id.isNotNull(), it works.
Hibernate is probably removing some parenthesis it shouldn't...

I've reported https://hibernate.onjira.com/browse/HHH-6913

Anyway, I'll keep my workaround, and suggest that HQLTemplates include it, until that bug is fixed.

luisfpg commented Dec 21, 2011

After upgrading to Hibernate 3.6.9, I still had the same problem.
Simple queries work, but I've managed to isolate the problem, and it seems like a bug in Hibernate.

    QPerson p = QPerson.person;
    JPAQuery query = new JPAQuery(em)
        .from(p)
        .where(
            p.id.isNotNull(), 
            p.category.isNull().or(p.category.in("A", "B")));
        .list(p);

generates the following JPQL:

from Person person
where person.id is not null and (person.category is null or person.category in :a1)

However, I get this error in Hibernate:

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: {vector} [select person
from entities.Person person
where person.id is not null and (person.category is null or person.category in :a10_, :a11_)]

The interesting part ist that if I remove the first id.isNotNull(), it works.
Hibernate is probably removing some parenthesis it shouldn't...

I've reported https://hibernate.onjira.com/browse/HHH-6913

Anyway, I'll keep my workaround, and suggest that HQLTemplates include it, until that bug is fixed.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Dec 22, 2011

Member

Thanks for the bug report to JBOSS:

Maybe in one of the coming releases I could make the JPQLTemplates default instead of HQLTemplates and bring back this HQL fix. The current situation in Querydsl JPA with HQLTemplates as default is a little bit confusing, but I don't want to change it in a minor release.

Member

timowest commented Dec 22, 2011

Thanks for the bug report to JBOSS:

Maybe in one of the coming releases I could make the JPQLTemplates default instead of HQLTemplates and bring back this HQL fix. The current situation in Querydsl JPA with HQLTemplates as default is a little bit confusing, but I don't want to change it in a minor release.

@haraldradi

This comment has been minimized.

Show comment
Hide comment
@haraldradi

haraldradi Feb 6, 2012

I have a similar problem where the paranthesis is definitelly missing:

QParameterDefinition $ = QParameterDefinition.parameterDefinition;

$.parameterRelease.id.eq(releaseId).and($.parameterGroups.any().id.in(filter.getGroups()));

results in:

select parameterDefinition
from cc.catalysts.ptool.entities.ParameterDefinition parameterDefinition
where parameterDefinition.parameterRelease.id = :a1 and exists (select 1
from cc.catalysts.ptool.entities.ParameterGroup parameterDefinition_parameterGroups
where parameterDefinition_parameterGroups in elements(parameterDefinition.parameterGroups) and parameterDefinition_parameterGroups.id in :a20_, :a21_)

I get a org.hibernate.hql.ast.QuerySyntaxException: unexpected token: , near line 5, column 143 with hibernate 3.6.9

I have a similar problem where the paranthesis is definitelly missing:

QParameterDefinition $ = QParameterDefinition.parameterDefinition;

$.parameterRelease.id.eq(releaseId).and($.parameterGroups.any().id.in(filter.getGroups()));

results in:

select parameterDefinition
from cc.catalysts.ptool.entities.ParameterDefinition parameterDefinition
where parameterDefinition.parameterRelease.id = :a1 and exists (select 1
from cc.catalysts.ptool.entities.ParameterGroup parameterDefinition_parameterGroups
where parameterDefinition_parameterGroups in elements(parameterDefinition.parameterGroups) and parameterDefinition_parameterGroups.id in :a20_, :a21_)

I get a org.hibernate.hql.ast.QuerySyntaxException: unexpected token: , near line 5, column 143 with hibernate 3.6.9

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 6, 2012

Member

@haraldradi This is probably not the query Querydsl constructs but some internal representation of Hibernate.

Member

timowest commented Feb 6, 2012

@haraldradi This is probably not the query Querydsl constructs but some internal representation of Hibernate.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 6, 2012

Member

I couldn't find any other solution than to add the wrapping back. Hibernate's query pre-processing seems to be buggy.

Member

timowest commented Feb 6, 2012

I couldn't find any other solution than to add the wrapping back. Hibernate's query pre-processing seems to be buggy.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Feb 7, 2012

Member

released in 2.3.1

Member

timowest commented Feb 7, 2012

released in 2.3.1

@timowest timowest closed this Feb 7, 2012

@anthony-o

This comment has been minimized.

Show comment
Hide comment
@anthony-o

anthony-o Nov 6, 2012

Contributor

It seems that the parenthesis after the in are still not generated for JPAQuery.countDistinct() method... I don't think if it's related but my generated queryString is :

select count(distinct folder)
from Folder folder
where folder.type = ?1 and (folder.delegation.idDelegation in ?2 or folder.right.idRight in ?3)

(Using QueryDSL 2.8.2 & Hibernate 4.1.8.Final)

Contributor

anthony-o commented Nov 6, 2012

It seems that the parenthesis after the in are still not generated for JPAQuery.countDistinct() method... I don't think if it's related but my generated queryString is :

select count(distinct folder)
from Folder folder
where folder.type = ?1 and (folder.delegation.idDelegation in ?2 or folder.right.idRight in ?3)

(Using QueryDSL 2.8.2 & Hibernate 4.1.8.Final)

@anthony-o

This comment has been minimized.

Show comment
Hide comment
@anthony-o

anthony-o Nov 6, 2012

Contributor

I think that it's because I first create a new JPAQuery() which I later clone(EntityManager) so it takes the default JPQLTemplates and not the HQLTemplates for that entityManager, that's the bug...

Contributor

anthony-o commented Nov 6, 2012

I think that it's because I first create a new JPAQuery() which I later clone(EntityManager) so it takes the default JPQLTemplates and not the HQLTemplates for that entityManager, that's the bug...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment