Subject: Queries using self-defined operators

From: SEP Lutz <[email-masked]>
Date: Tue, 22 Dec 92 05:46:35 -0800

Hello,

we sent a bug report about the usage of defined operators in 
queries which use joins some time ago, but did not yet get any reply.

Because the problem has even increased since, we decided to try it again,
urgently waiting for any help.

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.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.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.

Another problem is with queries containing two comparisons,
one of them using our self-defined like-operator ~=:

Go
* create testtbl (name = char16) \g

Query sent to backend is "create testtbl (name = char16) "
CREATE
Go
* append testtbl (name = "someone") \g

Query sent to backend is "append testtbl (name = "someone") "
APPEND 143051

Now the problem:

Go
* retrieve (testtbl.name) where testtbl.name = "noone" or testtbl.name ~= "so?e*" \g

Query sent to backend is "retrieve (testtbl.name) where testtbl.name = "noone" or testtbl.name ~= "so?e*" "
WARN:Dec 22 14:28:14:fmgr: Cache lookup failed for procedure 0

And some alternatives which work:

Go
* retrieve (testtbl.name) where testtbl.name = "noone" \g

Query sent to backend is "retrieve (testtbl.name) where testtbl.name = "noone" "
---------------
| name        |
---------------

Go
* retrieve (testtbl.name) where testtbl.name ~= "so?e*" \g

Query sent to backend is "retrieve (testtbl.name) where testtbl.name ~= "so?e*" "
---------------
| name        |
---------------
| someone     |
---------------

Go
* retrieve (testtbl.name) where testtbl.name = "noone" or testtbl.name = "someone" \g

Query sent to backend is "retrieve (testtbl.name) where testtbl.name = "noone" or testtbl.name = "someone" "
---------------
| name        |
---------------
| someone     |
---------------

Go
* retrieve (testtbl.name) where testtbl.name = "noone" or (pg_like_char(testtbl.name,"so?e*"::char16)) \g

Query sent to backend is "retrieve (testtbl.name) where testtbl.name = "noone" or (pg_like_char(testtbl.name,"so?e*"::char16)) "
---------------
| name        |
---------------
| someone     |
---------------

What is wrong there?

Could you please also explain the parameters

	restrict, hashes, join and sort

of define operator (rsp. the semantics of the restrict and join functins)
more detailedly?

Thank you very much,

Gesamic project group
[email-masked]