X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A97F1D1C9E5 for ; Tue, 16 Dec 2003 11:47:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15628-10 for ; Tue, 16 Dec 2003 07:46:53 -0400 (AST) Received: from vsmtp3alice.tin.it (vsmtp3.tin.it [212.216.176.223]) by svr1.postgresql.org (Postfix) with ESMTP id 846D7D1B450 for ; Tue, 16 Dec 2003 07:46:50 -0400 (AST) Received: from dedalus1 (82.48.207.64) by vsmtp3alice.tin.it (7.0.019) id 3FCEEABF00114F24 for pgsql-sql@postgresql.org; Tue, 16 Dec 2003 12:46:51 +0100 Message-ID: <001e01c3c3ca$4c0c4af0$0200a8c0@dedalus1> From: "Alessandro Depase" To: Subject: failed to build any 5-way joins Date: Tue, 16 Dec 2003 12:46:49 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001B_01C3C3D2.AC78EB20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.5 tagged_above=0.0 required=5.0 tests=HTML_20_30 X-Spam-Level: * X-Archive-Number: 200312/112 X-Sequence-Number: 16278 This is a multi-part message in MIME format. ------=_NextPart_000_001B_01C3C3D2.AC78EB20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I got the error message reported in the subject and I could not find anythi= ng 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=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 I already have a workaround, but I don't like it because it seems to me tha= t it could be a little slower (executing the exists clause for every possib= le record got from the rest of the query, according to the explain plan res= ults - and if I understood them well :) ): select * from info where parent_infoid is null=20 and exists (select * from users_auth_groups uag1, field_auth fa where useri= d =3D 8 and fa.groupid =3D uag1.groupid and info.fieldtypeid =3D fa.fieldty= peid)=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 This seems to work, however (and I will keep it until I will find another s= olution - well, I know that, for example, I can do it using stored procedur= e, splitting the query in more steps, but this is not my first choice, at t= he 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 tra= nslate the first query in a join and it gets an error doing so. But, is thi= s error related to some limits in PostgreSQL? Are there limits in joining t= ables? I could not find anything about this too (I just found a my-sql cras= h-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 ------=_NextPart_000_001B_01C3C3D2.AC78EB20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,
I got the error message reported in the su= bject and=20 I could not find anything about it anywhere.
I cannot get any explain plan for this=20 query.
 
I'm working on PosgreSQL 7.4-1, the latest= cygwin=20 distribution I can find.
 
The query generating the error is:<= /DIV>
 
select  *
from info where parent_i= nfoid is=20 null
and fieldtypeid in (select fieldtypeid from users_auth_groups uag1= ,=20 field_auth fa where userid =3D 8 and fa.groupid =3D uag1.groupid)
and= =20 main_infoid in (select ic.infoid from info_category ic, category_auth ca,= =20 users_auth_groups uag2
  where ic.categoryid =3D ca.categoryi= d and=20 uag2.userid =3D 8 and uag2.groupid =3D ca.groupid and read_write =3D 'W')= =20
I already have a workaround, but I don't l= ike it=20 because it seems to me that it could be a little slower (executing the exis= ts=20 clause for every possible record got from the rest of the query, according = to=20 the explain plan results - and if I understood them well :) ):
 
select  *
from info where parent_i= nfoid is=20 null
and exists (select * from users_auth_groups uag1, field_auth fa wh= ere=20 userid =3D 8 and fa.groupid =3D uag1.groupid and info.fieldtypeid =3D fa.fi= eldtypeid)=20
and main_infoid in (select ic.infoid from info_category ic, category_au= th=20 ca, users_auth_groups uag2
  where ic.categoryid =3D ca.categ= oryid=20 and uag2.userid =3D 8 and uag2.groupid =3D ca.groupid and read_write =3D 'W= ')=20
 
This seems to work, however (and I will ke= ep it=20 until I will find another solution - well, I know that, for example, I can = do it=20 using stored procedure, splitting the query in more steps, but this is not = my=20 first choice, at the moment, for reasons too long to explain here).<= /DIV>
 
The problem is: does anyone know what sort= of error=20 did I get and/or where can I find docs about it?
I suppose (just from the error message) th= at the=20 optimizer is trying to translate the first query in a join and it gets an e= rror=20 doing so. But, is this error related to some limits in PostgreSQL? Are ther= e=20 limits in joining tables? I could not find anything about this too (I just = found=20 a my-sql crash-me test report in which they tell that PostgreSQL - v. 7.1.1= -=20 passed the test with 64+ tables).
 
Thanks to you all for you help
 
Bye
    Alessandro Depase
 
 
------=_NextPart_000_001B_01C3C3D2.AC78EB20--