FetchStrategy.SUBSELECT Issues

[morphace]

Hi!

I intend to use FetchStrategy.SUBSELECT for certain collections, but it doesn't work as expected.

Standard mapping

Entity:

    @JoinColumn(name = "work_order_id")
    @OneToMany(cascade = [CascadeType.ALL], orphanRemoval = true)
    override var timeRecording: MutableList<TimeRecordingEntity> = mutableListOf(),

View:

    @get:Mapping(fetch = FetchStrategy.SUBSELECT)
    abstract val timeRecording: MutableList<TimeRecordingView>

Correlated

Entity:

    @ElementCollection
    var successorIds: MutableList<String> = mutableListOf()

View:

    @get:MappingCorrelatedSimple(
        correlationBasis = "successorIds",
        correlated = WorkOrderEntity::class,
        correlationExpression = "id IN correlationKey",
        fetch = FetchStrategy.SUBSELECT
    )
    abstract val successors: List<WorkOrderSiblingView>

I can see that the subqueries are executed properly and return the correct rows.

However the collections (we use Kotlin) stay empty.

Is that supposed to work? Any idea?

Thanks!

Version: 1.6.11 JPA-Provider: Spring Data JPA DBMS: PostgreSQL/CockroachDB Application Server: Spring Boot

[beikov]

Hi there,

this is supposed to work, but note that there is a problem with Hibernate ORM and Kotlin with respect to inheritance and how the override in Kotlin behaves.

To get consistent behavior, I would suggest you to use Hibernate ORM property mapping i.e. prefix all jakarta.persistence and Hibernate annotations with get: e.g. @get:ElementCollection etc.

[morphace]

Hi Christian,

Thanks for the hint.

I have to say that we're still on Hibernate 5.x... but I'll try.

[morphace]

Hi,

I followed your advice (set @Access(AccessType.PROPERTY) and add @get: on the entity), but the collections keep being empty...

Any other idea?

[beikov]

Can you share the entity model here? I feel like there is a simple little mistake somewhere which is easy to spot when I see the model.

Overall, the problem is that Kotlin creates a new field for every type in a hierarchy that declares a Kotlin property (even the override ones), but places annotations by default on the fields. So Hibernate sees e.g.

class Base {
  @Id
  int id;
  @Column
  String name;

  public int getId() {
    return id;
  }
  public String getName() {
    return name;
  }
}
class Sub extends Base {
  @Column
  String name;

  public String getName() {
    return name;
  }
}

Hibernate ORM then decides that Base#name is the field to store information for the persistent attribute named name, but with Kotlin you can only ever see null by calling Sub#getName(), because that accesses the Sub#name field, which is never written to by Hibernate ORM.

Blaze-Persistence relies on the information that Hibernate ORM provides, so this weird Kotlin behavior results in the same problem there.

[morphace]

I need to say that when I use MULTISET instead of SUBSELECT, everything works perfectly (except that CockroachDB handles the subquery inefficitently, because it's performing a FULL SCAN for the subquery, but that's another story and that's I want to switch to SUBSELECT.

My classes:

@Entity(name = "WorkOrder")
@Table(indexes = [
    Index(columnList = "locationId"),
    Index(columnList = "manufacturing_order_id"),
    Index(columnList = "locationId, status"),
    Index(columnList = "plannedStart, plannedEnd"),
    Index(columnList = "scheduledStart, scheduledEnd"),
    Index(columnList = "plannedEnd, status")
])
@TypeDef(name = "jsonb", typeClass = JsonBinaryType::class)
class WorkOrderEntity(
    override val id: String,

    override var sequence: Int = 0,
    override var title: String = "",
    override var routingOperationId: String? = null,
    override var number: String,
    override var code: String? = null,
    override var description: String? = null,
    override var plannedQuantity: Double = 0.0,
    override var actualQuantity: Double = 0.0,
    override var setupTime: Double = 0.0,
    override var operationTime: Double = 0.0,
    override var waitTimeAfter: Double = 0.0,
    override var takeOver: Boolean = false,

    @Enumerated(EnumType.STRING)
    override var status: WorkOrderStatus = WorkOrderStatus.NONE,

    override var locationId: String,

    override var plannedStart: LocalDateTime? = null,
    override var plannedEnd: LocalDateTime? = null,

    override var scheduledStart: LocalDateTime? = null,
    override var scheduledEnd: LocalDateTime? = null,

    override var actualStart: LocalDateTime? = null,
    override var actualEnd: LocalDateTime? = null,

    @ManyToOne
    @JoinColumn(name = "manufacturing_order_id")
    override var manufacturingOrder: ManufacturingOrderEntity,

    @JoinColumn(name = "work_order_id")
    @OneToMany(cascade = [CascadeType.ALL], orphanRemoval = true)
    override var timeRecording: MutableList<TimeRecordingEntity> = mutableListOf(),

    @ElementCollection
    var successorIds: MutableList<String> = mutableListOf(),

    @ElementCollection
    var predecessorIds: MutableList<String> = mutableListOf(),

    @JoinColumn(name = "related_id")
    @OneToMany(cascade = [CascadeType.ALL], orphanRemoval = true)
    override var messages: MutableList<MessageEntity> = mutableListOf(),

    override var comment: String? = null,

    override var pinned: Boolean = false,
    override var parallelism: Int = 1
): WorkOrder, BaseEntity(id) {
    fun consumedMaterials(): List<MaterialConsumption> {
        return manufacturingOrder.consumedMaterial
    }
}

@EntityView(WorkOrderEntity::class)
abstract class WorkOrderView {
    @get:IdMapping
    abstract val id: String
    abstract val taskId: String?
    abstract val sequence: Int
    abstract val title: String
    abstract val number: String?
    abstract val code: String?
    abstract val description: String?
    abstract val plannedQuantity: Double
    abstract val actualQuantity: Double
    abstract val setupTime: Double
    abstract val operationTime: Double
    abstract val waitTimeAfter: Double
    abstract val takeOver: Boolean
    abstract val status: WorkOrderStatus
    abstract val locationId: String?
    abstract val plannedStart: LocalDateTime?
    abstract val plannedEnd: LocalDateTime?
    abstract val scheduledStart: LocalDateTime?
    abstract val scheduledEnd: LocalDateTime?
    abstract val actualStart: LocalDateTime?
    abstract val actualEnd: LocalDateTime?
    abstract val comment: String?
    abstract val pinned: Boolean
    abstract val parallelism: Int

    abstract val manufacturingOrder: ManufacturingOrderSlimView

    @get:Mapping(fetch = FetchStrategy.MULTISET)
    abstract val timeRecording: MutableList<TimeRecordingView>

    @get:Mapping(fetch = FetchStrategy.MULTISET)
    abstract val messages: MutableList<MessageView>

    private val workload: Double
        get() = computeWorkload(plannedQuantity, setupTime, operationTime)

    @get:MappingCorrelatedSimple(
        correlationBasis = "successorIds",
        correlated = WorkOrderEntity::class,
        correlationExpression = "id IN correlationKey",
        fetch = FetchStrategy.MULTISET
    )
    abstract val successors: List<WorkOrderSiblingView>
    }
}

