Bug on complex join

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 just GOSUB 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 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.

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.

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 just GOSUB 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

Hello!

Now for those who said he cannot beleive there is a bug in join - the
following query produced two different results on two different systems
with identical data loaded:

SELECT DISTINCT p.subsec_id
FROM central cn, shops sh, districts d, positions p
WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
AND d.city_id = 2 AND cn.pos_id = p.pos_id
;
subsec_id
---------
1
2
10
11
12
13
14
15
(8 rows)

on one system and

subsec_id
---------
(0 rows)

on the other.

First system is sparc-solrais and second is intel-linux (Debian 2.0,
glibc2). Another glibc2-realted bug?

Oleg.
----
Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.