Using Pagination with CTE query generates wrong countQuery

[saadalmogren]

Hello,
I am using the latest spring-data-jpa:3.4.2 with postgresql and faced the below issue:

using two CTE Queries with native query enabled as below:

@Query(value = """
            WITH credit_transactions AS (
                SELECT
                    *
                FROM transaction trx
                WHERE trx."CreditDebit" = 'CR' AND trx."Message Reference Id" > :transactionIdOffset
            ),
            debit_transactions AS (
                SELECT
                    *
                FROM transaction trx
                WHERE trx."CreditDebit" = 'DR' AND trx."Message Reference Id" > :transactionIdOffset
            )
            SELECT
                COALESCE(c."Message Reference Id", d."Message Reference Id") AS "Message Reference Id",
            FROM credit_transactions c
            FULL OUTER JOIN debit_transactions d
                ON c."Message Reference Id" = d."Message Reference Id" ORDER BY "Message Reference Id" ASC 
            """,
            nativeQuery = true)
    Page<Transaction> getTransactionsByOffset(@Param("transactionIdOffset") Long transactionIdOffset, Pageable pageable);

but hibernate executes the following query

WITH credit_transactions AS (select count(trx) FROM transaction trx
    WHERE trx."CreditDebit" = 'CR' AND trx."Message Reference Id" > ?
),
debit_transactions AS (
    SELECT
        *
    FROM transaction trx
    WHERE trx."CreditDebit" = 'DR' AND trx."Message Reference Id" > ?
)
SELECT
COALESCE(c."Message Reference Id", d."Message Reference Id") AS "Message Reference Id",
FROM credit_transactions c
FULL OUTER JOIN debit_transactions d
    ON c."Message Reference Id" = d."Message Reference Id"

and throws the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: column c.Message Reference Id does not exist
  Hint: Perhaps you meant to reference the column "d.Message Reference Id".

CAUSE:
It changed the first CTE with:

WITH credit_transactions AS (select count(trx) FROM transaction trx
    WHERE trx."CreditDebit" = 'CR' AND trx."Message Reference Id" > ?
)

Workaround:
I was able to workaround the issue by specifying an explicit countQuery. Thanks to: #3726 (comment)

[mp911de]

Spring Data requires JSqlParser for overly complex queries that use functions or more advanced features beyond a simple SELECT statement with regular column projections. See also our reference documentation on native query rewriting. Dropping JSqlParser onto the class path seems to generate the correct query. Care to verify?

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.