Wrong rows selected with view - Mailing list pgsql-general

PostgreSQL 7.4.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2 20050816 (prerelease) (Debian 4.0.1-5)

Hopefully this is something simple -- I assume it's a problem with my
SQL.  But it looks really weird to me at this late hour.

I have some tables for managing workshops, and I have a view (below).
I noticed for a given select it was returning the wrong number of
rows.  I expected seven rows back, but only got six.

I rewrote the view with just the joins and it returned the correct number
of rows.  So I started the brute force method of removing one thing at
a time in the view to see what would make it start returning the
correct number of rows.  That just confused me more.

Below if I comment out *any* single column that's marked "-- this" then
I get the correct number of rows.  If I comment out any rows marked
"-- nope" then there's no change (get six rows returned).  But, if I
start commenting out more than one "-- nope" then I get seven rows.

Can someone point out my error?


create view bar AS
    SELECT DISTINCT ON (class.id)  -- this
               class.id AS id,
               class.name AS name, -- this
               class.class_time AS class_time, -- this
               class.begin_reg_time AS begin_reg_time, -- this

               (CASE WHEN class.register_cutoff_time > class.class_time -- this
                    THEN class.register_cutoff_time
                    ELSE class.class_time
                END) AS register_stop_time,

               class.location AS location, -- nope
               class.workshop AS workshop, -- nope
               class.review_mode AS review_mode, -- nope
               class.workshop_group AS workshop_group, -- nope
               location.name AS location_name, -- this
               location.address AS address, -- this
               location.city AS city, -- nope
               location.state AS state, -- this
               location.zip AS zip, -- nope

               region.id AS region, -- nope
               region.name AS region_name, -- nope
               region.sort_order AS region_sort,  -- nope

               person.id AS person_id,
               UPPER( person.last_name || person.first_name ) AS instructor, -- this

               class.cutoff_message AS cutoff_message,    -- this
               class.full_message AS full_message,      -- this
               class.wait_description AS wait_description  -- this

          FROM class, location, region, person, instructors

         WHERE class.location           = location.id   -- join with location
               AND class.id             = instructors.class   -- join the instructors
               AND instructors.person   = person.id     -- join the person(s)
               AND location.region      = region.id;    -- join the location to a region
select id from bar where person_id = 84;
drop view bar;

  id
------
  727
  739
  804
  813
  867
 1244
(6 rows

Comment out one column:


               -- class.full_message AS full_message,      -- this
               class.wait_description AS wait_description  -- this

          FROM class, location, region, person, instructors

         WHERE class.location           = location.id   -- join with location
               AND class.id             = instructors.class   -- join the instructors
               AND instructors.person   = person.id     -- join the person(s)
               AND location.region      = region.id;    -- join the location to a region
select id from bar where person_id = 84;
drop view bar;

  id
------
  727
  739
  804
  813
  867
 1243
 1244
(7 rows)

It's always class.id 1243 that doesn't show up.  Not sure this helps,
but:


moseley@bumby:~$ echo '\x \\ select * from class where id = 1243' | psql ws2 > 1243
moseley@bumby:~$ echo '\x \\ select * from class where id = 1244' | psql ws2 > 1244
moseley@bumby:~$ diff -U 0 1243 1244
--- 1243        2005-11-15 20:16:26.619412721 -0800
+++ 1244        2005-11-15 20:16:30.438646443 -0800
@@ -3 +3 @@
-id                      | 1243
+id                      | 1244
@@ -8,4 +8,4 @@
-class_time              | 2005-12-12 07:00:00-08
-class_end_time          | 2005-12-12 14:00:00-08
-class_size              | 55
-begin_reg_time          | 2005-11-15 17:36:00-08
+class_time              | 2005-12-25 07:15:00-08
+class_end_time          | 2005-12-25 11:00:00-08
+class_size              | 33
+begin_reg_time          |
@@ -27 +27 @@
-register_cutoff_time    | 2005-12-11 19:00:00-08
+register_cutoff_time    | 2005-12-24 19:15:00-08



Thanks,


--
Bill "stabbing in the dark" Moseley
moseley@hank.org