Hibernate returns different results for the same query than directly on SQL Server

Hibernate ORM
1

Hello,

I’m using spring with hibernate 5.0.2.Final to map ms sql 2017 database.
In a select statement with “not in” clause Hibernate returns all objects and “not in” condition has been ignored.

I’ve executed the same sql, generated by hibernate, direct in the database. I’ve got another result because the not in has been taken into consideration.
Why the two results are different? And how can I fix this issue?

Thanks

Hibernate’s query:

select TOP(?) this_.mitarbeiter_id as mitarbei1_149_0_, this_.mitarbeiter_anzeige_name as mitarbei2_149_0_, this_.mitarbeiter_email as mitarbei3_149_0_, this_.mitarbeiter_num as mitarbei4_149_0_, this_.mitarbeiter_vorname as mitarbei5_149_0_, this_.mitarbeiter_name as mitarbei6_149_0_, this_.org_einheit_id as org_einh7_149_0_, this_.org_einheit as org_einh8_149_0_, this_.mitarbeiter_position as mitarbei9_149_0_, this_.position_name_de as positio10_149_0_, this_.position_name_en as positio11_149_0_, this_.telefon_nummer as telefon12_149_0_ from dbo.vw_mitarbeiter this_ where (not this_.mitarbeiter_name in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) or this_.mitarbeiter_name is null) order by this_.mitarbeiter_name asc

2

I think it should be

this_.mitarbeiter_name not in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

How does the HQL query looks like?

3

Thank you very much for your quick response.

The problem was: in front-end each element, in the not in list, was surrounded with an escaped apostrophes (\‘value1\’, \‘value2\’, \‘value3\’, \‘value4\’, \‘value5\’).

After removing them the filter is applied and Hibernate returns the right result.