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

[Steve Ebersole]

The SQL spec says that numerics are completely valid within a
concatenation.  The issue is strictly limited to SQL Server because of
its overloading of '+' as both addition and concatenation and its having
no dedicated concatenation operator.

Arguably we should handle this in the SQL Server dialect since obviously
we want concatenation instead of addition in these instances (use of the
concat function).  The fix would be isolated to the dialect function
registration to handle SQL Servers non-compliance.  Basically register a
special function definition which wraps all arguments representing
unquoted numerics in cast calls (or SQL Server convert function calls).

-  

Steve Ebersole
Project Lead
http://hibernate.org
steve at hibernate.org

Principal Software Engineer
JBoss, a division of Red Hat
http://jboss.com
http://redhat.com
steve.ebersole at jboss.com
steve.ebersole at redhat.com


On Fri, 2008-11-28 at 06:16 -0500, Juraci Costa wrote:
> 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.
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev