2

I read through this post...

Is there a SQL mode for MySQL allowing "WHERE x = NULL" (meaning "WHERE x IS NULL")?

In hibernate, NamedQuery is constantly used as the goto in our company and criteria api is a big one-off. They are just easy as far as

select c from Customer c where c.firstName=:firstName and c.middleName=:middleName and c.lastName=:lastName

Of course, then we ran into middleName being null and things broke. Then we ran into firstName being null which then broke. All over the schema, we are going to have situations like these constantly soooo, we are looking for a way to interpret

c.middleName = null to c.middleName is null when it is null.

Is there a hibernate setting for this so it just does it correctly in mysql and postgres(the best ideal situation) OR that paramater in the above post for postgres. Where is that and how can I set that up? or is it per query(ugh)?

We are in the process of switching from MySQL to postgres since MySQL's method of dealing with uniqueConstraints on null columns is very non-ideal for us compared to postgres (in that MySQL allows John null Smith twice where postgres does not).

thanks, Dean

Dean Hiller
  • 19,235
  • 25
  • 129
  • 212
  • According to the documenation = null is not possible. Probably because this is not SQL standard. https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#hql-null-predicate – Simon Martinelli Jul 14 '20 at 13:05

1 Answers1

0

In PostgreSQL the IS NOT DISTINCT FROM operator is the null-safe equal predicate. However, similarly to mysql's <=> operator, this predicate is an extension to the SQL standard.

I'm not aware of hibernate having any null-safe equal operators, so I guess the only way to handle such queries is through the native query feature. Obviously, such queries are specific to the underlying database used, so not really ideal.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • I think `IS [NOT] DISTINCT FROM` is standard. PostgreSQL also has the `transform_null_equals` parameter to make that transformation for benighted applications. – Laurenz Albe Jul 14 '20 at 13:38
  • yeah, we are thinking of using transform_null_equals as TOOO many of our developers keep writing the queries wrong AND modifying our querys to accout for nulls on 5 columns gets nasty nasty REALLY fast. I wish the spec would allow for that as it seems like it's causing really long SQL statements in tons of locations for us. – Dean Hiller Jul 14 '20 at 15:03