How to NOT select empty string

Question

Hi.

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 doesn't work and returns empty string instead of omitting it. However, I am looking for something like this to be generated:

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

However, I can't figure out why our jpa query doesn't generate something simliar like that. Any ideas?

Answer 1

I think that you are misusing IS [NOT] EMPTY that is used to check whether a collection association path resolves to an empty collection or has at least one value. From the JPA specification:

4.6.11 Empty Collection Comparison Expressions

The syntax for the use of the comparison operator IS EMPTY in an empty_collection_comparison_expression is as follows:

collection_valued_path_expression IS [NOT] EMPTY

This expression tests whether or not the collection designated by the collection-valued path expression is empty (i.e, has no elements).

Example:

SELECT o
FROM Order o
WHERE o.lineItems IS EMPTY

If the value of the collection-valued path expression in an empty collection comparison expression 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 <> ''