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} |
|