Thread: BUG #1735: row number -1 is out of range 0..-1 error
The following bug has been logged online:
Bug reference: 1735
Logged by: Borislav Ivanov
Email address: b.ivanov@exsisto.com
PostgreSQL version: 8.0.3
Operating system: Fedora Core
Description: row number -1 is out of range 0..-1 error
Details:
I get this message 'row number -1 is out of range 0..-1' sometimes. I say
sometimes, because this happens unexpected when I submit absolutely correct
query. This is the last time it happened:
I have two tables:
CREATE TABLE billingplans_freetalktime
(
plan_id int4 NOT NULL,
tariff_id int4 NOT NULL,
seconds int4 NOT NULL,
accumulate int2 NOT NULL,
period interval NOT NULL,
description varchar(255),
id serial NOT NULL
)
WITHOUT OIDS;
ALTER TABLE billingplans_freetalktime OWNER TO ser;
CREATE TABLE tariffs
(
id_tariffs_key serial NOT NULL,
id_tariff int4 NOT NULL,
prefix char(20) NOT NULL,
description char(30) NOT NULL,
voice_rate numeric(8,4) NOT NULL,
from_day int2 NOT NULL,
to_day int2 NOT NULL,
from_hour int2,
to_hour int2 NOT NULL,
CONSTRAINT pk_tariffs PRIMARY KEY (id_tariffs_key),
CONSTRAINT fk_tariffs_id_tariff FOREIGN KEY (id_tariff)
REFERENCES tariffsnames (id_tariff) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITHOUT OIDS;
ALTER TABLE tariffs OWNER TO ser;
and when i submit this query:
select
billingplans_freetalktime.id, billingplans_freetalktime.seconds,
billingplans_freetalktime.accumulate, billingplans_freetalktime.period
from (billingplans_freetalktime inner join tariffs
on billingplans_freetalktime.tariff_id = tariffs.id_tariff)
where billingplans_freetalktime.plan_id = 5
and '3597' like tariffs.prefix||'%'
order by length(tariffs.prefix) desc;
it returns:
row number -1 is out of range 0..-1
Total query runtime: 151 ms.
Data retrieval runtime: 80 ms.
0 rows retrieved.
although there are these rows in each table:
insert into billingplans_freetalktime
values
(5,41,12,0,'7 days','za proba',3)
insert into tariffs
values
(1, 41, '359', 'descr', 12.3, 0, 6, 0, 23)
"3";"12";"0";"7 days is the result I'm getting in pgadmin. Regards, Andreas
On Tue, Jun 28, 2005 at 07:03:06PM +0200, Andreas Pflug wrote: > > "3";"12";"0";"7 days > > is the result I'm getting in pgadmin. Same here in psql: id | seconds | accumulate | period ----+---------+------------+-------- 3 | 12 | 0 | 7 days (1 row) Borislav, apparently something's different in your environment than what we're testing with. Locale and encoding maybe? Although I don't know if or how that would cause the results you're seeing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
OK, but check this thing out:
(I did my best to replicate this example on another pgSQL server
running on different platform)
CREATE TABLE billingplans (
id int NOT NULL,
reseller_id integer NOT NULL,
base_tariff_id integer NOT NULL,
discount smallint NOT NULL,
freetalktime smallint NOT NULL,
description character varying(255),
status integer DEFAULT 0 NOT NULL,
created timestamp without time zone
);
INSERT INTO billingplans (id, reseller_id, base_tariff_id, discount,
freetalktime, description, status, created) VALUES (5, 1004, 38, 0, 0, 'rt',
1, '2005-06-20 00:00:00');
INSERT INTO billingplans (id, reseller_id, base_tariff_id, discount,
freetalktime, description, status, created) VALUES (1, 1000, 29, 1, 1, 'test
billing plan', 1, NULL);
INSERT INTO billingplans (id, reseller_id, base_tariff_id, discount,
freetalktime, description, status, created) VALUES (7, 1000, 29, 0, 0,
'base', 1, '2005-06-28 00:00:00');
CREATE TABLE billingplans_lots (
plan_id integer NOT NULL,
lot_id integer NOT NULL
);
INSERT INTO billingplans_lots (plan_id, lot_id) VALUES(1, 11);
CREATE TABLE resellerlots (
lotid int NOT NULL,
resellerid integer NOT NULL,
quantity integer DEFAULT 0 NOT NULL,
total numeric(12,4) DEFAULT 0 NOT NULL,
tariffid integer DEFAULT 1 NOT NULL,
lot_desc character(20) NOT NULL,
creation_date timestamp(6) without time zone NOT NULL,
client_type integer NOT NULL,
status integer DEFAULT 0 NOT NULL
);
INSERT INTO resellerlots (lotid, resellerid, quantity, total, tariffid,
lot_desc, creation_date, client_type, status) VALUES (11, 1000, 0, 0.0000,
38, 'testov za plan ', '2005-06-16 14:02:25.711764', 1, 1);
INSERT INTO resellerlots (lotid, resellerid, quantity, total, tariffid,
lot_desc, creation_date, client_type, status) VALUES (9, 1004, 1, 5.0000,
38, 'testov lot ', '2005-06-14 14:47:08.949854', 1, 1);
Then I submit:
SELECT
billingplans.id AS plan_id,
billingplans.description as plan_name
FROM (billingplans inner join billingplans_lots
on billingplans.id = billingplans_lots.plan_id) inner join resellerlots
on billingplans_lots.lot_id = resellerlots.lotid
WHERE resellerlots.lot_desc = 45678 and resellerlots.resellerid = 1000
and this is what I get:
row number -1 is out of range 0..-1
Total query runtime: 40 ms.
Data retrieval runtime: 30 ms.
0 rows retrieved.
I checked this through psql and:
plan_id | plan_name
---------+-----------
(0 rows)
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Andreas Pflug" <pgadmin@pse-consulting.de>
Cc: "Borislav Ivanov" <b.ivanov@exsisto.com>; <pgsql-bugs@postgresql.org>
Sent: Wednesday, June 29, 2005 4:20 PM
Subject: Re: [BUGS] BUG #1735: row number -1 is out of range 0..-1 error
> On Tue, Jun 28, 2005 at 07:03:06PM +0200, Andreas Pflug wrote:
>>
>> "3";"12";"0";"7 days
>>
>> is the result I'm getting in pgadmin.
>
> Same here in psql:
>
> id | seconds | accumulate | period
> ----+---------+------------+--------
> 3 | 12 | 0 | 7 days
> (1 row)
>
> Borislav, apparently something's different in your environment than
> what we're testing with. Locale and encoding maybe? Although I
> don't know if or how that would cause the results you're seeing.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> __________ NOD32 1.1157 (20050628) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.nod32.com
>
>
On Thu, Jun 30, 2005 at 06:03:09PM +0300, Bobi Ivanov wrote: > > SELECT > billingplans.id AS plan_id, > billingplans.description as plan_name > FROM (billingplans inner join billingplans_lots > on billingplans.id = billingplans_lots.plan_id) inner join resellerlots > on billingplans_lots.lot_id = resellerlots.lotid > WHERE resellerlots.lot_desc = 45678 and resellerlots.resellerid = 1000 > > and this is what I get: > > row number -1 is out of range 0..-1 > Total query runtime: 40 ms. > Data retrieval runtime: 30 ms. > 0 rows retrieved. > > I checked this through psql and: > > plan_id | plan_name > ---------+----------- > (0 rows) I get the same result (zero rows) in psql; that appears to be correct, given the join conditions and the WHERE clause. Maybe Andreas or somebody else can test the query with pgAdmin. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
=20 > -----Original Message----- > From: pgsql-bugs-owner@postgresql.org=20 > [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Michael Fuhr > Sent: 01 July 2005 14:35 > To: Bobi Ivanov > Cc: Andreas Pflug; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #1735: row number -1 is out of range=20 > 0..-1 error >=20 > On Thu, Jun 30, 2005 at 06:03:09PM +0300, Bobi Ivanov wrote: > > > > SELECT > > billingplans.id AS plan_id, > > billingplans.description as plan_name > > FROM (billingplans inner join billingplans_lots > > on billingplans.id =3D billingplans_lots.plan_id) inner join=20 > resellerlots > > on billingplans_lots.lot_id =3D resellerlots.lotid > > WHERE resellerlots.lot_desc =3D 45678 and=20 > resellerlots.resellerid =3D 1000 > >=20 > > and this is what I get: > >=20 > > row number -1 is out of range 0..-1 > > Total query runtime: 40 ms. > > Data retrieval runtime: 30 ms. > > 0 rows retrieved. > >=20 > > I checked this through psql and: > >=20 > > plan_id | plan_name > > ---------+----------- > > (0 rows) >=20 > I get the same result (zero rows) in psql; that appears to be > correct, given the join conditions and the WHERE clause. Maybe > Andreas or somebody else can test the query with pgAdmin. I get 0 rows and no errors with pgAdmin SVN trunk (not that we have changed any of that code recently anyway). Regards, Dave