Just some code to get you started.

@Entity
public Class Entity

With the following HQL code:

FROM Entity
WHERE
( ( entity.dateTime is null AND entity.toDateTime < :dateTime)
OR
( entity.dateTime >= :dateTime AND entity.toDateTime is null )
OR
( entity.dateTime >= :dateTime AND entity.toDateTime < :dateTime )
)

Copy paste errors all over in the previous comment, this is better:

@Entity
public Class Entity

With the following HQL code:

FROM Entity
WHERE
( ( entity.fromDateTime is null AND entity.toDateTime < :dateTime)
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime is null )
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime < :dateTime )
)

I am getting the same problem:

"c.owner=? and ("
+ "(c.homeNumber is not null and c.mobileNumber<>'' and c.homeNumber=?) or "
+ "(c.mobileNumber is not null and c.mobileNumber<>'' and c.mobileNumber=?) or "
+ "(c.workNumber is not null and c.workNumber<>'' and c.workNumber=?) or "
+ "(c.pagerNumber is not null and c.pagerNumber<>'' and c.pagerNumber=?) or "
+ "(c.otherNumber1 is not null and c.otherNumber1<>'' and c.otherNumber1=?) or "
+ "(c.otherNumber2 is not null and c.otherNumber2<>'' and c.otherNumber2=?) or "
+ "(c.otherNumber3 is not null and c.otherNumber3<>'' and c.otherNumber3=?) or "
+ "(c.otherNumber4 is not null and c.otherNumber4<>'' and c.otherNumber4=?))"

HQL snipped is getting converted to:

select contact0_.ym_contact_id as ym1_25_, contact0_.title as title25_, contact0_.firstName as firstName25_, contact0_.middleName as middleName25_, contact0_.lastName as lastName25_,
contact0_.emailAddress as emailAdd6_25_, contact0_.organization as organiza7_25_, contact0_.home_num as home8_25_, contact0_.mobile_num as mobile9_25_, contact0_.work_num as work10_25_,
contact0_.pager_num as pager11_25_, contact0_.other_num1 as other12_25_, contact0_.other_num2 as other13_25_, contact0_.other_num3 as other14_25_, contact0_.other_num4 as other15_25_,
contact0_.owner_id as owner16_25_ from ym_contact contact0_ where contact0_.owner_id=? and (
(contact0_.home_num is not null) and contact0_.mobile_num<>'' and contact0_.home_num=? or
(contact0_.mobile_num is not null) and contact0_.mobile_num<>'' and contact0_.mobile_num=? or
(contact0_.work_num is not null) and contact0_.work_num<>'' and contact0_.work_num=? or
(contact0_.pager_num is not null) and contact0_.pager_num<>'' and contact0_.pager_num=? or
(contact0_.other_num1 is not null) and contact0_.other_num1<>'' and contact0_.other_num1=? or
(contact0_.other_num2 is not null) and contact0_.other_num2<>'' and contact0_.other_num2=? or
(contact0_.other_num3 is not null) and contact0_.other_num3<>'' and contact0_.other_num3=? or
(contact0_.other_num4 is not null) and contact0_.other_num4<>'' and contact0_.other_num4=?)

Hi,

I had the same problem and it is very annoying.

Anyway, i found a work around.
Apparently the parser for the request remove all parenthesis and replace them when he think they are necessary.
Unfortunately, it seems that in the query builder they forgot to put parenthesis surrounding an OR condition.
But, hopefuly, the query builder put parenthesis for a NOT condition.

Then the request :
(1 and 2) or (3 and 4) or (5 and 6)
can be rewrited like that :
(1 and 2) AND NOT(~3 or ~4) AND NOT (~5 or ~6)

where ~3 is the opposite condition of 3

If I took the example given by Dennis Fleurbaaij , the request:
FROM Entity
WHERE
( ( entity.fromDateTime is null AND entity.toDateTime < :dateTime)
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime is null )
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime < :dateTime )
)

will become :
FROM Entity
WHERE
( ( entity.fromDateTime is null AND entity.toDateTime < :dateTime)
AND NOT
( entity.fromDateTime < :dateTime OR entity.toDateTime is not null )
AND NOT
( entity.fromDateTime < :dateTime AND entity.toDateTime >= :dateTime )
)

