[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5645) Criteria.createAlias with specified criterion results in wrong parameters passed into SQL statement
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5645?page=c...
]
Paweł Szafran commented on HHH-5645:
------------------------------------
Hi Fekete,
Unfortunately, my team has faced the very same problem:
We have a complex criteria query that has 3 joins defined by createAlias() with addition
restrictions (to appear in on() clause). One of our testers found that the query returns
wrong results. We investigated the problem and went to the exact same conclusions as you
did:
The order of joins in final sql statement doesn't match the list of values to be put
in that statement!
The order of joins in Java code is e.g. ABC, and so the values to be put in the sql
statement are in the same ABC order, but the generate sql statement itself is somehow in
ACB order, so the values are put in wrong places, and this produces the incorrect result.
Of course, changing the order of calling createAlias() in the Java code to ACB, makes the
query works fine, but it's not a solution - the order of using createAlias()
shouldn't matter.
This is really frustrating, especially that no one from Hibernate team replied to this
issue. And it's been almost a year now!
So in this case, I have to ask you Fekate: Have you found the solution to this problem?
Od how did you solve this issue in your project?
I would really appreciate your help!
Best Regards,
Pawel
Criteria.createAlias with specified criterion results in wrong
parameters passed into SQL statement
---------------------------------------------------------------------------------------------------
Key: HHH-5645
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5645
Project: Hibernate Core
Issue Type: Bug
Components: core
Affects Versions: 3.5.6
Environment: Windows XP SP2, Hibernate 3.5.6, JRE 1.6.0_14, PostgreSQL (tested
also with Sybase ASE)
Reporter: Fekete Kamosh
Attachments: aliases_test.zip
Suppose to have tables A, B, C.
Relationships: A(one)=>B(many); B(one)=>C(many)
Table A is used as main entity.
For tables B and C lets *establish aliases* using method
_public Criteria createAlias(String associationPath, String alias, int joinType,
*Criterion withClause*) throws HibernateException;_
Each created alias has addition Criterion to force Hibernate generate clause _"on
column = column and (other_criterion_condition)"._
If aliases are created in *order B, C* everything is OK:
Resulting SQL:
_SELECT this_.table_a_character AS y0_
FROM table_a this_
LEFT OUTER JOIN table_b tablebalia1_
ON this_.table_a_id = tablebalia1_.table_a_id
AND ( tablebalia1_.table_b_date = ? )
LEFT OUTER JOIN table_c tablecalia2_
ON tablebalia1_.table_b_id = tablecalia2_.table_b_id
AND ( tablecalia2_.table_c_boolean = ? )
WHERE this_.table_a_character = ?_
Passed parameters:
*[Sun Oct 10 22:09:31 CEST 2010, false, c]*
But if aliases are created in *order C, B error occurs* as resulting SQL remains the same
as already shown, but
Passed parameters are:
*[false, Sun Oct 10 22:12:13 CEST 2010, c]*
which causes SQL exception, because there are passed *parameters in wrong order to SQL
statement*:
43843 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState:
42883
43843 [main] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: operator does not
exist: date = boolean
*Test example* (database tables, entities, hibernate.cfg and test file) *attached.*
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira