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(at)earthling(dot)net
Programmers don't die, they just GOSUB without RETURN.