Search code examples
javasqlopenjpa

OpenJPA generates weird SQL statements

OpenJPA generates the following where sql part

WHERE (t3.USERNAME = ? AND CAST(1 AS INTEGER) <> CAST(1 AS INTEGER) 
AND t5.USERNAME IS NOT NULL AND 1 = 1 AND 1 = 1 AND 1 = 1)

I just joined a few tables and finally did

Join<SomeEntity, User> userJoin = someJoin.join(SomeEntity_.user);
Path<String> usernamePath = userJoin.get(User_.username);
CriteriaBuilder cb = getCb();
Predicate usernamePredicate = cb.equal(usernamePath, username);

the weird part in the sql that JPA sends to the database is

CAST(1 AS INTEGER) <> CAST(1 AS INTEGER)

This expression will ever be false. So there will never be a user selected.

Ok, also the

1 = 1 AND 1 = 1 AND 1 = 1

expressions are really weird, but the database's query optimizer should remove them as they are ever true.

  1. Is anybody out there who has the same or similar weird sql statements generated by OpenJPA?
  2. Can anybody tell me (hopefully someone of the OpenJPA developers) why OpenJPA generates such weird statements?

Research goes on

Today I found the place in the OpenJPA 2.2.1 source code where the statement is generated. I took a screenshot of my debugging session and marked the interesting places.

Enlarge 1: https://i.sstatic.net/LBmzM.png

enter image description here

Solution

  • Solved

    I finally found the place in the OpenJPA 2.2.1 source code that causes this wierd statement to be generated.

    The explanation is very interresting even the cause of my problem is located in my code, because I have would never expected that OpenJPA creates such a statement.

    It happens when you create a SQL in expression with an empty "in-values" collection. For example:

    Collection<String> usernames = .... // dynamically created 
                                    // (maybe by another query before)
Path<String> username = userJoin.get(User_.username);
Predicate usernamePredicate = username.in(usernames);

    When the usernames collection is empty you get the wierd SQL generated by OpenJPA. Ok if the usernames collection is empty the SQL-in expression will ever evaluate to false. I think the OpenJPA developers wanted to make the life of the database optimizer easier by generating an SQL expression that will ever evaluate to false in this case. Therefore they placed the

     CAST(1 AS INTEGER) <> CAST(1 AS INTEGER)

    in the SQL statement.

    So far I can understand what the purpose is, but why couldn't they make the life of us developers easier too by generating an SQL that let us know why they generated the always false expression. The statement for example could be much more clear if it would give human beings information about what is going on (a hint). For example:

     WHERE 'user.username in(emptyCollection)' IS NOT NULL;

    This would also be always false, but a developer might understand what the problem is.