Thread: width_bucket() per SQL2003 (WIP)

width_bucket() per SQL2003 (WIP)

From
Neil Conway
Date:
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

Re: width_bucket() per SQL2003 (WIP)

From
Tom Lane
Date:
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

Re: width_bucket() per SQL2003 (WIP)

From
Christopher Kings-Lynne
Date:
> 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


Re: width_bucket() per SQL2003 (WIP)

From
Neil Conway
Date:
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


Re: width_bucket() per SQL2003 (WIP)

From
Tom Lane
Date:
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

Re: width_bucket() per SQL2003 (WIP)

From
Neil Conway
Date:
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


Re: width_bucket() per SQL2003 (WIP)

From
Tom Lane
Date:
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

Re: width_bucket() per SQL2003 (WIP)

From
Neil Conway
Date:
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