The column name id was not found in this ResultSet.[] : "spring boot native query error"

Question

When I am executing the below query in Dbeaver I am getting the correct result

WITH A_ID AS (SELECT ROR.ID FROM RECORD_ORDER ROR WHERE ROR.order_no = '123' UNION SELECT RL.record_key FROM RECORD_LINE RL where RL.line_no = '077') SELECT RO FROM RECORD_ORDER RO,A_ID WHERE RO.domain_id = 'test_domain' AND RO.hub_node = 'test_node' AND RO.id = A_ID.id

But when I am using a native query spring boot with the same query:

@Query(value = "WITH A_ID AS (SELECT ROR.ID FROM RECORD_ORDER ROR WHERE ROR.order_no = '123' UNION SELECT RL.record_key FROM RECORD_LINE RL where RL.line_no = '077') SELECT RO FROM RECORD_ORDER RO,A_ID WHERE RO.domain_id = 'test_domain' AND RO.hub_node = 'test_node' AND RO.id = A_ID.id\n", nativeQuery = true) List findRecordOrder(String domainId, String hubNode, String recordNo, String lineNo);

I am getting error :

2023-02-21 03:24:45,344 [http-nio-8066-exec-2] [WARN ] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:137) - SQL Error: 0, SQLState: 42703[]

2023-02-21 03:24:45,344 [http-nio-8066-exec-2] [ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:142) - The column name id was not found in this ResultSet.[]

Answer 1

This part is incorrect

WITH A_ID AS (SELECT ROR.ID FROM RECORD_ORDER ROR WHERE ROR.order_no = '123' UNION SELECT RL.record_key FROM RECORD_LINE RL where RL.line_no = '077') SELECT RO FROM RECORD_ORDER RO,A_ID WHERE RO.domain_id = 'test_domain' AND RO.hub_node = 'test_node' AND RO.id = A_ID.id

It should be

WITH A_ID AS (SELECT ROR.ID FROM RECORD_ORDER ROR WHERE ROR.order_no = '123' UNION SELECT RL.record_key FROM RECORD_LINE RL where RL.line_no = '077') SELECT RO.* FROM RECORD_ORDER RO,A_ID WHERE RO.domain_id = 'test_domain' AND RO.hub_node = 'test_node' AND RO.id = A_ID.id

Btw, the original code does work in Postgresql but the result is a collection of rows with no column names.