Kathey Marsden wrote:
Rick Hillegas wrote:
I think that this discussion has gotten seriously off-track. It is
the intent of the standard that the offset and window length values
be parameterized. This is clear from the standard language
Hmmm, I thought the problem was that the standard did not allow for
parameters and that is why we were having this discussion. Dag said:
On the contra side, we have the fact that dynamic arguments are not
allowed by the SQL standard for this construct, at least not yet.
I have to admit I haven't had time to research the standard myself,
but am a bit confused. Can you resolve your statement with Dag's?
Other forms of parameterization are allowed by the standard. It is just
that ? parameters are not explicitly included. The consensus of the
committee members who discussed this was that this was an oversight, and
no-one could explain why ? parameters had been omitted.
The ? parameters would be, technically, an extension to what's in the
standard--an extension which is compatible with the standard and which
clearly fits the standard's intent.
I believe this is a serious usability defect of our OFFSET/FETCH
implementation. As it stands today, you can only scroll one of these
windows forward by sacrificing the performance benefits of prepared
statements. It would be a shame if this feature had to remain
unusable until the next rev of the standard in 2011. If the committee
approves some other language at that time, then we can implement that
extension.
I agree this would potentially improve performance but don't see it as
a bug. Hopefully the statement cache will help.
The statement cache does not help. Without the ? parameters, each window
has to be constructed by a separate prepared statement. This is the crux
of the problem.
If people wish to veto this proposal, then I would ask them to
propose an alternative solution which makes this feature usable and
which they believe fits more comfortably within the intention of the
standard.
Hopefully it won't come down to a veto. Hopefully we can reach
consensus in the community.
Yes, please. I'm looking forward to consensus when I get back from vacation!
Cheers,
-Rick
Kathey
