Hello!

   I rewrote my 4-tables join to use subselects:

SELECT DISTINCT subsec_id FROM positions
   WHERE pos_id IN
      (SELECT DISTINCT pos_id
         FROM central
            WHERE shop_id IN
               (SELECT shop_id FROM shops
                  WHERE distr_id IN
                     (SELECT distr_id FROM districts
                        WHERE city_id = 2)
               )
      )
;

   This does not work, either - postgres loops forever, until I cancel
psql.

   I splitted it - I ran

      (SELECT DISTINCT pos_id
         FROM central
            WHERE shop_id IN
               (SELECT shop_id FROM shops
                  WHERE distr_id IN
                     (SELECT distr_id FROM districts
                        WHERE city_id = 2)
               )
      )

   and stored result in a file. Then I substituted the subselect with the
file:

SELECT DISTINCT subsec_id FROM positions
   WHERE pos_id IN
(1, 2, 3, 6, 22, 25, 26, 27, 28, 29, 31, 33, 34, 35, 38, 41, 42, 44, 45,
46, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63, 64)

   and got desired result within a second.

   This finally solves my problem, but I need to pass a long way to find
that postgres cannot handle such not too complex joins and subselects.

Oleg.
---- 
    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
           Programmers don't die, they just GOSUB without RETURN.


Wed, 10 Mar 1999 12:33:34 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Bug on complex subselect (was: Bug on complex join)
Hello!

   Now for those who said he cannot beleive there is a bug in join - the
following query produced two different results on two different systems
with identical data loaded:

SELECT DISTINCT p.subsec_id
   FROM central cn, shops sh, districts d, positions p
      WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
      AND   d.city_id = 2 AND cn.pos_id = p.pos_id
;
subsec_id
---------
        1
        2
       10
       11
       12
       13
       14
       15
(8 rows)

   on one system and

subsec_id
---------
(0 rows)

   on the other.

   First system is sparc-solrais and second is intel-linux (Debian 2.0,
glibc2). Another glibc2-realted bug?

Oleg.
---- 
    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
           Programmers don't die, they just GOSUB without RETURN.


Mon, 15 Mar 1999 16:36:06 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Bug on complex join