[relationJoins] join_alias_name exceeding maximum postgres identifier length -> results in table name "..." specified more than once error

[timbodeit]

Description
Lateral joins generated by LateralJoinSelectBuilder are aliased by a concatenation of model name and field name generated in join_alias_name. https://github.com/prisma/prisma-engines/blob/5e70d191c94f7d16f2ebf48387832b89f72d2ec3/query-engine/connectors/sql-query-connector/src/query_builder/select/mod.rs#L660-L662

This works fine as long as the length of the model name, field name and underscore together do not exceed 63 characters.

While Postgres allows including longer identifiers in SQL commands, it will truncate / discard any parts of the identifier that exceed 63 characters.
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Knowing the limitations of PostgreSQL, Prisma users would expect 63 characters as an upper limit for unambiguous identifiers for both the model as well as the field. By joining the two together, this can still result in an alias with up to 127 characters, that Postgres can no longer handle unambigously.

This has lead to Postgres Query errors table name "..." specified more than once on our project.

Contrived example
(Not related to our project, just to illustrate the issue)

model ExtendedPassengerAutomobileWarrantyMaintainanceEvent {
  id                              Int                      @id @default(autoincrement())
  special_invoice_recipient       InvoiceRecipient         @relation(fields: [recipientId], references: [id])
  special_invoice_content         InvoiceContent           @relation(fields: [contentId], references: [id])

  recipientId Int
  contentId   Int
}

join_alias_name will turn these into:

ExtendedPassengerAutomobileWarrantyMaintainanceEvent_special_invoice_recipient
ExtendedPassengerAutomobileWarrantyMaintainanceEvent_special_invoice_content

Postgres will truncate both to the same value ExtendedPassengerAutomobileWarrantyMaintainanceEvent_special_in, complaining about table name "ExtendedPassengerAutomobileWarrantyMaintainanceEvent_special_in" specified more than once if both of them appear in the same query.

Possible solution
A possible solution for this issue may be to drop the join_alias_name and m2m_join_alias_name functions and replace them with aliases generated by next_alias instead. I tried drafting a PR for this, but unfortunately don't have enough insight yet, how the different code locations using this function (namely build_selection, add_to_one_relation and build_json_obj_fn) fit together.
Would appreciate some pointers to help me get started in the right direction.

CC @Weakky

[jacek-prisma]

Hi @timbodeit , this code has changed a little since but I think your suggestion is viable and if you want to implement it, we'd be happy to get a fix merged.

• I think join_alias_name and m2m_join_alias_name can be replaced with ctx.next_join_alias()
• we might need to thread the alias through a few functions because it looks like the existing code relies on two places in the code arriving at the same name
  • we start at fn build in impl JoinSelectBuilder for LateralJoinSelectBuilder
  • the FieldSelection is passed through to both with_relations and with_selection
    • with_relations calls with_relation which calls add_to_many_relation and add_to_one_relation, both of which use the alias name functions (add_to_one_relation might also need to pass it down to build_json_obj_fn)
    • with_selection calls build_selection which also uses the alias name functions