[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=40043#action_40043 ] 

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

The patch is related to org.hibernate.hql.ast.tree.BinaryLogicOperatorNode.

> 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