nativeDistinct doesn't work when we include orderBy or take and limit in the queries

[meotimdihia]

Bug description

Native DISTINCT doesn't work when we used orderBy

          await fastify.prisma.user_info.findMany({
            distinct: ["role"],

            select: {
              role: true
            },
            orderBy: {
              role: {
                sort: "desc",
                nulls: "last"
              }
            }
          })

or use take and limit

          await fastify.prisma.user_info.findMany({
            distinct: ["role"],
            take: req.query.limit,
            skip: req.query.page * req.query.limit,
            select: {
              role: true
            }
          })

The raw queries when used these queries:

SELECT "public"."user_info"."id", "public"."user_info"."role"::text FROM "public"."user_info" WHERE 1=1 ORDER BY "public"."user_info"."id" ASC OFFSET 0

It looks like we have to work around this problem by using subqueries: https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by

How to reproduce

.

Expected behavior

No response

Prisma information

I posted them.

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: 20

Prisma Version

    "prisma": "5.8.1",

[Druue]

Hey, @meotimdihia, this is work that is still in progress and as such, this is expected behaviour currently.

You can find the PR in our engines here #4525 if you would like to track progress