Query not working as expected... - Mailing list pgsql-general

I don't know if this is a bug or my lack of understanding of how the query is
working.  I have this table:

test=# select * from dates2;

 did  |    date    |     type     |       timestamp        | cid
------+------------+--------------+------------------------+------
 5647 | 2002-05-06 | Open         | 2002-05-06 13:28:35-04 | 1039
 5648 | 2002-07-27 | Schedule     | 2002-05-06 13:28:35-04 | 1039
 5708 | 2002-05-15 | Edit         | 2002-05-15 11:12:09-04 | 1039
 5709 | 2002-05-15 | Edit         | 2002-05-15 11:39:59-04 | 1039
 5710 | 2002-05-15 | Edit         | 2002-05-15 11:41:01-04 | 1039
 5711 | 2002-05-15 | Edit         | 2002-05-15 11:41:37-04 | 1039
 5712 | 2002-05-15 | Open         | 2002-05-15 14:34:32-04 | 1053
 5713 | 2002-05-26 | Schedule     | 2002-05-15 14:34:32-04 | 1053
 5714 | 2002-05-15 | Open         | 2002-05-15 14:36:00-04 | 1054
 5715 | 2002-05-15 | Schedule     | 2002-05-15 14:36:00-04 | 1054
 5716 | 2002-05-15 | Edit         | 2002-05-15 14:36:25-04 | 1054
 5717 | 2002-05-15 | Edit         | 2002-05-15 14:37:06-04 | 1054
 5718 | 2002-05-15 | Edit         | 2002-05-15 14:37:13-04 | 1054
 5728 | 2002-05-15 | Approve      | 2002-05-15 14:45:49-04 | 1054
 5729 | 2002-05-19 | Notify       | 2002-05-15 14:45:59-04 | 1054
 5730 | 2002-05-21 | Complete     | 2002-05-15 14:46:28-04 | 1054
 5731 | 2002-05-15 | Close        | 2002-05-15 14:46:28-04 | 1054
 5732 | 2002-05-27 | Schedule     | 2002-05-15 15:24:30-04 | 1039
 5733 | 2002-05-15 | Edit         | 2002-05-15 11:41:37-04 | 1055
 5734 | 2002-05-15 | Edit         | 2002-05-15 11:41:01-04 | 1055
 5735 | 2002-05-15 | Edit         | 2002-05-15 11:39:59-04 | 1055
 5736 | 2002-05-15 | Edit         | 2002-05-15 11:12:09-04 | 1055
 5737 | 2002-05-06 | Open         | 2002-05-06 13:28:35-04 | 1055
 5738 | 2002-05-27 | Schedule     | 2002-05-15 15:24:30-04 | 1055
 5739 | 2002-07-27 | Schedule     | 2002-05-06 13:28:35-04 | 1055
 5740 | 2002-07-23 | Schedule     | 2002-05-15 15:33:00-04 | 1055
(26 rows)

I want to extract only the most recently added rows that are uniquely defined
by cid and type.

test=# select distinct on (cid,type) * from dates2
       order by cid,type,timestamp desc;

 did  |    date    |     type     |       timestamp        | cid
------+------------+--------------+------------------------+------
 5711 | 2002-05-15 | Edit         | 2002-05-15 11:41:37-04 | 1039
 5647 | 2002-05-06 | Open         | 2002-05-06 13:28:35-04 | 1039
 5732 | 2002-05-27 | Schedule     | 2002-05-15 15:24:30-04 | 1039
 5712 | 2002-05-15 | Open         | 2002-05-15 14:34:32-04 | 1053
 5713 | 2002-05-26 | Schedule     | 2002-05-15 14:34:32-04 | 1053
 5728 | 2002-05-15 | Approve      | 2002-05-15 14:45:49-04 | 1054
 5731 | 2002-05-15 | Close        | 2002-05-15 14:46:28-04 | 1054
 5730 | 2002-05-21 | Complete     | 2002-05-15 14:46:28-04 | 1054
 5718 | 2002-05-15 | Edit         | 2002-05-15 14:37:13-04 | 1054
 5729 | 2002-05-19 | Notify       | 2002-05-15 14:45:59-04 | 1054
 5714 | 2002-05-15 | Open         | 2002-05-15 14:36:00-04 | 1054
 5715 | 2002-05-15 | Schedule     | 2002-05-15 14:36:00-04 | 1054
 5733 | 2002-05-15 | Edit         | 2002-05-15 11:41:37-04 | 1055
 5737 | 2002-05-06 | Open         | 2002-05-06 13:28:35-04 | 1055
 5740 | 2002-07-23 | Schedule     | 2002-05-15 15:33:00-04 | 1055
(15 rows)

Now, I want only those rows from the subquery that have a date between
2002-05-17 and 2002-05-31 and a type of "Schedule".

test=# select * from (select distinct on (cid,type) * from dates2 order by
                      cid,type,timestamp desc) as foo
       where type='Schedule' and foo.date between '2002-05-17' and '2002-05-31';

 did  |    date    |     type     |       timestamp        | cid
------+------------+--------------+------------------------+------
 5732 | 2002-05-27 | Schedule     | 2002-05-15 15:24:30-04 | 1039
 5713 | 2002-05-26 | Schedule     | 2002-05-15 14:34:32-04 | 1053
 5738 | 2002-05-27 | Schedule     | 2002-05-15 15:24:30-04 | 1055
(3 rows)

Notice the last row.  The row with did 5738 is NOT in the result set from the
second query, which is the subquery of the above command.  So why is this
showing up in the results?  Anyone have an idea?
--
Titus Anderson