Thread

1. Re: [SQL] subqueries

   Walt Bigelow <walt@stimpy.com> — 1999-04-13T13:12:34Z

   
   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
   
   
   
   

2. Re: [SQL] subqueries

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-04-13T15:37:43Z

   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