[insertAndFetch]: Error: undefined passed as an item in argument #1 for 'whereInComposite' operation, in parallel queries

[brunorota0]

Hey guys,

I found an issue, maybe is on the user's side, but the query is very simple and i don't understand why this could break.

Executing a couple of insertAndFetch in simultaneous. Using Promise.map from bluebird.

The thing is that for the first one reach the insertAndFetch, all is good.

The next three iterations, the error appears:
Error: undefined passed as an item in argument #1 for 'whereInComposite' operation. Call skipUndefined() method to ignore the undefined values.

The query:

  const bodyFiltered = pick(body, ["name", "isin", "instrument_type"]);

  return Instrument.query(trx).insertAndFetch(bodyFiltered).onConflict("isin").ignore();

The model:

class Instrument extends Model {
  static get tableName() {
    return "instrument";
  }

  static get idColumn() {
    return "id";
  }

  static get jsonSchema() {
    return {
      type: "object",
      properties: {
        id: { type: "integer" },
        name: { type: "string" },
        instrument_type: { type: "integer" },
        isin: { type: "string" },
      },
    };
  }
} 

I tested it doing two queries and it worked!

  const props = ["name", "isin", "instrument_type"];
  const bodyFiltered = pick(body, props);

  await Instrument.query(trx).insert(bodyFiltered).onConflict("isin").ignore();

  return Instrument.query(trx).findOne({
    isin: body.isin,
  });

Always keeping the Promise.map from bluebird. Of course that if i change it to Promise.mapSeries which is not doing anything simultaneously, it worked as well.

Also, there are some inserts repeated, it means that it is entering to the onConflict("isin").ignore() part.

Can you help me?

Maybe it's a missing thing on the user side that I'm not addressing

Thanks in advance

[yulianovdey]

@brunorota0 We also ran into this. I assume that what's happening inside Objection is that when onConflict is used the insert part of insertAndFetch doesn't fail if the row already exists, but the subsequent fetch which is a new query doesn't have the result it needs from the insert if the insert didn't return anything, so the fetch fails.

As mentioned here you can use returning (at least for Postgres) as an alternative:

Instrument.query(trx).insert(bodyFiltered).onConflict("isin").ignore().returning('*');

Which will prevent the error you're seeing, but unlike returning('*') suggests the resulting object will only have the fields you passed to the insert, which is different from insertAndFetch:

  let result = await MyEntity.query().insertAndFetch({ key: 'value' }).onConflict('key').ignore();
  console.log(result.id); // 1
  
  // this will error
  await MyEntity.query().insertAndFetch({ key: 'value' }).onConflict('key').ignore();
  
  result = await MyEntity.query().insert({ key: 'value' }).onConflict('key').ignore().returning('*');
  console.log(result.id); // undefined
  console.log(result.key); // value

TLDR neither is ideal (unless this behavior has changed in a more recent version). If you're using a transaction anyway the best version is probably using separate insert and fetch like you did in the example that worked.