Prisma1: [Mongo] Bug in "orderby" when using "first" and "where" on a relation

[AhmadElsagheer]

Describe the bug
I have the following two models:

type Blog {
  id: ID! @id
  title: String!
  score: Int!
  labels: [Label!]! @relation(name: "BlogLabels", link: INLINE)
}
type Label {
  id: ID! @id
  text: String! @unique
}

When I tried to using pagination, sorting and filtering on labels, the returned results are not ordered as expected.

There is something weird that happens when I do the query using a filter on a relation similar to the aggregation count bug #4422 . The bug occured with mongo connector. I tried it using MySQL connector and It worked fine.

To reproduce

1. Create the two models above.
2. Create a label

mutation {
  createLabel(data: {
    text: "x"
  }) {
    text
  }
}

1. Create three blogs with the label x & three different scores in ascending order (blog_1: 10, blog_2: 20, blog_3: 30)

mutation {
  createBlog(data: {
    title: "blog_1",
   score: 10,
    labels: {
      connect: {
        text: "x"
      }
    }
  }) {
    title
  }
}

1. Do the query below

query {
  blogs(first: 2, orderBy: score_DESC) {
    title
    score
  }
}

The output will be (as expected)

{
  "data": {
    "blogs": [
      {
        "title": "blog_3",
        "score": 30
      },
      {
        "title": "blog_2",
        "score": 20
      }
    ]
  }
}

1. Do the query below

query {
  blogs(first: 2, orderBy: score_DESC, where:{
    labels_some: {
      text: "x"
    }
  }) {
    title
    score
  }
}

The output will be (not expected)

{
  "data": {
    "blogs": [
      {
        "title": "blog_1",
        "score": 10
      },
      {
        "title": "blog_2",
        "score": 20
      }
    ]
  }
}

Expected behavior

{
  "data": {
    "blogs": [
      {
        "title": "blog_3",
        "score": 30
      },
      {
        "title": "blog_2",
        "score": 20
      }
    ]
  }
}

Versions:

• Connector: [MongoDB]
• Prisma Server: [1.31.0]
• prisma CLI: [prisma/1.31.0 (linux-x64) node-v10.15.3]
• OS: [Ubuntu 18.04]

[KumarAbhirup]

I think that issue is related to this issue. What do you think? Try having scores of 1, 2 and 3 to understand the bug better to check if it's the same bug that I am experiencing!
🐞 BUG: orderBy Int type in connection query sorts by only the indifferent values!

[AhmadElsagheer]

@KumarAbhirup It is quite different. When I used first and orderBy, it works fine even if the values are not unique.

For example, when I inserted 4 records with scores in this order [10, 20, 30, 10], then I queried first: 2 and orderBy: score_ASC, I got two records [10, 10].

But when I added where: { labels_some: { text: "x" } } to the query, I go two records [10, 20].

In addition, the scenario I described above didn't work on MongoDB connector but worked fine on MySQL connector.

I doubt that the orderBy operation is ignored when a relation filterer exists which explains the unexpected results I got.

[pantharshit00]

I can confirm this

cc @do4gr

[do4gr]

Thanks for the excellent reproduction. We have a fix and will release it to master soon.