OpenJPA generates strange SQL statements

Question

OpenJPA generates the following: 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 sql that JPA sends to the database

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

This expression will always be false. Thus, the user will not be selected.

Ok also

 1 = 1 AND 1 = 1 AND 1 = 1 

the expressions are really strange, but the database query optimizer should remove them, because they are always true.

• Is there anyone who has the same or similar weird SQL expressions created by OpenJPA?
• Can someone tell me (I hope someone from the OpenJPA developers) why OpenJPA generates such strange statements?

Research continues

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

Click to enlarge 1 : http: //i.stack. imgur.com/LBmzM.png

Answer 1

solvable

Finally, I found a place in the OpenJPA 2.2.1 source code that caused this wierd statement to be created.

The explanation is very interesting, even the cause of my problem is in my code, because I never expected OpenJPA to create such a statement.

This happens when you create an SQL expression as an empty "in-values" collection. For instance:

 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 created by OpenJPA. Well, if the usernames collection is empty, the SQL-in expression will ever evaluate to false. I think that OpenJPA developers wanted to make life easier for the database optimizer by generating an SQL expression that would ever be evaluated as false in this case. Therefore they posted

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

in the SQL statement.

So far I can understand what the purpose is, but why they cannot make life easier for our developers by generating SQLs that tell us why they always generated a false expression. For example, a statement can be much clearer if it gives people information about what is happening (hint). For instance:

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

It will also always be false, but the developer can understand what the problem is.