[morphace]

As I said - I tried to switch to AccessType.PROPERTY but got the same result when I changed to SUBSELECT.

[beikov]

Can you inspect the entity view object that contains the collections to see if there are other same named collections in the object which are properly initialized? I have the feeling that this abstract val Kotlin stuff might produce something odd in terms of JVM class files. Unfortunately, I am no Kotlin expert, so it's hard for me to say what goes wrong here. Have you tried using a Kotlin interface instead yet?

[morphace]

Thanks for your support - I'll inspect the objects. I'll check if I can switch to an interface - I'll keep you updated.

[morphace]

I've inspected the entity view object - there's no other same named collection. And I've tried to use an interface instead of an abstract class without success. I'll try to debug somehow what's happening to hopefully find the reason why it's not working...

[morphace]

But somehow it's not really logic - why does it work for MULTISET, but not for SUBSELECT (without any further changes), collections are the same...

[morphace]

I've debugged the SQL statements including the bound parameters - interstingly the parameters are set to null!

2024-04-12 16:52:50.487 DEBUG 92837 --- [ueryProcessor-6] org.hibernate.SQL                        : select timerecord3_.id as col_0_0_, timerecord3_.end_time as col_1_0_, timerecord3_.net_minutes as col_2_0_, timerecord3_.quantity as col_3_0_, timerecord3_.reason_code as col_4_0_, timerecord3_.resource_id as col_5_0_, timerecord3_.start_time as col_6_0_, workordere0_.id as col_7_0_, workordere0_.id as col_8_0_ from work_order workordere0_ left outer join manufacturing_order manufactur1_ on workordere0_.manufacturing_order_id=manufactur1_.id inner join work_order workordere2_ on (workordere2_.id=workordere0_.id) inner join time_recording timerecord3_ on workordere2_.id=timerecord3_.work_order_id where (workordere0_.location_id in (?)) and (workordere0_.status in (?))
2024-04-12 16:52:50.487 TRACE 92837 --- [ueryProcessor-6] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [null]
2024-04-12 16:52:50.487 TRACE 92837 --- [ueryProcessor-6] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [null]

[beikov]

I've debugged the SQL statements including the bound parameters - interstingly the parameters are set to null!

This is a bit more involved then. Could you try to create a reproducer application for this? For example based on one of our quickstarts?

[morphace]

Ok, I'll try.

Question: could you point me to the spot where those parameters are injected to the query, so I can try to debug myself?

[beikov]

The source query is copied in AbstractCorrelatedSubselectTupleTransformer#prepare to then construct the subselect query.

[morphace]

Ok, I tried to chase the issue by debugging, which is not easy due to the complexity. But doing that at least I've got a clue where the problem probably is.

What I understood is that BP is taking the root level's query parameters in order to use them for the correlated queries.

However while debugging I could not find, where the values of those parameters would be transferred to the lower levels (AbstractCorrelatedSubselectTupleTransformer). The cb's parameter values are empty by the time of the instantiation of the transformer.

I need to say that I used entity view repositories so far and from what I understood the query method's parameters should be taken and used in the correlated queries. However somehow those values don't make it to the JpaParameters (unlike optional parameters for example).

Consequently, which supports my theory, when I use the criteria builder rather than the entity view repo, the queris function as they are supposed to.

I hope this analysis gives you a hint about what to check?

[beikov]

That's very interesting. Thanks for the analysis. I'd still need a simple reproducer for debugging to see where it goes wrong, but with your description, I'm pretty sure this should be easily possible.

In the meantime, I guess you can workaround this by constructing the query "by hand" i.e. with the Blaze-Persistence CriteriaBuilder API?

[morphace]

Yes - i constructed the queries "by hand" and that makes it work...

[beikov]

Thanks for confirming and sorry for the trouble.

[morphace]

Don't worry... I'm doing some rework, not too much.