Re: 8.1 substring bug?

On Fri, 11 Nov 2005, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > It looks to me like we should be supporting any exact numeric with scale 0
> > there (at least AFAICS from SQL92 and SQL03), so I don't think the current
> > behavior is compliant. It doesn't look like adding a numeric overload
> > of the function works, and the function also becomes ambiguous for int2
> > inputs. :(
>
> Currently (see gram.y, about line 7600) the grammar converts
>
>     SUBSTRING(foo FOR bar)
>
> into
>
>     pg_catalog.substring(foo, 1, bar)
>
> and then leaves the normal function-call-analysis code to make the best
> of it with that.  If "bar" isn't implicitly castable to integer then
> you've got trouble.

Right, I was thinking we could get around it with another substring that
took two numerics, but then I think FROM int2 FOR int2 would be
ambiguous.

> I was toying with the idea of making it translate instead to
>
>     pg_catalog.substring(foo, 1, (bar)::int4)
>
> since AFAICS there isn't any other reasonable mapping once you have
> committed to having the "1" in there.  This does not solve the general
> problem, but it'd address the particular case anyway ...

And, it's fairly reasonable to assume at least right now that any
reasonable string offset or length fits in an int4.