HQL query returns different results comparing to same logic criteria query

Question

I need some HQL guru to help me with a complicated query. My mappings are AccountingDocument:

AccountingDocument extends Document

@ManyToOne
@JoinColumn(name = "gestiune_id", nullable = false)
private Gestiune gestiune;

@OneToMany(fetch = FetchType.LAZY, mappedBy = "accDoc", cascade = CascadeType.ALL)
private Set<Operatiune> operatiuni = new HashSet<>();

Document:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Document

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToOne
@JoinColumn(name = "partner_id", nullable = true)
private Partner partner;

@Column(columnDefinition = "text")
private String name;

@Enumerated(EnumType.STRING)
@Column(nullable = false)
private TipDoc tipDoc;

@Column(precision = 16, scale = 2, nullable = true)
private BigDecimal total;

Operation:

@Entity
public class Operatiune

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToOne
@JoinColumn(name = "accounting_doc_id", nullable = false)
private AccountingDocument accDoc;

@Enumerated(EnumType.STRING)
@Column(nullable = false)
private TipOp tipOp;

@Column(nullable = false)
private String barcode; // taken from product
private String name; // taken from product
private String uom; // taken from product

@Column(precision = 12, scale = 2, nullable = false)
private BigDecimal valoareVanzareFaraTVA;

@Column(precision = 10, scale = 2, nullable = false)
private BigDecimal valoareVanzareTVA;

Partner:

@Entity
public class Partner
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(nullable = false, unique = true)
private String name;

@OneToMany(fetch = FetchType.LAZY, mappedBy = "partner")
private List<Document> documents = new ArrayList<>();

Basically the Operation class is a sale operation and AccountingDocument class can be both a sale document(example: Invoice) or a cash document(example: Receipt).

For Invoices the AccountingDocument.total is null. In this case we add the total from all the operations. Operation total=Operatiune.valoareVanzareFaraTVA+Operatiune.valoareVanzareTVA

For Receipts the operatiuni set is empty. In this case AccountingDocument.total is filled.

Requirement: Return all Partners(clients) which haven't fully paid us. totalCashed < totalSold => is not fully paid

I have created a CriteriaQuery+Java filtering for this but it is too slow so I am trying to convert it to HQL.

Expectation: CriteriaQuery+Java filtering returns same result as HQL.

Result: CriteriaQuery+Java filtering returns 29 partners while HQL returns 6 partners.

CriteriaQuery+Java filtering (note: 'incasat' means 'cashed'):

final ImmutableList<Partner> unpaidPartners = allUnpaidPartners(); // 29


public ImmutableList<Partner> allUnpaidPartners() {
    final CriteriaBuilder cb = em.getCriteriaBuilder();
    final CriteriaQuery<Partner> cq = cb.createQuery(Partner.class);
    final Root<Partner> rootEntry = cq.from(Partner.class);
    final CriteriaQuery<Partner> all = cq.select(rootEntry);
    final TypedQuery<Partner> allQuery = em.createQuery(all);
    final List<Partner> allPartners = allQuery.getResultList();

    return allPartners.stream()
            .filter(partner -> globalIsMatch(partner.getName(), Partner.L1_L2, TextFilterMethod.NOT_EQUALS))
            .filter(partner -> globalIsMatch(partner.getName(), Partner.OP_INTERNA, TextFilterMethod.NOT_EQUALS))
            .filter(partner -> globalIsMatch(partner.getName(), Partner.STAT_PLATA, TextFilterMethod.NOT_EQUALS))
            .filter(partner -> globalIsMatch(partner.getName(), Partner.MARFA, TextFilterMethod.NOT_EQUALS))
            .filter(partner -> globalIsMatch(partner.getName(), Partner.CARD_NAME, TextFilterMethod.NOT_EQUALS))
            .filter(partner -> globalIsMatch(partner.getName(), Partner.STANDARD_PARTNER_NAME, TextFilterMethod.NOT_EQUALS))
            .filter(VanzariBean::isNotPaid)
            .sorted(Comparator.comparing(Partner::getName))
            .collect(toImmutableList());
}

