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@xxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
