unexpected join results - Mailing list pgsql-general

Here is my problem in the most abstract way I can convey it...

I am doing a simple join on two tables. The query:

select m.source_id, second, begin_time, end_time
from measurements m,
     measurement_list l
where m.source_id = l.source_id
and m.second > l.begin_time
and l.end_time IS NOT NULL;

produces the output:

 source_id |       second        |     begin_time      |      end_time      
-----------+---------------------+---------------------+---------------------
         2 | 2003-03-18 10:09:45 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:46 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:47 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:48 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:49 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:50 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:51 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:52 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:53 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:54 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:55 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:56 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:57 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:58 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:09:59 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:00 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:01 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:02 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:03 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:04 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:05 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:06 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
         2 | 2003-03-18 10:10:07 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03
(23 rows)
source_id is an integer identifier, the rest of the columns are timestamp without time zone.


This output is expected and perfectly ok... the problem is when I add one more condition to the query. 'and m.second <= l.end_time'. This would match rows in which second falls in between begin_time and end time, something all the above rows do.

but...
select m.source_id, second, begin_time, end_time
from measurements m,
     measurement_list l
where m.source_id = l.source_id
and m.second > l.begin_time
and l.end_time IS NOT NULL
and m.second <= l.end_time;

produces:
 source_id | second | begin_time | end_time
-----------+--------+------------+----------
(0 rows)

But I can look back on the previous query results and see that in every row the second is less than the end time! I am not exactly an sql guru, but it seems to me that the output should be identical with the extra line in or out. Any clues as to what is going on here? Please give me a quick fix  :-) I have been spinning wheels for half a day on this one. I can post more schema info if that is needed.

using 7.3.1 on FreeBSD 4.7

matthew