Thread: width_bucket() per SQL2003 (WIP)
The attached patch implements the width_bucket() function (as specified by Section 6.27 of the SQL2003 standard, in particular page 250 in my copy). This function accepts an operand, a lower bound, an upper bound, and a number of buckets. It returns the number of the bucket to which the operand would be assigned in an equidepth histogram with the specified characteristics. I believe the function comes from Oracle 9i, so you can search the web for more information. Questions / Issues: (1) This patch only implements width_bucket() for the 'numeric' type (I thought I would get the behaviour right for a single type before writing the other variants). What other types should I provide implementations for? (2) SQL2003 says that the fourth argument to the function should be an "exact numeric with scale 0" (and the function's return value must have the same type). I chose to make this argument an int4 -- does anyone think that int2 or int8 would be more appropriate? This patch includes some basic regression tests for the function, but I haven't bothered updating the docs yet -- a subsequent version of this patch will do so. Comments welcome. -Neil
Attachment
Neil Conway <neilc@samurai.com> writes:
> The attached patch implements the width_bucket() function (as specified
> by Section 6.27 of the SQL2003 standard, in particular page 250 in my
> copy).
Other than "it's in SQL2003", what's the point of implementing this?
It seems like a mighty marginal feature ...
> I believe the function comes from Oracle 9i,
Trying to implement everything in Oracle is a goal up with which we must
not try to keep.
regards, tom lane
> Other than "it's in SQL2003", what's the point of implementing this? > It seems like a mighty marginal feature ... I don't see a problem with implementing every function that SQL2003 has...isn't that the point of implementing the standard? Chris
On 30-Apr-04, at 1:06 AM, Tom Lane wrote: > Other than "it's in SQL2003", what's the point of implementing this? No idea, but it doesn't really matter IMHO: it is in SQL2003, it doesn't interfere with the rest of the system or impose a significant maintenance burden and it has essentially zero negative side effects. So why _not_ implement it? > Trying to implement everything in Oracle is a goal up with which we > must > not try to keep. Granted, but that's not the case here. -Neil
Neil Conway <neilc@samurai.com> writes:
> On 30-Apr-04, at 1:06 AM, Tom Lane wrote:
>> Other than "it's in SQL2003", what's the point of implementing this?
> No idea, but it doesn't really matter IMHO: it is in SQL2003, it
> doesn't interfere with the rest of the system or impose a significant
> maintenance burden and it has essentially zero negative side effects.
> So why _not_ implement it?
Because we have only limited manpower. It's water over the dam now,
since you've already expended the work, but IMHO you'd be better advised
to spend your time implementing features or performance improvements
that someone has actually asked for. We certainly have more than enough
things that desperately need attention. How did width_bucket() come to
look important enough to rise to the top of your to-do list?
regards, tom lane
On 30-Apr-04, at 11:57 AM, Tom Lane wrote: > Because we have only limited manpower. It's water over the dam now, > since you've already expended the work, but IMHO you'd be better > advised > to spend your time implementing features or performance improvements > that someone has actually asked for. Okay, your opinion has been duly noted :-) Did you have any comment on the two questions I asked in my original patch email? -Neil
Neil Conway <neilc@samurai.com> writes:
> Okay, your opinion has been duly noted :-)
> Did you have any comment on the two questions I asked in my original
> patch email?
MHO: you already spent more time on this than it deserves. What you
have sounds fine.
regards, tom lane
On Fri, 2004-04-30 at 23:45, Tom Lane wrote: > MHO: you already spent more time on this than it deserves. What you > have sounds fine. I applied the patch in basically its current form (I added some minimal docs and fixed a few cosmetic details). I haven't implemented variants of it for other datatypes, I may do that in the future. In any case, the numeric-only implementation should be sufficient to conform to the standard... -Neil