Cosmos: subquery pushdown

[roji]

No description provided.

[roji]

I spent some time on this today, here are some findings. tl;dr Cosmos limitations around LIMIT/OFFSET make subquery pushdown only barely useful, as LIMIT/OFFSET are the main reason. I'll shelf this for now - see WIP in https://github.com/roji/efcore/tree/CosmosPushdown.

• Cosmos doesn't support LIMIT/OFFSET in subqueries.
• As a result, we have special translation logic for LINQ Skip/Offset in subqueries; rather than translating to LIMIT/OFFSET, we convert the subquery in question to an array via the Cosmos ARRAY() operator, and compose ARRAY_SLICE() over that. This allows us to translate e.g. context.Blogs.Where(b => b.Ints.Skip(2).Take(3)...). Note that these query shapes don't involve a pushdown - the translation of the Where predicate is aware that it's in subquery context, and takes that into account.
• An actual pushdown needs to happen when LINQ operators are composed in an order which would be incompatible with the SELECT clause evaluation order. For example, at the top-level, if Take().Skip() are composed, Skip() would need to trigger a a pushdown to make sure Take() is evaluated first.
• Unfortunately, the ARRAY_SLICE() technique can't be used when pushing down a top-level query, since Cosmos doesn't support referencing the root container from inside an ARRAY() subquery. So although SELECT * FROM (SELECT * FROM root c) works fine (note root referenced inside the subquery, the following does not: SELECT * FROM (SELECT VALUE ARRAY(SELECT * FROM root c)). This makes it impossible for us to perform pushdown on a (top-level) query that has LIMIT/OFFSET.
• Apart from LIMIT/OFFSET which can't be supported, the remaining causes for pushdown are:
  • Composing aggregate functions (e.g. Count) over DISTINCT
  • Composing ORDER BY over DISTINCT (but this shouldn't actually be needed, see #34121).
• Note that since for subqueries we already avoid LIMIT/OFFSET, translating ARRAY_SLICE() as above; so once again there's little need to push down within subquery context as well (only the above two cases).
• As a result, I'm shelving this effort for the meantime and will consult with the Cosmos people. If LIMIT/OFFSET in subqueries becomes supported, or some other workaround is found, we can bring this back and implement proper pushdown.