protected static boolean isNotPaid(final Partner partner)
{
    final Optional<BigDecimal> totalIncasat = partner.getDocuments().stream()
            .filter(AccountingDocument.class::isInstance)
            .filter(doc -> doc.getTipDoc().equals(TipDoc.INCASARE))
            .map(Document::getTotal)
            .collect(Collectors.reducing(BigDecimal::add));

    final Optional<BigDecimal> totalSold = partner.getDocuments().stream()
            .filter(AccountingDocument.class::isInstance)
            .filter(doc -> doc.getTipDoc().equals(TipDoc.VANZARE))
            .map(Document::getTotal)
            .collect(Collectors.reducing(BigDecimal::add));

    return totalIncasat.orElse(BigDecimal.ZERO).compareTo(totalSold.orElse(BigDecimal.ZERO)) < 0;
}

public BigDecimal Document.getTotal()
{
    return total;
}

@Override
public BigDecimal AccountingDocument.getTotal()
{
    final BigDecimal total = super.getTotal();

    if (total == null)
        return add(getVanzareTotalFaraTva(), getVanzareTotalTva());

    return total;
}

public BigDecimal AccountingDocument.getVanzareTotalFaraTva()
{
    return getOperatiuni().stream()
            .map(Operatiune::getValoareVanzareFaraTVA)
            .reduce(BigDecimal::add)
            .orElse(BigDecimal.ZERO);
}

public BigDecimal AccountingDocument.getVanzareTotalTva()
{
    return getOperatiuni().stream()
            .map(Operatiune::getValoareVanzareTVA)
            .reduce(BigDecimal::add)
            .orElse(BigDecimal.ZERO);
}

HQL('incasat'='cashed', 'vandut'='sold'):

final StringBuilder sb = new StringBuilder();
    sb.append("SELECT p FROM Partner p");
    sb.append(" LEFT JOIN AccountingDocument incasat WITH incasat.partner = p AND incasat.tipDoc = :qIncasatDoc");
    sb.append(" LEFT JOIN AccountingDocument vandut WITH vandut.partner = p AND vandut.tipDoc = :qVandutDoc");
    sb.append(" LEFT JOIN vandut.operatiuni opVandut");
    sb.append(" WHERE p.name != :qL1L2 AND p.name != :qOpInterna AND p.name != :qStatPlata AND p.name != :qMarfa AND p.name != :qCard AND p.name != :qStandard");
    // totalIncasat < totalSold => is not fully paid
    sb.append(" AND (select COALESCE(SUM(doc.total), 0) from AccountingDocument doc WHERE doc=incasat) < ")
        .append("(select COALESCE(SUM(op.valoareVanzareFaraTVA)+SUM(op.valoareVanzareTVA), 0) from Operatiune op WHERE op=opVandut)");
    sb.append(" GROUP BY p");

    final Query query = em.createQuery(sb.toString());
    query.setParameter("qL1L2", Partner.L1_L2);
    query.setParameter("qOpInterna", Partner.OP_INTERNA);
    query.setParameter("qStatPlata", Partner.STAT_PLATA);
    query.setParameter("qMarfa", Partner.MARFA);
    query.setParameter("qCard", Partner.CARD_NAME);
    query.setParameter("qStandard", Partner.STANDARD_PARTNER_NAME);

    query.setParameter("qIncasatDoc", TipDoc.INCASARE);
    query.setParameter("qVandutDoc", TipDoc.VANZARE);

    final List result = query.getResultList(); // 6 partners

score 0 · Accepted Answer · answered Mar 07 '20 at 07:03

Okay, so it seems the logic of the HQL was inherently wrong. It was actually checking if any cashed total of any AccDoc of that Partner is smaller than any sold total of AccDocs of that Partner, instead of actually comparing the cashed total and sold total of all AccDocs of each Partner.

So, because subqueries in the JOIN clause are not supported in HQL, I had to change to a native query. So the correct query is:

