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

[Hendrik D (JIRA)]

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
        Type: Bug

  Components: query-hql  
    Versions: 3.2.1    
    Reporter: Hendrik D


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