NonUniqueFieldNameException: column reference "xxx" is ambiguous

[ml1nk]

Describe the bug
In some, probably rare, circumstances the on part of left join is created without using the alias, even when there is the same column in both tables.

select "a0".* from "a" as "a0" left join "b" as "b1" on "node_id" = "b1"."node_id" where "b1"."test" = '5'

leads to

column reference "node_id" is ambiguous

Stack trace

/home/mlink/mikroormpersist/node_modules/.pnpm/@mikro-orm+postgresql@5.8.3_@mikro-orm+core@5.8.3_@mikro-orm+migrations@5.8.3/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:34
                return new core_1.NonUniqueFieldNameException(exception);
                       ^
NonUniqueFieldNameException: select "a0".* from "a" as "a0" left join "b" as "b1" on "node_id" = "b1"."node_id" where "b1"."test" = '5' - column reference "node_id" is ambiguous
    at PostgreSqlExceptionConverter.convertException (/home/mlink/mikroormpersist/node_modules/.pnpm/@mikro-orm+postgresql@5.8.3_@mikro-orm+core@5.8.3_@mikro-orm+migrations@5.8.3/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:34:24)
    at PostgreSqlDriver.convertException (/home/mlink/mikroormpersist/node_modules/.pnpm/@mikro-orm+core@5.8.3_@mikro-orm+migrations@5.8.3_@mikro-orm+postgresql@5.8.3_@mikro-orm+sqlite@5.8.3/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:197:54)
    at /home/mlink/mikroormpersist/node_modules/.pnpm/@mikro-orm+core@5.8.3_@mikro-orm+migrations@5.8.3_@mikro-orm+postgresql@5.8.3_@mikro-orm+sqlite@5.8.3/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:201:24
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgreSqlDriver.find (/home/mlink/mikroormpersist/node_modules/.pnpm/@mikro-orm+knex@5.8.5_@mikro-orm+core@5.8.3_@mikro-orm+migrations@5.8.3_pg@8.11.3_sqlite3@5.1.6/node_modules/@mikro-orm/knex/AbstractSqlDriver.js:52:24)
    at async SqlEntityManager.find (/home/mlink/mikroormpersist/node_modules/.pnpm/@mikro-orm+core@5.8.3_@mikro-orm+migrations@5.8.3_@mikro-orm+postgresql@5.8.3_@mikro-orm+sqlite@5.8.3/node_modules/@mikro-orm/core/EntityManager.js:116:25)
    at async /home/mlink/mikroormpersist/app/server.ts:15:3

To Reproduce
A and B are in a separate package than N, so persist: false is a workaround to travel vom A to B without going over N.

A.ts

@Entity()
export class A  {
  [OptionalProps]?: 'b'
  @ManyToOne({ entity: () => B, fieldName: 'node_id',persist: false, ref: true })
    b!: Ref<B>

  [PrimaryKeyType]!: number
  [PrimaryKeyProp]!: 'node'
  @OneToOne({ entity: () => N, primary: true, onDelete: 'cascade', onUpdateIntegrity: 'cascade', ref: true })
    node!: Ref<N>

  @Property()
  test!: string;
}

B.ts

@Entity()
export class B  {

  [PrimaryKeyType]!: number
  [PrimaryKeyProp]!: 'node'
  @OneToOne({ entity: () => N, primary: true, onDelete: 'cascade', onUpdateIntegrity: 'cascade', ref: true })
    node!: Ref<N>

  @Property()
  test!: string;

}

N.ts

@Entity()
export class N {
  @PrimaryKey()
  id!: number;
}

Execute:

await orm.em.find(A, { b: { test: '5' }})

Expected behavior

select "a0".* from "a" as "a0" left join "b" as "b1" on "a0"."node_id" = "b1"."node_id" where "b1"."test" = '5'

Additional context
The usage of

formula: (alias)=> `${alias}.node_id`

seems to workaround the problem.

Versions

Dependency	Version
node	18.18.0
mikro-orm	5.8.5

[B4nan]

persist: false means the column is virtual, it does not represent any database column, as such it shouldn't be aliased. I know why this happens, and it is from a recent change, but generally speaking, you shouldn't mark a property that represents a real column as virtual.

A and B are in a separate package than N, so persist: false is a workaround to travel vom A to B without going over N.

Not sure what you mean by this.

[ml1nk]

I am of your opinion but couldn't find a way to do the same without adding such a virtual property. I cannot add anything related to A and B in N, as A and B are in a separate Package. As A and B both have the same node_id, i need a way to link these together without creating additional properties.

Do you have a idea to achieve that otherwise?

[ml1nk]

Not sure what you mean by this.
There are for example three packages: P-N, P-B and P-A. P-B installs P-N and P-A install P-B and P-N. Every package defines it own entities, which are dependent on each other like shown above. The logic in P-A restricts the creation of A when there is no B and N with the specified node_id.

If i want all A's where the correspondant B has a specific value, then would normally need to use:
{ node: { b: { test: '5' }}}
but as N does not know B, i can't add the relation inside N.ts.

By defining the the property with persist:false and fieldName:"node_id" i could reuse the column node_id to create a link between A and B which can be used with:
{ b: { test: '5' }}

As persist is false it does not try to create more columns or persist something inconsistently.

[B4nan]

I am still not really sure what your problem is (complete repro welcome, maybe we can find a better way), but I will be reverting that part anyway, as I had to do it when rebasing to v6, some tests in there were also failing because of that change.

[ml1nk]

I'm sorry for my bad explanation. I will try to create a repro in the next days which contains different packages containing the entities, where the dependency and column-reuse problem is more visible to you.