SELECT * FROM Partner p 
    LEFT JOIN 
    (
        SELECT doc.partner_id, SUM(doc.total) incasat
        FROM Document doc
        INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id 
        WHERE doc.tipDoc = 'INCASARE'
        GROUP BY doc.partner_id
    ) i ON p.id = i.partner_id
    INNER JOIN 
    (
        SELECT doc.partner_id, SUM(op.valoareVanzareFaraTVA)+SUM(op.valoareVanzareTVA) vandut
        FROM Document doc
        INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id 
        INNER JOIN Operatiune op ON op.accounting_doc_id = accDoc.id
        WHERE doc.tipDoc = 'VANZARE'
        GROUP BY doc.partner_id
    ) v ON p.id = v.partner_id
    WHERE p.name != 'L1<->L2' AND p.name != 'OP INTERNA' AND p.name != 'STAT DE PLATA' AND p.name != 'MARFA' AND p.name != 'CARD INCASARE' AND p.name != 'STANDARD' 
    AND COALESCE(i.incasat, 0) < COALESCE(v.vandut, 0)

And in Java this would be:

final StringBuilder sb = new StringBuilder();
    sb.append("SELECT * FROM Partner p").append(NEWLINE);
    sb.append(" LEFT JOIN ").append(NEWLINE)
        .append("(").append(NEWLINE)
        .append("SELECT doc.partner_id, SUM(doc.total) incasat ").append(NEWLINE)
        .append("FROM Document doc ").append(NEWLINE)
        .append("INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id ").append(NEWLINE)
        .append("WHERE doc.tipDoc = :qIncasatDoc ").append(NEWLINE)
        .append("GROUP BY doc.partner_id ").append(NEWLINE)
        .append(") i ON p.id = i.partner_id").append(NEWLINE);
    sb.append(" INNER JOIN ").append(NEWLINE)
        .append("(").append(NEWLINE)
        .append("SELECT doc.partner_id, SUM(op.valoareVanzareFaraTVA)+SUM(op.valoareVanzareTVA) vandut ").append(NEWLINE)
        .append("FROM Document doc ").append(NEWLINE)
        .append("INNER JOIN AccountingDocument accDoc ON accDoc.id = doc.id ").append(NEWLINE)
        .append("INNER JOIN Operatiune op ON op.accounting_doc_id = accDoc.id ").append(NEWLINE)
        .append("WHERE doc.tipDoc = :qVandutDoc ").append(NEWLINE)
        .append("GROUP BY doc.partner_id ").append(NEWLINE)
        .append(") v ON p.id = v.partner_id").append(NEWLINE);
    sb.append(" WHERE p.name != :qL1L2 AND p.name != :qOpInterna AND p.name != :qStatPlata AND p.name != :qMarfa AND p.name != :qCard AND p.name != :qStandard").append(NEWLINE);
    // totalIncasat < totalSold => is not fully paid
    sb.append(" AND COALESCE(i.incasat, 0) < COALESCE(v.vandut, 0)");

    final Query query = em.createNativeQuery(sb.toString(), Partner.class);
    query.setParameter("qL1L2", Partner.L1_L2);
    query.setParameter("qOpInterna", Partner.OP_INTERNA);
    query.setParameter("qStatPlata", Partner.STAT_PLATA);
    query.setParameter("qMarfa", Partner.MARFA);
    query.setParameter("qCard", Partner.CARD_NAME);
    query.setParameter("qStandard", Partner.STANDARD_PARTNER_NAME);

    query.setParameter("qIncasatDoc", TipDoc.INCASARE.toString());
    query.setParameter("qVandutDoc", TipDoc.VANZARE.toString());

Small tip here! em.createNativeQuery returns a non-generic query so if you want the result as a stream and you want to process that stream and cast the elements you need to do the following trick:

final Stream<?> resultStream = query.getResultStream();

and then you can process it:

resultStream
            .map(Partner.class::cast)
            .flatMap(Partner::getDocumentsStream)

HQL query returns different results comparing to same logic criteria query

1 Answers1