Unexpected 500 response: "...values and fields must be sorted..."

[bickelj]

This URL currently returns a 500: https://api.philanthropydatacommons.org/proposals/452?includeFieldsAndValues=true

{"name":"UnknownError","message":"Given values and fields must be sorted such that they correspond. index=87: 712 !== 706.","details":[{}]}

[bickelj]

The running version is ghcr.io/philanthropydatacommons/service:20230525-f59e112.

In proposalsHandlers.ts, these first two queries are expected to be sorted the same way such that they can be joined in software (see #198 (comment) and following).

      Promise.all([
        db.sql('proposalFieldValues.selectByProposalId', { proposalId: req.params.id }),
        db.sql('applicationFormFields.selectByProposalId', { proposalId: req.params.id }),
        db.sql('proposalVersions.selectByProposalId', { proposalId: req.params.id }),
      ]).then(([
//...
        const valuesWithFields = joinApplicationFormFieldsToProposalFieldValues(
          proposalFieldValuesQueryResult.rows,
          applicationFormFieldsQueryResult.rows,
        );

The join function does some consistency/assumption checks before joining:

export const joinApplicationFormFieldsToProposalFieldValues = (
  values: ProposalFieldValue[],
  fields: ApplicationFormField[],
): ProposalFieldValue[] => {
  const newValues = structuredClone(values);
  if (newValues.length !== fields.length) {
    // It is invalid to try to zip values and fields of unequal length.
    throw new Error(`Given arrays must be of equal length. ${values.length} !== ${fields.length}`);
  }

  return newValues.map((value, index) => {
    // Look in the fields array at the same index as the values array.
    const field = fields[index];
    if (field === undefined) {
      throw new Error(
        `Expected only defined fields and values, copiedFields[${index}] was undefined`,
      );
    }
    if (value.applicationFormFieldId !== field.id) {
      throw new Error(
        `Given values and fields must be sorted such that they correspond. index=${index}: ${value.applicationFormFieldId} !== ${field.id}.`,
      );
    }
    // When all is well, add the field to the value.
    return { ...value, applicationFormField: field };
  });
};

The queries ([aff]/selectByProposalId.sql and [pfv]/selectByProposalId.sql) appear to order by the same columns, ORDER BY pv.version DESC, pfv.position;, so it is not as if the SQL changed.

The length of the two query result sets is identical or we would have gotten Given arrays must be of equal length.

I ran the queries manually and am comparing the results.

Indeed the 88th and 89th (of 90) rows order is flipped. The proposal field value position is repeated and the ORDER BY clause is insufficient by only using position. The position may not always be the absolute order or be unique across proposal field values within a proposal version. Perhaps an additional id column is needed in that ORDER BY.

After dump and restore locally, the issue does not appear locally, which is not entirely unexpected because the issue only appeared after some time and the order if not explicit can be non-deterministic.

[bickelj]

It appears to have fixed the issue in prod.