[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6643) Criteria doesn't support a chaining of 2 not restrictions (sql = not not criterion)

[Lorber Sebastien (JIRA)]

Criteria doesn't support a chaining of 2 not restrictions (sql = not not criterion)
-----------------------------------------------------------------------------------

                 Key: HHH-6643
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6643
             Project: Hibernate Core
          Issue Type: Bug
          Components: query-criteria
    Affects Versions: 3.2.7
         Environment: Oracle 10g
Hibernate 3.2.7
            Reporter: Lorber Sebastien
            Priority: Trivial




When we do:
"Restrictions.not( Restrictions.not( Restrictions.eq("field",3) ) );"

Hibernate generates 
"where not not field=3"
Which generates an SQL exception

The correct SQL syntax is:
"where not (not field=3)"
(At least our Oracle)


I think Hibernate should handle such a case.

/!\
See org.hibernate.criterion.NotExpression#toSqlString
It seems that for MySQLDialect the parenthesis are added, but not for other dialects...
I don't know the sql specificities of all dialects but perhaps the parenthesis should be added also for oracle dialect?

Note that we use a custom dialect, extending Oracle10gDialect





For those interested: i'm doing two not on a criterion instead of using the criterion directly.
In real world, my code sample would be a little more complex: sometimes we compose with a lot of criterions, in many different methods.
So i do not exactly use Restrictions.not( Restrictions.not( criterion ) ); 
Trust me, bad luck, it happened that the dynamic composition of my restrictions, in one specific case, resulted on a double not restriction applied to a criterion.

Note that an easy workaround of this is to "add the parenthesis manually".
A conjunction is a bit like an "empty restriction" thus what i've done is:

Restrictions.not( 
	Restrictions.and(
		Restrictions.not( 
			Restrictions.eq("field",3) 
		),
		Restrictions.conjunction()
	)
);

Adding the "not criterion" into conjunction containing only the criterion.
This way, the sql of my inner not restriction become something like:

( not field = 3 AND 1=1 ) 
-> We can apply a not on that, while we weren't able on "not field = 3"







--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira