How to Find Missing @ManyToOne Relationships With JPQL, Compound Primary/Foreign Keys, and MySQL Dialect

Question

I'm trying to do something that sounds simple, which is to query a repository of employees for the root managers, where a root manager is an employee who do not have manager. There are two things complicating this simple problem:

1. The underlying employee table has a composite primary key
2. The relationship I need to refer to the "manager" employee has to share an element of that primary key, very similar to this question

I believe I'm on the right road to the answer, given that my unit tests pass with an embedded H2 database and corresponding H2 dialect. However, when I switch to either the MySQL or MariaDB dialects for production, I get what seems (to me at least) to be invalid SQL, and the MySQL database throws an exception.

Here's the (simplified) employees table, and unfortunately, I cannot change its design:

+-------------------------------+
|           employees           |
+------------+-------------+----+
| id         | varchar(36) | PK |
| tenant_id  | char(15)    | PK |
| manager_id | varchar(36) |    |
+------------+-------------+----+

Here's the entity:

@Entity
@Table(name = "employees")
@IdClass(EmployeeTenantKey.class)
public class Employee {

    static final String ID_DEF = "varchar(36)";
    static final String TID_DEF = "char(15) default 'default'";

    @Id
    @Column(name = "id", nullable = false, updatable = false, columnDefinition = ID_DEF)
    private String id;

    @Id
    @Column(name = "tenant_id", nullable = false, updatable = false, columnDefinition = TID_DEF)
    private String tenantId;

    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumnsOrFormulas(value = {
            @JoinColumnOrFormula(formula = @JoinFormula(value = "tenant_id", referencedColumnName = "tenant_id")),
            @JoinColumnOrFormula(column = @JoinColumn(name = "manager_id", referencedColumnName = "id",
                    columnDefinition = ID_DEF, foreignKey = @ForeignKey(ConstraintMode.NO_CONSTRAINT)))
    })
    private Employee manager;

    ...
}

And finally, the spring data repository with the JPQL to find the root managers:

public interface EmployeeRepository extends JpaRepository<Employee, EmployeeTenantKey> {

    @Query("select rootManager from Employee rootManager " +
            "where not exists (" +
            "   select employee from Employee employee " +
            "   where rootManager.manager = employee" +
            ")")
    List<Employee> findRootManagers();

}

When the dialect is org.hibernate.dialect.H2Dialect, here is the SQL that is generated:

select * from employees employee0_ 
where not exists (
  select (employee1_.id, employee1_.tenant_id) from employees employee1_ 
  where employee0_.manager_id=employee1_.id and employee0_.tenant_id=employee1_.tenant_id
)

If I change the dialect to either org.hibernate.dialect.MySQL8Dialect or org.hibernate.dialect.MariaDB103Dialect, notice the change in the second where clause:

select * from employees employee0_ 
where not exists (
  select (employee1_.id, employee1_.tenant_id) from employees employee1_ 
  where (employee0_.manager_id, employee0_.tenant_id)=(employee1_.id, employee1_.tenant_id)
)

While the H2 database engine will accept this SQL in my unit test (in spite of the MySQL dialects), the MySQL database engine (via the v8.0.21 Connector/J) throws the following error:

java.sql.SQLException: Operand should contain 1 column(s)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) ~[mysql-connector-java-8.0.21.jar:8.0.21]

Is this a bug in the Hibernate dialects? Is there a better way to solve this problem?

If you'd like to see a working example, I have the simplified source checked in here:

https://github.com/chaserb/foreign-key-dereference

After cloning and building with Maven 3, you'll see the generated "select ..." SQL immediately above the only unit test in the build. Out of the gate, it's using the H2 dialect. To change this, update src/main/resources/application-test.properties then re-run the Maven build. You'll see the difference in the generated SQL.

NOTE: I'm able to get the desired result with a native query. The logic of a native query is more straight-forward to the nature of the problem, since I'm able to deal with the foreign key elements directly--a feature that I don't know how to do in JPQL:

public interface EmployeeRepository extends JpaRepository<Employee, EmployeeTenantKey> {

    @Query(value = "select * from employees emp " +
            "where emp.manager_id is null or emp.manager_id = ''", nativeQuery = true)
    List<Employee> findRootManagers();

}

However, I'm trying to avoid native queries if at all possible.

Answer 1

Is this a bug in the Hibernate dialects?

It sure looks that way. Does it work if you replace the inner query's select employee with select employee.id, for instance?

Is there a better way to solve this problem?

Well, you could repeat the mapping for manager_id as a simple column:

@Column(name = "manager_id", insertable = false, updatable = false)
private String managerId;

You'll then be able to use managerId in JPQL queries.

Side note #1: Initially I thought SELECT e FROM Employee e WHERE e.manager IS NULL was the answer to your question, but then I tested it and it got translated to where (employee.manager_id is null) AND (employee.tenant_id is null), which feels rather stupid of Hibernate - it seems logical to me that OR should be used instead.

Side note #2: you might want to take a look at this approach to multitenancy, it might enable you to use more natural mapping scheme.