Path: utzoo!attcan!uunet!mcvax!enea!kth!draken!serafim
From: serafim@nada.kth.se (Serafim Dahl)
Newsgroups: comp.databases
Subject: bug in INGRES-SQL
Message-ID: <760@draken.nada.kth.se>
Date: 23 Jan 89 11:08:32 GMT
Reply-To: serafim@nada.kth.se (Serafim Dahl)
Organization: Royal Institute of Technology, Stockholm, Sweden
Lines: 27




Sorry about the last letter. The first two lines were missing.

In a relational database there is a base relation:
	Emp ((name), sal, mgr, dept)

where Emp=employee, sal=salary, mgr=manager, dept=department

the following query:
a)
	select avg(sal) from Emp where dept in
		(select dept from Emp where name = 'Smith')
gives the answer '8383.333'.

Examining the query, breaking it down as follows:
b)
	Select dept from Emp where name='Smith';
(giving the answer 'food')
	select avg(sal) from Emp where dept='food';
gives the answer '7795.833'.

It turns out that, in query a all duplicates in sal are eliminated,
while in b they quite correctly are not eliminated.

					/serafim@nada.kth.se