Обсуждение: Query not working as expected...

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

"Titus J. Anderson" <titus.anderson@louisville.edu> writes:
> 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?

What version are you running?  This looks a lot like a bug that was
fixed in 7.1.3:

2001-07-31 14:39  tgl

    * src/: backend/optimizer/path/allpaths.c,
    backend/optimizer/util/clauses.c, backend/utils/adt/ruleutils.c,
    include/optimizer/clauses.h (REL7_1_STABLE): Fix optimizer to not
    try to push WHERE clauses down into a sub-SELECT that has a
    DISTINCT ON clause, per bug report from Anthony Wood.  While at it,
    improve the DISTINCT-ON-clause recognizer routine to not be fooled
    by out-of-order DISTINCT lists.  Also, back-patch earlier fix to
    not push down into sub-SELECT with LIMIT.

            regards, tom lane