Invalid order by when using native query and contains union [DATAJPA-1514]

[spring-projects-issues]

sattamshobaki opened DATAJPA-1514 and commented

Hello,
while I am using native query to fetch data for some reasons as below example:

 SELECT
 * FROM
 ( SELECT col1, col2, (select DECODE(COUNT(1), 0, 0, 1) 
 from table2 WHERE table2 .table1_id = a.id) col3

From
 table1 a

UNION
 ALL SELECT col1, col2, col3 From
 table2 b

) 
 WHERE
 lower(col1) like lower(?) 
 OR col2 like ? 
 OR lower(col3) like lower(?) 

when i added pageable object with order property the generated query will be as below

 SELECT * from(SELECT
 * FROM
 ( SELECT col1, col2, (select DECODE(COUNT(1), 0, 0, 1) 
 from table2 WHERE table2 .table1_id = a.id) col3

From
 table1 a

UNION
 ALL SELECT col1, col2, col3 From
 table2 b

) 
 WHERE
 lower(col1) like lower(:p1) 
 OR col2 like :p1 
 OR lower(col3) like lower(:p1) 
 *order by*
 *a.col1* ) 
 where
 rownum <= :p2

So in case there is union query the result should not contains alias table name

---

No further details from DATAJPA-1514