JPA Projections query with GROUP BY not working with null fields

Question

I would like to replicate a query that runs happily in both MySQL and H2:

SELECT 
    pedido_linha.produto_id AS material,
    COALESCE(pedido_linha.unidade_medida_id, "UN") AS uom, 
    pedido_linha.data_pedido  AS referenceDate,
    SUM(COALESCE(pedido_linha.quantidade, 0)) AS totalQuantity,
    SUM(COALESCE(pedido_linha.valor_total, 0)) AS totalGross,
    SUM(COALESCE(pedido_linha.valor_total, 0) - COALESCE(pedido_linha.valor_descontos, 0) - 
        COALESCE(pedido_linha.valor_impostos, 0)) AS totalNet,
    SUM(COALESCE(pedido_linha.valor_custo, 0)) AS totalCogs
FROM pedido_linha
GROUP BY pedido_linha.produto_id, pedido_linha.data_pedido, COALESCE(pedido_linha.unidade_medida_id, "UN");

As an example the query runs fine in MySQL, even though some Group By fields are empty:

The following attempt has been made extract the grouped results in a JPA closed projection:

public interface AggregatedByMaterialUOMDate {

Produto getMaterial();
UnidadeMedida getUom();
LocalDate getReferenceDate();

Float getTotalQuantity();
Float getTotalGross();
Float getTotalNet();
Float getTotalCogs();

}

Along with the repository query:

@Repository
public interface PedidoLinhaRepository extends JpaRepository<PedidoLinha, PedidoLinha.PedidoLinhaCompositeKey> {

    @Query("SELECT pl.produto AS material, "
            + "COALESCE(pl.unidadeMedida, :unidadeMedidaPadrao) AS uom, "
            + "pl.dataPedido  AS referenceDate, "
            + "SUM(COALESCE(pl.quantidade, 0)) AS totalQuantity, "
            + "SUM(COALESCE(pl.valorTotal, 0)) AS totalGross, "
            + "SUM(COALESCE(pl.valorTotal, 0) - COALESCE(pl.valorDescontos, 0) - COALESCE(pl.valorImpostos, 0)) AS totalNet, "
            + "SUM(COALESCE(pl.valorCusto, 0)) AS totalCogs "
            + "FROM PedidoLinha pl "
            + "GROUP BY pl.produto, pl.dataPedido, COALESCE(pl.unidadeMedida, :unidadeMedidaPadrao)")
    List<AggregatedByMaterialUOMDate> consolidatedSelloutByMaterialUOMDayAtLocation(@Param("unidadeMedidaPadrao") UnidadeMedida unidadeMedidaPadrao);
}

The JPQL query above returns a list with 0 elements, thus inconsistent with the native query previously shown.

We have attempted to change COALESCE(pl.unidadeMedida, :unidadeMedidaPadrao) to simply pl.unidadeMedida (both in the SELECT and GROUP BY clauses), with no success.

The query successfully returned all values only when all references to pl.unidadeMedida were completely removed from both clauses.

Below is a sample of the PedidoLinha (referenced by pl in the query) class showing the reference to UnidadeMedida:

@Getter
@Setter
@NoArgsConstructor
@RequiredArgsConstructor
@EqualsAndHashCode(of = "pedidoLinhaCompositeKey")
@Entity
public class PedidoLinha {

    @EmbeddedId
    @NonNull // torna campo obrigatório e parâmetro do construtor gerado pelo @Data (lombok)
    private PedidoLinhaCompositeKey pedidoLinhaCompositeKey;

    @Data // lombok: @ToString, @EqualsAndHashCode, @Getter on all fields @Setter on all non-final fields, and @RequiredArgsConstructor
    @NoArgsConstructor
    @RequiredArgsConstructor
    @Embeddable
    @EqualsAndHashCode
    public static class PedidoLinhaCompositeKey implements Serializable {

        @NonNull // torna campo obrigatório e parâmetro do construtor gerado pelo @Data (lombok)
        private String id;
        
        @ManyToOne(optional = false, fetch = FetchType.LAZY)
        @NonNull // torna campo obrigatório e parâmetro do construtor gerado pelo @Data (lombok)
        private Pedido pedido;
                
    }
    
    @ManyToOne(optional = false)
    private Produto produto;
    
    @ManyToOne
    private UnidadeMedida unidadeMedida;

    public UnidadeMedida getUnidadeMedida() {
        return (unidadeMedida == null) ? new UnidadeMedida("UN") : unidadeMedida;
    }

