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

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

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 2.2.0
    • None
    • 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.

      Attachments

        1. openjpa-test.zip
          8 kB
          Michael Dick

        Issue Links

          Activity

            allee8285 Albert Lee added a comment -

            Close issue in preparation for 2.2.0 release.

            allee8285 Albert Lee added a comment - Close issue in preparation for 2.2.0 release.
            milosz Milosz Tylenda added a comment -

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

            I am afraid the getCurrentPlatform() might not work.

            milosz Milosz Tylenda added a comment - I guess Donald meant setUnsupportedDatabases and setSupportedDatabases methods in the SingleEMFTestCase. I am afraid the getCurrentPlatform() might not work.
            drwoods 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.

            drwoods 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.
            struberg 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.

            struberg 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.
            drwoods Donald Woods added a comment -

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

            drwoods Donald Woods added a comment - Mark, did you include the tests anyway and just set them to only run on Oracle for now?
            struberg 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

            struberg 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
            struberg Mark Struberg added a comment -

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

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

            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?

            rade.martinovic 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?

            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?

            rade.martinovic 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?
            agurkas 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

            agurkas 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
            dirichs 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).

            dirichs 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).
            mikedd 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)

            mikedd 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)

            People

              struberg Mark Struberg
              struberg Mark Struberg
              Votes:
              3 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: