OpenJPA
  1. OpenJPA
  2. OPENJPA-2390

HSQLDB SELECT NEXT VALUE FOR Sequence will skip 1 sequence value if allocation size is 1

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.1
    • Fix Version/s: 2.3.0
    • Component/s: jdbc
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      The HSQLDictionary sets the DBDictionary.nextSequenceQuery to "SELECT NEXT VALUE FOR

      {0} FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES" when the HSQLDictionary is initialized.

      The NativeJDBCSeq uses
      _select = MessageFormat.format(format, new Object[]{name, String.valueOf(_allocate * _increment)});
      to build the select next value sql. The format is pased 2 arguments "name" and "_allocate * _increment"
      but the second is not honored by the nextSequenceQuery.

      So when the allocation size is set to 1 the hsqldb nextSequenceQuery statement will return 2 next values
      anyway. That is why always one next value will be skipped... 1, 3, 5, 7, and so on.

      The nextSequenceQuery should be set with an limit. E.g.
      "SELECT NEXT VALUE FOR {0}

      FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES LIMIT

      {1}

      "

      I will attatch an unit test that uses in memory hsqldb to show the problem soon.

      1. HSQLSequenceTest.java
        2 kB
        René Link
      2. OPENJPA-2390.patch
        12 kB
        Di Wu Lau

        Activity

        Hide
        ASF subversion and git services added a comment -

        Commit 1496124 from allee8285@apache.org
        [ https://svn.apache.org/r1496124 ]

        OPENJPA-2390 - commit patch contributed by Di Lau.

        Show
        ASF subversion and git services added a comment - Commit 1496124 from allee8285@apache.org [ https://svn.apache.org/r1496124 ] OPENJPA-2390 - commit patch contributed by Di Lau.
        Hide
        Di Wu Lau added a comment -

        Problem is fixed by adding LIMIT 1 to the nextSequenceQuery String in HSQLDictionary. This force the sequence to hand back only 1 value at a time.

        Any version that is lower than 2.1.0 does not need to add LIMIT 1. The older versions do not have the skipping problem.

        To make the code more compatible, I added some conditions in HSQLDictionary to check the HSQL versions so that only version 2.1.0 and beyond will add LIMIT 1 to the nextSequenceQuery.

        Uploadded is my patch file. In this file, I added two test cases that use allocationSize=1 and allocationSize=2 to demonstrate the problem.

        Thanks.

        Show
        Di Wu Lau added a comment - Problem is fixed by adding LIMIT 1 to the nextSequenceQuery String in HSQLDictionary. This force the sequence to hand back only 1 value at a time. Any version that is lower than 2.1.0 does not need to add LIMIT 1. The older versions do not have the skipping problem. To make the code more compatible, I added some conditions in HSQLDictionary to check the HSQL versions so that only version 2.1.0 and beyond will add LIMIT 1 to the nextSequenceQuery. Uploadded is my patch file. In this file, I added two test cases that use allocationSize=1 and allocationSize=2 to demonstrate the problem. Thanks.
        Hide
        René Link added a comment -

        The attatched tests shows the problem I described in the Issue.

        Show
        René Link added a comment - The attatched tests shows the problem I described in the Issue.

          People

          • Assignee:
            Albert Lee
            Reporter:
            René Link
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development