Re: unexpected join results

ACK THATS TWICE NOW!
I figured it out. Give me the postgresql bonehead award. Thats twice in
a row that I have posted and figured it out myself right after. The
answer is right there, my begin_time and end time were a month off. My
stupid bug...

Thanks for you patience. I'm sure you haven't heard the last from me.

matthew

Matthew Phillips wrote:

> 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