SELECT aliases being referenced in WHERE statement clause Postgres

[rstims]

Describe the bug
Mikro seems to be using SELECT aliases in WHERE clause.

e.g.

SELECT "u0".*,
	"t1"."vendor_id" AS "technician_manager_vendor_id",
	"t1"."user_id" AS "technician_manager_user_id",
	"t2"."id" AS "technician_id"
FROM "user" AS "u0"
LEFT JOIN "technician_manager" AS "t1" ON "u0"."id" = "t1"."user_id"
LEFT JOIN "technician" AS "t2" ON "u0"."id" = "t2"."user_id"
WHERE NOT (("t1"."technician_manager_vendor_id","t1"."technician_manager_user_id") IS NULL)
ORDER BY "u0"."updated_at" DESC

Stack trace

InvalidFieldNameException: select "u0".*, "t1"."vendor_id" as "technician_manager_vendor_id", "t1"."user_id" as "technician_manager_user_id", "t2"."id" as "technician_id" from "user" as "u0" left join "technician_manager" as "t1" on "u0"."id" = "t1"."user_id" left join "technician" as "t2" on "u0"."id" = "t2"."user_id" where not (("t1"."technician_manager_vendor_id", "t1"."technician_manager_user_id") is null) order by "u0"."updated_at" desc limit 25 - column t1.technician_manager_vendor_id does not exist
        at PostgreSqlExceptionConverter.convertException (/Users/rstimmler/Code/mpx-api-service/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:36:24)
        at PostgreSqlDriver.convertException (/Users/rstimmler/Code/mpx-api-service/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:192:54)
        at /Users/rstimmler/Code/mpx-api-service/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:196:24
        at processTicksAndRejections (node:internal/process/task_queues:95:5)
        at async PostgreSqlDriver.find (/Users/rstimmler/Code/mpx-api-service/node_modules/@mikro-orm/knex/AbstractSqlDriver.js:49:24)
        at async SqlEntityManager.find (/Users/rstimmler/Code/mpx-api-service/node_modules/@mikro-orm/core/EntityManager.js:106:25)
        at async UserResolver.usersConnection (/Users/rstimmler/Code/mpx-api-service/src/graphql/resolvers/user.ts:17:23)

To Reproduce
Steps to reproduce the behavior:

1. Triggered when adding a where clause to check if a relationship is null
2. Relationship has a composite primary key, both being an FK of another entity

User Entity

@Entity()
export class User extends BaseEntity<User> {
  @OneToOne(() => TechnicianManager, (technician_manager) => technician_manager.user)
  technician_manager: TechnicianManager
}

TechnicianManager Entity

@Entity()
export class TechnicianManager extends BaseEntityNoId<TechnicianManager, 'vendor' & 'user'> {
  @ManyToOne({ primary: true })
  vendor!: Vendor

  @OneToOne({ primary: true })
  user!: User
}

Where object

{
   "$and":[
      {
         "$or":[
            {
               "$not":{
                  "technician_manager":null
               }
            }
         ]
      }
   ]
}

Versions

Dependency	Version
node	18.11.0
typescript	4.8.4
mikro-orm	5.5.0
your-driver	postgres 14.3

[B4nan]

FYI you can also use { technician_manager: { $ne: null } }, personally I'd prefer that over explicit $not operator. $ne stands for not equal and will be probably better supported than $not (which is somehow quirky).

Also I would appreciate complete reproduction, there is a lot of ambiguity in provided entity definition. I don't want to know how your entities are set up or about your various base entity classes, I want simplicity but completeness.

Lastly, I don't understand what you expect from having a 1:1 primary key as part of a composite key - by definition, it will be always unique on its own.

edit: I just verified the very same problem surfaces without wrapping the query in $and and $or, and unfortunately I can confirm the same problem with $ne.

[rstims]

@B4nan I ended up just removing the composite and making it a unique index and everything works fine now.

Did you still want a repro?

[B4nan]

Already got one:

import { Entity, ManyToOne, MikroORM, OneToOne, PrimaryKey, Property } from '@mikro-orm/core';
import type { SqliteDriver } from '@mikro-orm/sqlite';

@Entity()
class Vendor {

  @PrimaryKey()
  id!: number;

  @Property()
  prop!: string;

}

@Entity()
class TechnicianManager {

  @ManyToOne({ entity: () => Vendor, primary: true })
  vendor!: Vendor;

  @OneToOne({ entity: () => User, primary: true })
  user!: any;

}

@Entity()
class User {

  @PrimaryKey()
  id!: number;

  @OneToOne(() => TechnicianManager, technician_manager => technician_manager.user)
  technician_manager!: TechnicianManager;

}

describe('GH issue 3669', () => {

  let orm: MikroORM<SqliteDriver>;

  beforeAll(async () => {
    orm = await MikroORM.init({
      entities: [TechnicianManager],
      dbName: ':memory:',
      type: 'sqlite',
    });
    await orm.schema.createSchema();
  });

  afterAll(() => orm.close(true));

  test('$not operator on 1:1 inverse side', async () => {
    await expect(orm.em.find(User, { $not: { technician_manager: null } })).resolves.toEqual([]);
  });

  test('$ne operator on 1:1 inverse side', async () => {
    await expect(orm.em.find(User, { technician_manager: { $ne: null } })).resolves.toEqual([]);
  });

});

(generally, if I tag something as bug, it means I am able to reproduce)