Обсуждение: substr or char_length problem

Dear ALL

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);


Thanks
Fatih Cerit 

am  17.06.2005, um 13:01:15 +0300 mailte Fatih Cerit folgendes:
> Dear ALL
> 
> 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('196895588454554545454',0,coalesce(char_length(tbl_xxx.A2),0)+1);


Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

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)         | tsubstr(text,integer)          |
tsubstr(bytea,integer,integer)| tsubstr(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/