Incorrect SQL for `AggregateReference` with composite id in `findBy...In`

[chrshnv]

Incorrect SQL for AggregateReference with composite id in findBy...In

Summary

Derived query methods using AggregateReference with a composite id generate incorrect SQL.

Instead of expanding the composite key into multiple columns, Spring Data JDBC treats it as a single column.

---

Expected SQL

SELECT "order_item"."external_id",
       "order_item"."sku",
       "order_item"."quantity"
FROM "order_item"
WHERE ("order_item"."external_id" = ? AND "order_item"."sku" = ?)
   OR ("order_item"."external_id" = ? AND "order_item"."sku" = ?)

---

Actual SQL

SELECT "order_item"."external_id",
       "order_item"."sku",
       "order_item"."quantity"
FROM "order_item"
WHERE "order_item"."product" IN (?, ?)

---

Minimal example

public record ProductId(
    @Column("external_id") UUID externalId,
    @Column("sku") String sku
) {}

@Table("order_item")
class OrderItem {

    private AggregateReference<Product, ProductId> product;

    @Column("quantity")
    private Long quantity;
}

interface OrderItemRepository extends ListCrudRepository<OrderItem, Object> {

    List<OrderItem> findByProductIn(
        Collection<AggregateReference<Product, ProductId>> products
    );
}

---

Reproduction

List<AggregateReference<Product, ProductId>> query =
    List.of(AggregateReference.to(productId));

orderItemRepository.findByProductIn(query);

---

Expected behavior

Spring Data JDBC should expand composite ids into:

(external_id = ? AND sku = ?)

instead of:

product IN (?)

[mp911de]

Thank you for your issue report. We appreciate community contributions, but we do need them to meet a reasonable baseline for consideration.

Tip

This means:

• Expressing your intent with a concise and reasonable description
• Providing what situation you've encountered, describing your setup, ideally with a reproducer
  provided as GitHub repository, and what you would expect

When issues require significant effort from our side to initially understand your request, we have to deprioritize or even reject them in favor of tickets that are ready for discussion as we have to be mindful of our limited time, and we need to focus it where it has the most impact. If you would like us to spend some time working on this issue, please spend some time making this a substantial and quality ticket description.

When describing your concern, please focus on the aspects that matter. There's no need to duplicate parts of our code in the description or use excessive summarization or bullet lists. Instead, use rather links if you want to point out subtle details that matter in the specific context. Avoid conflicting requests such as asking for guidance and then answering questions yourself, as such descriptions neglect raising a ticket in the first place.

Important

Help us understand:

• Your reasoning for the approach you've chosen
• Trade-offs you considered
• Any areas where you're uncertain and would value guidance

Concise, thoughtful explanations of your decision-making process are far more valuable than detailed restatements of the code.

If you would like us to spend some time helping you to diagnose the problem, please spend some time describing it and, ideally, providing a minimal yet complete sample that reproduces the problem.
You can share it with us by pushing it to a separate repository on GitHub or by zipping it up and attaching it to this issue.

Please also consider #2113 that has been fixed in version 4.0.4. Since there's no version detail, it's hard to tell from all the surrounding noise in this ticket against which version you've tested.

[chrshnv]

Minimal sample
src/test/java/ltd/chrshnv/spring_data_jdbc_2276/SpringDataJdbc2276ApplicationTests.java

Spring Data JDBC 4.0.5