Gavin Evans (gavin@consultant.com) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
Use a view as opposed to a table and it doesn't work!
Long Description
If you perform the SQL below using a view to hold the avg(salary) you only get 1 row returned:
name | salary | avg_sal | sal_diff
------+--------+----------+----------
mike | 1500 | 1948.646 | -448.646
if you comment out the 1st line and un comment the 2nd it works as expected returning:
name | salary | avg_sal | sal_diff
---------+---------+----------+-----------
mike | 1500 | 1948.646 | -448.646
sally | 877.5 | 1948.646 | -1071.146
georgia | | 1948.646 |
ted | 2615.73 | 1948.646 | 667.084
edna | 2000 | 1948.646 | 51.354
malcolm | 2750 | 1948.646 | 801.354
(6 rows)
I have tested the same code on INGRES and it works fine. Therefore I am assuming IT MUST be a bug in postgresql.
Pleasecorrect me if I am wrong.
Best regards
Gavin Evans
Sample Code
CREATE VIEW avg_int AS SELECT AVG(salary) AS avg_sal FROM employee;
--SELECT AVG(salary) AS avg_sal INTO TEMP TABLE avg_int FROM employee;
CREATE VIEW average AS SELECT employee.name, employee.salary, avg_int.avg_sal, (salary-avg_sal) as sal_diff FROM
employee, avg_int;
SELECT * FROM average;
DROP VIEW average;
DROP VIEW avg_int;
--------------------- base table sql creation code ------------------
create table employee(
name varchar(10) not null,
age integer,
salary float,
deptname varchar(10),
manager varchar(10),
primary key(name));
insert into employee
values ('mike', 29, 1500.00, 'shoe', 'edna');
insert into employee
values ('sally', 42, 877.50, 'toy', 'ted');
insert into employee
(name, age, deptname)
values ('georgia', 22, 'book');
insert into employee
(name, salary, deptname, manager)
values ('ted', 2615.73, 'toy', 'malcolm');
insert into employee
values ('edna', 39, 2000.00, 'shoe', 'malcolm');
insert into employee
(name, age, salary, deptname)
values ('malcolm', 50, 2750.00, 'admin');
No file was uploaded with this report