A native query with paging and semicolon is generated incorrectly

[AlexanderShchelkunov]

Description

Hello!

When I have a native query with paging and semicolon, Spring Data generates incorrect query (it inserts 'fetch first ? rows only' before the semicolon):

SELECT *
FROM users
WHERE login = ' fetch first ? rows only;' OR login IS NULL

Example of the query:

  @Query(
      value = """
          SELECT *
          FROM users
          WHERE login = ';' OR login IS NULL
          """,
      nativeQuery = true
  )
  Page<UserEntity> findUsers(Pageable pageable);

In real application I have regexp_split_to_array function with semicolon (PostgreSQL).
It worked fine in Spring ~2.6.3.

Environment

Spring Boot: 3.3.9
Java: JDK 17

Steps to Reproduce

To reproduce the defect run the app and the error will occur during @PostConstruct execution in UserService

Error Message

	at com.example.demo.DemoApplication.main(DemoApplication.java:10) ~[main/:na]
Caused by: org.springframework.dao.DataIntegrityViolationException: JDBC exception executing SQL [SELECT *
FROM users
WHERE login = ' fetch first ? rows only;' OR login IS NULL

Minimal sample application

See the attachment:
demo-native-query.zip

Workaround

Pass ';' to the query as a parameter.

[quaff]

Please make sure you are using Hibernate ORM 6.6+, I fixed it by hibernate/hibernate-orm@df7f104.

[mp911de]

Spring Data uses regex by default to rewrite SQL queries and so we can only rewrite trivial queries. Please add JSqlParser to your classpath to rewrite queries using enclosed semicolon.