Re: substr or char_length problem

On Fri, Jun 17, 2005 at 12:25:28PM +0200, Andreas Kretschmer wrote:
> am 17.06.2005, um 13:01:15 +0300 mailte Fatih Cerit folgendes:
> >
> > I have a problem with function substr or char_length or both. I guery A2
> > and it works fine. But sometimes gives 'ERROR: negative substring length
> > not allowed'. When I test many many times with diffrent values, never gives
> > error. Sample table and query below.
> >
> > A1 A2
> > -------------------
> > 1 1957
> > 2 197
> > 3 19
> > 4
> > 5 NULL
> > 6 1
> > 7 195
> >
> > Select * from tbl_xxx where
> > tbl_xxx.A2=substr('196895588454554545454',0,char_length(tbl_xxx.A2)+1);
>
> Perhaps because char_length() returns NULL and this is a invalid value
> for substr(). Use coalesce():

substr() is marked STRICT, also known as RETURNS NULL ON NULL INPUT,
so it should simply return NULL if the length is NULL:

SELECT oid::regprocedure, proisstrict
FROM pg_proc
WHERE proname = 'substr';
oid | proisstrict
-------------------------------+-------------
substr(bytea,integer) | t
substr(text,integer) | t
substr(bytea,integer,integer) | t
substr(text,integer,integer) | t
(4 rows)

SELECT substr('196895588454554545454', 0, NULL + 1) IS NULL;
?column?
----------
t
(1 row)

The error "negative substring length not allowed" implies that the
length being passed is negative. Since the query adds 1 to the
return value of char_length(), that implies that char_length() is
returning a value <= -2. I don't know what could cause that short
of a bug in the backend. Or am I missing something?

I couldn't duplicate the error with the given example -- is that the
real data and query or just a contrived example that doesn't actually
fail? What version of PostgreSQL are you using? What encoding?
What OS and version? What are the results of the following query?

SELECT a1, char_length(a2), a2 FROM tbl_xxx WHERE char_length(a2) < 0;

Could you post a self-contained test case, that is, a complete list
of SQL statements that somebody could load into an empty database
to reproduce the problem?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/