0

When I run our Java program, I get a weird error that none of my team mates get, because a JPA-Query gives a different result on my machine, even if I and a fellow developer check out the exact same git commit, and the same DB content, build it and then debug it together. What this JPA-Query does is, it basically checks if a certain entity already exists in the database. It explicitly excludes entries that have the same Id, so the entity doesn't get compared to itself. It appears that this doesn't work on my machine. Here's a dummy version of the code:

import com.mysema.query.BooleanBuilder;
import com.mysema.query.jpa.impl.JPAQuery;

public class ExampleClass {

@Autowired
protected ClassThatExtendsJpaRepository customerDAO;

public void checkUniqueness(Customer inputCustomer) throws NotUniqueException {
    //Or-condition
    BooleanBuilder condition = new BooleanBuilder();
    Condition.or(QCustomer.customer.registerNr.eq(inputCustomer.getRegisterNr());
    Condition.or(QCustomer.customer.registerNr.eq(inputCustomer.getRegisterNr());

    JPAQuery query = new JPAQuery().from(Qcustomer.customer).where(condition);

    //This should exclude customers of the same id, but it has no effect
    query.where(Qcustomer.customer.id.ne(inputCustomer.getId());

    Customer existingCustomer = customerDAO.findOne(query);
    //Result: finds customer with same id! Should find nothing!

    if(existingCustomer != null) {
        throw new NotUniqueException();
    }
}

}

As you can see, aside from the check that the existing entity doesn't have the same ID as the one we're trying to compare it to, there are also other conditions connected by an OR. For simplicity, I've used the same condition for both in this example. The way the code is written now, it will find the entity with the same ID in the database and throw the NotUniqueException even though it shouldn't. But remove one of the Condition.or, and it works. This leads me to suspect that the query is putting the brackets wrong. According to the debugger, the query is

Select customer
from Customer customer
where (customer.registerNr = ?1 or customer.registerNr = ?1) and customer.id <> ?2

(And for the record: when I run this directly on my DB it works correctly, finding nothing.)
But I suspect it's actually running it as if the brackets were different:

Select customer
from Customer customer
where customer.registerNr = ?1 or (customer.registerNr = ?1 and customer.id <> ?2)

Either way, this still doesn't explain why this occurs, and why it's only on my machine.

We don't want to change the code, since this bug is only on my machine and doesn't stop me from working. So hopefully if I find the cause I can fix it some other way.

Versions:
Spring-data-jpa: 1.9.6.RELEASE
com.mysema.querydsl: 3.7.4
Hibernate: 4.2.21.Final
OJDBC: 19.17.0.0
Database: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Java: 8

MJL
  • 352
  • 3
  • 11
  • Are you connecting to the same environment as the other developers? Have you turned on logging for hibernate and checked generated query plus parameters? https://stackoverflow.com/questions/1710476/how-to-print-a-query-string-with-parameter-values-when-using-hibernate – XtremeBaumer Feb 09 '23 at 12:21
  • What do you mean by "environment"? – MJL Feb 09 '23 at 13:43
  • Usually you have different environments. At least 2. One for production and one for development. They usually don't use the same database and thus often hold different data – XtremeBaumer Feb 09 '23 at 13:46
  • We each have a local DB on our machines. – MJL Feb 09 '23 at 13:55
  • 1
    And you wonder that you get different results? – XtremeBaumer Feb 09 '23 at 13:57
  • You think a customer exists on one machine and not on the other? Because that's not the case. Keep in mind that in both cases you get an inputCustomer. On mine, this customer is compared to ITSELF, while on the other it is not. – MJL Feb 09 '23 at 13:58
  • Another idea is this: `a single entity matching the given Example or Optional.empty() if none was found.` - [docs](https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/repository/query/QueryByExampleExecutor.html#findOne(org.springframework.data.domain.Example)) and `Optional.empty() != null` is `true` – XtremeBaumer Feb 09 '23 at 14:18
  • `BooleanBuilder condition = new BooleanBuilder( Qcustomer.customer.id.ne(inputCustomer.getId())); condition.andAnyOf(QCustomer.customer.registerNr.eq(inputCustomer.getRegisterNr()), QCustomer.customer.registerNr.eq(inputCustomer.getRegisterNr())); JPAQuery query = new JPAQuery().from().where(condition); Customer existingCustomer = customerDAO.findOne(query);` you could also try this snippet. – XtremeBaumer Feb 09 '23 at 14:27
  • Like I said, we prefer not to change the code. But please put that suggestion into its own answer so others can profit from it. – MJL Feb 09 '23 at 14:36

1 Answers1

0

The solution was: I downgraded Oracle to version 18, like all the other developers. Yes, that's right, apparently the queries can give different results in Oracle 21.

MJL
  • 352
  • 3
  • 11