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.


Fri, 5 Mar 1999 16:13:12 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
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

Fri, 05 Mar 1999 15:07:08 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Bug on complex join
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.


Fri, 5 Mar 1999 18:13:50 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Bug on complex join
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

Sat, 06 Mar 1999 17:06:48 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Bug on complex join
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.


Tue, 9 Mar 1999 12:33:08 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Bug on complex join
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.
Tue, 9 Mar 1999 12:40:09 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Bug on complex join
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

Tue, 09 Mar 1999 14:56:30 +0200
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] Bug on complex join
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.


Tue, 9 Mar 1999 18:47:24 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Bug on complex join
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

Wed, 10 Mar 1999 09:20:13 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Bug on complex join