pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubstr or char_length problem
3+ messages / 3 participants
[nested] [flat]
* substr or char_length problem
@ 2005-06-17 10:01 Fatih Cerit <fatih@intersan.com.tr>
0 siblings, 1 reply; 3+ messages in thread
From: Fatih Cerit @ 2005-06-17 10:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [despammed] substr or char_length problem
@ 2005-06-17 10:25 Andreas Kretschmer <akretschmer@despammed.com>
parent: Fatih Cerit <fatih@intersan.com.tr>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas Kretschmer @ 2005-06-17 10:25 UTC (permalink / raw)
To: pgsql-sql
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 ===
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: substr or char_length problem
@ 2005-06-17 12:06 Michael Fuhr <mike@fuhr.org>
parent: Andreas Kretschmer <akretschmer@despammed.com>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-06-17 12:06 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-17 12:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-17 10:01 substr or char_length problem Fatih Cerit <fatih@intersan.com.tr>
2005-06-17 10:25 ` Andreas Kretschmer <akretschmer@despammed.com>
2005-06-17 12:06 ` Michael Fuhr <mike@fuhr.org>
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