Thread
-
Bug on complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-05T13:13:12Z
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 just GOSUB without RETURN. -
Re: [HACKERS] Bug on complex join
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-03-05T15:07:08Z
> 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 -
Re: [HACKERS] Bug on complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-05T15:13:50Z
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 just GOSUB without RETURN. -
Re: [HACKERS] Bug on complex join
Vadim Mikheev <vadim@krs.ru> — 1999-03-06T10:06:48Z
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
-
Re: [HACKERS] Bug on complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-09T09:33:08Z
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 while processing 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 just GOSUB without RETURN. -
Re: [HACKERS] Bug on complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-09T09:40:09Z
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 while processing 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 just GOSUB without RETURN. -
Re: [HACKERS] Bug on complex join
Hannu Krosing <hannu@trust.ee> — 1999-03-09T12:56:30Z
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
-
Re: [HACKERS] Bug on complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-09T15:47:24Z
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 just GOSUB without RETURN. -
Re: [HACKERS] Bug on complex join
Vadim Mikheev <vadim@krs.ru> — 1999-03-10T02:20:13Z
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