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

Clean up OpenJPA test case failures for PostgreSQL

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 2.1.0
    • 2.1.0
    • None
    • None

    Attachments

      1. OPENJPA-1726.patch
        2 kB
        Fay Wang
      2. OPENJPA-1726-2.patch
        3 kB
        Fay Wang
      3. TestMixedLockManagerFindPermutation_trace.zip
        4 kB
        Fay Wang

      Issue Links

        Activity

          faywang Fay Wang added a comment -

          TestIdClassCompanyModel.testBasicQueries fails with the following exception:

          Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: function pg_catalog.substring(character varying, bigint) does not exist
          Hint: No function matches the given name and argument types. You might need to add explicit type casts.
          Position: 658

          {prepstmnt 795758045 SELECT t0.id, t0.DTYPE, t0.firstName, t2.id, t2.city, t2.phoneNumber, t2.postalCode, t2.state, t2.streetAddress, t0.lastName, t1.id, t3.id, t3.city, t3.phoneNumber, t3.postalCode, t3.state, t3.streetAddress, t1.name, t0.hireDate, t4.id, t4.DTYPE, t4.firstName, t4.HOMEADDRESS_ID, t4.lastName, t4.COMPANY_ID, t4.hireDate, t4.title, t4.salary, t0.title, t0.salary, t0.wage, t0.weeklyHours FROM IDC_Person t0 INNER JOIN IDC_Company t1 ON t0.COMPANY_ID = t1.id LEFT OUTER JOIN IDC_Address t2 ON t0.HOMEADDRESS_ID = t2.id LEFT OUTER JOIN IDC_Person t4 ON t0.MANAGER_ID = t4.id LEFT OUTER JOIN IDC_Address t3 ON t1.ADDRESS_ID = t3.id WHERE (((POSITION(t1.name IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?) AND t0.DTYPE IN (?, ?, ?) AND (t4.DTYPE IS NULL OR t4.DTYPE IN (?)) [params=?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?]}

          [code=0, state=42883]
          FailedObject: select x from IDC_Employee x where locate(x.company.name, 'x', 1) > 0 [java.lang.String]

          In the push-down sql:
          (((POSITION(t0.firstName IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?)

          OpenJPA set the parameters as follows:

          pstmt.setString(1, "x");
          pstmt.setLong(2, 1);
          pstmt.setInt(3, 1);
          pstmt.setLong(4, 1);
          pstmt.setInt(5, 1);
          pstmt.setInt(6, 1);
          pstmt.setLong(7, 0);

          The second parameter is set to type Long, which causes the error that the function pg_catalog.substring(character varying, bigint) does not exist. It turns out the for Postgres, the start index in the locate function must be int:

          http://www.postgresql.org/docs/8.1/static/functions-string.html
          substring(string [from int] [for int])

          The fix is to modify JPQLExpressionBuilder to create a Lit of Integer value for the locateFromIndex.

          Although this fix affects all database, it is reasonable to assume that the start index in the locate function is in the integer range.

          faywang Fay Wang added a comment - TestIdClassCompanyModel.testBasicQueries fails with the following exception: Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: function pg_catalog.substring(character varying, bigint) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 658 {prepstmnt 795758045 SELECT t0.id, t0.DTYPE, t0.firstName, t2.id, t2.city, t2.phoneNumber, t2.postalCode, t2.state, t2.streetAddress, t0.lastName, t1.id, t3.id, t3.city, t3.phoneNumber, t3.postalCode, t3.state, t3.streetAddress, t1.name, t0.hireDate, t4.id, t4.DTYPE, t4.firstName, t4.HOMEADDRESS_ID, t4.lastName, t4.COMPANY_ID, t4.hireDate, t4.title, t4.salary, t0.title, t0.salary, t0.wage, t0.weeklyHours FROM IDC_Person t0 INNER JOIN IDC_Company t1 ON t0.COMPANY_ID = t1.id LEFT OUTER JOIN IDC_Address t2 ON t0.HOMEADDRESS_ID = t2.id LEFT OUTER JOIN IDC_Person t4 ON t0.MANAGER_ID = t4.id LEFT OUTER JOIN IDC_Address t3 ON t1.ADDRESS_ID = t3.id WHERE (((POSITION(t1.name IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?) AND t0.DTYPE IN (?, ?, ?) AND (t4.DTYPE IS NULL OR t4.DTYPE IN (?)) [params=?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?]} [code=0, state=42883] FailedObject: select x from IDC_Employee x where locate(x.company.name, 'x', 1) > 0 [java.lang.String] In the push-down sql: (((POSITION(t0.firstName IN SUBSTRING(?, ((? - ?) + 1))) - 1 + (? - ?)) + ?) > ?) OpenJPA set the parameters as follows: pstmt.setString(1, "x"); pstmt.setLong(2, 1); pstmt.setInt(3, 1); pstmt.setLong(4, 1); pstmt.setInt(5, 1); pstmt.setInt(6, 1); pstmt.setLong(7, 0); The second parameter is set to type Long, which causes the error that the function pg_catalog.substring(character varying, bigint) does not exist. It turns out the for Postgres, the start index in the locate function must be int: http://www.postgresql.org/docs/8.1/static/functions-string.html substring(string [from int] [for int] ) The fix is to modify JPQLExpressionBuilder to create a Lit of Integer value for the locateFromIndex. Although this fix affects all database, it is reasonable to assume that the start index in the locate function is in the integer range.
          faywang Fay Wang added a comment -

          TestTypeSafeCondExpression.testTrimFunc1 and testTrimFunc2 fail in Postgres when the criteria API is used:

          Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: function pg_catalog.btrim(character varying, integer) does not exist
          Hint: No function matches the given name and argument types. You might need to add explicit type casts.
          Position: 8

          {prepstmnt 598291031 SELECT TRIM(BOTH ? FROM t0.compName) FROM CompUser t0 WHERE (t0.name = ?) [params=?, ?]}

          [code=0, state=42883]

          In these test cases, OpenJPA criteria API sets the trim character as the Character type, resulting in the call of PreparedStatement.setInt for the trim character (via DBDictionary.setChar). Postgres therefore throws error that function pg_catalog.btrim(character varying, integer) does not exist. The patch OPENJPA-1726-2.patch modifies the trim character to String type to fix this problem.

          faywang Fay Wang added a comment - TestTypeSafeCondExpression.testTrimFunc1 and testTrimFunc2 fail in Postgres when the criteria API is used: Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: function pg_catalog.btrim(character varying, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8 {prepstmnt 598291031 SELECT TRIM(BOTH ? FROM t0.compName) FROM CompUser t0 WHERE (t0.name = ?) [params=?, ?]} [code=0, state=42883] In these test cases, OpenJPA criteria API sets the trim character as the Character type, resulting in the call of PreparedStatement.setInt for the trim character (via DBDictionary.setChar). Postgres therefore throws error that function pg_catalog.btrim(character varying, integer) does not exist. The patch OPENJPA-1726 -2.patch modifies the trim character to String type to fix this problem.
          faywang Fay Wang added a comment -

          The following four OpenJPA test cases fail with the same error:

          TestGeneratedValues.testDefaultValues
          TestGeneratedValues.testCustomSequenceGeneratorWithIndirection
          TestGeneratedValues.testUUIDGenerators
          TestNonstandardMappingAnnotations.testInsertAndRetrieve

          Caused by: <openjpa-2.1.0-SNAPSHOT-rexported fatal general error> org.apache.openjpa.persistence.PersistenceException: ERROR: invalid byte sequence for encoding "UTF8": 0x00. Hint: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

          This error is caused by the two auto-generated uuid values (uuidstring and uuidT4string) In the entity, org.apache.openjpa.persistence.generationtype.GeneratedValues:

          @GeneratedValue(generator="uuid-string")
          private String uuidstring;

          @GeneratedValue(generator="uuid-type4-string")
          private String uuidT4string;

          These two fields are populated in a way that org.apache.openjpa.lib.util.UUIDGenerator first randomly generates byte[] and then converts it to a String using charset "ISO-8859-1". The resulting string may contain 0x00, causing invalid byte sequence for encoding "UTF8": 0x00. The failures are not deterministic as the UUID values are randomly generated. One way to fix this problem is to exclude Postgres from running these four test cases. Any comments are welcome.

          faywang Fay Wang added a comment - The following four OpenJPA test cases fail with the same error: TestGeneratedValues.testDefaultValues TestGeneratedValues.testCustomSequenceGeneratorWithIndirection TestGeneratedValues.testUUIDGenerators TestNonstandardMappingAnnotations.testInsertAndRetrieve Caused by: <openjpa-2.1.0-SNAPSHOT-rexported fatal general error> org.apache.openjpa.persistence.PersistenceException: ERROR: invalid byte sequence for encoding "UTF8": 0x00. Hint: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". This error is caused by the two auto-generated uuid values (uuidstring and uuidT4string) In the entity, org.apache.openjpa.persistence.generationtype.GeneratedValues: @GeneratedValue(generator="uuid-string") private String uuidstring; @GeneratedValue(generator="uuid-type4-string") private String uuidT4string; These two fields are populated in a way that org.apache.openjpa.lib.util.UUIDGenerator first randomly generates byte[] and then converts it to a String using charset "ISO-8859-1". The resulting string may contain 0x00, causing invalid byte sequence for encoding "UTF8": 0x00. The failures are not deterministic as the UUID values are randomly generated. One way to fix this problem is to exclude Postgres from running these four test cases. Any comments are welcome.
          techhusky Jeremy Bauer added a comment -

          I ran into some issues with uuid-string and uuid-type4-string a while back. This problem isn't specific to PostgreSQL, but looks to be even more prevalent on that platform since it stores data in UTF-8. UTF-8 is particularly picky about character values. To be honest, I wouldn't ever recommend the use of uuid-string or uuid-type4-string since they generate integral byte values and plug them in as character data without taking into account the character encoding of either the client or database column. Depending on the conversion, the id may get converted to some other value or fail to convert (like we are witnessing on PostgreSQL). A better choice would have been to convert the values to ASCII base-10 numeric characters. This is not something we can change now though, since there may be existing applications happily running with the current generation strategy. The use of uuid-hex or uuid-type4-hex is a much better choice since the key value includes only invariant characters.

          Since this could fail intermittently on any platform a better choice might be to either discontinue testing uuid-string or uuid-type4-string (while continuing to test uuid-hex and uuid-type4-hex) or test the -string variants separately, excluding PostgreSQL. I'm OK with either, or simply excluding the test from running on PostgreSQL as you suggested.

          techhusky Jeremy Bauer added a comment - I ran into some issues with uuid-string and uuid-type4-string a while back. This problem isn't specific to PostgreSQL, but looks to be even more prevalent on that platform since it stores data in UTF-8. UTF-8 is particularly picky about character values. To be honest, I wouldn't ever recommend the use of uuid-string or uuid-type4-string since they generate integral byte values and plug them in as character data without taking into account the character encoding of either the client or database column. Depending on the conversion, the id may get converted to some other value or fail to convert (like we are witnessing on PostgreSQL). A better choice would have been to convert the values to ASCII base-10 numeric characters. This is not something we can change now though, since there may be existing applications happily running with the current generation strategy. The use of uuid-hex or uuid-type4-hex is a much better choice since the key value includes only invariant characters. Since this could fail intermittently on any platform a better choice might be to either discontinue testing uuid-string or uuid-type4-string (while continuing to test uuid-hex and uuid-type4-hex) or test the -string variants separately, excluding PostgreSQL. I'm OK with either, or simply excluding the test from running on PostgreSQL as you suggested.
          faywang Fay Wang added a comment -

          The failing test cases running on Postgres are now in TestMixedLockManagerFindPermutation. This test case spawns three threads and the pass/no pass of the test cases depends on the timing.

          One example is TestMixedLockManagerFindPermutation.testFindReadPessimisticRead when the second child thread is doing rollback:
          commonFindTest(
          "testFind(Read,Commit/PessimisticRead,Rollback)",
          LockModeType.READ, Act.CommitTx, 1, null,
          LockModeType.PESSIMISTIC_READ, Act.RollbackTx, 1, null);

          In the failing trace file, you can easily see that the find by the second child [Thread-8] is before the jdbc commit of the update statement by the first child [Thread-7], even though the commit action is issued by Thread-7 before the find by Thread-8.

          faywang Fay Wang added a comment - The failing test cases running on Postgres are now in TestMixedLockManagerFindPermutation. This test case spawns three threads and the pass/no pass of the test cases depends on the timing. One example is TestMixedLockManagerFindPermutation.testFindReadPessimisticRead when the second child thread is doing rollback: commonFindTest( "testFind(Read,Commit/PessimisticRead,Rollback)", LockModeType.READ, Act.CommitTx, 1, null, LockModeType.PESSIMISTIC_READ, Act.RollbackTx, 1, null); In the failing trace file, you can easily see that the find by the second child [Thread-8] is before the jdbc commit of the update statement by the first child [Thread-7] , even though the commit action is issued by Thread-7 before the find by Thread-8.
          faywang Fay Wang added a comment -

          The zip file contains trace log for success run and failing run to illustrate the timing issue.

          faywang Fay Wang added a comment - The zip file contains trace log for success run and failing run to illustrate the timing issue.

          People

            faywang Fay Wang
            faywang Fay Wang
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: