Unit test result differs in 1.4.188 Vs 1.4.198

[Pravin Patil]

Hi,

I'm getting the different result when upgraded to 1.4.198 version. While till 1.4.188 the results are expected works correctly.

The below test fetch data for Id:1 having list of two members, the sequence of member Id's in list is 3, 2. But which is 2, 3 in 1.4.198 that's why the tests are failing. There are 200 similar test cases which are failing with this reason.

List<Addr> memberAddr = _addrRepository.findmemberAddr(1, new SearchParams(), addrSearchParams);

    Assert.assertThat(memberAddr.isEmpty(), is(not(true)));

    Assert.assertThat(memberAddr.size(), is(2));

    Assert.assertThat(memberAddr.get(0).getMemberId(), is(3L));

    Assert.assertThat(memberAddr.get(1).getMemberId(), is(2L));

Please help.

[Noel Grandin]

Unless you are using an ORDER BY clause, the order of results is undefined in SQL, so it can change at any time

[Pravin Patil]

Hi Noel,

Thanks for the reply. Why I'm curious, because it wasn't affected till 1.4.188 version without using order by clause. So is there any special improvements after 188 to act like this. 

[Evgenij Ryazanov]

1.4.197 and 1.4.198(199) are huge releases with a lot of changes.

BTW, don't use 1.4.198, this release has many regressions, it was replaced with 1.4.199.

Some queries may return result in different orders on Java 7 and on later versions. Some queries may change the order of results depending on different unpredictable factors, such as amount of available memory.

You need to change your tests to accept results in any order or change your queries by including the ORDER BY clause with all necessary columns.

[Pravin Patil]

Thank you Evgenij,

Will test with 199. Lets see whats the result then. Unfortunately I cant apply the order by clause, it will affect a lot on application. Will be happy to see any other alternatives.

[Evgenij Ryazanov]

Most likely the results will be the same as in 1.4.199. But 1.4.199 is more reliable in other areas.

You can also sort the results by your own in your test cases.

[Pravin Patil]

Yes, the results are same as like 198. I think finally I've to change the 200+ test cases due to this problem. 

Thanks for the help Evgenij. I'm still searching...

[Evgenij Ryazanov]

I assume you understand that order of results in queries without ORDER BY can be changed again at any time. It's better to change tests in order-independent way.

[Pravin Patil]

Interesting that it even works in 197.

Env: Oracle JDK 8 and AmazonCorreto JDK 11.0.3, W10, Intellij

FYI I used orderby but didn't worked.

 return createQuery((cb, root, query) -> {

      List<Predicate> predicates = createCriteria(id, addrSearchParams, cb, root);

      Query q = entityManager.createQuery(query.where(predicates.toArray(new Predicate[0])).orderBy(searchParams(searchParams, cb, root)));

      rangeQuery(q, searchParams);

      return q.getResultList();

    });

searchParams param is blank object as we passed it from above mentioned test method.

[Evgenij Ryazanov]

If you have a problem with some implementation of JPA it's better to post your question on StackOverflow with appropriate tags or in some other place where your question will be visible by many people familiar with JPA, Hibernate and other related stuff.

I have no idea how to while a correct query with API that you use.

[Pravin Patil]

Sure Evgenij. I'll be posting, thanks for the help. I just posted here because want to know is there any specific H2 library change which is affecting my impl. As I only touched the H2 library in my impl

[Pravin Patil]

Hi,

Is this commit affecting my sorting order. Please help me.

https://github.com/h2database/h2database/pull/1707/commits/fa946672364a7b36ecb6c0c678728aab2d0694f5#diff-58e7066f27f34ec51d8ae7f079cca34f

Thanks.

[Noel Grandin]

As we said earlier, SQL does not guarantee the sort order of results.

You screwed up, and wrote bad tests, and now you need to fix them.

 

[Pravin Patil]

that is the final work. I'm on it.

BTW thanks.