Insert via Select Problem

Hello.

I've come across something that seems like it may be a bug. I was wondering
if someone could either confirm that this was the case or explain to me the
error of my ways. ;-)

In essence, what I have found is that if I execute a somewhat lengthy query
and dump the output to the screen I get a result set that is different than
if I execute the exact same query but use the query as the source for an
insert command. I have not seen this with other queries, just the ones that
follow below.

I've looked a lot at the queries and tables to see what might not be right,
but it seems to me that regardless of whether one goes to the screen or
another table, logically the number of rows returned by the select query
should be the same, right? In the queries below, however, the straight
select has a result set of 4 rows (correct) and the insert with a select
produces 5 rows (incorrect, I believe). When run against the entire table, I
estimate that roughly 600 rows should be created; however, the latter query
produces around 2,000.

This problem is showing up under version 7.02 -- I did not test other
versions. I have reproduced it under SuSE Linux PPC 6.4 and RedHat Linux 6.2
x86.

[tara(at)linux wismits.d]$ psql -e wismits < wismits_test.sql
SELECT b.last_name || ', ' || b.first_name as name,
a.consumer_id,
c.name,
0,
0,
0,
sum(case when d.provision_class_code = 'PR' then 1 else 0 end),
sum(case when d.provision_class_code = 'PR' then 0 else 1 end),
sum(case when d.provision_class_code = 'SA' then 1 else 0 end),
sum(case when d.provision_class_code = 'EA' then 1 else 0 end),
sum(case when d.provision_class_code = 'SC' then 1 else 0 end),
sum(case when d.provision_class_code = 'SS' then 1 else 0 end),
f.service_class_id
FROM transactions a,
students b,
districts c,
provision_codes d,
student_services e,
services f
WHERE a.consumer_id = b.consumer_id
AND a.consumer_id = 99999
AND a.service_date between '2000-08-01' and '2000-10-31'
AND b.district_id = c.district_id
AND a.provision_code = d.provision_code
AND a.student_service_id = e.student_service_id
AND e.service_id = f.service_id
GROUP BY b.last_name,
b.first_name,
a.consumer_id,
c.name,
f.service_class_id;
name | consumer_id | name | ?column? | ?column? |
?column? | sum | sum | sum | sum | sum | sum | service
------------------+-------------+-------------+----------+----------+-------
---+-----+-----+-----+-----+-----+-----+---------
Spicoli, Jeffrey | 99999 | Unspecified | 0 | 0 |
0 | 2 | 1 | 1 | 0 | 0 | 0 | 1
Spicoli, Jeffrey | 99999 | Unspecified | 0 | 0 |
0 | 0 | 1 | 0 | 0 | 1 | 0 | 3
Spicoli, Jeffrey | 99999 | Unspecified | 0 | 0 |
0 | 7 | 4 | 1 | 0 | 3 | 0 | 4
Spicoli, Jeffrey | 99999 | Unspecified | 0 | 0 |
0 | 1 | 2 | 0 | 0 | 2 | 0 | 6
(4 rows)

[tara(at)linux wismits.d]$ psql -e wismits < wismits_test3.sql
CREATE TEMPORARY TABLE rs2_temp
(student_name varchar(30) not null,
consumer_id numeric(9) not null,
district_name varchar(40) not null,
num_ind_iep_sessions numeric(9) not null,
num_grp_iep_sessions numeric(9) not null,
num_weeks_enrolled numeric(2) not null,
num_rs_sess_provided numeric(9) not null,
num_rs_sess_missed numeric(9) not null,
num_rs_sess_missed_ca numeric(9) not null,
num_rs_sess_missed_sa numeric(9) not null,
num_rs_sess_missed_sc numeric(9) not null,
num_rs_sess_missed_ss numeric(9) not null,
service_class_id numeric(9) not null);
CREATE
INSERT INTO rs2_temp (student_name, consumer_id,
district_name, num_ind_iep_sessions, num_grp_iep_sessions,
num_weeks_enrolled,
num_rs_sess_provided,
num_rs_sess_missed, num_rs_sess_missed_ca, num_rs_sess_missed_sa,
num_rs_sess_missed_sc, num_rs_sess_missed_ss, service_class_id)
SELECT b.last_name || ', ' || b.first_name as name,
a.consumer_id,
c.name,
0,
0,
0,
sum(case when d.provision_class_code = 'PR' then 1 else 0 end),
sum(case when d.provision_class_code = 'PR' then 0 else 1 end),
sum(case when d.provision_class_code = 'SA' then 1 else 0 end),
sum(case when d.provision_class_code = 'EA' then 1 else 0 end),
sum(case when d.provision_class_code = 'SC' then 1 else 0 end),
sum(case when d.provision_class_code = 'SS' then 1 else 0 end),
f.service_class_id
FROM transactions a,
students b,
districts c,
provision_codes d,
student_services e,
services f
WHERE a.consumer_id = b.consumer_id
AND a.consumer_id = 99999
AND a.service_date between '2000-08-01' and '2000-10-31'
AND b.district_id = c.district_id
AND a.provision_code = d.provision_code
AND a.student_service_id = e.student_service_id
AND e.service_id = f.service_id
GROUP BY b.last_name,
b.first_name,
a.consumer_id,
c.name,
f.service_class_id;
INSERT 0 5

Thanks for any help you can provide.

Tara

--
Tara Pierkowski
Network Administrator, vilaj.com