Re: BUG #1735: row number -1 is out of range 0..-1 error

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