getFunctionAliases fails for complex query

[TaridaGeorge]

If I use the nativeQuery approach and I have a query like this:

SELECT 
	CAST(('{' || string_agg(distinct array_to_string(c.institutes_ids, ','), ',') || '}') AS bigint[]) as institutesIds 
FROM
	city c

the getFunctionAliases method from org/springframework/data/jpa/repository/query/QueryUtils.java fails to take institutesIds as an alias and that's because that FUNCTION_PATTERN cannot handle complex cases like this.

What should we do about it?

[schauder]

Please post the stack trace and method this SQL statement is annotated to?

[schauder]

You can always implement it using a custom method implementation.

We are looking in a proper solution for this kind of problem here #2417

[TaridaGeorge]

Thank you for your response schaunder!

This is the method from spring-data that I'm talking about:

	static Set<String> getFunctionAliases(String query) {

		Set<String> result = new HashSet<>();
		Matcher matcher = FUNCTION_PATTERN.matcher(query);

		while (matcher.find()) {

			String alias = matcher.group(1);

			if (StringUtils.hasText(alias)) {
				result.add(alias);
			}
		}

		return result;
	}

It gets as input a String - query. If the query is somehow complex like in my initial post this method fails to match all function aliases. There is no stacktrace because there is no exception thrown. The problem is that the result from return result does not contain the institutesIds alias as I was expecting it to do.

Looking at #2417 I think that using JSqlParser will be a proper solution for this.

Regarding "custom method implementation" can you guide me to how can I achieve this? Any reference to a tutorial or documentation will be helpful. Thank you!

[schauder]

I was referring to the method in your repository, that you applied the @Query annotation to.

[TaridaGeorge]

It is something like this:

@Query("SELECT "  + 
	"CAST(('{' || string_agg(distinct array_to_string(c.institutes_ids, ','), ',') || '}') AS bigint[]) as institutesIds "  + 
"FROM " + 
	"city c",
nativeQuery = true)
Page<Object[]> findAllInstitutesIds()

[schauder]

Could you return a list instead, or provide a countQuery?

[TaridaGeorge]

I've made a repo in order to explain better what I found to not work properly. There are 2 tests in the DemoApplicationTests class. In my opinion both of them should pass but they fail. You can find the repo here:

https://github.com/TaridaGeorge/spring-data-jpa-issue-2441

[DiegoKrupitza]

We are looking in a proper solution for this kind of problem here #2417
@schauder

I added 2 new test cases that make sure this issue is not a problem anymore when using JSQLParser. For the new implementation this is not a problem anymore.

[TaridaGeorge]

Nice! Thank you!