OpenJPA
  1. OpenJPA
  2. OPENJPA-1615

CriteriaQuery test failures on MSSQL

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.0.0-beta3, 2.1.1, 2.2.0
    • Fix Version/s: 2.4.0
    • Component/s: jpa
    • Labels:
      None

      Description

      Seeing 2 different testcase failures in TestTypesafeCriteria for MSSQL:

      1) MSSQL doesn't support the MOD() function syntax but expects "%" instead, which is set in the SQLServerDictionary as "supportsModOperator = true", but CriteriaBuilderImpl and Expressions is ignoring this option when generating the JPQL, so the testFunctionWithTwoArgument() and testFunctionWithFunctionArgumentInOrderBy() tests fail -

      junit.framework.AssertionFailedError: CriteriaQuery corresponding to SELECT MOD(c.balanceOwed,10) FROM Customer c failed to execute
      java.lang.RuntimeException: SELECT MOD(t0.balanceOwed, ?) FROM CR_CUST t0
      at org.apache.openjpa.persistence.criteria.AbstractCriteriaTestCase.executeQueryAndCollectSQL(AbstractCriteriaTestCase.java:314)
      at org.apache.openjpa.persistence.criteria.AbstractCriteriaTestCase.executeAndCompareSQL(AbstractCriteriaTestCase.java:164)
      at org.apache.openjpa.persistence.criteria.AbstractCriteriaTestCase.assertEquivalence(AbstractCriteriaTestCase.java:141)
      at org.apache.openjpa.persistence.criteria.AbstractCriteriaTestCase.assertEquivalence(AbstractCriteriaTestCase.java:108)
      at org.apache.openjpa.persistence.criteria.TestTypesafeCriteria.testFunctionWithTwoArgument(TestTypesafeCriteria.java:908)
      . . .
      Caused by: <openjpa-2.0.0-SNAPSHOT-runknown fatal general error> org.apache.openjpa.persistence.PersistenceException: 'MOD' is not a recognized built-in function name.

      {prepstmnt 3164468 SELECT MOD(t0.balanceOwed, ?) FROM CR_CUST t0 [params=(int) 10]} [code=195, state=S00010]
      . . .
      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: 'MOD' is not a recognized built-in function name. {prepstmnt 3164468 SELECT MOD(t0.balanceOwed, ?) FROM CR_CUST t0 [params=(int) 10]}

      [code=195, state=S00010]

      2) There is a NVARCHAR to BigDecimal conversion failure in testBigDecimalConversion()-
      <openjpa-2.0.0-SNAPSHOT-runknown fatal general error> org.apache.openjpa.persistence.PersistenceException: Error converting data type nvarchar to decimal.

      {prepstmnt 17141330 SELECT (t0.accountNum * ?) FROM CR_CUST t0 WHERE (t0.id = ?) [params=(BigDecimal) 10.3259699999999998709654391859658062458038330078125, (long) 301]} [code=8114, state=S0005]
      . . .
      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Error converting data type nvarchar to decimal. {prepstmnt 17141330 SELECT (t0.accountNum * ?) FROM CR_CUST t0 WHERE (t0.id = ?) [params=(BigDecimal) 10.3259699999999998709654391859658062458038330078125, (long) 301]}

      [code=8114, state=S0005]
      . . .
      NestedThrowables:
      com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)

        Activity

        Hide
        Donald Woods added a comment -

        Patch that disables the 3 failing tests for now on MSSQL, until the real problems are fixed.

        Show
        Donald Woods added a comment - Patch that disables the 3 failing tests for now on MSSQL, until the real problems are fixed.
        Hide
        Donald Woods added a comment -

        Applied OPENJPA-1615-trunk-skiptests.patch to trunk (as r931648) and 2.0.x (as r931654) for now, so we can get past the test failures until this issue is fixed.

        Show
        Donald Woods added a comment - Applied OPENJPA-1615 -trunk-skiptests.patch to trunk (as r931648) and 2.0.x (as r931654) for now, so we can get past the test failures until this issue is fixed.
        Hide
        Pinaki Poddar added a comment -

        TestTypesafeCriteria.testFunctionWithTwoArgument() is invalid for SQLServer because CriteriaQuery function() method should refer to a valid database function and MOD() is not a valid function for SQLServer.

        Show
        Pinaki Poddar added a comment - TestTypesafeCriteria.testFunctionWithTwoArgument() is invalid for SQLServer because CriteriaQuery function() method should refer to a valid database function and MOD() is not a valid function for SQLServer.
        Hide
        Donald Woods added a comment -

        So, does runtime code need to be changed to fix this? There is a solution, that we should be using "%" instead of "MOD" when using MSSQL....

        Show
        Donald Woods added a comment - So, does runtime code need to be changed to fix this? There is a solution, that we should be using "%" instead of "MOD" when using MSSQL....
        Hide
        Albert Lee added a comment -

        Move fix version to 2.3.0 in preparation for 2.2.0 release.

        Show
        Albert Lee added a comment - Move fix version to 2.3.0 in preparation for 2.2.0 release.

          People

          • Assignee:
            Unassigned
            Reporter:
            Donald Woods
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development