[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-5645) Criteria.createAlias with specified criterion results in wrong parameters passed into SQL statement

[Paweł Szafran (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=43500#comment-43500 ] 

Paweł Szafran edited comment on HHH-5645 at 9/13/11 2:05 AM:
-------------------------------------------------------------

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 additional 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 generated 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 Fekete: Have you found the solution to this problem? Or how did you solve this issue in your project?

I would really appreciate your help!

Best Regards,
Pawel


      was (Author: pawel.jdfcafe):
    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 additional 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 generated 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? Or 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