Subject: JOIN query not working as expected
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Mob-Art
Date: Tue, 06 Dec 2005 15:08:03 +0100
Message-Id: <1133878084.8161.22.camel@ekim>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

How is this possible?

I have two tables. 'services', and 'messages'. Each message can be
assigned to one service, or it can be unnasigned. Therefore 'service_id'
column in table 'messages' is not foreign-keyed to 'id' column in
services table. services.id is PK for services, messages.id is PK for
messages.

Now, here goes:

pulitzer2=# select * from services where id = 1001;
  id  | keyword | type_id | vpn_id | start_time |        end_time
| day_boundary | week_boundary | month_boundary | recurrence |
random_message_count
------+---------+---------+--------+------------+------------------------+--------------+---------------+----------------+------------+----------------------
 1001 | cocker  |       1 |      1 |            | 2005-10-20 12:00:00+02
|              |               |                |          1 |
(1 row)


Ok, I have a service with id 1001 which is called 'cocker'.

Now, I want all the messages for that service within certain period:

pulitzer2=# select * from messages where service_id = 1001 and
receiving_time between '2005-10-01' and '2005-10-30';
 id | from | to | receiving_time | raw_text | keyword | destination_id |
vpn_id | service_id | status | reply
----+------+----+----------------+----------+---------+----------------+--------+------------+--------+-------
(0 rows)

Ok, no such messages.


Now I want all services which didn't have any messages within certain
period:
pulitzer2=# select * from services where id not in (select distinct
service_id from messages where receiving_time between '2005-10-01' and
'2005-10-30');
 id | keyword | type_id | vpn_id | start_time | end_time | day_boundary
| week_boundary | month_boundary | recurrence | random_message_count
----+---------+---------+--------+------------+----------+--------------+---------------+----------------+------------+----------------------
(0 rows)

Why is that?


I 'discovered' above mentioned when I was transforming this query:

SELECT
	services.id AS service_id,
	(SELECT 
		COUNT(id)
	FROM
		messages
	WHERE
		(messages.service_id = services.id)
		AND (messages.receiving_time >= '2005-10-01')
		AND (messages.receiving_time < '2005-10-30')
	) AS "count",
        services.keyword
FROM
	services
WHERE
	(services.vpn_id = 1)
        AND
	(
		(services.start_time IS NULL OR services.start_time <= '2005-10-30')
		AND
		(services.end_time IS NULL OR services.end_time >= '2005-10-01')
	)
GROUP BY
	services.id,
	services.keyword
ORDER BY
	services.keyword

[this query shows correctly, for service 'cocker', that '"count"' column
has value 0]


I transformed query to this:

SELECT
	services.id AS service_id,
	count(messages.id) as "count",
        services.keyword
FROM
	services
	LEFT OUTER JOIN messages
		ON services.id = messages.service_id
WHERE
	services.vpn_id = 1
	AND messages.receiving_time BETWEEN '2005-10-01' AND '2005-10-30'
GROUP BY
	services.id,
	services.keyword
ORDER BY
	services.keyword

This query runs MUCH faster, but it omits the 'cocker' column, as if I
used INNER JOIN. 

Any clues? I'm stuck here...

	Mike
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."