From: jude@jeeves.ucsd.edu (Jude Poole)
Newsgroups: comp.databases.ingres
Subject: possible ingres bug in retrieve unique on views
Keywords: retrieve unique views
Date: 21 Jan 93 17:43:32 GMT


Here is an apparent error in the way ingres is answering retrieve statements
for a large 3 table view we have.  The three tables in the view monster
have the relationship one -> many -> many.  It exists for the convenience of
fairly inexperienced users.  The people's names exist only once in the base 
table named people, but occur multiple times in the view monster because of 
multiple historical job titles and funding.  The theory was that retrieve
unique (or sorting) would eliminate duplicates from query results but it
doesn't seem to work that way when the view is joined to other tables (which it
frequently is to print out long definitions of various codes).  Does anyone
know why ingres behaves as demonstrated in the short script below?
(comments come after each query below) We are running 6.4/01 (su4.u42/01)
on OS/MP 4.1A.1 (same as Sun/OS)

  1> range of m is monster


  1> retrieve (m.lname, m.fname) where m.lname="POOLE"

+--------------------+---------------+
|lname               |fname          |
+--------------------+---------------+
|POOLE               |JUDE D         |
|POOLE               |JUDE D         |
+--------------------+---------------+
(2 rows)

/* As expected -- I have two entries in the second table */

  1> retrieve unique (m.lname, m.fname) where m.lname="POOLE"


+--------------------+---------------+
|lname               |fname          |
+--------------------+---------------+
|POOLE               |JUDE D         |
+--------------------+---------------+
(1 row)

/* again as expected, keyword unique causes "duplicated rows [to be] removed
   from the result table " -- Quel Ref manual, page 2-46
*/

  1> retrieve (zyx.all)

+--------------------+
|only                |
+--------------------+
|jjjjj               |
+--------------------+
(1 row)

/* a single row junk table */

  1> retrieve unique (m.lname, m.fname, zyx.only) where m.lname="POOLE"

+--------------------+---------------+--------------------+
|lname               |fname          |only                |
+--------------------+---------------+--------------------+
|POOLE               |JUDE D         |jjjjj               |
|POOLE               |JUDE D         |jjjjj               |
+--------------------+---------------+--------------------+
(2 rows)

/* here's the problem!! -- the retrieved rows are in fact not unique */
/* of course we join monster on a code field to the lookup tables in */
/* real queries, but the principle is the same */

  1> range of p is people

/* people is a 'real' table, not a view */

  1> retrieve (p.lname, p.fname) where p.lname="POOLE"

+--------------------+---------------+
|lname               |fname          |
+--------------------+---------------+
|POOLE               |DANIEL S       |
|POOLE               |JUDE D         |
+--------------------+---------------+
(2 rows)

/* only one row for me, but there is someone else named POOLE too */

  1> retrieve unique (p.lname, zyx.only) where p.lname="POOLE"

+--------------------+--------------------+
|lname               |only                |
+--------------------+--------------------+
|POOLE               |jjjjj               |
+--------------------+--------------------+
(1 row)

/* with the base table the unique keyword seems to work correctly */

Any thoughts would be appreciated.

Jude Poole
jpoole@ucsd.edu

Newsgroups: comp.databases.ingres