ManyToMany generates non optimal query when getting only ids

[ikss]

Description

I've noticed that ebean generates query which includes left join when trying to get only ids from ManyToMany relation.
Database - PostgreSQL.

Expected behavior

We expect execution of two queries:

1. firstly we select people:

select t0.id, t0.first_name, t0.last_name, t0.address, t0.city, t0.country, t0.birth_date, t0.created_at, t0.modified_at from person t0 order by t0.id desc limit 100;

2. after that we use subquery to fetch related ids:

select t0.person_id, t0.tag_id from person_tag t0 where (t0.person_id) = any(?);

Actual behavior

Actual behavior differs in second query, ebean generates it using left join

select int_.person_id, t0.id from tag t0 left join person_tag int_ on int_.tag_id = t0.id  where (int_.person_id) = any(?);

And this query can drastically affect performance on big tables.

Steps to reproduce

Consider we havethe following entities:

@Entity
class PersonEntity {
  @Id
  var id: Long? = null
  var firstName: String? = null
  var lastName: String? = null
  var address: String? = null
  var city: String? = null
  var country: String? = null
  var birthDate: LocalDate? = null
 
  @CreatedTimestamp
  @Column
  lateinit var createdAt: Timestamp
    protected set

  @UpdatedTimestamp
  @Version
  lateinit var modifiedAt: Timestamp
    protected set

  @ManyToMany
  var tags: List<TagEntity> = emptyList()
}

and

@Entity
class TagEntity {
  @Id
  var id: Long? = null

  @Column(nullable = false, updatable = false)
  @WhoCreated
  val createdBy: Long? = 0L

  @CreatedTimestamp
  lateinit var createdAt: Timestamp
    protected set

  @UpdatedTimestamp
  @Version
  lateinit var modifiedAt: Timestamp
    protected set

  @WhoModified
  val modifiedBy: Long? = 0L

  @ManyToMany(mappedBy = "tags")
  var persons: List<PersonEntity>? = null
}

And we're trying to fetch all fields from PersonEntity and only id field from TagEntity. For this we can use following query:

server.find(PersonEntity::class.java)
  .order("id DESC")
  .setMaxRows(100)
  .fetch("tags", "id", FetchConfig().query(100))
  .findPagedList()

Execution of this query will produce following log

DEBUG io.ebean.SQL - txn[1009] select t0.id, t0.first_name, t0.last_name, t0.address, t0.city, t0.country, t0.birth_date, t0.created_at, t0.modified_at from person t0 order by t0.id desc limit 100; --bind()
DEBUG io.ebean.SUM - txn[1009] FindMany type[person] origin[DtBvNV.A.A] exeMicros[9797] rows[4] predicates[] bind[]

DEBUG io.ebean.SQL - txn[1009] select int_.person_id, t0.id from tag t0 left join person_tag int_ on int_.tag_id = t0.id  where (int_.person_id) = any(?); --bind(Array[4]={5,3,2,1})
DEBUG io.ebean.SUM - txn[1009] FindMany mode[+query] type[tag] origin[DtBvNV.A.A] load[path:tags size:4] exeMicros[13778] rows[1] predicates[(int_.person_id) = any(?)] bind[Array[4]={5,3,2,1}]