Re: [HACKERS] Bug on complex subselect (was: Bug on complex join)

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.