OpenJPA
  1. OpenJPA
  2. OPENJPA-1651

Unique colums automatically defined as non-nullable (part 2)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.2.0
    • Component/s: None
    • Labels:
      None

      Description

      Continuing the discussion from OPENJPA-1387. The original issue was closed and marked as shipped in OpenJPA 2.0.0 (one of the betas) - it should not appear in 2.0.1 as well - thus the need for a new issue.

        Issue Links

          Activity

          Hide
          Michael Dick added a comment -

          Re posting Mark's comments :
          A few tips on my sample project:
          The NOT NULL can be checked by simply executing
          $> mvn openjpa:sql
          which generates the database creation SQL script in
          ./target/database.sql

          to execute the project you need to create the database upfront in MySQL with
          create database TestDb;
          user 'root' with no password is assumed (persistence.xml)

          After
          $> mvn clean install
          you will see the myString column as empty string instead of NULL when doing a
          mysql> select * from NullFieldTable;
          ------------+

          PK myString

          ------------+

          1  

          ------------+
          1 row in set (0.00 sec)

          Show
          Michael Dick added a comment - Re posting Mark's comments : A few tips on my sample project: The NOT NULL can be checked by simply executing $> mvn openjpa:sql which generates the database creation SQL script in ./target/database.sql to execute the project you need to create the database upfront in MySQL with create database TestDb; user 'root' with no password is assumed (persistence.xml) After $> mvn clean install you will see the myString column as empty string instead of NULL when doing a mysql> select * from NullFieldTable; --- ---------+ PK myString --- ---------+ 1   --- ---------+ 1 row in set (0.00 sec)
          Hide
          Martin Dirichs added a comment -

          What exactly is still holding back the resolution of this issue? I noticed that the changes made for the precessor OPENJPA-1387 were rolled back. Now that a newer version of Derby is used in the build process, it should be easy to fix this. Or is it because of compatibility considerations? If so, a switch could be added to support the old behaviour (which, as I see it, is really a bug, not a feature).

          Show
          Martin Dirichs added a comment - What exactly is still holding back the resolution of this issue? I noticed that the changes made for the precessor OPENJPA-1387 were rolled back. Now that a newer version of Derby is used in the build process, it should be easy to fix this. Or is it because of compatibility considerations? If so, a switch could be added to support the old behaviour (which, as I see it, is really a bug, not a feature).
          Hide
          agurkas added a comment -

          Dear openjpa developers. This is a serious and long standing bug which is still not fixed in openjpa 2.0.1.
          The problem is some people using openjpa might not realize that openjpa is setting unique fields to not null without any warning and the consequences can be horrible.
          In some particular scenarious one might not find records in the database even if those do exist (imagine this happening in production database for more excitement)
          Let me show one simple example.

          @Entity
          @Table(uniqueConstraints = { @UniqueConstraint(columnNames =

          { "a", "b" }

          ) })
          public class TestEntity

          { @ManyToOne @JoinColumn(nullable = true, name = "a") private EntityA a; //optional @ManyToOne @JoinColumn(nullable = false, name = "b") private EntityB b; //required //getters setters ommited for readability }

          @Test
          public void test() {

          EntityB b = getEntityBService().find(1);
          TestEntity entity = new TestEntity();
          entity.setB(b);
          getTestEntityService().save(entity);

          //so record is saved in database with a = 0 not a = NULL value as expected and b = 1
          //now try to find this TestEntity by unique constraints meaning by EntityA == null and EntityB with id = 1

          EntityA a = null;
          EntityB b = getEntityBService().find(1);
          TestEntity entity = getTestEntityService().findByAB(a, b);
          Assert.assertNotNull(entity);

          //result is FAILURE entity not found

          this is underlying openjpa generated sql that shows why it fails

          SELECT (... ommited...) FROM TestEntity t0 LEFT OUTER JOIN EntityA t1 ON t0.a = t1.id LEFT OUTER JOIN EntityB t2 ON t0.b = t2.id WHERE (t0.a IS NULL AND t0.b = 1)

          it cannot be found in the TestEntity table as we have 0 not a NULL value for optional entity a

          to avoid this we do run our tool to fix database structure to avoid this and possibly other scenarios but a openjpa fix would be nice to have.

          regards

          Show
          agurkas added a comment - Dear openjpa developers. This is a serious and long standing bug which is still not fixed in openjpa 2.0.1. The problem is some people using openjpa might not realize that openjpa is setting unique fields to not null without any warning and the consequences can be horrible. In some particular scenarious one might not find records in the database even if those do exist (imagine this happening in production database for more excitement) Let me show one simple example. @Entity @Table(uniqueConstraints = { @UniqueConstraint(columnNames = { "a", "b" } ) }) public class TestEntity { @ManyToOne @JoinColumn(nullable = true, name = "a") private EntityA a; //optional @ManyToOne @JoinColumn(nullable = false, name = "b") private EntityB b; //required //getters setters ommited for readability } @Test public void test() { EntityB b = getEntityBService().find(1); TestEntity entity = new TestEntity(); entity.setB(b); getTestEntityService().save(entity); //so record is saved in database with a = 0 not a = NULL value as expected and b = 1 //now try to find this TestEntity by unique constraints meaning by EntityA == null and EntityB with id = 1 EntityA a = null; EntityB b = getEntityBService().find(1); TestEntity entity = getTestEntityService().findByAB(a, b); Assert.assertNotNull(entity); //result is FAILURE entity not found this is underlying openjpa generated sql that shows why it fails SELECT (... ommited...) FROM TestEntity t0 LEFT OUTER JOIN EntityA t1 ON t0.a = t1.id LEFT OUTER JOIN EntityB t2 ON t0.b = t2.id WHERE (t0.a IS NULL AND t0.b = 1) it cannot be found in the TestEntity table as we have 0 not a NULL value for optional entity a to avoid this we do run our tool to fix database structure to avoid this and possibly other scenarios but a openjpa fix would be nice to have. regards
          Hide
          Rade Martinović added a comment -

          Today I have encountered this issue in OpenJPA 2.0.1. The test scenario is exactly the same as agurkas described.

          Is there a wordaround for this?

          Show
          Rade Martinović added a comment - Today I have encountered this issue in OpenJPA 2.0.1. The test scenario is exactly the same as agurkas described. Is there a wordaround for this?
          Hide
          Rade Martinović added a comment -

          Today I have tried the test scenario that agurkas described with OpenJPA 2.1.0 and it seems that the issue has been remedied in the latest release.

          Maybe now this small patch should be backported to all previos versions?

          Show
          Rade Martinović added a comment - Today I have tried the test scenario that agurkas described with OpenJPA 2.1.0 and it seems that the issue has been remedied in the latest release. Maybe now this small patch should be backported to all previos versions?
          Hide
          Mark Struberg added a comment -

          Rade, could you please also run your tests against a 2.2.0-SNAPSHOT? txs!

          Show
          Mark Struberg added a comment - Rade, could you please also run your tests against a 2.2.0-SNAPSHOT? txs!
          Hide
          Mark Struberg added a comment -

          I verified the behaviour with MySQL today and it works now with my test project. I also tried to implement unit tests in openjpa-persistence-jdbc, but that doesn't work because derby (which is used in the unit tests) doesn't support NOT NULL values for UNIQUE indexes.

          See the following discussion on the derby user list:
          http://mail-archives.apache.org/mod_mbox/db-derby-dev/200511.mbox/%3C20051102114053.GB7614@atum01.norway.sun.com%3E

          Show
          Mark Struberg added a comment - I verified the behaviour with MySQL today and it works now with my test project. I also tried to implement unit tests in openjpa-persistence-jdbc, but that doesn't work because derby (which is used in the unit tests) doesn't support NOT NULL values for UNIQUE indexes. See the following discussion on the derby user list: http://mail-archives.apache.org/mod_mbox/db-derby-dev/200511.mbox/%3C20051102114053.GB7614@atum01.norway.sun.com%3E
          Hide
          Donald Woods added a comment -

          Mark, did you include the tests anyway and just set them to only run on Oracle for now?

          Show
          Donald Woods added a comment - Mark, did you include the tests anyway and just set them to only run on Oracle for now?
          Hide
          Mark Struberg added a comment -

          Nope sorry, I reverted it. But if you point me to how I can provide a test which only runs against MySQL, Oracle, etc then I can redo my work pretty easily (~2hrs of work). I basically did put the logic I had in the attached openjpa-test.zip and moved it to openjpa-persistence-jdbc unique tests.

          I also duplicated the Entity to make sure that both @Column(unique=true) and @UniqueColumns on the whole table are tested.

          Show
          Mark Struberg added a comment - Nope sorry, I reverted it. But if you point me to how I can provide a test which only runs against MySQL, Oracle, etc then I can redo my work pretty easily (~2hrs of work). I basically did put the logic I had in the attached openjpa-test.zip and moved it to openjpa-persistence-jdbc unique tests. I also duplicated the Entity to make sure that both @Column(unique=true) and @UniqueColumns on the whole table are tested.
          Hide
          Donald Woods added a comment -

          Different ways depending on which abstract testcase you use -

          Annotation - org.apache.openjpa.persistence.test.DatabasePlatform

          org.apache.openjpa.persistence.jdbc.meta.TestLocators.testSmallBLOBs()
          if (getCurrentPlatform() == AbstractTestCase.Platform.ORACLE) {

          org.apache.openjpa.persistence.jdbc.query.TestHintedQuery.testHintedQuery()
          if (dict instanceof MySQLDictionary)

          Believe there is one other annotation that we created for JUnits, but couldn't find it with a quick search.

          Show
          Donald Woods added a comment - Different ways depending on which abstract testcase you use - Annotation - org.apache.openjpa.persistence.test.DatabasePlatform org.apache.openjpa.persistence.jdbc.meta.TestLocators.testSmallBLOBs() if (getCurrentPlatform() == AbstractTestCase.Platform.ORACLE) { org.apache.openjpa.persistence.jdbc.query.TestHintedQuery.testHintedQuery() if (dict instanceof MySQLDictionary) Believe there is one other annotation that we created for JUnits, but couldn't find it with a quick search.
          Hide
          Milosz Tylenda added a comment -

          I guess Donald meant setUnsupportedDatabases and setSupportedDatabases methods in the SingleEMFTestCase.

          I am afraid the getCurrentPlatform() might not work.

          Show
          Milosz Tylenda added a comment - I guess Donald meant setUnsupportedDatabases and setSupportedDatabases methods in the SingleEMFTestCase. I am afraid the getCurrentPlatform() might not work.
          Hide
          Albert Lee added a comment -

          Close issue in preparation for 2.2.0 release.

          Show
          Albert Lee added a comment - Close issue in preparation for 2.2.0 release.

            People

            • Assignee:
              Mark Struberg
              Reporter:
              Mark Struberg
            • Votes:
              3 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development