Re: 8.1 substring bug?

On Fri, 11 Nov 2005, Tom Lane wrote:

> Martijn van Oosterhout <kleptog@svana.org> writes:
> > It's even sillier than that:
>
> > test=# SELECT substring ('1234567890' FOR 4::bigint);
> >  substring
> > -----------
> >
> > (1 row)
>
> > test=# SELECT substring ('1234567890' FOR 4::int);
> >  substring
> > -----------
> >  1234
> > (1 row)
>
> This has been complained of before.  The problem is that there is no
> implicit cast from bigint to int, but there is one from bigint to text,
> so the only acceptable mapping the parser can find is to convert bigint
> to text and apply the pattern-match version of substring().  (There are
> some other things happening here because of the weird SQL99 syntax, but
> that's the bottom line.)

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. :(