subquery querydsl stack overflow queryfactory

What happens, if you add an ORDER BY clause to the original query?- PostgreSQL syntax:SELECT ID, TITLEFROM BOOKORDER BY SOMECOLUMNLIMIT 1 OFFSET 2- Naive SQL Server equivalent:SELECT b.c1 ID, b.c2 TITLEFROM (SELECT ID c1, TITLE c2,ROWNUMBER OVER (ORDER BY ID) rnFROM BOOKORDER BY SOMECOLUMN) bWHERE rn 2 AND rn 2 AND rn 2 AND rn 2 AND rn 2 AND rn 2 AND rn. So, let’s consider:- PostgreSQL syntax:SELECT ID, TITLEFROM BOOKLIMIT 1 OFFSET 2- SQL Server equivalent:SELECT b.FROM (SELECT ID, TITLE,ROWNUMBER OVER (ORDER BY ID) rnFROM BOOK) bWHERE rn 2 AND rn 2 AND rn. Once in each derived table:- Oracle equivalent:- Rename synthetic column names back to originalSELECT c1 ID, c2 IDFROM (SELECT b.c1, b.c2, ROWNUM rnFROM (- synthetic column names hereSELECT BOOK.ID c1, AUTHOR.ID c2FROM BOOKJOIN AUTHORON BOOK.AUTHORID = AUTHOR.ID) bWHERE ROWNUM 2But now, we’re done?Of course not! ID and ID.So, the solution is to rename the columns twice. And renaming the columns to random values is nasty, because the user of your home-grown in-house database framework wants to receive well-defined column names. And the trick of manually patching ID columns from the previous example doesn’t work, because you have multiple ID instances.

What if you want to do this:- PostgreSQL syntax:- Perfectly valid repetition of two ID columnsSELECT BOOK.ID, AUTHOR.IDFROM BOOKJOIN AUTHORON BOOK.AUTHORID = AUTHOR.IDLIMIT 1 OFFSET 2- Oracle equivalent:SELECT.FROM (SELECT b., ROWNUM rnFROM (- Ouch! ORA-00918: column ambiguously definedSELECT BOOK.ID, AUTHOR.IDFROM BOOKJOIN AUTHORON BOOK.AUTHORID = AUTHOR.ID) bWHERE ROWNUM 2Nope. Because you can have ambiguous column names in top-level SELECTs, but not in nested selects.

Subquery querydsl stack overflow queryfactory