Issue description

Invalid join queries when two relations of an entity use the same column

Expected Behavior

We can't put the full code for confidentiality purpose but the following setup is relevant to understand the issue.
We have those 3 entities:

Customer - standard table
PK on id

Order - partitioned table
PK composite on (customer_id, id)
FK on customer_id -> Customer(id)

Ticket - partitioned table
PK composite on (customer_id, id)
FK on customer_id -> Customer(id)
FK composite on (customer_id, order_id) -> Order(customer_id, id)

With this setup, we should be able to query tickets normally

Actual Behavior

We get invalid generated SQL when we query the Ticket entity
We have to disable entity synchronization and handle joins ourselves to make it work

Steps to reproduce

The following setup should produce the current behavior

@Entity({ name: 'customers' })
class Customer {
  @PrimaryColumn('uuid')
  id: string;
}

@Entity({ name: 'orders' })
class Order {
  @PrimaryColumn('uuid')
  id: string;

  @PrimaryColumn('uuid')
  customerId: string;

  @JoinColumn([{ name: 'customer_id', referencedColumnName: 'id' }])
  @ManyToOne(() => Customer)
  customer: Customer;

  @OneToMany(() => Ticket, (ticket) => ticket.order)
  tickets: Ticket[];
}

@Entity({ name: 'tickets' })
class Ticket {
  @PrimaryColumn('uuid')
  id: string;

  @PrimaryColumn('uuid')
  customerId: string;

  @Column({ nullable: true, type: 'uuid' })
  orderId: string;

  @JoinColumn([{ name: 'customer_id', referencedColumnName: 'id' }])
  @ManyToOne(() => Customer)
  customer: Customer;

  @ManyToOne(() => Order, (order) => order.tickets, { nullable: true })
  @JoinColumn([
    { name: 'customer_id', referencedColumnName: 'customerId' },
    { name: 'order_id', referencedColumnName: 'id' },
  ])
  order: Order | null;
}

My Environment

Additional Context

We believe the issue is due to the fact that we have both on Ticket entity

• 1 FK to Customer using customer_id
• 1 composite FK to Order which also uses customer_id

It works well if we have either one, but having both at the same time causes the bug.
It seems we're not the only one who had this problem as I found 2 issues which are almost the same:
#10148, #10121

Is there a known workaround ? Disabling synchro is really painful for us
Thank you 🙏

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, but I don't know how to start. I would need guidance.