SELECT WHERE ID in with duplicated IDs

[tatchi]

Given the following SQLite table:

CREATE TABLE "users" ("id" integer NOT NULL,"name" varchar NOT NULL, PRIMARY KEY (id));

With two users:

id	name
1	user 1
2	user 2

If we manually run the following query:

SELECT * FROM users where id in (1,2,1)

It returns two rows. If we do the same with sqlfx:

const GetById = sql.resolverId('GetUserById', {
  id: S.number,
  result: UserDbo,
  resultId: (_) => _.id,
  run: (ids) => sql`SELECT * FROM users WHERE id IN ${sql(ids)}`,
});

const getUserById = (id: number) =>
  Effect.gen(function* (_) {
    const user = yield* _(
      GetById.execute(id).pipe(Effect.map(Option.map((p) => new UserDbo(p))))
    );

    return user;
  });

const main = Effect.gen(function* (_) {
const { getUserById } = yield* _(UserRepro);

const allUsers = yield* _(
  Effect.all(
    pipe(
      [1, 2, 1],
      ReadonlyArray.map((id) => getUserById(id))
    ),
    { batching: false }
  )
);

console.log(allUsers);

return null;
});		

It returns 3 rows:

[
  {
    _id: 'Option',
    _tag: 'Some',
    value: UserDbo { id: 1, name: 'user 1' }
  },
  {
    _id: 'Option',
    _tag: 'Some',
    value: UserDbo { id: 2, name: 'user 2' }
  },
  {
    _id: 'Option',
    _tag: 'Some',
    value: UserDbo { id: 1, name: 'user 1' }
  }
]

If we set batching to true, it gives the following error

Exited failure state: All fibers interrupted without errors.

Is it expected? Shouldn't the code return 2 rows in both cases?

Repro

• clone https://github.com/tatchi/sqlx-repro
• git checkout same-id
• pnpm i
• ./src/main.ts

[tatchi]

Thanks 🤗 Just to be sure, it doesn't fail anymore but returns 3 rows (with duplicate user 1) both with batching on or off. Is this the expected result?

[tim-smart]

Yes, with a resolver this is the correct behaviour :)