[hibernate-dev] HHH-3627 - Should concat convert parameters to string?

[Juraci Costa]

All,

I don't know if this was discussed before (I didn't find it in the list's archives), but if so, I'm probably missing something basic here :-) 

I came across this issue, and I don't know whether this should be changed or not. The problem is that the guy is trying to use the concat function with numeric literals. Hibernate not only accepts these values, but sends them "as is" to the RDBMS's, causing an error for MSSQL when mixing the types, or causing unexpected behaviour when using only numeric types

s.createQuery( "from Human h where h.nickName = concat(1,2)" ).list(); 
s.createQuery( "from Human h where h.nickName = concat(1,2,'c')" ).list(); 

Results in:
        human0_.nickName=(
            1+2 // becomes "3", instead of 12
        ) 
and 
        human0_.nickName=(
            1+2+'c' // error in MSSQL
        ) 

I realize that the numeric parameters needs a cast to behave correctly, but I'm wondering if this cast should be in Hibernate or in the user's code. There are some arguments for both sides.

In Hibernate because:
- The HQL should be the same for as many RDBMS's as possible (that's one of the benefits of having a specific query language, right?). If Hibernate accepts integer parameters for "string concatenation"[1], it should do what is expected (concatenate strings)
- Both the concat and the || operator are clearly supposed to act on character types, but the MSSQL operator (+) can be used in both numeric and character types, with different purposes (arithmetic for numbers, concatenation for character). As Hibernate was asked to "concatenate", it should make this intention clear to the underlying RDBMS. In this case, by converting the values to character types. There is no such problems in other RDBMSs, as they have specific operators (||) or functions (concat) to this purpose. 
- org.hibernate.dialect.function.DerbyConcatFunction seems a good precedent for such specific things :-) 

In the Application because:
- As the RDBMS can accept other values than the "default string" type (varchar), the app developer should explicitly cast to the desired type, if this format is not a character type. 

[1] http://www.hibernate.org/hib_docs/reference/en/html/queryhql-expressions.html

So, should concat convert parameters to string?

- Juca.