[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5137) NULLIF misinterpreted when a comma is set as a decimal point (DB2 z/OS)

[Leonardo (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36639#action_36639 ] 

Leonardo commented on HHH-5137:
-------------------------------

>How would Hibernate determine which decimal representation it should use?
I don't think Hibernate can determine a decimal representation in advance (unless it ask for DB2 driver for this property). Anyways, this issue isn't a decimal numbers related but with the function parameters separated by comma interpreted as a number. It comes round because DB2 misinterprets a comma intended as separator in dynamic SQL and that is why the parameter must be followed by a space (to help db2 guys disambiguate them :).
so, if DB2Dialect puts a space after a comma (parameter separator) this works in any context. (I played it in DB2 9.7 Express/Windows and DB2 9 z/OS with et without comma as decimal representation)

> NULLIF misinterpreted when a comma is set as a decimal point (DB2 z/OS)
> -----------------------------------------------------------------------
>
>                 Key: HHH-5137
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5137
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.3.2
>         Environment: Hibernate 3.3.2.GA, DB2 9 for z/OS
>            Reporter: Leonardo
>            Priority: Minor
>         Attachments: NullIfForDB2_zOS.zip
>
>
> If we set in DB2 a comma (instead a period) as a separator for decimal point I get an error when Hibernate executes this query:
> DEBUG SQL:111 - select foo0_.id as id0_0_, foo0_.myBar_id as myBar1_3_0_, foo0_.clazz_ as clazz_0_ from ( select nullif(0,0) as myBar_id, id, 0 as clazz_ from Foo union all select nullif(0,0) as myBar_id, id, 1 as clazz_ from SubFoo2 union all select myBar_id, id, 2 as clazz_ from SubFoo1 ) foo0_ where foo0_.id=?
> com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -170,
> SQLSTATE: 42605, SQLERRMC: NULLIF
> In fact, DB2 interprets the argument of nullif(0,0) as a decimal number and not as two arguments (zero, zero). DB2 documentation [1] says that if the comma is set as a decimal point a comma intended as a separator must be followed by a space. so in that case, nullif(0, 0) (note space after ',').
> We are using DB2390Dialect and getSelectClauseNullString(int sqlType) method doesn't generate a correct nullif instruction in this case.
> we could circumvent this issue by extending DB2390Dialect class and placing spaces after comma like this: return "nullif(" + literal + ", " + literal + ')'. But isn't a proper solution for us because we must release an extra jar with custom dialect to our customers.
> [1] http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_decimalpointrepresentation.htm

-- 
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