[Hibernate-JIRA] Created: (HHH-5645) Criteria.createAlias with specified criterion results in wrong parameters passed into SQL statement
3:28 p.m.
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.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:36 a.m.
New subject: [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
3:38 a.m.
New subject: [Hibernate-JIRA] Issue Comment Edited: (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 edited comment on HHH-5645 at 9/12/11 3:37 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 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
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 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
3:38 a.m.
New subject: [Hibernate-JIRA] Issue Comment Edited: (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 edited comment on HHH-5645 at 9/12/11 3:36 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 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
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 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
3:40 a.m.
New subject: [Hibernate-JIRA] Issue Comment Edited: (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 edited comment on HHH-5645 at 9/12/11 3:38 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 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
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?
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
3:40 p.m.
New subject: [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...
]
Fekete Kamosh commented on HHH-5645:
------------------------------------
Hi,
I can offer some temporary solution for this. It is based on Hibernate version 3.5.6.
It might be OK if you use method org.hibernate.Criteria.list() to get results of aliased
entities.
I tried this "patched code" on the test I provided for this fault and it seems
fine.
You need to alter implementation of two Hibernate classes. I would recommend downloading
hibernate sources and copy these two into independent project -> change code ->
compile them (use hibernate3.jar in classpath to enable compiling) -> create jar file
(with two altered classes) -> add this jar file into classpath before standard
hibernate3.jar
org.hibernate.loader.criteria.CriteriaQueryTranslator
change method
public List list(SessionImplementor session)
throws HibernateException {
return list( session, translator.getQueryParameters(), querySpaces, resultTypes );
}
to
public List list(SessionImplementor session)
throws HibernateException {
// Query parameters for subqueries are sorted by order specified in userAliases
// This is temporary patch for Hibernate fault HHH-5645
// http://opensource.atlassian.com/projects/hibernate/browse/HHH-5645
return list( session, translator.getQueryParameters(userAliases), querySpaces,
resultTypes );
}
org.hibernate.loader.criteria.CriteriaQueryTranslator
add
import java.util.Collection;
change method
public QueryParameters getQueryParameters() {
LockOptions lockOptions = new LockOptions();
RowSelection selection = new RowSelection();
selection.setFirstRow( rootCriteria.getFirstResult() );
selection.setMaxRows( rootCriteria.getMaxResults() );
selection.setTimeout( rootCriteria.getTimeout() );
selection.setFetchSize( rootCriteria.getFetchSize() );
Iterator iter = rootCriteria.getLockModes().entrySet().iterator();
while ( iter.hasNext() ) {
Map.Entry me = ( Map.Entry ) iter.next();
final Criteria subcriteria = getAliasedCriteria( ( String ) me.getKey() );
lockOptions.setAliasSpecificLockMode( getSQLAlias( subcriteria ),
(LockMode)me.getValue() );
}
List values = new ArrayList();
List types = new ArrayList();
iter = rootCriteria.iterateSubcriteria();
while ( iter.hasNext() ) {
CriteriaImpl.Subcriteria subcriteria = ( CriteriaImpl.Subcriteria ) iter.next();
LockMode lm = subcriteria.getLockMode();
if ( lm != null ) {
lockOptions.setAliasSpecificLockMode( getSQLAlias( subcriteria ), lm );
}
if ( subcriteria.getWithClause() != null )
{
TypedValue[] tv = subcriteria.getWithClause().getTypedValues( subcriteria, this );
for ( int i = 0; i < tv.length; i++ ) {
values.add( tv[i].getValue() );
types.add( tv[i].getType() );
}
}
}
// Type and value gathering for the WHERE clause needs to come AFTER lock mode
gathering,
// because the lock mode gathering loop now contains join clauses which can contain
// parameter bindings (as in the HQL WITH clause).
iter = rootCriteria.iterateExpressionEntries();
while ( iter.hasNext() ) {
CriteriaImpl.CriterionEntry ce = ( CriteriaImpl.CriterionEntry ) iter.next();
TypedValue[] tv = ce.getCriterion().getTypedValues( ce.getCriteria(), this );
for ( int i = 0; i < tv.length; i++ ) {
values.add( tv[i].getValue() );
types.add( tv[i].getType() );
}
}
Object[] valueArray = values.toArray();
Type[] typeArray = ArrayHelper.toTypeArray( types );
return new QueryParameters(
typeArray,
valueArray,
lockOptions,
selection,
rootCriteria.isReadOnlyInitialized(),
( rootCriteria.isReadOnlyInitialized() ? rootCriteria.isReadOnly() : false ),
rootCriteria.getCacheable(),
rootCriteria.getCacheRegion(),
rootCriteria.getComment(),
rootCriteria.isLookupByNaturalKey(),
rootCriteria.getResultTransformer()
);
}
to new implementation and add new overloaded method getQueryParameters
public QueryParameters getQueryParameters() {
return getQueryParameters(null);
}
/**
* Overloaded {@link #getQueryParameters()} with possibility to pass
* aliases in the order they appear in resulting SQL
* @param orderedAliases Ordered aliases as they appear in resulting SQL
* @return Query parameters with ordered parameters
*/
public QueryParameters getQueryParameters(String[] orderedAliases) {
LockOptions lockOptions = new LockOptions();
RowSelection selection = new RowSelection();
selection.setFirstRow( rootCriteria.getFirstResult() );
selection.setMaxRows( rootCriteria.getMaxResults() );
selection.setTimeout( rootCriteria.getTimeout() );
selection.setFetchSize( rootCriteria.getFetchSize() );
Iterator iter = rootCriteria.getLockModes().entrySet().iterator();
while ( iter.hasNext() ) {
Map.Entry me = ( Map.Entry ) iter.next();
final Criteria subcriteria = getAliasedCriteria( ( String ) me.getKey() );
lockOptions.setAliasSpecificLockMode( getSQLAlias( subcriteria ),
(LockMode)me.getValue() );
}
List values = new ArrayList();
List types = new ArrayList();
// If order of aliases is specified, sort subcriterias by sorted aliases
iter = orderedAliases == null ? rootCriteria.iterateSubcriteria() :
iterSortedSubcriteria(rootCriteria.iterateSubcriteria(), orderedAliases);
while ( iter.hasNext() ) {
CriteriaImpl.Subcriteria subcriteria = ( CriteriaImpl.Subcriteria ) iter.next();
LockMode lm = subcriteria.getLockMode();
if ( lm != null ) {
lockOptions.setAliasSpecificLockMode( getSQLAlias( subcriteria ), lm );
}
if ( subcriteria.getWithClause() != null )
{
TypedValue[] tv = subcriteria.getWithClause().getTypedValues( subcriteria, this
);
for ( int i = 0; i < tv.length; i++ ) {
values.add( tv[i].getValue() );
types.add( tv[i].getType() );
}
}
}
// Type and value gathering for the WHERE clause needs to come AFTER lock mode
gathering,
// because the lock mode gathering loop now contains join clauses which can contain
// parameter bindings (as in the HQL WITH clause).
iter = rootCriteria.iterateExpressionEntries();
while ( iter.hasNext() ) {
CriteriaImpl.CriterionEntry ce = ( CriteriaImpl.CriterionEntry ) iter.next();
TypedValue[] tv = ce.getCriterion().getTypedValues( ce.getCriteria(), this );
for ( int i = 0; i < tv.length; i++ ) {
values.add( tv[i].getValue() );
types.add( tv[i].getType() );
}
}
Object[] valueArray = values.toArray();
Type[] typeArray = ArrayHelper.toTypeArray( types );
return new QueryParameters(
typeArray,
valueArray,
lockOptions,
selection,
rootCriteria.isReadOnlyInitialized(),
( rootCriteria.isReadOnlyInitialized() ? rootCriteria.isReadOnly() : false ),
rootCriteria.getCacheable(),
rootCriteria.getCacheRegion(),
rootCriteria.getComment(),
rootCriteria.isLookupByNaturalKey(),
rootCriteria.getResultTransformer()
);
}
add new method
/**
* Returns iterator of sorted subcriteria.
* Subcriteria with aliases specified in <code>orderedAliases</code> go
first
* @param iterSubcriteria Subcriteria iterator
* @param orderedAliases Ordered names of aliases
* @return Subcriteria iterator ordered by specified aliases
*/
private Iterator iterSortedSubcriteria(Iterator iterSubcriteria, final String[]
orderedAliases) {
// Fill collection used only as temporary container
Collection tempContainer = new ArrayList();
// Fill with data of iterator
while(iterSubcriteria.hasNext()) {
tempContainer.add(iterSubcriteria.next());
}
// Resulting subcriteria
List res = new ArrayList();
for(String orderedAlias :orderedAliases) {
// Lets find subcriteria with actual alias
Iterator i = tempContainer.iterator();
while(i.hasNext()) {
Object obj = i.next();
if(obj instanceof CriteriaImpl.Subcriteria) {
CriteriaImpl.Subcriteria subcriteria = (CriteriaImpl.Subcriteria) obj;
if(orderedAlias.equals(subcriteria.getAlias())) {
// Subcriteria found by its alias, lets remember it in resulting list
res.add(subcriteria);
// Remove subcriteria from temporary container
i.remove();
}
}
}
}
// Add rest of criteria (which were not found using alias name) into result
// , they will be added to the end of list
res.addAll(tempContainer);
return res.iterator();
}
My code on local machine is little bit messy and this is why I do not post ideal patch. In
addition I do not know what is your current version of Hibernate, sorry.
It is a pitty that Hibernate guys do not care about reported bugs :-(
Fekete
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
2:06 a.m.
New subject: [Hibernate-JIRA] Issue Comment Edited: (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 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
4804
days inactive
5142
days old
hibernate-issues@lists.jboss.org
6 comments
2 participants
participants (2)
-
Fekete Kamosh (JIRA) -
Paweł Szafran (JIRA)