Re: bug with prune_joinrel() in 6.3.2

Hello Bruce!

I don't know exactly if the following bug has already been corrected. Nor
do I know who is responsible for the code around prune_joinrel() and that's
the reason why I send this to you. Could you please forward it to the
appropriate mailinglist and (or) to the responsible developer?

I detected the bug when I wanted to test my HAVING code. It is already
finished (including the use of subselects in the HavingQual) and all
the bugs from April are covered. But I don't want to release a patch
without having tested it very well. So I hope I will be able to send
a patch next week!

Great.

Regards Stefan
------------------------------------------------------------------------
Here's the description of the prune_joinrel() bug:

When I formulated the following query (using a subselect) executing it
on postgresql-6.3.2 (original, not the snapshot) on linux I observed
the following strange behaviour: (The table definitions are attached
below!)

The prune_joinrels function had a bug in 6.3.* releases that was fixed
in 6.3.2:

revision 1.13
date: 1998/04/02 07:27:15; author: vadim; state: Exp; lines: +11 -25
Fix merging pathes of pruned rels ("indices are unused" problem).

The basic problem is that certain indexes were not being used when they
should have been. I introduced the bug when I tried to fix some
recursion in the optimizer, and Vadim found my errors and fixed them.

I would suggest that the current 6.3.2 is correct, and that there is a
bug somewhere in your code. I believe the 6.3 version is working for
you because it is buggy and is not using certain indexes that it
normally should be using. The error in psort() sould seem to confirm my
suspision. You may try the EXPLAIN command to see how the different
versions are executing your query. That is how we found out about the
'missing index' problem in the first place.

---------------------------------------------------------------------------

-

psql client:
------------
stefan=> select s.sid
stefan-> from supplier s
stefan-> where s.sid in (select se1.pid
stefan-> from supplier s1, sells se1, part p1
stefan-> where s1.sid=se1.sid and se1.sid=s.sid and se1.pid=p1.pid);

FATAL: unrecognized data from the backend. It probably dumped core.

postmaster server:
------------------
postgres:/home/postgres# postmaster
Failed Assertion("!(((Psortstate *)node->psortstate) !=
(Psortstate *) ((void *)0)):", File: "psort.c", Line: 778)
!(((Psortstate *)node->psortstate) !=
(Psortstate *) ((void *)0)) (0) [No such file or directory]

I found out that this error did *NOT* occur with postgresql-6.3 and
looked at the changes since 6.3. It seems that the new function

prune_joinrel() in the file
postgresql-6.3.2/src/backend/optimizer/path/prune.c

is the reason for the error. When I replaced the new function by the
old one from version 6.3, the error did not occur any more.

Table definition:
-----------------
create table supplier (sid int4,
sname char(20),
city char(20));

create table sells (pid int4,
sid int4);

create table part (pname char(20),
pid int4,
cost int4);

insert into supplier (sid, sname, city)
values (1,'stefan','wien');
insert into supplier (sid, sname, city)
values (2,'richi','breitenfurt');
insert into supplier (sid, sname, city)
values (3,'eva','breitenfurt');
insert into supplier (sid, sname, city)
values (4,'walter','wien');
insert into supplier (sid, sname, city)
values (5,'edith','moedling');
insert into supplier (sid, sname, city)
values (6,'manu','breitenfurt');
insert into supplier (sid, sname, city)
values (7,'hugo','moedling');

insert into sells (pid, sid)
values (1,1);
insert into sells (pid, sid)
values (1,2);
insert into sells (pid, sid)
values (2,3);
insert into sells (pid, sid)
values (2,4);
insert into sells (pid, sid)
values (3,5);
insert into sells (pid, sid)
values (4,6);
insert into sells (pid, sid)
values (5,2);
insert into sells (pid, sid)
values (6,1);

insert into part (pname, pid, cost)
values ('kabel',1,100);
insert into part (pname, pid, cost)
values ('patrone',2,200);
insert into part (pname, pid, cost)
values ('maus',3,500);
insert into part (pname, pid, cost)
values ('tastatur',4,750);
insert into part (pname, pid, cost)
values ('bildschirm',5,8430);
insert into part (pname, pid, cost)
values ('festplatte',6,3450);

Destroy tables:
---------------
drop table part;
drop table supplier;
drop table sells;

Regards 
Stefan
-- 
+------------------------------------------------------------------------+
+ Simkovics Stefan                                                       +
+ Student an der TU Wien (Informatik)                                    +
+ Tel.: 02239/3367                                                       +
+ email: ssimkovi@ag.or.at | e9225039@student.tuwien.ac.at               +
+------------------------------------------------------------------------+

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)