Content: Filter paths handling in query

[saskliutas]

There are 2 edge cases that are not handled with current base query building logic introduced by #1462:

• When filter path contains one to many path it might return more rows when anchor is related to multiple instances:

it("[gap 1] builds an anchor query that may return the same primary once per matching related instance", async () => {
  const path = [makeStep(primaryClass, "TestSchema.RelMany", "TestSchema.Many")];
  const field = makePropertyField({
    propertyName: "Name",
    propertyClassName: "TestSchema.Many",
    pathFromTarget: path,
    valueClassNames: ["TestSchema.Many"],
  });

  const result = await buildBaseQuery({
    schemaProvider,
    source: makeSource([path]),
    includeRelatedJoins: true,
    filters: [{ field, operator: "is-equal", value: "A" }],
  });

  const anchorQuery = `
    SELECT [this].[ECInstanceId]
    ${result.anchor.parts.from}
    ${result.anchor.parts.joins}
    WHERE ${result.anchor.parts.where}
  `;
  expect(trimWhitespace(anchorQuery)).to.equal(
    trimWhitespace(`
      SELECT [this].[ECInstanceId]
      FROM [TestSchema].[Primary] [this]
      OUTER JOIN (
        SELECT [${ECSQL_PREFIX}r0].*
        FROM [TestSchema].[RelMany] [${ECSQL_PREFIX}r0]
        INNER JOIN [TestSchema].[Many] [${ECSQL_PREFIX}t0] ON [${ECSQL_PREFIX}t0].[ECInstanceId] = [${ECSQL_PREFIX}r0].[TargetECInstanceId]
      ) [${ECSQL_PREFIX}r0] ON [${ECSQL_PREFIX}r0].[SourceECInstanceId] = [this].[ECInstanceId]
      OUTER JOIN [TestSchema].[Many] [${ECSQL_PREFIX}t0] ON [${ECSQL_PREFIX}t0].[ECInstanceId] = [${ECSQL_PREFIX}r0].[TargetECInstanceId]
      WHERE [${ECSQL_PREFIX}t0].[Name] = :${ECSQL_PREFIX}vf0
    `),
  );
  // For example, assume TestSchema.Primary(0x1) is related through TestSchema.RelMany to both
  // TestSchema.Many(0x2, Name = "A") and TestSchema.Many(0x3, Name = "A"). The relationship JOIN
  // produces these rows before projection:
  //   this.ECInstanceId | r0.TargetECInstanceId | t0.Name
  //   0x1              | 0x2                  | A
  //   0x1              | 0x3                  | A
  // Both rows pass the Name filter, so SELECT [this].[ECInstanceId] returns [0x1, 0x1].
  // An existential or de-duplicated subquery would keep
  // the anchor at one row per TestSchema.Primary instance.
});

• When filter path + anchor exceeds 64 JOIN limit:

it("[gap 2] keeps filter-required joins within SQLite's table limit", async () => {
  const paths = Array.from({ length: 32 }, (_, index) => [
    makeStep(primaryClass, `TestSchema.Rel${index}`, `TestSchema.Target${index}`),
  ]);
  const filters: ContentValueFilter[] = paths.map((path, index) => ({
    field: makePropertyField({
      propertyName: "Name",
      propertyClassName: `TestSchema.Target${index}`,
      pathFromTarget: path,
      valueClassNames: [`TestSchema.Target${index}`],
    }),
    operator: "is-equal",
    value: "A",
  }));

  const result = await buildBaseQuery({
    schemaProvider,
    source: makeSource(paths),
    includeRelatedJoins: false,
    filters,
  });
  // Filter-required paths are added directly to the anchor after related paths are partitioned by
  // the join budget. Each link-table path adds two top-level OUTER JOINs: one derived relationship
  // table and one target table. With the primary FROM table, 32 paths therefore use 65 tables and
  // exceed SQLite's 64-table limit instead of being split into separate query groups.
  const outerJoinCount = result.anchor.parts.joins.match(/\bOUTER JOIN\b/g)?.length ?? 0;

  expect(1 + outerJoinCount).to.be.at.most(64);
});

[grigasp]

I'm not too worried about the second issue - reaching 64 joins purely for filtering is unlikely. Would be nice to at least have an idea for how to fix this, though.

The first one is definitely a higher priority one. I think we could simply employ EXISTS (SELECT 1 FROM RelatedClass rc WHERE rc.ECInstanceId = this.NavProp.Id AND RelatedValue < ?). Need to see if we could do that for all cases or should only do it for 1-to-many relationships.