Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2720

Invalid syntax with coalesce and BigDecimal using DB2 for z/OS

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.2.0, 2.4.2
    • Fix Version/s: None
    • Component/s: criteria, sql
    • Labels:
      None

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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              pys Pierre-Yves Soblet
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: