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

[Gail Badner (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5137?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Gail Badner updated HHH-5137:
-----------------------------

    Issue Type: Bug  (was: Improvement)

I misunderstood the issue. I've changed it back to a bug.

Please attach a patch with your correction. 

If your fix should work for all DB2 dialects, then the patch should be against http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/DB2Dialect.java

Thanks,
Gail

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