Date: Fri, 17 Jun 2005 06:06:56 -0600
From: Michael Fuhr <mike@fuhr.org>
To: pgsql-sql@postgresql.org
Subject: Re: substr or char_length problem
Message-ID: <20050617120656.GA72339@winnie.fuhr.org>
References: <014a01c57323$90b72b20$b8e58bd5@cerit>
	<20050617102528.GD15166@webserv.wug-glas.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050617102528.GD15166@webserv.wug-glas.de>

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/