ORA-00918: "Column ambiguously defined"
Description
Activity
Show:
Marcos Nolasco June 12, 2024 at 2:22 PM
I managed to solve the problem by creating a CustomOracleDialect and overriding the supportsFetchClause method from Hibernate's OracleDialect, setting it to false. This way, Oracle doesn't use offset fetch and instead uses row_number, avoiding the duplication error.
Marco Belladelli June 12, 2024 at 2:02 PM
Could you please test using Hibernate’s latest stable version (6.5.2 at this time), or at least the latest 6.4 release (6.4.9)? If the issue persist, you could try using the column aliases you defined in the select statement of your query instead of the column path itself in the order clause, i.e. order by name_a.
We are using Hibernate 6.4.4 with Oracle and encountering the ORA-00918: "Column ambiguously defined" error when performing pagination with
OFFSETandFETCH FIRST. Our queries include column aliases, but the error persists. For example, in a query likeSELECT a.name as name_a, b.name as name_b FROM table_a a JOIN table_b b ON (a.code = b.code) order by a.name desc OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY, pagination triggers this error. Given that Hibernate 6 no longer generates automatic column aliases, is there a configuration or workaround to ensure unique aliases are generated or applied to prevent this issue?