Anyway, i hope there will be a bug fix soon, because this workaround make queries less readable.

Hi again,

I made an error on the refactoring, here is the correct code:

The request :
(1 and 2) or (3 and 4) or (5 and 6)
can be rewrited like that :
NOT(NOT(1 and 2) AND NOT(3 and 4) AND NOT (5 and 6) )

If I took the example given by Dennis Fleurbaaij , the request:
FROM Entity
WHERE
( ( entity.fromDateTime is null AND entity.toDateTime < :dateTime)
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime is null )
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime < :dateTime )
)

will become :
FROM Entity
WHERE NOT
( NOT( entity.fromDateTime is null AND entity.toDateTime < :dateTime)
AND NOT
( entity.fromDateTime >= :dateTime AND entity.toDateTime is null )
AND NOT
( entity.fromDateTime >= :dateTime AND entity.toDateTime < :dateTime )
)

Anyway, i hope there will be a bug fix soon, because this workaround make queries less readable.
[ Show » ] Nicolas De Cubber - [28/Aug/07 09:10 AM ] Hi, I had the same problem and it is very annoying. Anyway, i found a work around. Apparently the parser for the request remove all parenthesis and replace them when he think they are necessary. Unfortunately, it seems that in the query builder they forgot to put parenthesis surrounding an OR condition. But, hopefuly, the query builder put parenthesis for a NOT condition. Then the request : (1 and 2) or (3 and 4) or (5 and 6) can be rewrited like that : (1 and 2) AND NOT(~3 or ~4) AND NOT (~5 or ~6) where ~3 is the opposite condition of 3 If I took the example given by Dennis Fleurbaaij , the request: FROM Entity WHERE ( ( entity.fromDateTime is null AND entity.toDateTime < :dateTime) OR ( entity.fromDateTime >= :dateTime AND entity.toDateTime is null ) OR ( entity.fromDateTime >= :dateTime AND entity.toDateTime < :dateTime ) ) will become : FROM Entity WHERE ( ( entity.fromDateTime is null AND entity.toDateTime < :dateTime) AND NOT ( entity.fromDateTime < :dateTime OR entity.toDateTime is not null ) AND NOT ( entity.fromDateTime < :dateTime AND entity.toDateTime >= :dateTime ) ) Anyway, i hope there will be a bug fix soon, because this workaround make queries less readable.

Nicolas,

Great find! It fixes the problem in the short run and will not break when Hibernate is patched. The perfect workaround.

Dennis

I was still looking at that problem, and finally, i found it was not a problem.
If you look at the SQL condition Precedence, you will see that the 'AND' is always executed before the 'OR', so the query
((1 AND 2) OR (3 AND 4) OR (5 AND 6))
is equivalent to the query
(1 AND 2 OR 3 AND 4 OR 5 AND 6)

It is like in Mathematics where the multiplication has priority over the addition.

So, this bug (which is not a bug) can be close because it was a misunderstood of the sql language.

see SQL condition Precedence here : http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10759/conditions001.htm#i1034741

@Nicolas:

I disagree with you. While you are right that precedence does work in this case, the brackets are misaligned from the HQL to the SQL and that certainly is a bug.

Furthermore the feature (it's not a bug it's a feature!) of being able to arbitrarily place brackets is very handy and much used. That alone should be reason enough to have it. Stripping out the bracket possibility as a feature and declaring the bug therefore nonexistent because the feature is not supported is not the way to go IMHO.

I have the same problem in my application, and I think that is is exactly the same bug as HHH-377, which was agains core 3.01

@Nicolas
please note that precedence is not a correct answer, as (A OR B) AND C is not the same as A OR B AND C

This is a bug.

@Stephane
I agree with you that (A OR B) AND C is not the same as A OR B AND C, but the problem given by Dennis, is not with an 'OR' operator in parenthesis, but with an 'AND' operator. So if you have a version of Hibernate which transform your query '(A OR B) AND C' in 'A OR B AND C', it is clearly a bug.

