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
WHEREshop_id IN              (SELECT shop_id FROM shops                 WHERE distr_id IN                    (SELECT
distr_idFROM 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
justGOSUB without RETURN.