Subject: Postquel functions in Ver 4

From: mick <[email-masked]>
Date: Mon, 3 Aug 92 09:01:15 -0700

Ive come up with a problem with the modifications
to postquel functions in Version 4.
Now that there is a distinction between postquel functions
that return a setof instances and those that return a single instance
there could be problems with composite objects and unexpected
results when a user doesnt know the difference
between these functions (or forgets how they were defined).

For instance taking the manager function from the manual
and the hobbies and children functions from the reference pages.
I combined these (schema and data shown below) and you can get.

retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------
| joey        | jamming     | (null)      | (null)      |
---------------------------------------------------------
| jeff        | biking      | (null)      | (null)      |
---------------------------------------------------------
| wei         | basketball  | (null)      | wei         |
---------------------------------------------------------
| mike        | swimming    | matthew     | (null)      |
---------------------------------------------------------
| mike        | philately   | matthew     | (null)      |
---------------------------------------------------------
| mike        | swimming    | calvin      | (null)      |
---------------------------------------------------------
| mike        | philately   | calvin      | (null)      |
---------------------------------------------------------

Specify a manager name (single instance function) in a where clause
and you get the expected reult.

retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)
where EMP.manager.name="wei"\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------
| wei         | basketball  | (null)      | wei         |
---------------------------------------------------------

However the apparently very similar query where you
specify hobbies name or children name (set of instance function)
in a where clause and you get no instances returned.

retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)
where EMP.children.name="calvin"\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------

retrieve(EMP.name,hobby=EMP.hobbies.name,kid=EMP.children.name,boss=EMP.manager.name)
where EMP.hobbies.name="basketball"\g
---------------------------------------------------------
| name        | hobby       | kid         | boss        |
---------------------------------------------------------

Ive even got some bizarre results out.

 retrieve(EMP.name,hobby=EMP.hobbies.name) where EMP.hobbies.name = 
"jamming"\g
-----------------------------
| name        | hobby       |
-----------------------------
| jeff        | biking      |
-----------------------------

This distinction between the types of postquel functions may be what
was intended and I know the functions have plural names,
hobbies and children rather than hobby and child, but Im worried that
users could be confused by this situation.

Mick Ridley

**************************************************************

 create EMP (name = char16, salary = float8,
            dept = char16, age = int4)

 create KIDS (name = char16, dad = char16, mom = char16)

append EMP (name = "joey", salary = "100000.01"::float8,
            dept = "toy", age = 24) 
append EMP (name = "jeff", salary = "100000.01"::float8,
            dept = "shoe", age = 23)
append EMP (name = "wei", salary = "100000"::float8,
            dept = "tv", age = 30) 
append EMP (name = "mike", salary = "500000"::float8,
            dept = "appliances", age = 30)

append KIDS (name = "matthew", dad = "mike",
             mom = "teresa")
append KIDS (name = "calvin", dad = "mike",
             mom = "teresa")

create HOBBIES (name = char16, person = char16)

append HOBBIES (name = "biking", person = "jeff" ) 
append HOBBIES (name = "jamming", person = "joey" ) 
append HOBBIES (name = "basketball", person = "wei") 
append HOBBIES (name = "swimming", person = "mike") 
append HOBBIES (name = "philately", person = "mike")

 define function hobbies
    (language = "postquel", returntype = setof HOBBIES)
    arg is (EMP)
    as "retrieve (HOBBIES.all)
          where $1.name = HOBBIES.person"

define function children
    (language = "postquel", returntype = setof KIDS)
    arg is (EMP)
    as "retrieve (KIDS.all)
          where $1.name = KIDS.dad
             or $1.name = KIDS.mom"

 create DEPT (name=char16,manager=char16)

append DEPT(name="tv",manager="wei")

define function manager
      (language = "postquel", returntype = EMP)
      arg is (EMP)
      as "retrieve (E.all) from E in EMP
              where E.name = DEPT.manager
              and DEPT.name = $1.dept"

---

From: [email-masked]
Date: Mon, 03 Aug 92 10:27:04 -0700

>> where EMP.children.name="calvin"\g

Using a function returning "setof" in a where clause is not a
wonderful idea right now.  The current semantics of this query is that
a setof function in a where clause will return an arbitrary member of
the set, which produces the behavior you see above (EMP.children.name
is returning "matthew" for EMP mike, not "calvin").  I agree that this
is unintuitive, although note that SQL has similar confusions with
subqueries.  We're designing a cleaner and (hopefully) more intuitive
implementation of sets for V4.1.  Meantime your point is well taken:
beware of using functions returning setof, especially in the where
clause!

>> where EMP.hobbies.name="basketball"\g

This query should have returned a tuple for EMP wei.  This seems to be
a bug.

>>  retrieve(EMP.name,hobby=EMP.hobbies.name) where EMP.hobbies.name = 

This is decidedly a bug!!

>> users could be confused by this situation.

Again, point well taken.  Postquel function users be warned!  And
thanks for pointing out the bugs.

Joe Hellerstein

---

From: postarch (Postgres Mailing Archive)
Date: Mon, 03 Aug 92 11:40:39 PDT

> Ive even got some bizarre results out.

for 4.0.1, i've changed the behavior in the system as follows:  if the
user tries to use an operator (such as '=' or '+'), and if one of the
operands is a set-valued function, then postgres prints a message of the
form

  NOTICE:Aug  3 11:48:31:An operand to the '+' operator returns a set of int4,
  WARN:Aug  3 11:48:31:but '+' takes single values, not sets.

and aborts the transaction.  this provides deterministic, if somewhat
restrictive, behavior.
					mike olson
					project sequoia 2000
					uc berkeley
					[email-masked]