Thread: failed to build any 5-way joins

Alessandro Depase wrote:
> The query generating the error is:

This is useless unless we know table schema, what data is in the tables, 
and what software version you use.

"Alessandro Depase" <alessandro.depase@libero.it> writes:
> I got the error message reported in the subject and I could not find anythi=
> ng about it anywhere.

It's a bug, but we can't do much about it unless you provide a
reproducible example.  In addition to the query itself, schemas
for the tables involved would be needed (use pg_dump -s).  Test
that you can load the schema dump into an empty database, execute
the problem query, and get the failure.
        regards, tom lane

Peter Eisentraut wrote:
> > The query generating the error is:
>
> This is useless unless we know table schema, what data is in the tables,
> and what software version you use.

I can just give you (at the end of this answer) the DDL for the fields I
reported in the query, but they are enough because I tried the example I'm
giving you (with all tables empty and without FK, while in the original
database they had less than 50 rows each) and I got the same result (so, it
should be data independent - but I understand that you meant that the error
could be data type dependent, so I give you the - reduced - tables
definitions).
I expected that the error was data independent, because, as I told before, I
could not get the plan for the not-working query.

Version? In my previous message I told that I'm using 7.4-1 on cygwin
distribution. What other info do you need? How can I get them?
All I can think more than this is that I use a Windows XP O.S. and that I
got this error both using JDBC and using pgAdmin III (but it seems to me
that this is an error client-independent).

The other problem, however, as I asked before, is: where can I find the
documentation for this error (well, besides the source, of course)? are
there known limits in joining tables?

Thanks again
Bye
   Alessandro Depase

CREATE TABLE public.info
( infoid varchar(10) NOT NULL, parent_infoid varchar(10), main_infoid varchar(10), fieldtypeid varchar(10) NOT NULL,
CONSTRAINTinfo_pkey PRIMARY KEY (infoid)
 
) WITH OIDS;

CREATE TABLE public.users_auth_groups
( userid varchar(10) NOT NULL, groupid varchar(10) NOT NULL, CONSTRAINT users_auth_groups_pkey PRIMARY KEY (userid,
groupid)
) WITH OIDS;

CREATE TABLE public.field_auth
( groupid varchar(10) NOT NULL, fieldtypeid varchar(10) NOT NULL, read_write varchar(1) NOT NULL, CONSTRAINT
field_auth_pkeyPRIMARY KEY (groupid, fieldtypeid)
 
) WITH OIDS;

CREATE TABLE public.category_auth
( groupid varchar(10) NOT NULL, categoryid varchar(10) NOT NULL, read_write varchar(1), CONSTRAINT category_auth_pkey
PRIMARYKEY (groupid, categoryid)
 
) WITH OIDS;

CREATE TABLE public.info_category
( infoid varchar(10) NOT NULL, categoryid varchar(10) NOT NULL, CONSTRAINT info_category_pkey PRIMARY KEY (infoid,
categoryid)
) WITH OIDS;

On Tuesday 16 December 2003 15:34, Alessandro Depase wrote:
>
People smarter than me are looking at this, but I can give you some quick 
feedback.
> Version? In my previous message I told that I'm using 7.4-1 on cygwin
> distribution. What other info do you need? How can I get them?

Are you sure it's 7.4.1? I wasn't aware that was publicly released yet.

> All I can think more than this is that I use a Windows XP O.S. and that I
> got this error both using JDBC and using pgAdmin III (but it seems to me
> that this is an error client-independent).

It certainly looks like a bug in PG itself, rather than client-related. I'd 
agree with your guess that it's somewhere in the query rewriter.

> The other problem, however, as I asked before, is: where can I find the
> documentation for this error (well, besides the source, of course)? are
> there known limits in joining tables?

I'm not aware of any particular limits on joins. There's even a genetic 
optimiser that kicks in when joins go above a certain number of tables 
(default of 12 but configurable in 7.4 iirc?).

--  Richard Huxton Archonet Ltd

The version declared by the cygwin setup is 7.4-1 (I don't know if the '-1'
is the same that your '.1' or, for example, a progressive in the cygwin
PostgreSQL build sequence).

