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(at)ag(dot)or(dot)at | e9225039(at)student(dot)tuwien(dot)ac(dot)at +
> +------------------------------------------------------------------------+
>

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)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)