Cosmos: Data corruption due to missing subquery pushdowns

[roji]

Pushdown missing for Where over Skip/Take

For test NorthwindWhereQueryTestBase.Where_primitive:

public virtual Task Where_primitive(bool async)
    => AssertQueryScalar(
        async,
        ss => ss.Set<Employee>().Select(e => e.EmployeeID).Take(9).Where(i => i == 5));

Current SQL:

SELECT c["EmployeeID"]
FROM root c
WHERE ((c["Discriminator"] = "Employee") AND (c["EmployeeID"] = 5))
OFFSET 0 LIMIT @__p_0

This is incorrect, since the WHERE clause is evaluated before the OFFSET/LIMIT; composing the Where should cause a subquery pushdown (to be implemented in #33968.

Pushdown missing for Distinct over Skip/Take

Test NorthwindMiscellaneousQueryTestBase.Take_Distinct:

public virtual Task Take_Distinct(bool async)
    => AssertQuery(
        async,
        ss => ss.Set<Order>().OrderBy(o => o.OrderID).Take(5).Distinct());

Current SQL:

SELECT DISTINCT c
FROM root c
WHERE (c["Discriminator"] = "Order")
ORDER BY c["OrderID"]
OFFSET 0 LIMIT @__p_0

This is incorrect, since DISTINCT is evaluated before OFFSET/LIMIT.