Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: [SQL] subqueries

Re: [SQL] subqueries

From
Tom Lane
Date:
Walt Bigelow <walt@stimpy.com> writes:
> SELECT agencyid, first, last 
> FROM tbladdress
> WHERE type = 1
> AND agencyid NOT IN
>     (SELECT distinct agencylink
>     FROM tbladdress 
>     WHERE type = 2)
> ORDER BY last;

Looks reasonable ...

> This query returns 0 rows [ when it shouldn't ].

Sounds like a bug.  What version of Postgres are you running?
What do you get from "EXPLAIN"ing that query?  What are the
data types of the columns being used?
        regards, tom lane

Re: [SQL] subqueries

From
Walt Bigelow
Date:

On Tue, 13 Apr 1999, Tom Lane wrote:

> Walt Bigelow <walt@stimpy.com> writes:
> > SELECT agencyid, first, last 
> > FROM tbladdress
> > WHERE type = 1
> > AND agencyid NOT IN
> >     (SELECT distinct agencylink
> >     FROM tbladdress 
> >     WHERE type = 2)
> > ORDER BY last;
> 
> Looks reasonable ...
> 
> > This query returns 0 rows [ when it shouldn't ].
> 
> Sounds like a bug.  What version of Postgres are you running?
> What do you get from "EXPLAIN"ing that query?  What are the
> data types of the columns being used?
> 

Tom,

I am using postgres 6.4.2 on a RH5.1 SMP box.

The output of explain:
NOTICE: QUERY PLAN:

Seq Scan on tbladdress  (cost=50.34 size=348 width=24) SubPlan   ->  Unique  (cost=50.34 size=0 width=0)         ->
Sort (cost=50.34 size=0 width=0)              ->  Seq Scan on tbladdress  (cost=50.34 size=348 width=4)
 

EXPLAIN

The data types are int4 for both agencyid and agencylink.  And there are
about 800 rows total of 5 different types.  (the type field is int4 as
well)

Thanks again,
Walt

Re: [SQL] subqueries

From
Tom Lane
Date:
Walt Bigelow <walt@stimpy.com> writes:
> On Tue, 13 Apr 1999, Tom Lane wrote:
>>>> This query returns 0 rows [ when it shouldn't ].

> The output of explain:
> NOTICE: QUERY PLAN:

> Seq Scan on tbladdress  (cost=50.34 size=348 width=24)
>   SubPlan
>     ->  Unique  (cost=50.34 size=0 width=0)
>           -> Sort  (cost=50.34 size=0 width=0)
>                ->  Seq Scan on tbladdress  (cost=50.34 size=348 width=4)

That looks about like what I'd expect (although I suppose you left
off the ORDER BY clause when doing the EXPLAIN?  With it, there should
be a top-level Sort node).

I'm not able to duplicate your problem here on a toy table, using either
6.4.2 or 6.5-current.  Perhaps there is some platform dependency
involved.  Can anyone else try it?
        regards, tom lane