Column ambiguously defined

Hibernate ORM
1

I’m executing a query-dsl with joins, and the following error is thrown. Even when using .as, it doesn’t resolve the issue, and the error persists. Version 6.4.1.Final

SQL Error: 918, SQLState: 42000
2024-10-18 16:18:16.609 [http-nio-8131-exec-1] ERROR [,6712b4774f17af9569c5ce03dcaaa984,2b6068427f6ea951] org.hibernate.engine.jdbc.spi.SqlExceptionHelper : ORA-00918: column ambiguously defined

https://docs.oracle.com/error-help/db/ora-00918/
2024-10-18 16:18:16.616 [http-nio-8131-exec-1] ERROR org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select u1_0.id,u1_0.nome,u1_0.nome_usuario,u1_0.email,u1_0.foto_perfil,u1_0.nivel,u1_0.cargo,case when (u1_0.usuario_id is null) then 1 else 0 end,u1_0.territorio,u1_0.clusters,pr1_0.tipo_reacao,pr1_0.id from publicacao_reacao pr1_0 left join usuario u1_0 on u1_0.id=pr1_0.fk_usuario where pr1_0.fk_publicacao=? and pr1_0.tipo_reacao=? order by pr1_0.data_reacao offset ? rows fetch first ? rows only] [ORA-00918: column ambiguously defined

ORA-00918 - Database Error Messages] [n/a]; SQL [n/a]] with root cause

oracle.jdbc.OracleDatabaseException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608)
2

I just ran the following statements on Oracle directly but it didn’t complain with ORA-00918:

create table publicacao_reacao (id number(10,0), tipo_reacao nvarchar2(255));
create table usuario
(
    id   number(10, 0),
    nome nvarchar2(255),
    nome_usuario nvarchar2(255),
    email nvarchar2(255),
    foto_perfil nvarchar2(255),
    nivel nvarchar2(255),
    cargo nvarchar2(255),
    territorio nvarchar2(255),
    clusters nvarchar2(255),
    usuario_id number(10,0)
);

select u1_0.id,
       u1_0.nome,
       u1_0.nome_usuario,
       u1_0.email,
       u1_0.foto_perfil,
       u1_0.nivel,
       u1_0.cargo,
       case when (u1_0.usuario_id is null) then 1 else 0 end,
       u1_0.territorio,
       u1_0.clusters,
       pr1_0.tipo_reacao,
       pr1_0.id
from publicacao_reacao pr1_0,
usuario u1_0
order by u1_0.id
fetch first 1 rows only

I’m assuming that this is due to your version of Oracle trying to rewrite the fetch first clause to a subquery internally, which will then run into the ambiguous alias problem.
I believe this is an Oracle issue. What version are you using?