detectAlias() in QueryUtils will pick up other tokens that have "from" in them and pick the next alias from there.

[hedshogg]

In Spring Data JPA 2.6.4 something changed in the alias detection that causes it to sometimes incorrectly determine the alias in a query that uses a cast().

For example:

from User u where (cast(:effectiveFrom as date) is null) OR :effectiveFrom >= u.createdAt

incorrectly identifies the alias to be date when it should be u.

Oddly it doesn't pick up on some other similar uses of cast which it has no trouble with, e.g.

@Test
void detectAliasWithCastCorrectly() {

	assertThat(detectAlias("from User u where (cast(:effective as date) is null) OR :effective >= u.createdAt")).isEqualTo("u");
	assertThat(detectAlias("from User u where (cast(:effectiveDate as date) is null) OR :effectiveDate >= u.createdAt")).isEqualTo("u");
	assertThat(detectAlias("from User u where (cast(:effectiveFrom as date) is null) OR :effectiveFrom >= u.createdAt")).isEqualTo("u");

}

where only the last assertion fails. This is similar in some ways to 2232 although in my case it's with a JPA query rather than a native query.

[markkrause-wk]

It appears the change to the org.springframework.data.jpa.repository.query.QueryUtils#detectAlias method is the cause.
When previously it would return the first match, it now returns the last match.

Previous:

	public static String detectAlias(String query) {
		Matcher matcher = ALIAS_MATCH.matcher(query);
		return matcher.find() ? matcher.group(2) : null;
	}

Current:

	public static String detectAlias(String query) {
		String alias = null;
		Matcher matcher = ALIAS_MATCH.matcher(query);
		while (matcher.find()) {
			alias = matcher.group(2);
		}
		return alias;
	}

[gregturn]

Actually, the problem isn't about the CAST at all. It's that your third test case has "from" buried in the middle of :effectiveFrom. Change the "From" part to ANYTHING else, and the test case will pass.

That's because QueryUtils considers that a token.

[gregturn]

NOTE: Part of the issue in solving this is that many of our unit tests have "from User u..." (starting with from). When in truth, no JPQL/SQL statement starts with "from". Hence, making a pattern match that is ridiculously hard.

Unit tests should at least have a space before from.

[gregturn]

Resolved in main.

[gregturn]

Backported to 2.7.x.

[gregturn]

Backported to 2.6.x.

[gregturn]

Backported to 2.5.x.

[markkrause-wk]

@gregturn thanks for looking into this.
However, I think there is still an issue (although it may be different root case). We were unable to update to Spring Data JPA 2.6.4 because of what I highlighted above. Finding the last match breaks queries which contain sub-selects.

[gregturn]

@markkrause-wk You may like to know, that the new HQL parser handles this with ease (latest branch of Spring Data JPA). All three scenarios now pass. I'm adding this to our test suite. (2f6955e)

If you have another query to further check this out, please let us know.