Prisma1: "Unknown column 'Type_Alias.id' in 'IN/ALL/ANY subquery'" for deeply nested relational filters

[marktani]

Describe the bug

Running a query with deeply nested relational filters resutls in the internal server error: "Unknown column 'Role_Alias.id' in 'IN/ALL/ANY subquery'."

To Reproduce
Steps to reproduce the behavior:

1. Deploy this datamodel:

type Role {
  id: ID! @unique
  name: String!
  systemRole: SystemRole!
  userRoles: [UserRole!]! @relation(name: "RoleUserRoles", onDelete: CASCADE)
}

type UserRole {
  id: ID! @unique
  user: User! @relation(name: "UserUserRoles")
  role: Role! @relation(name: "RoleUserRoles")
}

type User {
  id: ID! @unique
  email: String! @unique
  userRoles: [UserRole!]! @relation(name: "UserUserRoles", onDelete: CASCADE)
}

type Organisation {
  id: ID! @unique
  name: String!
  roles(organisationId: String!): [Role!]!
    @relation(name: "OrganisationRoles", onDelete: CASCADE)
}

enum SystemRole {
  CLIENT
  CLIENT_MANAGER
  ADMIN
  CONCIERGE
}

1. Run this query:

query {
  organisations(
    where: {
      roles_some: {
        userRoles_some: { user: { id_contains: "cjg0z0pw9bz9m0b269lev8vy9" } }
      }
    }
  ) {
    id
  }
}

1. You'll get an internal server error.
2. See error in logs:

{
    "key": "error/unhandled",
    "requestId": "eu1:api:cjkmcofnw06oz0b30257qr6yw",
    "clientId": "nilan-marktanner-7ea852~r-unknown-columm@dev",
    "payload": {
        "exception": "java.sql.SQLSyntaxErrorException: (conn=760153) Unknown column 'Role_Alias.id' in 'IN/ALL/ANY subquery'",
        "query": "{\n  organisations(where: {roles_some: {userRoles_some: {user: {id: \"cjg0z0pw9bz9m0b269lev8vy9\"}}}}) {\n    id\n  }\n}\n",
        "variables": "{}",
        "code": "0",
        "stack_trace": "org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:163)\\n org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:106)\\n org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:235)\\n org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:224)\\n org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:159)\\n org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:173)\\n com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\\n com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\\n com.prisma.api.connector.jdbc.database.BuilderBase.$anonfun$queryToDBIO$1(BuilderBase.scala:57)\\n com.prisma.api.connector.jdbc.database.BuilderBase.$anonfun$jooqToDBIO$1(BuilderBase.scala:87)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)\\n java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\\n java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\\n java.lang.Thread.run(Thread.java:748)",
        "message": "(conn=760153) Unknown column 'Role_Alias.id' in 'IN/ALL/ANY subquery'"
    }
}

Expected behavior

The query returns some nodes and no error.

Versions (please complete the following information):

• Prisma Server: 1.14-beta-1.

Additional context

I reproduced this using MySQL. Have not looked into Postgres yet.

[ekron]

I can replicate on Prisma Server 1.13.6 using PostgreSQL.

[mavilein]

Thanks for reporting and providing thorough descriptions! 🙏 We fixed this issue and it is available in version 1.15.2 🎉 . The build will be available in a few minutes.