OpenJPA
  1. OpenJPA
  2. OPENJPA-1066

Generated ID starting with 0 can cause unexpected results

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.2.0
    • Fix Version/s: None
    • Component/s: kernel
    • Labels:
      None
    • Environment:
      any DB that allows a generated id to start with 0. And an entity that maps that generated id to an int instead of an Integer

      Description

      Several DB's allow a generated id column to start with 0.

      For instance, DB2 allows "GENERATED ALWAYS AS IDENTITY (START WITH 0)...."

      When this is used, the very first object will have an id of 0. When entities are first created, the user usually won't fill in the corresponding id field (since it will be generated by the DB when the entity is put in the DB), so it defaults to 0. The entity manager then uses this 0 and sees that an entity already exists and will update the old entity in stead of creating a new one.

      This is not a large issue, because a user could simply specify "START WITH 1" as a very simple workaround or they could use an Integer instead of an int, but it would be nice to fix so that new users don't hit this problem.

        Issue Links

          Activity

          Hide
          Donald Woods added a comment -

          Sounds like good candidate for the User Guide as a note/best practice for @Id usage....

          Show
          Donald Woods added a comment - Sounds like good candidate for the User Guide as a note/best practice for @Id usage....
          Hide
          Michael Dick added a comment -

          Targeting for 2.0.1.

          Show
          Michael Dick added a comment - Targeting for 2.0.1.
          Hide
          Rick Curtis added a comment -

          Fixed one problem, but others do exist.

          One problem is as follows:

          • Joe user has an an Entity with a generated id that starts are zero and no version field
          • He has an existing Entity with id=0.
          • He creates a new Entity, but calls em.merge() rather than persist. <- problem. This will result in the original data being wiped out by the new Entity.
          Show
          Rick Curtis added a comment - Fixed one problem, but others do exist. One problem is as follows: Joe user has an an Entity with a generated id that starts are zero and no version field He has an existing Entity with id=0. He creates a new Entity, but calls em.merge() rather than persist. <- problem. This will result in the original data being wiped out by the new Entity.
          Hide
          Rick Curtis added a comment -

          Fixed one problem, but others do exist.

          One problem is as follows:

          • Joe user has an an Entity with a generated id that starts are zero and no version field
          • He has an existing Entity with id=0.
          • He creates a new Entity, but calls em.merge() rather than persist. <- problem. This will result in the original data being wiped out by the new Entity.
          Show
          Rick Curtis added a comment - Fixed one problem, but others do exist. One problem is as follows: Joe user has an an Entity with a generated id that starts are zero and no version field He has an existing Entity with id=0. He creates a new Entity, but calls em.merge() rather than persist. <- problem. This will result in the original data being wiped out by the new Entity.
          Hide
          Vermeulen added a comment - - edited

          I was making a test case for another problem (which may or may not be related) and ran into this with an in-memory hsqldb and generated schema.

          Even if I use em.persist and even if I use a non-primitive id (I used Long), the entity with id 0 is overwritten.
          For most use cases OpenJPA doesn't force me to manually write SQL or manually create tables. Yet for this simple use case I have to learn hsqldb's way of starting with 1 (which I only use for small tests, we normally use mssql server).

          Don't know if this helps, but I found a similar bug on EclipseLink (https://bugs.eclipse.org/bugs/show_bug.cgi?id=297198) they solved it by using something like "GENERATED BY DEFAULT AS IDENTITY (START WITH 1...".

          Show
          Vermeulen added a comment - - edited I was making a test case for another problem (which may or may not be related) and ran into this with an in-memory hsqldb and generated schema. Even if I use em.persist and even if I use a non-primitive id (I used Long), the entity with id 0 is overwritten. For most use cases OpenJPA doesn't force me to manually write SQL or manually create tables. Yet for this simple use case I have to learn hsqldb's way of starting with 1 (which I only use for small tests, we normally use mssql server). Don't know if this helps, but I found a similar bug on EclipseLink ( https://bugs.eclipse.org/bugs/show_bug.cgi?id=297198 ) they solved it by using something like "GENERATED BY DEFAULT AS IDENTITY (START WITH 1...".
          Hide
          Christoph Ewerlin added a comment -

          GENERATED BY DEFAULT AS IDENTITY (START WITH 1...) works for us.
          Please update the respective data base dictionaries so that during schema auto-creation tables are generated with IDs not starting at 0.
          We encounter the issue with org.apache.openjpa.jdbc.sql.HSQLDictionary

          Show
          Christoph Ewerlin added a comment - GENERATED BY DEFAULT AS IDENTITY (START WITH 1...) works for us. Please update the respective data base dictionaries so that during schema auto-creation tables are generated with IDs not starting at 0. We encounter the issue with org.apache.openjpa.jdbc.sql.HSQLDictionary

            People

            • Assignee:
              Unassigned
              Reporter:
              B.J. Reed
            • Votes:
              2 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development