[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2365) Incorrect parameter order for parameters used as map-key in HQL

Incorrect parameter order for parameters used as map-key in HQL --------------------------------------------------------------- Key: HHH-2365 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2365 Project: Hibernate3 Issue Type: Bug Components: query-hql Affects Versions: 3.2.1 Reporter: Hendrik D Assignee: Diego Pires Plentz When using parameters in HQL for the key of a domain object map in the where-clause, the query parsing mechanism results in an SQL query with the parameters used in the wrong places. Since my real domain objects require you to know too much of the application I'm writing, I'll describe the problem with a simplified setup: CLASSES: ------- class Foo { int id; int meaninglessNumber; Map<Language, Document> documents; } class Document { int id; String name; } class Language { String code; } TABLES: ------- FOO id meaninglessNumber documents_link_id DOCUMENTS_LINK documents_link_id language_code document_id name LANGUAGE language_code Now, let't try to retrieve all Foo that contain an english document with name "English", and have a meaninglessNumber of '5'. This is the HQL I produce: from Foo as foo where foo.meaninglessNumber = param1 and foo.documents[:param2]=:param3 These are the parameter values: param1 = int 5 param2 = Language {code="en"} param3 = String "English" I expect an sql query like this: select * from foo foo0_ inner join documents documents1_ where foo0_.documents_link_id = documents1_.documents_link_id and documents1_.language_code = 'en' and foo0_.meaninglessNumber = 5 and documents1_.name = 'English' But instead, the real query looks like this: select * from foo foo0_ inner join documents documents1_ where foo0_.documents_link_id = documents1_.documents_link_id and documents1_.language_code = 5 and foo0_.meaninglessNumber = 'en' and documents1_.name = 'English' The subtle difference is, that the parameters param1 and param2 seem to be switched! A lot of debugging pointed out that the order of the indexes is determined based on the HQL, resulting in the following indexes: param1 -> 1 param2 -> 2 param3 -> 3 In the generated SQL, all parameters are replaced by '?', and on execution, these are replaced one by one, following the order of the parameter indexes. While creating the SQL, the map key parameter is transformed into 'documents1_.language_code = ?'. No problem here, but this condition is placed in the beginning of the where clause, before the use of parameter param1. The indexes of the parameters should become: param2 -> 1 param1 -> 2 param3 -> 3 But this is never done! I see 3 possible ways to fix this: - Recalculate the indexes of the parameters when the sql is generated - Generate the sql in the right position, so the order of the parameters stays the same - Use named parameters in the sql and only replace them with '?' right before creating the PreparedStatement. This seems to me the most reliable way, but I guess I'm missing the point of calculating the parameter indexes and replacing them with '?' immediately... Hendrik