Accept additional function argument literals for correct native query with pageable rewrite

[futzlig]

I'm migrating a project from Spring Boot 2.7.9 to 3.0.4. I have the following pageable, native query with a list_agg function:

    @Query(
            value = """
                    select s.id as id, s.name as name, gp.points
                    from specialist s
                    left join (select q.specialist_id, listagg(q.points, ';') as points from qualification q group by q.specialist_id) gp on gp.specialist_id = s.id
                    where name like :name
                    """
            , nativeQuery = true)
    Page<SpecialistOverview> overview(@Param("name") String name, Pageable pageable);

Calling this function results under Spring Boot 3.0.x in a DataIntegrityViolationException because the second parameter could not be bound. This ist the generated SQL:

select s.id as id, s.name as name, gp.points
from specialist s
left join (select q.specialist_id, listagg(q.points, ' fetch first ? rows only;') as points from qualification q group by q.specialist_id) gp on gp.specialist_id = s.id
where name like ?

As you can see the "fetch first ? rows only" section is now part of the delimter literal of the list_agg function.

Under Spring Boot 2.7.9 everything works as expected.

Attached projects to reproduce this anomality.

Hibernate6NativeQueryIssue.zip
PageableNativeQuerySpringBoot2.zip

[gregturn]

The problem lies in QueryUtils. It is being used to parse this native query, since JSqlParser is not on the classpath. One of the things it tries to do is process function aliases, which is contained in listagg(q.points, ';') as points, such that points would be captured as an alias.

Unfortunately, the ; character is not recognized on the current pattern's list of approved characters, so it stops right there and never sees points, thus causing the the processing to stop there. If you switch to something like ',', then the processing continues, because that is a recognized token.

A flaw in all this is the fact that the , token in the function parsing algorithm of QueryUtils is trying to spot arguments with the functional call. It's a little hard to read the regex to see how it would handle strings like ';' or ',' or whatever. I could simply add ; to the list of approve tokens, but I need to make sure this is done properly considering we don't have a parser for SQL.