Why multiple inner SQL queries are executed on List Items with relations?

[MurzNN]

In this issue #6035 there is an example of Items query with relations, that executes separate query for each relation field on resulted item:

query {
  allPosts {
    id, author {id, name}
  }
}

Queries log:

prisma:query SELECT `main`.`Post`.`id`, `main`.`Post`.`title`, `main`.`Post`.`status`, `main`.`Post`.`content`, `main`.`Post`.`publishDate`, `main`.`Post`.`author` FROM `main`.`Post` WHERE 1=1 LIMIT ? OFFSET ?
prisma:query SELECT 1
prisma:query SELECT `main`.`Author`.`id`, `main`.`Author`.`name`, `main`.`Author`.`email` FROM `main`.`Author` WHERE (`main`.`Author`.`id`) IN (SELECT `t0`.`id` FROM `main`.`Author` AS `t0` INNER JOIN `main`.`Post` AS `j0` ON (`j0`.`author`) = (`t0`.`id`) WHERE (`j0`.`id` = ? AND `t0`.`id` IS NOT NULL)) LIMIT ? OFFSET ?
prisma:query SELECT `main`.`Author`.`id`, `main`.`Author`.`name`, `main`.`Author`.`email` FROM `main`.`Author` WHERE (`main`.`Author`.`id`) IN (SELECT `t0`.`id` FROM `main`.`Author` AS `t0` INNER JOIN `main`.`Post` AS `j0` ON (`j0`.`author`) = (`t0`.`id`) WHERE (`j0`.`id` = ? AND `t0`.`id` IS NOT NULL)) LIMIT ? OFFSET ?
prisma:query SELECT `main`.`Author`.`id`, `main`.`Author`.`name`, `main`.`Author`.`email` FROM `main`.`Author` WHERE (`main`.`Author`.`id`) IN (SELECT `t0`.`id` FROM `main`.`Author` AS `t0` INNER JOIN `main`.`Post` AS `j0` ON (`j0`.`author`) = (`t0`.`id`) WHERE (`j0`.`id` = ? AND `t0`.`id` IS NOT NULL)) LIMIT ? OFFSET ?
prisma:query SELECT `main`.`Author`.`id`, `main`.`Author`.`name`, `main`.`Author`.`email` FROM `main`.`Author` WHERE (`main`.`Author`.`id`) IN (SELECT `t0`.`id` FROM `main`.`Author` AS `t0` INNER JOIN `main`.`Post` AS `j0` ON (`j0`.`author`) = (`t0`.`id`) WHERE (`j0`.`id` = ? AND `t0`.`id` IS NOT NULL)) LIMIT ? OFFSET ?

Is there any way to optimize this query to execute only one subquery, or this is a Prisma limitation or other reasons?

[molomby]

Fixed. See #6035

[MurzNN]

Hmm, this is strange output from prisma:client because real executed SQL queries count is two:

prisma:query SELECT `main`.`Post`.`id`, `main`.`Post`.`title`, `main`.`Post`.`status`, `main`.`Post`.`content`, `main`.`Post`.`publishDate`, `main`.`Post`.`author` FROM `main`.`Post` WHERE 1=1 LIMIT ? OFFSET ?
prisma:query SELECT `main`.`Author`.`id`, `main`.`Author`.`name`, `main`.`Author`.`email` FROM `main`.`Author` WHERE `main`.`Author`.`id` IN (?,?,?,?,?) LIMIT ? OFFSET ?

[dcousens]

@MurzNN, following up to this comment I am going to close/lock this discussion for now.
If you can reproduce the problem, could you please re-open this as a bug and provide reproduction steps 🙏