Use a view as opposed to a table and it doesn't work! - Mailing list pgsql-bugs

From pgsql-bugs@postgresql.org
Subject Use a view as opposed to a table and it doesn't work!
Date
Msg-id 200011211711.eALHBrx61708@hub.org
Whole thread Raw
Responses Re: Use a view as opposed to a table and it doesn't work!
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Vlyminckx Frederic
Date:
Subject: libpq++ : constructor - destructor problem with PgConnection
Next
From: Tom Lane
Date:
Subject: Re: Use a view as opposed to a table and it doesn't work!