From: zweije@slc9 (Vincent Zweije)
Subject: Suspicion of bug in ingres sql
Keywords: bug, sql
Organization: Department Of Mathematics and Computer Science
Date: Fri, 18 Dec 92 11:27:42 GMT

I found an SQL query that behaves strangely, I suspect a bug is the
cause of it.

The idea of the query is the following: I wish to select all employees
who work on all projects. Employee<->project is a total M:N relation,
modeled by an extra table called works_on. I tried to make a query for
this using the rephrasing "there is no project the employee does not
work on". I traced the strange behaviour down to a difference between
the results of two queries that should return the same results. A
listing is appended below.

I'm using ingres version 6.4. The example is actually from the book
"Fundamentals of Database Systems" by Elmasri/Navathe.

My question is: did I do something wrong, or is there indeed a bug in
this SQL implementation? Please reply by e-mail, because I usually
don't read this newsgroup.

Thanks,                                                       Vincent.

___ Fri Dec 18 11:53:04 1992 _________________________________________

  1> select e.ssn,e.fname,e.minit,e.lname
  2> from employee e

+-------------+----------+------+----------+
|ssn          |fname     |minit |lname     |
+-------------+----------+------+----------+
|    123456789|John      |B     |Smith     |
|    333445555|Franklin  |T     |Wong      |
|    453453453|Joyce     |A     |English   |
|    666884444|Ramesh    |K     |Narayan   |
|    888665555|James     |E     |Borg      |
|    987654321|Jennifer  |S     |Wallace   |
|    987987987|Ahmad     |V     |Jabbar    |
|    999887777|Alicia    |J     |Zelaya    |
+-------------+----------+------+----------+
(8 rows)


  2> select w.essn,w.pno
  3> from works_on w

+-------------+------+
|essn         |pno   |
+-------------+------+
|    123456789|     1|
|    123456789|     2|
|    333445555|     2|
|    333445555|     3|
|    333445555|    10|
|    333445555|    20|
|    453453453|     1|
|    453453453|     2|
|    666884444|     3|
|    888665555|    20|
|    987654321|    20|
|    987654321|    30|
|    987987987|    10|
|    987987987|    30|
|    999887777|    10|
|    999887777|    30|
+-------------+------+
(16 rows)


  2> select p.pnumber
  3> from project p

+------+
|pnumbe|
+------+
|     1|
|     2|
|     3|
|    10|
|    20|
|    30|
+------+
(6 rows)


  2> select e.ssn,e.fname,e.minit,e.lname
  3> from employee e
  4> where e.ssn=123456789
  5>   and not exists
  6>     ( select p.pnumber
  7>       from project p
  8>       where p.pnumber not in
  9>           ( select w.pno
 10>             from works_on w
 11>             where w.essn=123456789 ) )

+-------------+----------+------+----------+
|ssn          |fname     |minit |lname     |
+-------------+----------+------+----------+
+-------------+----------+------+----------+
(0 rows)


  2> select e.ssn,e.fname,e.minit,e.lname
  3> from employee e
  4> where e.ssn=123456789
  5>   and not exists
  6>     ( select p.pnumber
  7>       from project p
  8>       where p.pnumber not in
  9>           ( select w.pno
 10>             from works_on w
 11>             where w.essn=e.ssn ) )

+-------------+----------+------+----------+
|ssn          |fname     |minit |lname     |
+-------------+----------+------+----------+
|    123456789|John      |B     |Smith     |
+-------------+----------+------+----------+
(1 row)
End of Request
______________________________________________________________________