@Denis
Hibernate does not make a copy of SQL code into HQL code, there is a transformation. In fact, it seems there is a parser on the query, that make a Tree, and when visiting the tree, when it arrives at an 'AND' operator, it does not write the parenthesis.
If your problem is that it sometimes seems to move parenthesis, like in your example result 'and ( ( 1 ) and 2 or 3 and ( 4 ) or 5 and 6 )', it is easy to explain. Your request 1 and 4 are certainly of type 'IS NULL' or 'IS NOT NULL'.
If your problem is to find your query in your server log, you should use an other mechanism, like making your own log, because the conversion from HQL to SQL is not so straightforward.
In the example i had given by rewritting the query with 'NOT' operators, the SQL generated query, in my case, was not generated with 'NOT' operator, but seems much more to :
FROM Entity
WHERE
( ( entity.fromDateTime is null AND entity.toDateTime < :dateTime)
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime is null )
OR
( entity.fromDateTime >= :dateTime AND entity.toDateTime < :dateTime )
)
with parentehis placed (due to the fact that we had used a not operator).
And this reconversion is good because it reduces the number or operation to do, so it improves performance.

Finally, if it is a feature and not a bug, you should have created it has a 'New Feature' type and not with the 'bug' type

@Nicolas

In despite of the fact that this is some obscure undocumented* behaviour and that a natural feeling of parenthesis, as used in math/normal SQL/programming languages is concidered a feature requested, I still think it's a bug. Bugs aren't only against erronous code, but also against things like these.

Performance improvements are something to watch out for, I do agree with you on that, but the loss in developer time and increase in complexity/non standards conformity (see how long this topic is opened) far outwheighs any small optimizations in the query IMHO.

• = I couldn't make this behaviour up from the documentation.

Dear all,

I think I encountered the same problem. Will there be a fix for this ? or is there any workaround ?

HQL query :

select o from VwPubQaAnswer o
where articleId <> :articleId
and parentArticleId is null
and ((repliedDate < :repliedDate) or (repliedDate = :repliedDate and articleId < :articleId ))
order by repliedDate desc, articleId desc

SQL generated:

select vwpubqaans0_.QA_ID as QA1_1652_, vwpubqaans0_.ANSWER as ANSWER1652_, vwpubqaans0_.ARTICLE_ID as ARTICLE3_1652_, vwpubqaans0_.CREATED_DATE as CREATED4_1652_, vwpubqaans0_.PARENT_ARTICLE_ID as PARENT5_1652_, vwpubqaans0_.QUESTION as QUESTION1652_, vwpubqaans0_.REPLIED_DATE as REPLIED7_1652_, vwpubqaans0_.REPLIED_DISPLAY_NAME as REPLIED8_1652_, vwpubqaans0_.SENDER_NICKNAME as SENDER9_1652_
from QUAM_ADM.VW_PUB_QA_ANSWER vwpubqaans0_
where
vwpubqaans0_.ARTICLE_ID<>?
and (vwpubqaans0_.PARENT_ARTICLE_ID is null)
and (vwpubqaans0_.REPLIED_DATE<? or vwpubqaans0_.REPLIED_DATE=? and vwpubqaans0_.ARTICLE_ID<?)
order by vwpubqaans0_.REPLIED_DATE desc, vwpubqaans0_.ARTICLE_ID desc

Thanks.

Please attach a runnable test case (Java + mapping).

I tried changing the query by using the NOT and AND variation. But doesn't look like it's resolving the issue.
The hql query send to hibernate createQuery method:
[3/16/08 1:43:29:957 EDT] 00000032 SystemOut O from QueueCustomerView where (formStatusId in (102, 103, 104, 105)) and (accountOfficer in ('..','...')) and ( not ( not (responsibleBranchId = 127 and businessLineId = 112) and not (responsibleBranchId = 130 and businessLineId = 104) and not (responsibleBranchId = 122 and businessLineId = 112) and not (responsibleBranchId = 108 and businessLineId = 100))) order by customerStatusId desc , reassessmentDate asc

The hibernate generated SQL output from console:
[3/16/08 1:43:29:973 EDT] 00000032 SystemOut O Hibernate: select ... from kycr.vw_customer_queue queuecusto0_ where (queuecusto0_.STATUS_ID in (102 , 103 , 104 , 105)) and (queuecusto0_.ACCT_OFF_ID in ('...'.'...')) and (queuecusto0_.BK_BRNCH=127 and queuecusto0_.BUSNSS_LINE=112 or queuecusto0_.BK_BRNCH=130 and queuecusto0_.BUSNSS_LINE=104 or queuecusto0_.BK_BRNCH=122 and queuecusto0_.BUSNSS_LINE=112 or queuecusto0_.BK_BRNCH=108 and queuecusto0_.BUSNSS_LINE=100) order by queuecusto0_.CUST_IS_NEW desc, queuecusto0_.REASS_DT asc

