Consider a variant of offset and limit which operate on an Expression type?

[smklein]

As currently implemented, limit and offset provide a way to set the LIMIT and OFFSET of a SQL query to a specific i64 value.

This is clearly the common case, and a worthwhile use-case to keep "easy" - however, there are situations where these values may be inferred as sub-queries.

For example, here's a SQL query attempting to select enough users to could take on a job:

SELECT
  id
FROM
  user
WHERE
  -- Only consider users that don't have the job already
  user.job != "programmer"
LIMIT
  -- We only want to SELECT enough users to make *this selection* plus the existing
  -- count equal 5.
  -- For example:
  --  - If we had all positions empty, this would set LIMIT GREATEST(5, 0) - 0 = 5
  --  - If we had all positions filled, this would set LIMIT GREATEST(5, 5) - 5 = 0
  GREATEST(5, (SELECT COUNT(*) FROM user WHERE user.job != "programmer")
   - (SELECT COUNT(*) FROM user WHERE user.job != "programmer"

This is an example query which may want to use a LIMIT that is only known at the time the query is issued. It would be possible to use an interactive transaction to perform this type of operation, but that would introduce unnecessary contention and latency.

I've found this type of query particularly useful in the context of a CTE - we may want to conditionally operate on a limit, based on prior subqueries.

I believe eq_any actually provides some precedent here - this is an example of a query which can operate on a subquery, with values not known ahead-of-time.

I think something like the following might work:

pub trait LimitExpressionDsl: LimitDsl {
  type Output;

  fn limit_expression(self, limit: Expression<SqlType = BigInt>) -> Self::Output;
}

What do you think?

[smklein]

Also, throwing it out there, I'd be happy to help with implementation, but figured I'd probe the space to see if this is a good idea first.

[weiznich]

I think the main issue here is that it depends on the database system what should be accepted as limit/offset clause at all. As far as I remember out of our supported backends only postgres supports real expressions there. Both sqlite and mysql do only support integers there. Any implementation needs to ensure that you can only use this feature with postgresql and not with the other backends.

Other than that the implementation should be actually quite straight forward as we could simply make the existing implementations accept more parameters by using something that implements AsExpression<SqlType = BigInt> there (which is also implemented for integers).