Column 'orderby_.....' in on clause is ambiguous

[terion-name]

Bug description

On complex queries orderBy is not properly set in sql.

Given query:

prisma.ccStructureUnit.findMany({
  "where": {
    "AND": [
      {
        "id": {
          "contains": "ckx91vssp005r7urz2rci4ht7"
        },
        "entityId": {
          "equals": "+:Jr<|?65d"
        }
      }
    ],
    "OR": [
      {
        "id": {
          "contains": "ckx91vssq005u7urzgm1zfwse"
        },
        "updatedAt": {
          "lt": "2021-12-16T13:14:43+02:00"
        },
        "entityId": {
          "in": [
            "UBHoFKzV\"X"
          ]
        },
        "documentId": {
          "gte": "<lfwH%]TuR"
        }
      }
    ],
    "NOT": [
      {
        "updatedAt": {
          "lte": "2021-12-16T05:50:45+02:00"
        },
        "entityId": {
          "gt": "AU\"QD;yMPJ"
        },
        "document": {
          "id": {
            "equals": "ckx91vssq005t7urz8rip0mhu"
          },
          "no": {
            "notIn": [
              "96okPbabfL"
            ]
          },
          "session": {
            "notIn": [
              1570746009
            ]
          },
          "convocation": {
            "gt": "VE{w&pEYP]"
          },
          "sessionRel": {},
          "type": {},
          "status": {},
          "category": {},
          "publishedAt": {
            "lt": "2021-12-16T06:27:47+02:00"
          },
          "acceptedAt": {
            "gte": "2021-12-16T13:44:57+02:00"
          },
          "related": {},
          "relatedBy": {},
          "display": {
            "equals": "INACTIVE",
            "in": [
              "INACTIVE"
            ],
            "notIn": [
              "INACTIVE"
            ]
          },
          "translations": {},
          "responsableId": {
            "contains": "#k[+0w5O0_"
          },
          "statusId": {
            "gt": "z4xrHa=Bku"
          },
          "typeId": {
            "lt": "I&/D9(k]lE"
          }
        }
      }
    ],
    "id": {
      "in": [
        "ckx91vssq005s7urzfdzxgwck"
      ]
    },
    "entityId": {
      "contains": "jVxNJS0Ii;"
    },
    "document": {
      "id": {
        "lt": "ckx91vssr005v7urz71332d9c"
      },
      "no": {
        "endsWith": "or*p1RrsA]"
      },
      "session": {
        "lte": 9882987
      },
      "sessionRel": {
        "id": {
          "notIn": [
            "ckx91vsss005y7urzc8hee7nn"
          ]
        },
        "title": {
          "lte": ":UAN)pkgm1"
        },
        "sort": {
          "in": [
            412814542
          ]
        },
        "convocation": {},
        "updatedAt": {
          "in": [
            "2021-12-16T09:51:05+02:00"
          ]
        },
        "convocationId": {
          "contains": "6QzIbwmm}b"
        },
        "document": {}
      },
      "type": {
        "id": {
          "lt": "ckx91vssr005w7urz7aiybxg5"
        },
        "display": {
          "equals": "INACTIVE",
          "notIn": [
            "INACTIVE"
          ]
        },
        "color": {
          "equals": "N\\g]#i\"X:M"
        },
        "translations": {},
        "documents": {}
      },
      "tags": {
        "every": {},
        "some": {},
        "none": {}
      },
      "status": {
        "id": {
          "lte": "ckx91vssr005x7urz8kz51cr1"
        },
        "updatedAt": {
          "lte": "2021-12-15T23:50:58+02:00"
        },
        "display": {
          "equals": "INACTIVE",
          "in": [
            "ACTIVE"
          ],
          "notIn": [
            "ACTIVE"
          ]
        },
        "color": {
          "lt": "Ed`&T<]O}V"
        },
        "documents": {}
      },
      "updatedAt": {
        "lt": "2021-12-16T09:33:04+02:00"
      },
      "publishedAt": {
        "equals": "2021-12-15T18:42:37+02:00"
      },
      "acceptedAt": {
        "gt": "2021-12-15T17:03:18+02:00"
      },
      "expiresAt": {
        "lt": "2021-12-15T20:04:52+02:00"
      },
      "relatedBy": {
        "some": {}
      },
      "attachments": {
        "every": {}
      },
      "file": {
        "encoding": {
          "notIn": [
            "#\\j_b:tARu"
          ]
        },
        "directory": {}
      },
      "keywords": {
        "every": {},
        "some": {}
      },
      "translations": {
        "every": {},
        "none": {}
      },
      "responsableId": {
        "contains": ":6TR%P82\\>"
      },
      "responsable": {
        "updatedAt": {
          "notIn": [
            "2021-12-16T13:17:41+02:00"
          ]
        },
        "entityId": {
          "gte": "pll?VSJB?`"
        },
        "document": {},
        "documentId": {
          "endsWith": "+iQA-Ss:zq"
        }
      },
      "categoryId": {
        "lt": "=DxAJLRT)3"
      }
    },
    "documentId": {
      "lte": "W\"\\+G|@HFC"
    }
  },
  "orderBy": [
    {
      "document": {
        "category": {
          "documents": {
            "_count": "desc"
          }
        }
      }
    }
  ],
  "take": 3,
  "select": {
    "id": true,
    "createdAt": true,
    "updatedAt": true,
    "entityId": true,
    "documentId": true
  }
})

Getting:

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1052, message: "Column 'orderby_0_Document.categoryId' in on clause is ambiguous", state: "23000" })) })

Dunno how to extract generated sql

How to reproduce

run query above

Expected behavior

it works

Prisma information

model CcStructureUnit {
  id String  @unique    
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  entityId String @id @default(cuid())     
  document Document?      @relation(name: "ResponsibleUnit", fields: [documentId], references: [id])
  documentId String?      
}

