Why is Hibernate ALTER ing my QUERY ? multiple columns with IN operator

If I debug, I can see that the string I'm building is

and ( property1 , property2 ) in ( values (0, 5) , (3, 4) )

but Hibernate thinks it's better to transform it like this

and (
    (
    tabAlias.C开发者_Python百科OLUMN1 , tabAlias.COLUMN2
    ) in (

    values
    (0, 5) ,
    )
)

I don't mind the extra ( ) Hibernate added, but I don't understand why after (0, 5) , it doesn't appear (3, 4) ?? Why is Hibernate tryin' to be smart and mess things up?

ps: I'm using DB2 9

ps2: don't tell me to use criteria query cuz' I'm fixing somebodyelse's code, I don't want to rewrite it all. ps3: I know this isn't portable, but I don't care.

Any workarounds ? I also don't want to use multple OR clauses to simulate this.

HQL is not SQL. Hibernate parses the HQL and builds a new SQL query based on it. I suspect what you're trying to do is not supported in HQL. Normally you would get an error but for some reason it's accepting your HQL and generating invalid SQL.

If you want to use SQL you can get a connection with Session.connection().

You may be able to get your syntax working by modifying it a bit. This may help: http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html#queryhql-tuple

[EDIT]

Looks like this person experienced the same problem and switched to using createSQLQuery() to solve it. It must be a bug in hibernate.

https://forum.hibernate.org/viewtopic.php?f=1&t=982785&view=previous

[EDIT]

Shouldn't your query be:

and ( property1 , property2 ) in ( values ((0, 5) , (3, 4)) )

Note the extra parens.

I used the multiple OR version. As my business requirements made it a little bit complicated with the multi column IN version I wanted to use. The answer to this question is: this is not supported by hibernate, nor it gives you the flexibility to pass whatever he doesn't understand untouched.