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

[Kristoffer Erlandsson (JIRA)]

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

Kristoffer Erlandsson commented on HHH-2365:
--------------------------------------------

Could you elaborate on why this is considered a duplicate of HHH-1423? From what I understand the problem in HHH-1423 is triggered when the query contains the string 'call'. We have the problem described in this issue and we do not have the string 'call' in our query.

> 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

-- 
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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira