使用Criteria Builder排序价格触发PostgreSQL DISTINCT排序错误排查

问题现象 #

调用fetchProducts方法时触发PostgreSQL错误：

Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Position: 1272 at org.postgres@42.1.1//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)

仅当按price排序时触发该错误，按id或其他字段排序正常。按price排序的核心逻辑：

if(sort.equals("price")) {
    Expression<Object> sortOrderExpression = criteriaBuilder.selectCase()
      .when(criteriaBuilder.isNotNull(root.get("unitDiscountPrice")), root.get("unitDiscountPrice"))
      .otherwise(root.get("unitPrice"));

完整方法代码：

public List<ProductEntity> fetchProducts(Integer page, Integer perPage, String search, String sort, Boolean descending, Long localeId, String brands, String categories, String colors, Long minPrice, Long maxPrice) {
    int limit = perPage != null ? perPage : LIMIT;
    if(page == null) {
      page = 1;
    }
    System.out.println("LOCALE ID:");
    System.out.println(localeId);
    if (localeId == null) {
      localeId = 1L;
    }
    int offset = limit * (page - 1);


    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<ProductEntity> root = criteriaQuery.from(ProductEntity.class);
    Join<ProductEntity, ProductTranslationEntity> translationJoin = root.join("productTranslationEntities", JoinType.LEFT);
    Join<ProductEntity, ProductPriceEntity> productPriceJoin = root.join("productPrice", JoinType.LEFT);

    translationJoin.on(criteriaBuilder.or(
      criteriaBuilder.equal(translationJoin.get("locale"), localeId),
      criteriaBuilder.isNull(translationJoin.get("locale"))
    ));
    List<Predicate> predicates = new ArrayList<>();
    if (brands != null && !brands.isEmpty()) {
      System.out.println("I AM IN Brand!");
      Join<ProductEntity, BrandEntity> brandJoin = root.join("brand", JoinType.INNER);
      List<String> brandList = Arrays.asList(brands.split(","));
      predicates.add(brandJoin.get("id").in(brandList));
    }
    // Handle CSV values for categories
    if (categories != null && !categories.isEmpty()) {
      System.out.println("I AM IN CATEGORY!");
      Join<ProductEntity, CategoryEntity> categoryJoin = root.join("categories", JoinType.INNER);

      List<String> categoryList = Arrays.asList(categories.split(","));
      predicates.add(categoryJoin.get("id").in(categoryList));
    }
    // Handle CSV values for categories
    if (colors != null && !colors.isEmpty()) {
      Join<ProductPriceEntity, VariationEntity> productPriceVariationJoin = productPriceJoin.join("variations", JoinType.LEFT);
      List<String> colorList = Arrays.asList(colors.split(","));
      predicates.add(productPriceVariationJoin.get("id").in(colorList));
    }

    // Handle "like" query for product name
    if (search != null && !search.isEmpty()) {
      predicates.add(criteriaBuilder.or(
          criteriaBuilder.like(criteriaBuilder.lower(root.get("name")), "%" + search.toLowerCase() + "%"),
          criteriaBuilder.like(criteriaBuilder.lower(root.get("description")), "%" + search.toLowerCase() + "%"),
          criteriaBuilder.like(criteriaBuilder.lower(translationJoin.get("productName")), "%" + search.toLowerCase() + "%"),
          criteriaBuilder.like(criteriaBuilder.lower(translationJoin.get("productDescription")), "%" + search.toLowerCase() + "%")
        )
      );
    }
    if((minPrice != null || maxPrice != null)) {
      if(minPrice == null) {
        minPrice = 0L;
      }
      if(maxPrice == null) {
        maxPrice = Long.MAX_VALUE;
      }
      predicates.add(criteriaBuilder.or(
          criteriaBuilder.between(root.get("unitPrice"), minPrice, maxPrice),
          criteriaBuilder.between(root.get("unitDiscountPrice"), minPrice, maxPrice),
          criteriaBuilder.between(productPriceJoin.get("price"), minPrice, maxPrice),
          criteriaBuilder.between(productPriceJoin.get("discountedPrice"), minPrice, maxPrice)
        )
      );
    }
    criteriaQuery.select(criteriaBuilder.array(
      criteriaBuilder.coalesce(translationJoin.get("productName"), root.get("name")),
      criteriaBuilder.coalesce(translationJoin.get("productDescription"), root.get("description")),
      root
    ));

    Order order;
    order = criteriaBuilder.desc(root.get("id"));
    // Handle sorting
    if (sort != null && !sort.isEmpty()) {
      String sortBy = descending != null && descending ? "desc" : "asc";

      if(sort.equals("price")) {
        Expression<Object> sortOrderExpression = criteriaBuilder.selectCase()
          .when(criteriaBuilder.isNotNull(root.get("unitDiscountPrice")), root.get("unitDiscountPrice"))
          .otherwise(root.get("unitPrice"));
        if ("asc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.asc(sortOrderExpression);
        } else if ("desc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.desc(sortOrderExpression);
        } else {
          // Handle invalid sortBy parameter, or provide a default sorting strategy
          order = criteriaBuilder.desc(root.get("id"));
        }
      } else {
        if ("asc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.asc(root.get(JSON_TO_DB_FIELDS.get(sort)));
        } else if ("desc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.desc(root.get(JSON_TO_DB_FIELDS.get(sort)));
        } else {
          // Handle invalid sortBy parameter, or provide a default sorting strategy
          order = criteriaBuilder.desc(root.get("id"));
        }
      }
    }
    // Apply the predicates to the criteria query
    criteriaQuery.where(predicates.toArray(new Predicate[0])).orderBy(order).distinct(true);
    return this.transformObjectToProductEntityList(em.createQuery(criteriaQuery).setMaxResults(limit).setFirstResult(offset).getResultList());
  }

错误原因 #

PostgreSQL对SELECT DISTINCT有严格约束：用于排序的表达式必须出现在SELECT查询的字段列表中。

• 按id或其他字段排序时，排序字段是root.get(id)，而root本身在SELECT列表中（criteriaQuery.select里包含了root），符合约束；
• 按price排序时，排序用的是selectCase生成的动态表达式（优先取unitDiscountPrice，否则取unitPrice），这个表达式未被包含在SELECT的字段列表里，因此触发错误。

解决方案 #

把排序用的price表达式添加到SELECT的字段数组中，后续转换实体时忽略该字段即可。修改核心代码如下：

// 提前定义排序表达式，方便后续复用
Expression<Number> sortOrderExpression = null;
Order order = criteriaBuilder.desc(root.get("id"));

// Handle sorting
if (sort != null && !sort.isEmpty()) {
  String sortBy = descending != null && descending ? "desc" : "asc";

  if(sort.equals("price")) {
    sortOrderExpression = criteriaBuilder.selectCase()
      .when(criteriaBuilder.isNotNull(root.get("unitDiscountPrice")), root.get("unitDiscountPrice"))
      .otherwise(root.get("unitPrice"));
    order = "asc".equalsIgnoreCase(sortBy) ? criteriaBuilder.asc(sortOrderExpression) : criteriaBuilder.desc(sortOrderExpression);
  } else {
    String dbField = JSON_TO_DB_FIELDS.get(sort);
    order = "asc".equalsIgnoreCase(sortBy) ? criteriaBuilder.asc(root.get(dbField)) : criteriaBuilder.desc(root.get(dbField));
  }
}

// 修改select部分，加入排序表达式（仅当按price排序时）
if (sortOrderExpression != null) {
  criteriaQuery.select(criteriaBuilder.array(
    criteriaBuilder.coalesce(translationJoin.get("productName"), root.get("name")),
    criteriaBuilder.coalesce(translationJoin.get("productDescription"), root.get("description")),
    root,
    sortOrderExpression // 新增排序字段到SELECT列表
  ));
} else {
  criteriaQuery.select(criteriaBuilder.array(
    criteriaBuilder.coalesce(translationJoin.get("productName"), root.get("name")),
    criteriaBuilder.coalesce(translationJoin.get("productDescription"), root.get("description")),
    root
  ));
}

同时调整transformObjectToProductEntityList方法，忽略数组中新增的第4个元素（排序表达式的值）。

调试方法 #

1. 打印生成的SQL：执行查询前，通过em.createQuery(criteriaQuery).unwrap(org.hibernate.query.Query.class).getQueryString()获取完整SQL，直接在PostgreSQL客户端执行，验证错误是否复现，同时直观对比SELECT和ORDER BY的字段差异；
2. 移除DISTINCT测试：临时注释掉.distinct(true)，看是否还报错，确认错误由DISTINCT与排序字段不匹配导致；
3. 单独验证排序表达式：把排序用的CASE表达式单独写在SELECT语句中执行，确认表达式本身无语法问题。

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