PostgreSQL Left Join在ManyToOne属性为Null时丢失行的问题

场景还原 #

在Spring Boot + PostgreSQL项目中，原本的JPQL查询可以正常从ConfigElement表获取数据并映射到ConfigurationReview实体：

@Query(
          "SELECT new xx.xx.xx.dao.ConfigurationReview(r.id, MAX(ce.id) AS configElementId,"
        + " r.checkMethod, r.targetDate, r.endDate, r.sessionDate, SUM(CASE WHEN i.criticality ="
        + " 0 AND i.relation = 0 THEN 1 ELSE 0 END) AS majorFindings, SUM(CASE WHEN i.criticality"
        + " = 1 AND i.relation = 0 THEN 1 ELSE 0 END) AS minorFindings, r.state) FROM"
        + " ConfigElement ce "
        + "LEFT JOIN ce.reviews r ON r.configElement.id = ce.id "
        + "LEFT JOIN r.criteria cr ON cr.review.id = r.id "
        + "LEFT JOIN cr.issues i ON i.criterion.id = cr.id "
        + "WHERE ce.id = :configurationId AND r.id IS NOT NULL GROUP BY r.id")
  Page<ConfigurationReview> findConfigurationReviews(
          @Param("configurationId") Long configurationId, Pageable pageable);

当给ConfigurationReview的构造函数添加r.milestone（ManyToOne关联属性）后，查询出现异常：仅返回milestone非NULL的记录，milestone为NULL的记录被过滤。现有2条非NULL、2条NULL的记录，查询仅返回2条。

修改后的查询：

@Query(
          "SELECT new xx.xx.xx.dao.ConfigurationReview(r.id, MAX(ce.id) AS configElementId,"
        + " r.checkMethod, r.targetDate, r.endDate, r.sessionDate, SUM(CASE WHEN i.criticality ="
        + " 0 AND i.relation = 0 THEN 1 ELSE 0 END) AS majorFindings, SUM(CASE WHEN i.criticality"
        + " = 1 AND i.relation = 0 THEN 1 ELSE 0 END) AS minorFindings, r.state, r.milestone) FROM"
        + " ConfigElement ce "
        + "LEFT JOIN ce.reviews r ON r.configElement.id = ce.id "
        + "LEFT JOIN r.criteria cr ON cr.review.id = r.id "
        + "LEFT JOIN cr.issues i ON i.criterion.id = cr.id "
        + "WHERE ce.id = :configurationId AND r.id IS NOT NULL GROUP BY r.id")
  Page<ConfigurationReview> findConfigurationReviews(
          @Param("configurationId") Long configurationId, Pageable pageable);

ConfigurationReview中milestone的定义：

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(foreignKey = @ForeignKey(name = "fk_review_milestones_on_milestone_id"))
private Milestone milestone;

原因分析 #

JPQL要求SELECT中的非聚合字段必须全部出现在GROUP BY子句中。当你在SELECT中加入r.milestone（实体对象），但GROUP BY仅包含r.id时，PostgreSQL的分组逻辑会对NULL值的milestone进行错误处理，导致对应记录被排除。

解决办法 #

方案1：将r.milestone加入GROUP BY #

修改GROUP BY子句，把r.milestone也包含进去：

@Query(
          "SELECT new xx.xx.xx.dao.ConfigurationReview(r.id, MAX(ce.id) AS configElementId,"
        + " r.checkMethod, r.targetDate, r.endDate, r.sessionDate, SUM(CASE WHEN i.criticality ="
        + " 0 AND i.relation = 0 THEN 1 ELSE 0 END) AS majorFindings, SUM(CASE WHEN i.criticality"
        + " = 1 AND i.relation = 0 THEN 1 ELSE 0 END) AS minorFindings, r.state, r.milestone) FROM"
        + " ConfigElement ce "
        + "LEFT JOIN ce.reviews r ON r.configElement.id = ce.id "
        + "LEFT JOIN r.criteria cr ON cr.review.id = r.id "
        + "LEFT JOIN cr.issues i ON i.criterion.id = cr.id "
        + "WHERE ce.id = :configurationId AND r.id IS NOT NULL GROUP BY r.id, r.milestone")
  Page<ConfigurationReview> findConfigurationReviews(
          @Param("configurationId") Long configurationId, Pageable pageable);

方案2：用聚合函数包裹r.milestone #

由于每个r.id对应唯一的milestone（每个Review只有一个关联的Milestone），可以用MAX()或MIN()聚合函数包裹r.milestone，这样无需修改GROUP BY：

@Query(
          "SELECT new xx.xx.xx.dao.ConfigurationReview(r.id, MAX(ce.id) AS configElementId,"
        + " r.checkMethod, r.targetDate, r.endDate, r.sessionDate, SUM(CASE WHEN i.criticality ="
        + " 0 AND i.relation = 0 THEN 1 ELSE 0 END) AS majorFindings, SUM(CASE WHEN i.criticality"
        + " = 1 AND i.relation = 0 THEN 1 ELSE 0 END) AS minorFindings, r.state, MAX(r.milestone)) FROM"
        + " ConfigElement ce "
        + "LEFT JOIN ce.reviews r ON r.configElement.id = ce.id "
        + "LEFT JOIN r.criteria cr ON cr.review.id = r.id "
        + "LEFT JOIN cr.issues i ON i.criterion.id = cr.id "
        + "WHERE ce.id = :configurationId AND r.id IS NOT NULL GROUP BY r.id")
  Page<ConfigurationReview> findConfigurationReviews(
          @Param("configurationId") Long configurationId, Pageable pageable);

补充说明 #

两种方案都能解决NULL值记录丢失的问题：

• 方案1严格遵循JPQL的GROUP BY规范，适合需要明确分组逻辑的场景；
• 方案2利用聚合函数的特性，无需修改GROUP BY，代码改动更小。

内容的提问来源于stack exchange，提问作者bullgr