How to NOT select an empty string

Question

We have the following JPQL:

Select distinct sys.ipAddress from SystemLog sys where sys.ipAddress is not null and sys.ipAddress is not empty 

And this generates the following mysql statement.

 select distinct systemlog0_.ipAddress as col_0_0_ from SystemLog systemlog0_ where ( systemlog0_.ipAddress is not null ) and ( exists ( select systemlog0_.id from SystemLog systemlog0_ ) ) 

This obviously does not work and returns an empty string instead of deleting it. However, I am looking for something like this:

 select distinct ipAddress from SystemLog where ipAddress is not null and ipAddress <> ''; 

However, I cannot understand why our jpa request does not generate anything like this. Any ideas?

Answer 1

I think that you are abusing IS [NOT] EMPTY , which is used to check if the collection association path is allowed for an empty collection or has at least one value. From the JPA specification:

4.6.11. Fuzzy Collection Comparison Expressions

The syntax for using the IS EMPTY comparison operator in empty_collection_comparison_expression is as follows:

 collection_valued_path_expression IS [NOT] EMPTY 

This expression verifies the collection, the assigned expression for the collection is empty (i.e. has no elements).

Example:

 SELECT o FROM Order o WHERE o.lineItems IS EMPTY 

If the value of the collection expression path in an empty collection expression comparison is unknown, the value of the empty comparison expression is unknown.

In my opinion, you should just use the comparison operator <> :

 select distinct sys.ipAddress from SystemLog sys where sys.ipAddress is not null and sys.ipAddress <> ''