[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5946) Wrong generated SQL when used composite user type in HQL with not equal operator

[Emanuele Gesuato (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5946?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=40101#action_40101 ] 

Emanuele Gesuato commented on HHH-5946:
---------------------------------------

The testcase i provided uses the suite of hibernate and using the patch you can try running it before and after the application of the patch.
So, the patch could be "ready" to be merged in hibernate-core (org.hibernate.hql.ast.tree.BinaryLogicOperatorNode).

Thanks,
Emanuele Gesuato

> Wrong generated SQL when used composite user type in HQL with not equal operator
> --------------------------------------------------------------------------------
>
>                 Key: HHH-5946
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5946
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.6.1
>         Environment: hibernate version 3.6.1, databases db2 and oracle
>            Reporter: Emanuele Gesuato
>         Attachments: CompositeUserTypeTest.java, patch.txt
>
>
> If i use a not operator in a hql query using a composite user type (that uses at least two fields) the sql generated has an AND statement between the elements but i expect an OR instead.
> I have tried to reproduce a bug using an hibernate test case.
> In particular using the following class:
> {code:title=org.hibernate.test.cut.Transaction.java|borderStyle=solid}
> public class Transaction {
> 	private Long id;
> 	private String description;
> 	private MonetoryAmount value;
> 	
> 	public String getDescription() {
> 		return description;
> 	}
> 	
> 	public void setDescription(String description) {
> 		this.description = description;
> 	}
> 	
> 	public Long getId() {
> 		return id;
> 	}
> 	
> 	public void setId(Long id) {
> 		this.id = id;
> 	}
> 	
> 	public MonetoryAmount getValue() {
> 		return value;
> 	}
> 	
> 	public void setValue(MonetoryAmount value) {
> 		this.value = value;
> 	}
> }
> {code} 
> i've modified CompositeUserTypeTest.testCompositeUserType (present in hibernate tests) as following (added the last two lines inside if statement):
> {code:title=org.hibernate.test.cut.CompositeUserTypeTest.java|borderStyle=solid}
> 	public void testCompositeUserType() {
> 		Session s = openSession();
> 		org.hibernate.Transaction t = s.beginTransaction();
> 		
> 		Transaction tran = new Transaction();
> 		tran.setDescription("a small transaction");
> 		tran.setValue( new MonetoryAmount( new BigDecimal(1.5), Currency.getInstance("USD") ) );
> 		s.persist(tran);
> 		
> 		List result = s.createQuery("from Transaction tran where tran.value.amount > 1.0 and tran.value.currency = 'USD'").list();
> 		assertEquals( result.size(), 1 );
> 		tran.getValue().setCurrency( Currency.getInstance("AUD") );
> 		result = s.createQuery("from Transaction tran where tran.value.amount > 1.0 and tran.value.currency = 'AUD'").list();
> 		assertEquals( result.size(), 1 );
> 		
> 		if ( !(getDialect() instanceof HSQLDialect) ) {
> 		
> 			result = s.createQuery("from Transaction txn where txn.value = (1.5, 'AUD')").list();
> 			assertEquals( result.size(), 1 );
> 			result = s.createQuery("from Transaction where value = (1.5, 'AUD')").list();
> 			assertEquals( result.size(), 1 );
> 			result = s.createQuery("from Transaction where value != (1.4, 'AUD')").list();
> 			assertEquals( result.size(), 1 );
> 		}
> 		
> 		s.delete(tran);
> 		t.commit();
> 		s.close();
> 	}
> {code}
> the last assert should succeed because transaction (1.5, 'AUD') isn't equal to (1.4, 'AUD') and i should retrieve the (1.5, 'AUD') but the sql generated has an "AND" instead of an "OR":
> {code:title=Generated SQL|borderStyle=solid}
>     select
>         transactio0_.id as id0_,
>         transactio0_.description as descript2_0_,
>         transactio0_.amount as amount0_,
>         transactio0_.currency as currency0_ 
>     from
>         Trnsctn transactio0_ 
>     where
>         transactio0_.amount<>1.4 
>         and transactio0_.currency<>'AUD'
> {code}
> In attachment i provide a patch and the full modified test case.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira