home *** CD-ROM | disk | FTP | other *** search
- Path: sparky!uunet!spool.mu.edu!agate!iat.holonet.net!news.cerf.net!network.ucsd.edu!sdcc12!jeeves!jude
- From: jude@jeeves.ucsd.edu (Jude Poole)
- Newsgroups: comp.databases.ingres
- Subject: possible ingres bug in retrieve unique on views
- Keywords: retrieve unique views
- Message-ID: <43822@sdcc12.ucsd.edu>
- Date: 21 Jan 93 17:43:32 GMT
- Sender: news@sdcc12.ucsd.edu
- Lines: 100
- Nntp-Posting-Host: jeeves.ucsd.edu
-
-
- 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
-
-