A 'select version()' answers with this information:
PostgreSQL 7.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.3.1 (cygming
special)

Are there other ways to get a better insight?
   Alessandro Depase

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Alessandro Depase" <alessandro.depase@libero.it>; "Peter Eisentraut"
<peter_e@gmx.net>; <pgsql-sql@postgresql.org>
Sent: Tuesday, December 16, 2003 5:12 PM
Subject: Re: [SQL] failed to build any 5-way joins


> On Tuesday 16 December 2003 15:34, Alessandro Depase wrote:
> >
> People smarter than me are looking at this, but I can give you some quick
> feedback.
> > Version? In my previous message I told that I'm using 7.4-1 on cygwin
> > distribution. What other info do you need? How can I get them?
>
> Are you sure it's 7.4.1? I wasn't aware that was publicly released yet.
>
> > All I can think more than this is that I use a Windows XP O.S. and that
I
> > got this error both using JDBC and using pgAdmin III (but it seems to me
> > that this is an error client-independent).
>
> It certainly looks like a bug in PG itself, rather than client-related.
I'd
> agree with your guess that it's somewhere in the query rewriter.
>
> > The other problem, however, as I asked before, is: where can I find the
> > documentation for this error (well, besides the source, of course)? are
> > there known limits in joining tables?
>
> I'm not aware of any particular limits on joins. There's even a genetic
> optimiser that kicks in when joins go above a certain number of tables
> (default of 12 but configurable in 7.4 iirc?).
>
> -- 
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

"Alessandro Depase" <alessandro.depase@libero.it> writes:
> select  *
> from info where parent_infoid is null=20
> and fieldtypeid in (select fieldtypeid from users_auth_groups uag1, field_a=
> uth fa where userid =3D 8 and fa.groupid =3D uag1.groupid)=20
> and main_infoid in (select ic.infoid from info_category ic, category_auth c=
> a, users_auth_groups uag2=20
>   where ic.categoryid =3D ca.categoryid and uag2.userid =3D 8 and uag2.grou=
> pid =3D ca.groupid and read_write =3D 'W')=20

Hmm.  It's right, there's no way to construct a sub-plan that joins just
that number of relations, because of the constraint that the IN
sub-selects have to be fully formed before we can do IN processing.

A brute force solution is to just remove the error cross-check in 
src/backend/optimizer/path/joinrels.c:
       if (result_rels == NIL)           elog(ERROR, "failed to build any %d-way joins", level);

I'll probably install some less-drastic fix for 7.4.1, but if you need a
solution right now, that will get you going.
        regards, tom lane

Thanks Tom, but I think I can wait, being possible to use the exists clause
and not having, at the moment a big database.
When there will be the less-drastic fix you refer to, I will use it and
change my query.

Thanks again.
Bye   Alessandro Depase

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Alessandro Depase" <alessandro.depase@libero.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, December 17, 2003 4:32 PM
Subject: Re: [SQL] failed to build any 5-way joins


> "Alessandro Depase" <alessandro.depase@libero.it> writes:
> > select  *
> > from info where parent_infoid is null=20
> > and fieldtypeid in (select fieldtypeid from users_auth_groups uag1,
field_a=
> > uth fa where userid =3D 8 and fa.groupid =3D uag1.groupid)=20
> > and main_infoid in (select ic.infoid from info_category ic,
category_auth c=
> > a, users_auth_groups uag2=20
> >   where ic.categoryid =3D ca.categoryid and uag2.userid =3D 8 and
uag2.grou=
> > pid =3D ca.groupid and read_write =3D 'W')=20
>
> Hmm.  It's right, there's no way to construct a sub-plan that joins just
> that number of relations, because of the constraint that the IN
> sub-selects have to be fully formed before we can do IN processing.
>
> A brute force solution is to just remove the error cross-check in
> src/backend/optimizer/path/joinrels.c:
>
>         if (result_rels == NIL)
>             elog(ERROR, "failed to build any %d-way joins", level);
>
> I'll probably install some less-drastic fix for 7.4.1, but if you need a
> solution right now, that will get you going.
>
> regards, tom lane
>
>