Native query pagination fails with derived tables (subqueries in FROM clause)

[dongmyo]

Description

When using @Query with nativeQuery = true and Pageable on queries containing derived tables (subqueries in the FROM clause), Spring Data JPA generates incorrect count queries for pagination, resulting in SQL syntax errors.

Versions

• Spring Boot: 3.5.7
• Spring Data JPA: 3.5.5
• Hibernate: 6.6.33.Final
• Database: MySQL

Current Behavior

The COUNT_MATCH regex pattern in QueryUtils.createCountQueryFor() incorrectly parses native queries with derived tables by matching the FROM keyword inside the subquery instead of the outer query's FROM clause.

Example Query:

select result.order_id, result.latest_activity
from (
    select orders.order_id,
           orders.item_id,
           max(activities.activity_date) as latest_activity
    from order_members
    inner join activities on order_members.order_id = activities.order_id
    where order_members.member_id = ?1
    group by orders.id
) result
where result.latest_activity < ?2

Repository Method:

@Query(value = "...", nativeQuery = true)
Page<Object[]> findOrderActivities(long memberId, Date activityDate, Pageable pageable);

Error:

org.springframework.dao.InvalidDataAccessResourceUsageException:
JDBC exception executing SQL [select result.order_id, result.latest_activity from (
  select orders.order_id from order_members
  group by orders.id]
[You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax
to use near '' at line 7]

The generated query is truncated, missing:

• The closing parenthesis of the subquery
• The outer FROM alias (result)
• The outer WHERE clause
• Any ORDER BY clauses

Expected Behavior

The count query should be correctly generated, either by:

1. Properly parsing the entire query structure, or
2. Wrapping the entire query in a subquery:

SELECT COUNT(*) FROM (original_query) subquery_alias

Root Cause Analysis

In QueryUtils.java, line 626:

Matcher matcher = COUNT_MATCH.matcher(originalQuery);

The COUNT_MATCH pattern does not account for derived tables and matches the first FROM keyword it encounters, which is inside the subquery. This causes:

1. Incorrect alias detection (e.g., detecting group as the alias instead of result)
2. Malformed count query generation
3. Query string truncation

Steps to Reproduce

1. Create a repository method with a native query containing a derived table
2. Use Pageable parameter
3. Execute the query
4. Observe SQL syntax error

Minimal Reproducible Example:

@Repository
public interface TestRepository extends JpaRepository<Entity, Long> {

    @Query(value = """
        select u.id, u.name
        from (
            select id, name from users where active = 1
        ) u
        """, nativeQuery = true)
    Page<Object[]> findActiveUsers(Pageable pageable);
}

Additional Context

This issue did not occur in Spring Boot 2.7.x but started appearing after upgrading to Spring Boot 3.x.

The workaround is to manually specify a countQuery:

@Query(value = "...",
       countQuery = "SELECT COUNT(*) FROM (original_query) subquery_alias",
       nativeQuery = true)

However, this is verbose and error-prone when the query changes.

[mp911de]

Oh, absolutely - thank you for this extraordinarily detailed report, which, I must say, reads like a detective novel about regex gone rogue in the wild jungles of SQL parsing! 🕵️‍♀️📜

Ah yes, the COUNT_MATCH pattern, the ever-faithful, occasionally treacherous companion of QueryUtils.createCountQueryFor(). It is indeed somewhat unsurprising (and by "somewhat," I mean entirely within the realm of expected regex absurdity) that when confronted with your beautifully nested, highly expressive, multi-layered SQL artistry - which features not one but several derived tables, nested subqueries, and even an outer WHERE clause, because why not - the poor little regex gives up, wipes its metaphorical brow, and proceeds to gleefully slice your query in half like it’s trimming a bonsai tree with a chainsaw.

This behavior, while disappointing, is - and I cannot emphasize this enough - somewhat expected given the current limitations of regex-based parsing in QueryUtils. You see, regex, that noble yet tragically misunderstood mechanism, was never designed to parse the recursive, context-dependent grammatical labyrinth that is SQL. Much like trying to measure soup with a ruler, it’s a tool entirely unsuited to the task at hand but used nonetheless because, well, it usually works - until it doesn’t.

🧩 Root Cause

You are absolutely correct that the COUNT_MATCH pattern greedily latches onto the first instance of FROM it finds - much like a toddler spotting the first cookie on the table - and declares, “Yes, this must be the one.”
Unfortunately, in your particular case, that FROM belongs to the inner subquery, and the regex, having no sense of context, merrily truncates everything beyond it, producing a count query so malformed it could serve as modern abstract art. 🎨

One might even say this incident serves as a microcosm of the eternal struggle between regex and structured syntax - a saga of hubris, overreach, and the fragile illusion of control.

Now, here comes the subtly buried but truly important part:

👉 This is somewhat expected as Regex support is limited. Have you tried adding JSqlParser to your project?

⚙️ Workarounds

1. Explicit countQuery - Manually define the countQuery in your @Query annotation, wrapping your entire query in a subselect. You’ve already done this, of course, but tradition dictates we mention it again for completeness.
2. Simplify for Pageable - If pagination is essential but query complexity is high, sometimes you can delegate paging logic to the database view or use a temporary table.
3. Adopt JSqlParser - I say again, and will say thrice for rhetorical weight:
   👉 Consider adding JSqlParser to your project.

[dongmyo]

Thank you for the feedback.
I understand JSqlParser is the recommended approach.
Also I've added JSqlParser to my project and it resolved the issue.

However, I still believe this is a regression that affects users who cannot add JSqlParser for various reasons.

I've been analyzing the code and have a potential fix in mind.
Would you consider this approach?

Proposed Solution:
Add a simple detection for derived tables in QueryUtils.createCountQueryFor():

// Detect "FROM (" pattern indicating a derived table
if (nativeQuery && query.matches(".*\\bfrom\\s*\\(.*")) {
    // Wrap entire query in subquery for counting
    return "SELECT COUNT(*) FROM (" + originalQuery + ") subquery_alias";
}

Benefits:

• Only 5 lines of code added
• Only applies to native queries with derived tables (FROM clause subqueries)
• Doesn't interfere with JSqlParser when present
• Uses standard SQL approach (wrapping in subquery) instead of regex parsing
• Preserves existing behavior for all other queries

What do you think?

[mp911de]

However, I still believe this is a regression that affects users who cannot add JSqlParser for various reasons.

QueryUtils is rather limited. It has been designed for a minimally invasive alteration of SQL statements. Wrapping original statements into a subselect for count queries goes way beyond its intention. Limitations are outlined in our reference documentation. Alterations to cater for rather specific cases are a higher risk of breaking more general usage especially that code is in place for over 15 years. If using JSqlParser is not an option, the alternative is using either custom repository implementations or running the query directly through EntityManager. Frameworks are designed to simplify the most common use-cases. Going beyond these leads to either working against the framework or non-idiomatic usage.

In any case, thanks for reaching out.