Thread: unexpected join results

unexpected join results

From

Matthew Phillips

Date:

18 March 2003, 16:28:09

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

Re: unexpected join results

From

"Dann Corbit"

Date:

18 March 2003, 16:44:43

-----Original Message-----
From: Matthew Phillips [mailto:mphillips@timing.com]
Sent: Tuesday, March 18, 2003 10:28 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] unexpected join results


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
-------------------------------------------------------->>
Consider:
 second=2003-03-18 10:09:45  end_time=2003-02-18 10:10:03
Notice that the month is March for second, and February
For end_time.  This is true for all the rows.  The query
Result set is correct.
Any time stamp in march of 2003 will be bigger than one in
February of 2003.
<<--------------------------------------------------------

Re: unexpected join results

From

Matthew Phillips

Date:

18 March 2003, 16:45:05

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:

Re: unexpected join results

From

Tom Lane

Date:

18 March 2003, 16:49:30

Matthew Phillips <mphillips@timing.com> writes:
>          2 | 2003-03-18 10:10:07 | 2003-02-18 10:09:06 | 2003-02-18 10:10:03

> 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.

Uh ... March comes after February, last I checked.

            regards, tom lane

Re: unexpected join results

From

Manfred Koizar

Date:

20 March 2003, 05:59:18

On Tue, 18 Mar 2003 11:45:02 -0700, Matthew Phillips
<mphillips@timing.com> wrote:
>answer is right there, my begin_time and end time were a month off.
Matthew,
I really like your subject line.  We have seen "bug reports" after
similar mistakes.  You just called your join results "unexpected"
which they were without any doubt :-)

Servus
 Manfred