[OPENJPA-2720] Invalid syntax with coalesce and BigDecimal using DB2 for z/OS

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.2.0, 2.4.2
• Component/s: criteria, sql
• Assignee: Unassigned
• Reporter: Pierre-Yves Soblet
• Created: 18/Oct/17 14:57
• Updated: 18/Oct/17 14:57

Description

Assuming an entity with a BigDecimal column:

@Entity
@Table(name = "TEST_TABLE")
public class TestTable {

    @Id
    @Basic(optional = false)
    private Integer id;

    private BigDecimal amount;
// ...

executing the following criteria query against a DB2 for z/OS database:

CriteriaQuery<BigDecimal> criteriaQuery = builder.createQuery(BigDecimal.class);
Root<TestTable> root = criteriaQuery.from(TestTable.class);
criteriaQuery.select(builder.coalesce(root.get(TestTable_.amount), BigDecimal.ZERO));
entityManager.createQuery(criteriaQuery).getSingleResult();

produces the following SQL:

SELECT COALESCE(t0.amount,0) 
    FROM TEST_TABLE t0 optimize for 1 row 

which generates the following error:

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=,0;??( [ CONCAT || / MICROSECONDS MICROSECOND SECONDS SECOND

which is caused by the missing space between the COALESCE parameters.

A workaround is to use another function around the BigDecimal literal. For instance:

criteriaQuery.select(builder.coalesce(root.get(TestTable_.amount), builder.function("DECIMAL", BigDecimal.class, builder.literal(BigDecimal.ZERO))));