We're updating the issue view to help you get more done. 

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

Description

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"

Environment

Oracle 10g Hibernate 3.2.7

Status

Assignee

Brett Meyer

Reporter

SébastienL

Suitable for new contributors

Yes, likely

Time Tracking

8h

Components

Fix versions

Affects versions

3.2.7

Priority

Trivial