Return-Path: pg_adm@postgres.berkeley.edu Received: by postgres.Berkeley.EDU (5.61/1.29) id AA03642; Tue, 22 Dec 92 05:46:35 -0800 Date: Tue, 22 Dec 92 05:46:35 -0800 Message-Id: <9212221346.AA03642@postgres.Berkeley.EDU> From: SEP Lutz Subject: Queries using self-defined operators To: postgres@postgres.berkeley.edu Sender: pg_adm@postgres.berkeley.edu 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 gesamic@isa.informatik.th-darmstadt.de