order by clause is not removed from derived count query when newline after the order by clause [DATAJPA-1500]

[spring-projects-issues]

Jean-Baptiste Nizet opened DATAJPA-1500 and commented

When the query of a Query annotation ends with an order by clause, it's normally removed from the derived count query used for pagination. But that doesn't happen if there is a newline character after the order by clause.

It might sounds weird to have newlines after the end of a query, but it's not at all when using Kotlin multi-line strings to define the query, such as in the following example, which uses the default formatting suggested by IntelliJ when using multiline strings.

@Query(value = """
  select foo from Foo foo
  where foo.bar = :bar
  order by foo.name
""")

The issue seems to be the pattern ORDER_BY, used at the end of the method createCountQueryFor of QueryUtils

---

Affects: 2.1.4 (Lovelace SR4)

Referenced from: pull request #380

Backported to: 2.1.9 (Lovelace SR9), 1.11.23 (Ingalls SR23)

[spring-projects-issues]

Oliver Drotbohm commented

Good catch, thanks for that. Should require a tweak to our regex, I guess

[spring-projects-issues]

Florian Lüdiger commented

I can't reproduce this issue. The following Unit test describes the expected behavior and it passes.

@Test // DATAJPA-1500
public void createCountQuerySupportsNewLineCharacter() {
   assertThat(createCountQueryFor("select foo from Foo foo\n" +
               "  where foo.bar = :bar\n" +
               "  order by foo.name\n"),
         is("select count(foo) from Foo foo\n" +
               "  where foo.bar = :bar\n"));
}

[spring-projects-issues]

Jean-Baptiste Nizet commented

I still reproduce the issues in my tests, but indeed not with just a newline at the end of the query. If the newline is followed by at least one space character, it fails