Thread: Bug on complex join
Hi! I am continuing playing with the query (I reduced it to): SELECT sh.distr_id , d.distr_id FROM central cn, shops sh, districts d WHERE cn.shop_id = sh.shop_id and got 27963 rows. I filtered it out (with awk:) where $1 == $2 (1st column is equal to second) and got 3104 rows. But the query SELECT d.* FROM central cn, shops sh, districts d WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id returned 0 rows. Where is the bug? Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they justGOSUB without RETURN.
> SELECT sh.distr_id , d.distr_id
> FROM central cn, shops sh, districts d
> WHERE cn.shop_id = sh.shop_id
> and got 27963 rows.
> But the query
> SELECT d.*
> FROM central cn, shops sh, districts d
> WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
Why did you change both the target columns *and* the query qualification
between these two examples? Is the "SELECT d.*" required to get the
query to fail?? If not, then...
For some reason sh.distr_id is not equal to d.distr_id. Are they
different data types? Do they have some embedded blanks?? Probably not a
Postgres bug, since the query itself looks pretty simple...
- Tom
Hi! On Fri, 5 Mar 1999, Thomas G. Lockhart wrote: > > SELECT sh.distr_id , d.distr_id > > FROM central cn, shops sh, districts d > > WHERE cn.shop_id = sh.shop_id > > and got 27963 rows. > > But the query > > SELECT d.* > > FROM central cn, shops sh, districts d > > WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id > > Why did you change both the target columns *and* the query qualification > between these two examples? Is the "SELECT d.*" required to get the > query to fail?? If not, then... Cause I need something in the target list. Wrong way to test it? What is a better way? > For some reason sh.distr_id is not equal to d.distr_id. Are they > different data types? Do they have some embedded blanks?? Probably not a Both are int2. No blanks (at least I cannot imagine blanks in int2 :). > Postgres bug, since the query itself looks pretty simple... That's why I am very confused. :( > > - Tom > Oleg. ---- Oleg Broytmann National Research Surgery Centre http://sun.med.ru/~phd/ Programmers don't die, they justGOSUB without RETURN.
Oleg Broytmann wrote: > > Hi! > > I am continuing playing with the query (I reduced it to): > SELECT sh.distr_id , d.distr_id > FROM central cn, shops sh, districts d > WHERE cn.shop_id = sh.shop_id > > and got 27963 rows. I filtered it out (with awk:) where $1 == $2 (1st > column is equal to second) and got 3104 rows. > > But the query > SELECT d.* > FROM central cn, shops sh, districts d > WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id > > returned 0 rows. > > Where is the bug? Please post me EXPLAIN VERBOSE for second query. Vadim
On Sat, 6 Mar 1999, Vadim Mikheev wrote: > Oleg Broytmann wrote: > > > > Hi! > > > > I am continuing playing with the query (I reduced it to): > > SELECT sh.distr_id , d.distr_id > > FROM central cn, shops sh, districts d > > WHERE cn.shop_id = sh.shop_id > > > > and got 27963 rows. I filtered it out (with awk:) where $1 == $2 (1st > > column is equal to second) and got 3104 rows. > > > > But the query > > SELECT d.* > > FROM central cn, shops sh, districts d > > WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id > > > > returned 0 rows. > > > > Where is the bug? > > Please post me EXPLAIN VERBOSE for second query. EXPLAIN VERBOSE SELECT d.* FROM central cn, shops sh, districts d WHERE cn.shop_id = sh.shop_id AND sh.distr_id =d.distr_id ; pqReadData() -- backend closed the channel unexpectedly.This probably means the backend terminated abnormally before or whileprocessing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. :((( > Vadim > Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they justGOSUB without RETURN.
Hello!
Another symptom. The query
SELECT cn.date_i, cn.pos_id FROM central cn
WHERE cn.date_i >= current_date - '300 days'::timespan
returns 3156 rows. But this:
SELECT cn.date_i, p.subsec_id, cn.pos_id, p.pos_id FROM central cn, shops sh, districts d, positions p WHERE
cn.date_i>= current_date - '300 days'::timespan
failed:
pqReadData() -- backend closed the channel unexpectedly.This probably means the backend terminated abnormally before or
whileprocessing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
Tables attached (ZIP file with script to recreate tables and SQL
commands).
Oleg.
---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they
justGOSUB without RETURN.
Oleg Broytmann wrote: > > Hello! > > Another symptom. The query > > SELECT cn.date_i, cn.pos_id > FROM central cn > WHERE cn.date_i >= current_date - '300 days'::timespan > > returns 3156 rows. But this: > > SELECT cn.date_i, p.subsec_id, cn.pos_id, p.pos_id > FROM central cn, shops sh, districts d, positions p > WHERE cn.date_i >= current_date - '300 days'::timespan this should return 3156 * count(shops) * count(districts) * count(positions) which is probably too much for the backend ;( ----------------------- Hannu
Hi! On Tue, 9 Mar 1999, Hannu Krosing wrote: > > SELECT cn.date_i, cn.pos_id > > FROM central cn > > WHERE cn.date_i >= current_date - '300 days'::timespan > > > > returns 3156 rows. But this: > > > > SELECT cn.date_i, p.subsec_id, cn.pos_id, p.pos_id > > FROM central cn, shops sh, districts d, positions p > > WHERE cn.date_i >= current_date - '300 days'::timespan > > this should return > 3156 * count(shops) * count(districts) * count(positions) > > which is probably too much for the backend ;( Bad news. Thanks for pointing this. > ----------------------- > Hannu > Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they justGOSUB without RETURN.
Oleg Broytmann wrote: > > Hi! > > On Tue, 9 Mar 1999, Hannu Krosing wrote: > > > SELECT cn.date_i, cn.pos_id > > > FROM central cn > > > WHERE cn.date_i >= current_date - '300 days'::timespan > > > > > > returns 3156 rows. But this: > > > > > > SELECT cn.date_i, p.subsec_id, cn.pos_id, p.pos_id > > > FROM central cn, shops sh, districts d, positions p > > > WHERE cn.date_i >= current_date - '300 days'::timespan > > > > this should return > > 3156 * count(shops) * count(districts) * count(positions) > > > > which is probably too much for the backend ;( ^^^^^^^^^^^^^^^ For the client-side, not for the backend - backend doesn't keep all result tuples in memory. > > Bad news. Thanks for pointing this. Vadim