sql - 错误 : NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [name] during auto-discovery of a native-sql query

Question

我无法解决这个错误，这是我的数据:

模式:

CREATE TABLE public.algorithm (
    algorithm_id serial NOT NULL,
    company_spec varchar(255) NULL,
    description varchar(255) NULL,
    docker_image_name varchar(255) NOT NULL,
    input_spec varchar(255) NOT NULL,
    "name" varchar(30) NOT NULL,
    output_spec varchar(255) NOT NULL,
    CONSTRAINT algorithm_pkey PRIMARY KEY (algorithm_id)
);

CREATE TABLE public.pi (
    pi_id serial NOT NULL,
    "name" varchar(255) NULL,
    pi_type_id int4 NULL,
    CONSTRAINT pi_pkey PRIMARY KEY (pi_id),
    CONSTRAINT fko2uqm9q0pxhoq3lg48favc2s8 FOREIGN KEY (pi_type_id) REFERENCES pi_type(pi_type_id)
);

数据:

INSERT INTO algorithm (algorithm_id, name, description, input_spec, output_spec, company_spec, docker_image_name) VALUES
(1, 'fake algorithm 1', 'fake algorithm 1 description', 'python pi_scalar.py', 'scalar', NULL, 'pi_python_docker_image'),
(2, 'fake algorithm 2', 'fake algorithm 2 description', 'python pi_timeseries.py', 'timeseries', NULL, 'pi_python_docker_image'),
(3, 'csic''s algorithm', 'csic''s algorithm description', './pi_csic.m;.csv;anthropometry.yaml', 'scalar;scalar;scalar;scalar;scalar;scalar', '', 'pi_csic_docker_image');

INSERT INTO pi (pi_id, name, pi_type_id) VALUES
(1, 'fake_scalar_1', 1),
(2, 'fake_time_series_1', 2),
(4, 'step_length_left', 3),
(5, 'step_time_right', 4),
(6, 'step_time_left', 4),
(7, 'stride_time_right', 5),
(8, 'stride_time_left', 5),
(3, 'step_length_right', 3); 

还有我的查询代码:

public interface IExperimentRepository  extends JpaRepository<Experiment, Integer>{

    @Query(value ="SELECT a.name, e.pi_id, p.name, g.aggregation_type_name, e.score "
            + "FROM experiment_pi_aggregation_score e "
            + "JOIN algorithm a "
            + "ON e.algorithm_id=a.algorithm_id "
            + "JOIN pi p ON e.pi_id=p.pi_id "
            + "JOIN aggregation_type g ON e.aggregation_type_id=g.aggregation_type_id "
            + "WHERE e.experiment_id = :id "
            + "ORDER BY e.pi_id ASC, g.aggregation_type_name ASC", nativeQuery = true)  
    public List<Object> findScores(
            @Param("id") Integer id
            ); 
      }

我已经用别名更改了长列名称，但仍然出现同样的错误..

我正在使用 Postgres 11。

Answer 1

在查询中设置别名:

public interface IExperimentRepository  extends JpaRepository<Experiment, Integer>{

@Query(value ="SELECT a.name as algorithm_name, e.pi_id, p.name as pi_name, g.aggregation_type_name, e.score "
        + "FROM experiment_pi_aggregation_score e "
        + "JOIN algorithm a "
        + "ON e.algorithm_id=a.algorithm_id "
        + "JOIN pi p ON e.pi_id=p.pi_id "
        + "JOIN aggregation_type g ON e.aggregation_type_id=g.aggregation_type_id "
        + "WHERE e.experiment_id = :id "
        + "ORDER BY e.pi_id ASC, g.aggregation_type_name ASC", nativeQuery = true)  
public List<Object> findScores(
        @Param("id") Integer id
        ); 
  }