Hi, I've executed the following native query with CTE (common table expression) syntax and positional parameter, but the result returned is always empty. From the trace log printed, hibernate seems to be able to bind the parameter to the correct position but oddly enough the results were never returned. There's no issue with the SQL as concatenating the value onto the SQL without instead of positional parameter is returning the expected results.
p.s. there were no errors thrown when executing the query
{code:sql}
WITH RECURSIVE CTE(id, parent_id) AS (
SELECT id, parent_id
FROM some_table
WHERE runtime_ref_id = ?1
UNION ALL
SELECT parent.id, parent.parent_id
FROM some_table parent
JOIN CTE cte
ON cte.parent_id = parent.id
) SELECT DISTINCT id
FROM CTE cte
{code}
{code:java}
...
entityManager.createNativeQuery(SQL).setParameter(1, id).getResultList();
...
{code}
|
|