    // rest of the entity code
}

UnidadeMedida class

@Getter
@Setter
@EqualsAndHashCode(of = "id")
@NoArgsConstructor
@Entity
public class UnidadeMedida {

    @Id
    private String id;

    private String descricao;
    
    public UnidadeMedida(String id) {
        this.id = id;
    }
    
}

What could be happening? I believe this could be a Hibernate bug but could not find any reference to this issue.

I have included the class : we have used Lombok in all entities, with no issues so far. just tried including @AllArgsConstructor, to no effect — epol, Aug 26 '20 at 12:58
If you want someone help you, you need to include all required information. No provided definition for: `Producto`, `Pedido`, etc Comments like `// rest of the entity code` does not help. A person who wants to help you will need to create several stuff by himself and can miss some important details. — doctore, Aug 30 '20 at 18:43
@doctore, thanks for the tips. in the end the issue was not related to the dependent entities but to the way JPQL queries are translated into SQL code — epol, Sep 02 '20 at 15:56

epol · Accepted Answer · 2020-08-31T12:45:03.947

Turns out the key reason the JPQL query was not turning out any records was the way the implicit joins are made in the database call.

The following code represents the actual SQL code executed in the database (How to show the last queries executed on MySQL?) after running the JPQL query above:

SELECT 
    pedidolinh0_.produto_id as col_0_0_, 
    coalesce(pedidolinh0_.unidade_medida_id, 'UN') as col_1_0_, 
    pedidolinh0_.data_pedido as col_2_0_, 
    sum(coalesce(pedidolinh0_.quantidade, 0)) as col_3_0_, 
    sum(coalesce(pedidolinh0_.valor_total, 0)) as col_4_0_, 
    sum(coalesce(pedidolinh0_.valor_total, 0)-coalesce(pedidolinh0_.valor_descontos, 0)-coalesce(pedidolinh0_.valor_impostos, 0)) as col_5_0_, 
    sum(coalesce(pedidolinh0_.valor_custo, 0)) as col_6_0_, 
    produto1_.id as id1_88_, produto1_.ativo as ativo2_88_, 
    produto1_.data_descontinuacao as data_des3_88_, 
    produto1_.data_introducao as data_int4_88_, 
    produto1_.descricao as descrica5_88_, 
    produto1_.ean as ean6_88_, 
    produto1_.lote_minimo_requisicoes as lote_min7_88_, 
    produto1_.multiplo_requisicoes as multiplo8_88_, 
    produto1_.ncm as ncm9_88_, 
    produto1_.unidade_medida_padrao_id as unidade10_88_ 
from pedido_linha pedidolinh0_ 
    inner join produto produto1_ on pedidolinh0_.produto_id=produto1_.id 
    cross join unidade_medida unidademed2_ 
    cross join pedido pedido3_ 
where pedidolinh0_.unidade_medida_id=unidademed2_.id
group by 
    pedidolinh0_.produto_id , 
    pedidolinh0_.data_pedido , 
    coalesce(pedidolinh0_.unidade_medida_id, 'UN')

The issue is that the translation to SQL automatically includes a a cross join + WHERE clause pedidolinh0_.unidade_medida_id=unidademed2_.id in the query, effectively ignoring every instance where this field is null. Even though coalescing is applied in the select statement, these records were already ignored by the where clause and thus it has no effect.

The solution was to force a left join in the JPQL query, as shown below:

SELECT 
    pl.produto AS material,
    COALESCE(um, :unidadeMedidaPadrao) AS uom,
    pl.dataPedido AS referenceDate,
    SUM(COALESCE(pl.quantidade, 0)) AS totalQuantity,
    SUM(COALESCE(pl.valorTotal, 0)) AS totalGross,
    SUM(COALESCE(pl.valorTotal, 0) - COALESCE(pl.valorDescontos, 0) - COALESCE(pl.valorImpostos, 0)) AS totalNet,
    SUM(COALESCE(pl.valorCusto, 0)) AS totalCogs
FROM PedidoLinha pl
    LEFT JOIN pl.unidadeMedida um
GROUP BY pl.produto, pl.dataPedido, pl.unidadeMedida

As a result, the cross join statement is replaced by a left outer join in the final SQL executed in the database and is not included in the where clause.

Any insights as to why this design decision (use of cross join + where statement instead of left joins) was made in Hibernate would be appreciated.

JPA Projections query with GROUP BY not working with null fields

1 Answers1