[jira] Commented: (OPENJPA-1651) Unique colums automatically defined as non-nullable (part 2)

["agurkas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-1651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12908454#action_12908454 ] 

agurkas commented on OPENJPA-1651:
----------------------------------

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





> Unique colums automatically defined as non-nullable (part 2)
> ------------------------------------------------------------
>
>                 Key: OPENJPA-1651
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-1651
>             Project: OpenJPA
>          Issue Type: Bug
>            Reporter: Mark Struberg
>            Assignee: Pinaki Poddar
>         Attachments: openjpa-test.zip
>
>
> 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. 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.