From: Michael Fuhr <mike@fuhr.org>
To: pgsql-sql@postgresql.org
Subject: Re: substr or char_length problem
Date: Fri, 17 Jun 2005 06:06:56 -0600
Message-ID: <20050617120656.GA72339@winnie.fuhr.org> (raw)
In-Reply-To: <20050617102528.GD15166@webserv.wug-glas.de>
References: <014a01c57323$90b72b20$b8e58bd5@cerit>
<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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org
Subject: Re: substr or char_length problem
In-Reply-To: <20050617120656.GA72339@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox