Subject: user defined functions - join problem

From: SEP Lutz <[email-masked]>
Date: Wed, 9 Dec 92 08:15:51 -0800

Hello,

while testing our project database application, we discovered a problem
when executing complex queries, which use our own user defined functions
(rsp. operators).

We use our own like-function, which we have defined to be called by
the operator ~=.

define function pg_like_char (language = "c", returntype = bool)
arg is (char16, char16)
as "/usr/gesamic/pg/char16/char16.o"

define operator ~= (arg1 = char16, arg2 = char16, procedure = pg_like_char,
                    associativity = left)

(The like-function itself is ok and works correctly in simpler queries, too.)

The relevant part of our schema definition:

create pb_objtype (objtypeid = oid,
                   objtypename = char16,
                   noobjs = int4,
                   noatts = int2,
                   keyattrs = int28)

(our pg_class-equivalent) and

define view pb_attribute (attid = pga.oid,
                          attrelid = pga.attrelid,
                          attname = pga.attname,
                          attnorel = pga.attnum,
                          atttypeid = pga.atttypid)
  from   pga in pg_attribute,
         pbot in pb_objtype
  where  pga.attrelid = pbot.objtypeid and pga.attnum > 0

(a view on pg_attribute).

The contents of the database:

Go
* retrieve (pb_objtype.all) \g

Query sent to backend is "retrieve (pb_objtype.all) "
-----------------------------------------------------------------------
| objtypeid   | objtypename | noobjs      | noatts      | keyattrs    |
-----------------------------------------------------------------------
| 115392      | object      | 0           | 1           | 0 0 0 0 0 0 0 0|
-----------------------------------------------------------------------
| 115488      | c_source    | 0           | 3           | 0 0 0 0 0 0 0 0|
-----------------------------------------------------------------------

Go
* retrieve (pb_attribute.all) \g

Query sent to backend is "retrieve (pb_attribute.all) "
-----------------------------------------------------------------------
| attid       | attrelid    | attname     | attnorel    | atttypeid   |
-----------------------------------------------------------------------
| 115396      | 115392      | objname     | 1           | 19          |
-----------------------------------------------------------------------
| 115492      | 115488      | objname     | 1           | 19          |
-----------------------------------------------------------------------
| 115495      | 115488      | autorname   | 2           | 19          |
-----------------------------------------------------------------------
| 115498      | 115488      | koautorname | 3           | 19          |
-----------------------------------------------------------------------

Go
* retrieve (ot = ot.objtypename, at = at.attname, bt = bt.attname) from ot in pb_objtype, at, bt in pb_attribute where ot.objtypename ~= "*" and ot.objtypeid = at.attrelid and ot.objtypeid = bt.attrelid \g

Query sent to backend is "retrieve (ot = ot.objtypename, at = at.attname, bt = bt.attname) from ot in pb_objtype, at, bt in pb_attribute where ot.objtypename ~= "*" and ot.objtypeid = at.attrelid and ot.objtypeid = bt.attrelid "
-------------------------------------------
| ot          | at          | bt          |
-------------------------------------------
| object      | objname     | objname     |
-------------------------------------------
| c_source    | objname     | objname     |
-------------------------------------------
| c_source    | autorname   | objname     |
-------------------------------------------
| c_source    | koautorname | objname     |
-------------------------------------------
| c_source    | objname     | autorname   |
-------------------------------------------
| c_source    | autorname   | autorname   |
-------------------------------------------
| c_source    | koautorname | autorname   |
-------------------------------------------
| c_source    | objname     | koautorname |
-------------------------------------------
| c_source    | autorname   | koautorname |
-------------------------------------------
| c_source    | koautorname | koautorname |
-------------------------------------------

Go
* retrieve (ot = ot.objtypename, at = at.attname, bt = bt.attname) from ot in pb
_objtype, at, bt in pb_attribute where ot.objtypeid = at.attrelid and ot.objtype
id = bt.attrelid and at.attname ~= "*aut*" \g

Query sent to backend is "retrieve (ot = ot.objtypename, at = at.attname, bt = b
t.attname) from ot in pb_objtype, at, bt in pb_attribute where ot.objtypeid = at
.attrelid and ot.objtypeid = bt.attrelid and at.attname ~= "*aut*" "
-------------------------------------------
| ot          | at          | bt          |
-------------------------------------------
| c_source    | autorname   | objname     |
-------------------------------------------
| c_source    | koautorname | objname     |
-------------------------------------------
| c_source    | autorname   | autorname   |
-------------------------------------------
| c_source    | koautorname | autorname   |
-------------------------------------------
| c_source    | autorname   | koautorname |
-------------------------------------------
| c_source    | koautorname | koautorname |
-------------------------------------------

When combining these two queries, we expected to get all the 6 tuples
of the latter query again.

But, as you can see,

Go
* retrieve (ot = ot.objtypename, at = at.attname, bt = bt.attname) from ot in pb_objtype, at, bt in pb_attribute where ot.objtypename ~= "*" and ot.objtypeid = at.attrelid and ot.objtypeid = bt.attrelid and at.attname ~= "*aut*" \g

Query sent to backend is "retrieve (ot = ot.objtypename, at = at.attname, bt = bt.attname) from ot in pb_objtype, at, bt in pb_attribute where ot.objtypename ~= "*" and ot.objtypeid = at.attrelid and ot.objtypeid = bt.attrelid and at.attname ~= "*aut*" "
-------------------------------------------
| ot          | at          | bt          |
-------------------------------------------

returns an empty result.

What is wrong here?! Do we need to define a join procedure for the join parameter
of 'define operator'? (If we do, how to do so?)

Thanks in advance,

Gesamic project group
[email-masked]