medveshonok117 medveshonok117 - 14 days ago 4
SQL Question

Hibernate/JPA JPQL to wrong SQL when querying Map<String,String> field

This is my Entity configuration

@Entity
@NamedQuery(name = "Payment.findByEmail", query = "SELECT p FROM Payment p JOIN p.additionalAuthData a " +
"WHERE KEY(a) = 'email' AND VALUE(a) = ?1 AND (p.paymentType = 4 OR p.paymentType = 10)")
public class Payment {

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

@Column(name = "payment_type")
private Integer paymentType;

/** other properties, getters and setters */

@ElementCollection
@CollectionTable(name = "additional_auth_data")
@MapKeyJoinColumn(name = "id", referencedColumnName = "id")
@MapKeyColumn(name = "field")
@Column(name = "data_value")
private Map<String, String> additionalAuthData;
}


The NamedQuery findByEmail("test@example.com") generates the following SQL

select -- all fields ...
from payment payment0_ inner join additional_auth_data additional1_ on payment0_.id=additional1_.id
where
additional1_.field='email' and (select additional1_.data_value from additional_auth_data additional1_ where payment0_.id=additional1_.id)='test@example.com' and (payment0_.payment_type=4 or payment0_.payment_type=10)


which is wrong: it may work if you have only one row but it blows up otherwise. H2 complains Scalar subquery contains more than one row and PostgreSQL more than one row returned by a subquery used as an expression. In fact, query's where condition compares a scalar value ('test@example.com') with a subquery.

The correct SQL should be:

select -- all fields
from payment payment0_ inner join additional_auth_data additional1_ on payment0_.id=additional1_.id
where additional1_.field='payerEmail' and additional1_.data_value='test@example.com' and (payment0_.payment_type=4 or payment0_.payment_type=10)


Is the HSQL correct? Is there a way to instruct Hibernate to generates a clever, better SQL? Is this a Hibernate bug?

Note: Hibernate shipped with Spring Boot Starter 1.3.7.RELEASE

Edit:
Using an @Embeddable class

@ElementCollection
@JoinTable(name = "additional_auth_data", joinColumns = @JoinColumn(name = "id"))
@MapKeyColumn(name = "field")
@Column(name = "data_value")
private Set<AdditionalData> additionalAuthData;

@Embeddable
public static class AdditionalData {
@Column(name = "field", nullable = false)
private String field;
@Column(name = "data_value")
private String dataValue;

protected AdditionalData() {
}

public AdditionalData(String field, String dataValue) {
this.field = field;
this.dataValue = dataValue;
}
/** Getters, setters; equals and hashCode on "field" */
}


@NamedQuery(name = "Payment.findByEmail", query = "SELECT p FROM Payment p JOIN p.additionalAuthData a " +
"WHERE a.field = 'email' AND a.dataValue = ?1 AND (p.paymentType = 4 OR p.paymentType = 10)")


solves the problem, and the SQL is correct, but it looks just plain wrong, like shooting a fly with a bazooka...

Answer

It generates correct SQL without value(). Use just a=?1 But I would expect is should generate it simple also with it.