failed to build any 5-way joins

Hi all,
I got the error message reported in the subject and I could not find anything about it anywhere.
I cannot get any explain plan for this query.

I'm working on PosgreSQL 7.4-1, the latest cygwin distribution I can find.

The query generating the error is:

select *
from info where parent_infoid is null
and fieldtypeid in (select fieldtypeid from users_auth_groups uag1, field_auth fa where userid = 8 and fa.groupid = uag1.groupid)
and main_infoid in (select ic.infoid from info_category ic, category_auth ca, users_auth_groups uag2
where ic.categoryid = ca.categoryid and uag2.userid = 8 and uag2.groupid = ca.groupid and read_write = 'W')

I already have a workaround, but I don't like it because it seems to me that it could be a little slower (executing the exists clause for every possible record got from the rest of the query, according to the explain plan results - and if I understood them well :) ):

select *
from info where parent_infoid is null
and exists (select * from users_auth_groups uag1, field_auth fa where userid = 8 and fa.groupid = uag1.groupid and info.fieldtypeid = fa.fieldtypeid)
and main_infoid in (select ic.infoid from info_category ic, category_auth ca, users_auth_groups uag2
where ic.categoryid = ca.categoryid and uag2.userid = 8 and uag2.groupid = ca.groupid and read_write = 'W')

This seems to work, however (and I will keep it until I will find another solution - well, I know that, for example, I can do it using stored procedure, splitting the query in more steps, but this is not my first choice, at the moment, for reasons too long to explain here).

The problem is: does anyone know what sort of error did I get and/or where can I find docs about it?
I suppose (just from the error message) that the optimizer is trying to translate the first query in a join and it gets an error doing so. But, is this error related to some limits in PostgreSQL? Are there limits in joining tables? I could not find anything about this too (I just found a my-sql crash-me test report in which they tell that PostgreSQL - v. 7.1.1 - passed the test with 64+ tables).

Thanks to you all for you help

Bye
Alessandro Depase

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(dot)depase(at)libero(dot)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,
CONSTRAINT info_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_pkey PRIMARY 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 PRIMARY KEY (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(at)archonet(dot)com>
To: "Alessandro Depase" <alessandro(dot)depase(at)libero(dot)it>; "Peter Eisentraut"
<peter_e(at)gmx(dot)net>; <pgsql-sql(at)postgresql(dot)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(dot)depase(at)libero(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Alessandro Depase" <alessandro(dot)depase(at)libero(dot)it>
Cc: <pgsql-sql(at)postgresql(dot)org>
Sent: Wednesday, December 17, 2003 4:32 PM
Subject: Re: [SQL] failed to build any 5-way joins

> "Alessandro Depase" <alessandro(dot)depase(at)libero(dot)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
>
>