Thanks

I do not see the problem.
If i replace your query filters by letters (A,B,C...)
i will have :
[3/16/08 1:43:29:957 EDT] 00000032 SystemOut O from QueueCustomerView where (A and (B) and ( not ( not (C and D) and not (E and F) and not (G and D) and not (H and I))) order by customerStatusId desc , reassessmentDate asc

and
3/16/08 1:43:29:973 EDT] 00000032 SystemOut O Hibernate: select ... from kycr.vw_customer_queue queuecusto0_ where (A and (B) and (C and D or E and F or G and D or H and I) order by queuecusto0_.CUST_IS_NEW desc, queuecusto0_.REASS_DT asc

which the where condition can be write has follow :
A.B. Unable to render embedded object: File (( !(C.D).) not found.(E.F).Unable to render embedded object: File ((G.D).) not found.(H.I))
which can be rewriten has follow:
A.B. ( !Unable to render embedded object: File ((C.D)+) not found.Unable to render embedded object: File ((E.F)+) not found.Unable to render embedded object: File ((G.D)+) not found.!(H.I))
or has follow:
A.B.( (C.D)(E.F)(G.D)+(H.I))

but, because '.' has prioritary on '+', you could also write it like :
A.B.(C.D+E.F+G.D+H.I) which is what is given by hibernate, so there is no problem, in my point of view.

I'm getting the same problem with the following and Nicolas's workaround to use NOT(not(3 and 4) and not(5 and 6) doesnt work.
Here's the HQL produced and the SQL results:

HQL: from SymbolMappingImpl e
where (e.businessKey.subBusinessDivisionCdId = 5412 and e.businessKey.productFamilyCdId = 4540 and e.businessKey.symbolCdId = 5969 and e.businessKey.companyCdId = 3802 and e.businessKey.stateCdId = 3744) or
(e.businessKey.subBusinessDivisionCdId = 5412 and e.businessKey.productFamilyCdId = 4540 and e.businessKey.symbolCdId = 5969
and e.businessKey.companyCdId = 3802 and e.businessKey.stateCdId = 3744)

SQL: select symbolmapp0_.SymbolMappingId as SymbolMa1_217_, symbolmapp0_.SubBusinessDivisionCdId as SubBusin2_217_,
symbolmapp0_.ProductFamilyCdId as ProductF3_217_, symbolmapp0_.SymbolCdId as SymbolCdId217_,
symbolmapp0_.CompanyCdId as CompanyC5_217_, symbolmapp0_.StateCdId as StateCdId217_, symbolmapp0_.EffectiveDt as Effectiv7_217_,
symbolmapp0_.ExpirationDt as Expirati8_217_
from SymbolMapping symbolmapp0_
where symbolmapp0_.SubBusinessDivisionCdId=5412 and symbolmapp0_.ProductFamilyCdId=4540 and symbolmapp0_.SymbolCdId=5969 and
symbolmapp0_.CompanyCdId=3802 and symbolmapp0_.StateCdId=3744 or
symbolmapp0_.SubBusinessDivisionCdId=5412 and symbolmapp0_.ProductFamilyCdId=4540 and symbolmapp0_.SymbolCdId=5969 and
symbolmapp0_.CompanyCdId=3802 and symbolmapp0_.StateCdId=3744

I attached XML and java file. I have to fallback to raw SQL query to solve that one....

apologies, after doing more testing it does appear that the results are consistent however I think that HQL should not tamper with parenthesis put there on purpose by the developer, it is only confusing and time consuming for the developer to figure things out.

Thanks,
-Guillaume

Any news regarding this issue?

Regards,
Loïc

The first phase of the query parser actually groups and aligns the predicates according to your parenthesis; it changes the order in which they are evaluated (imagine, just like would happen in the sql's query translator!).

@Guillaume, @Denis Would it be ideal to be able to match the parenthesis from the input string into the output string. Well perhaps (I personally think it is not necessary since as y'all are finding out the results are consistent). However, this will not happen unless someone steps up and produces a patch for this. It is extremely difficult to do and you get zero benefit for all that effort.