model Document {
  id String @id @default(cuid())     
  no String      
  session Int?      
  convocation String?      
  sessionRel Session?      @relation(name: "DocumentSession")
  type Type      @relation(name: "DocumentType", fields: [typeId], references: [id])
  tags Tag[]      @relation(name: "DocumentTags")
  status Status      @relation(name: "DocumentStatus", fields: [statusId], references: [id])
  category Category?      @relation(name: "DocumentCategory", fields: [categoryId], references: [id])
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  publishedAt DateTime      
  acceptedAt DateTime?      
  expiresAt DateTime?      
  related Document[]      @relation(name: "RelatedDocuments", references: [id])
  relatedBy Document[]      @relation(name: "RelatedDocuments", references: [id])
  attachments Attachment[]      @relation(name: "DocumentAttachments")
  display DISPLAY_STATUS?     @default(INACTIVE) 
  file File?      @relation(name: "DocumentToFile")
  keywords Keyword[]      @relation(name: "DocumentKeywords")
  translations DocumentTranslation[]      @relation(name: "DocumentTranslations")
  responsableId String?      
  responsable CcStructureUnit?      @relation(name: "ResponsibleUnit")
  categoryId String?      
  statusId String      
  typeId String      
}

model Session {
  id String @id @default(cuid())     
  title String      
  sort Int?      
  convocation Convocation      @relation(name: "SessionConvocation", fields: [convocationId], references: [id])
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  convocationId String      
  document Document?      @relation(name: "DocumentSession", fields: [documentId], references: [id])
  documentId String?      
}

model Convocation {
  id String @id @default(cuid())     
  title String      
  sort Int?      
  sessions Session[]      @relation(name: "SessionConvocation")
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
}

model Type {
  id String @id @default(cuid())     
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  display DISPLAY_STATUS?     @default(INACTIVE) 
  color String      
  translations TypeTranslation[]      @relation(name: "TypeTranslations")
  documents Document[]      @relation(name: "DocumentType")
  sort Int?      
}

model Tag {
  id String @id @default(cuid())     
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  display DISPLAY_STATUS?     @default(INACTIVE) 
  color String      
  translations TagTranslation[]      @relation(name: "TagTranslations")
  documents Document[]      @relation(name: "DocumentTags")
}

model Status {
  id String @id @default(cuid())     
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  display DISPLAY_STATUS?     @default(INACTIVE) 
  color String      
  translations StatusTranslation[]      @relation(name: "StatusTranslations")
  documents Document[]      @relation(name: "DocumentStatus")
}

model File {
  id String @id @default(cuid())     
  path String      
  filename String      
  mimetype String      
  encoding String      
  size Int?      
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  archiveAttachment ArchiveAttachment?      @relation(name: "ArchiveAttachmentFile", fields: [archiveAttachmentId], references: [id])
  archiveAttachmentId String?      
  directory Directory?      @relation(name: "ArchiveDirectoriesFiles", fields: [directoryId], references: [id])
  directoryId String?      
  attachment Attachment?      @relation(name: "AttachmentToFile", fields: [attachmentId], references: [id])
  attachmentId String?      
  document Document?      @relation(name: "DocumentToFile", fields: [documentId], references: [id])
  documentId String?      
}

model Attachment {
  id String @id @default(cuid())     
  createdAt DateTime   @default(now())   
  updatedAt DateTime    @updatedAt  
  file File?      @relation(name: "AttachmentToFile")
  translations AttachmentTranslation[]      @relation(name: "AttachmentTranslations")
  document Document?      @relation(name: "DocumentAttachments", fields: [documentId], references: [id])
  order Int?     @default(0) 
  documentId String?      
}

Environment & setup

• OS: Mac OS
• Database: MySQL
• Node.js version: v16.12.0

Prisma Version

prisma                  : 3.6.0
@prisma/client          : 3.6.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : dc520b92b1ebb2d28dc3161f9f82e875bd35d727
Studio                  : 0.440.0

[terion-name]

another:

prisma.attachment.findMany({
  "where": {
    "AND": [
      {
        "id": {
          "in": [
            "ckx99sqq8001zke9wezdjhkd0"
          ]
        },
        "document": {
          "publishedAt": {
            "in": [
              "2021-12-16T16:09:07Z"
            ]
          },
          "acceptedAt": {
            "lt": "2021-12-15T18:57:03Z"
          },
          "display": {
            "equals": "INACTIVE",
            "in": [
              "INACTIVE"
            ]
          },
          "keywords": {},
          "responsable": {},
          "categoryId": {
            "lte": "1z!{IIq^P6"
          }
        },
        "order": {
          "lte": 900112736
        }
      }
    ],
    "NOT": [
      {}
    ],
    "updatedAt": {
      "lt": "2021-12-16T13:38:46Z"
    },
    "documentId": {
      "startsWith": ">F*r*}OOEe"
    }
  },
  "orderBy": [
    {
      "document": {
        "relatedBy": {
          "_count": "asc"
        }
      }
    }
  ],
  "cursor": {
    "id": "ckx99sqq80020ke9w9xhydcu6"
  },
  "select": {
    "id": true,
    "createdAt": true,
    "updatedAt": true,
    "order": true,
    "documentId": true
  }
})

Invalid `prisma.attachment.findMany()` invocation:


  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1052, message: "Column 'orderby_0_Document.id' in on clause is ambiguous", state: "23000" })) })
Stack:

[janpio]

https://pris.ly/d/logging and https://pris.ly/d/debugging should enable you to get the SQL query that Prisma generates and executes.

[Weakky]

Hey, this issue was just fixed by prisma/prisma-engines#3208 and will be available in the next Prisma version.