'SQLITE_ERROR: row value misused' when executing query with 'order by' for entity with nested composite primary key

[itsame-luigi]

I have an entity model that contains an entity whose primary key is derived in part from another entity (as previously discussed in #2647). When I invoke Collection.init() on a @OneToMany collection on one of the entities, the following exception is thrown:

Stack trace

DriverException: select `p0`.* from `participant` as `p0` where `p0`.`session_id` = 3 order by `p0`.`session_id` asc, (`p0`.`member_provider_id`, `p0`.`member_user_id`) asc, (`p0`.`member_provider_id`, `p0`.`member_user_id`) asc - SQLITE_ERROR: row value misused
    at SqliteExceptionConverter.convertException (C:\project\node_modules\@mikro-orm\core\platforms\ExceptionConverter.js:8:16)
    at SqliteExceptionConverter.convertException (C:\project\node_modules\@mikro-orm\sqlite\SqliteExceptionConverter.js:46:22)
    at SqliteDriver.convertException (C:\project\node_modules\@mikro-orm\core\drivers\DatabaseDriver.js:180:54)
    at C:\project\node_modules\@mikro-orm\core\drivers\DatabaseDriver.js:184:24
    at async SqliteDriver.find (C:\project\node_modules\@mikro-orm\knex\AbstractSqlDriver.js:46:24)
    at async SqlEntityManager.find (C:\project\node_modules\@mikro-orm\core\EntityManager.js:105:25)
    at async Collection.init (C:\project\node_modules\@mikro-orm\core\entity\Collection.js:180:23)

To Reproduce
The following test case illustrates the behavior:

import { Entity, ManyToOne, OneToMany, Collection, MikroORM, PrimaryKey } from '@mikro-orm/core';

@Entity()
export class Provider {

  @PrimaryKey()
  id: number;

  constructor(id: number) {
    this.id = id;
  }
}

@Entity()
export class User {

  @PrimaryKey()
  id: number;

  constructor(id: number) {
    this.id = id;
  }

}

@Entity()
export class Member {

  @ManyToOne(() => Provider, { eager: true, primary: true })
  provider: Provider;

  @ManyToOne(() => User, { eager: true, primary: true })
  user: User;

  constructor(a: Provider, b: User) {
    this.provider = a;
    this.user = b;
  }

}

@Entity()
export class Session {

  @PrimaryKey()
  id: number;

  @ManyToOne(() => Member, { eager: true })
  owner: Member;

  @OneToMany(() => Participant, participant => participant.session)
  participants = new Collection<Participant>(this);

  constructor(id: number, owner: Member) {
    this.id = id;
    this.owner = owner;
  }

}

@Entity()
export class Participant {

  @ManyToOne(() => Session, { eager: true, primary: true })
  session: Session;

  @ManyToOne(() => Member, { eager: true, primary: true })
  member: Member;

  constructor(session: Session, member: Member) {
    this.session = session;
    this.member = member;
  }

}

describe('GH #2886', () => {

  let orm: MikroORM;

  beforeAll(async () => {
    orm = await MikroORM.init({
      entities: [Provider, User, Member, Session, Participant],
      dbName: `:memory:`,
      type: 'sqlite',
    });
    await orm.getSchemaGenerator().createSchema();
  });

  afterAll(async () => {
    await orm.close(true);
  });

  beforeEach(async () => {
    await orm.em.nativeDelete(Participant, {});
    await orm.em.nativeDelete(Member, {});
    await orm.em.nativeDelete(Session, {});
    await orm.em.nativeDelete(User, {});
    await orm.em.nativeDelete(Provider, {});
  });

  function createEntities(pks: [providerId: number, userId: number, sessionId: number]) {
    const provider = new Provider(pks[0]);
    const user = new User(pks[1]);
    const member = new Member(provider, user);
    const session = new Session(pks[2], member);
    const participant = new Participant(session, member);
    orm.em.persist([provider, user, member, session, participant]);
  }

  it('should be able to call init', async () => {
    const sessionId = 3;
    createEntities([1, 2, sessionId]);
    await orm.em.flush();
    orm.em.clear();

    const session = await orm.em.findOneOrFail(Session, { id: sessionId });
    await session.participants.init();
  });
});

Expected behavior
The query should execute successfully.

Additional context

This seems to be due to a malformed order by clause: (`s0`.`member_provider_id`, `s0`.`member_user_id`) asc generated by ObjectCriteriaNode.process. The orderBy object passed to QueryBuilder.orderBy contains the following three entries:

[
  { session_id: 'ASC' },
  { member_provider_id: 'ASC' },
  { member_user_id: 'ASC' }
]

In the call to ObjectCriteriaNode.process for the second and third items in the list, both fields are renamed to "p0.member_provider_id~~~p0.member_user_id", which results in the QueryBuilder having the following values for QueryBuilder._orderBy:

[
  { 'p0.session_id': 'ASC' },
  { 'p0.member_provider_id~~~p0.member_user_id': 'ASC' },
  { 'p0.member_provider_id~~~p0.member_user_id': 'ASC' }
]

The SQL that is then generated produces invalid syntax in the order by clause.

Versions

Dependency	Version
node	16.6.2
typescript	4.5.4
mikro-orm	5.0.5
your-driver	sqlite