(a) Types of plug-in distribution functions

Table 5-1 lists the types of plug-in distribution functions.

Table 5-1 Types of plug-in distribution functions

Function type	Plug-in process
	Process that generates inter-function values to be passed and sends them to other plug-in distribution functions	Process that receives inter-function values sent from other plug-in distribution functions
Function that does not have inter-function values	Not supported	Not supported
Function that sends inter-function values1	Not supported	Supported
Function that receives passed inter-function values2	Supported	Not supported
Function that sends and receives inter-function values	Supported	Supported

¹ An example of this function type that the HiRDB Text Search Plug-in provides is the score function.

² An example of this function type that the HiRDB Text Search Plug-in provides is the contains_with_score function.

The HiRDB system allows values to be passed between plug-in distribution functions. Because HiRDB automatically passes inter-function values between plug-in distribution functions, the inter-function value does not have to be specified as an argument of the plug-in distribution function.

Note

Where the plug-in distribution function can be specified in an SQL differs depending on the function type. For details about the plug-in distribution function types, see the plug-in manuals.

The following explanations use these terms:

• Function that does not have passing inter-function values Function without inter-function values
• Function that receives passing inter-function values Receive function for passing inter-function values
• Function that sends passing inter-function values Send function for passing inter-function values
• Function that sends and receives passing inter-function values Send/receive function for passing inter-function values

(b) Correspondences between send and receive functions for passing inter-function values

The following are rules for the correspondences between send and receive functions for passing inter-function values.

• Some combinations of send and receive functions for passing inter-function values do not allow inter-function values to be passed. For details about the correspondences between send and receive functions for passing inter-function values, see the plug-in manuals.
• The first arguments in both the send and receive functions for passing inter-function values must be the same and must be a column specification for a base table, an SQL parameter, or an SQL variable. The first argument cannot be a component specification.
• Use one query specification to close the send and receive functions for passing inter-function values. However, when you specify a send function for passing inter-function values during list creation to store the passing inter-function values to a list, and then specify a receive function for passing inter-function values during search via a list to get the passing inter-function values from the list, you can specify the send and receive functions for passing inter-function values across multiple queries. (For details, see (3)(c) Methods of executing set operations between lists.)

Table 5-2 shows the HiRDB operations for combinations between receive and send functions for passing inter-function values.

Table 5-2 Correspondences between receive and send functions for passing inter-function values

Receive function for passing inter-function values	Send function for passing inter-function values	HiRDB operation
Not specified	Not specified	Can be executed
	Specified
Specified	Not specified	Cannot be executed*
	Specified (one)	Can be executed
	Specified (two or more)	Cannot be executed

^* When passing inter-function values are to be obtained from a list, the send and receive functions for passing inter-function values can be specified across multiple queries.

(c) Restrictions on plug-in distribution functions

• Functions without inter-function values
  Any of these functions can be specified in locations where a function can be specified.
  
• Receive functions for passing inter-function values
  
  • These functions can be specified only in the selection expression of a SELECT statement, the selection expression of an INSERT statement that has a query specification, or the update value of a SET clause in an UPDATE statement.
  • These functions cannot be specified in CASE expressions or the VALUE scalar function.
  • When a GROUP BY clause, a HAVING clause, or a set function is specified, receive functions for passing inter-function values that have an SQL variable or an SQL parameter as the first argument can be specified only in a set function argument.
• Send functions for passing inter-function values
  

  If a receive function for passing inter-function values is not specified

  Any send function for passing inter-function values can be specified in locations where a function can be specified.

  If a receive function for passing inter-function values is specified

  • The receive function can be specified only in a WHERE clause or an ON search condition.
  • If a send function for passing inter-function values is specified in the ON search condition of a joined table that specifies an outer join, a column of the outer table cannot be specified in the first argument of the function.
  • If a send function for passing inter-function values is specified in the search conditions of the OR operand, all of the following conditions must be satisfied:
    - A plug-in instance is defined in the first argument of the send function for passing inter-function values.
    - The first argument of the send function for passing inter-function values specifies a base table column that is not a reference column to the outside.
    - The second and subsequent arguments of the send function for passing inter-function values do not specify a column (except a reference column to the outside) or an argument that includes a value expression for a component specification of a column.
    - A predicate that includes IS FALSE, IS UNKNOWN, or NOT is not specified for the send function for passing inter-function values.
    - The send function for passing inter-function values is not specified in a CAST specification.
    - If the FROM clause contains specifications for two or more tables, a table column that is different from the column specified in the first argument of the send function for passing inter-function values cannot be specified in the search conditions of the OR operand. (When the WHERE clause or the ON search condition contains the NOT Boolean operator, the same applies, even if the previous condition is satisfied after the NOT Boolean operator is eliminated by De Morgan's theorem.)
    
  • Send functions for passing inter-function values cannot be specified in CASE expressions or the VALUE scalar function.
  • A restriction applies if a named derived table defined by specifying the GROUP BY clause, HAVING clause, or a set function is specified in the FROM clause, and the named derived table does not create an internal derived table. In this case, a send function for passing inter-function values in which the first argument becomes an SQL value or SQL parameter cannot be specified in the search conditions of the query specification that specifies the named derived table.
• Send/receive functions for passing inter-function values
  These functions cannot be specified in SQL statements.
  

(a) Execution methods for plug-in distribution functions

You can execute plug-in distribution functions in two ways:

• Use an index-type plug-in to execute a plug-in distribution function
• Execute a plug-in distribution function without using an index-type plug-in

Some plug-in distribution functions can be executed only if an index-type plug-in is used (index-type plug-in-dependent function).

When HiRDB executes an index-type plug-in-dependent function, an error occurs if HiRDB determines that the index-type plug-in cannot be used. Table 5-3 shows the combinations that trigger an error. To find out whether a plug-in distribution function requires an index-type plug-in, see the plug-in manuals.

Table 5-3 Combinations that trigger an error when a plug-in distribution function is executed

Method that uses index-type plug-in to execute function	Method that executes function without using index-type plug-in	Retrieval method selected by HiRDB
		Retrieval with index-type plug-in	Retrieval without index-type plug-in
Provided	Provided	E	E
Provided*	Not provided*	E
Not provided	Provided	NA	E

E: Can be executed

: Error occurs when executed

NA: Not applicable

^* Index-type plug-in-dependent functions fall into this category. Examples for the HiRDB Text Search Plug-in are contains and contains_with_score.

(b) Restrictions on execution methods for index-type plug-in-dependent functions

The following restrictions apply when index-type plug-in-dependent functions are used:

1. Only a base table column specification can be specified in the first argument. Also, the column cannot be an external-referencing column.
2. Arguments that include the following value expressions cannot be specified in any argument except the first:
   
   • Column specifications, except external-referencing columns
   • Component specifications for columns
3. Index-type plug-in-dependent functions can be specified in WHERE clauses and ON search conditions.
4. If an index-type plug-in-dependent function is specified in the WHERE clause of a query specification that specifies an outer join, a column that becomes the inner table of the outer join cannot be specified in the first argument. An example is shown as follows:
   

   

5. If a index-type plug-in dependent function is specified in the ON search condition of a joined table that specifies an outer join, the following columns cannot be specified in the first argument:
   
   • Columns of the outer table
   • Columns of the outer-joined inner table included in the inner table, if the inner table is a joined table containing an outer join
6. If the FROM clause contains specifications for two or more tables, a table column that is different from the column specified in the first argument of an index-type plug-in-dependent function cannot be specified in the search condition of the OR operand. However, when the WHERE clause or the ON search condition contains the NOT logical operator, a different table column can be specified, if the previous condition is not satisfied after the NOT logical operator is eliminated by De Morgan's theorem.^* An example is shown as follows:
   

   Example:

   SELECT T1.C1,T2.C2 FROM T1,T2
     WHERE T1.C1=10 AND ((CONTAINS(T2.ADT,'ABC') IS TRUE)
                      OR CONTAINS(T2.ADT,'DEF') IS TRUE))

   The UNION representation of this SQL is as follows:
   

   (SELECT T1.C1,T2.C2 FROM T1,T2
     WHERE T1.C1=10 AND (CONTAINS(T1.ADT,'ABC') IS TRUE)
   UNION ALL
     SELECT T1.C1,T2.C2 FROM T1,T2
         WHERE T1.C1=10 AND (CONTAINS(T2.ADT,'DEF') IS TRUE))
   EXCEPT ALL
     SELECT T1.C1,T2.C2 FROM T1,T2
         WHERE T1.C1=10 AND (CONTAINS(T2.ADT,'DEF') IS TRUE)
               AND (CONTAINS(T1.ADT,'ABC') IS TRUE)

   ^* Assume that the following SQL statements have been specified:

   SELECT T1.C1,T2.C2 FROM T1,T2
     WHERE NOT(CONTAINS(T1.ADT, ...)IS NOT TRUE AND T1.C1=10)
     AND T1.C1=T2.C1

   If the NOT logical operator is eliminated according to De Morgan's theorem, the result is as follows:
   

   SELECT T1.C1,T2.C2 FROM T1,T2
     WHERE NOT(CONTAINS(T1.ADT, ...)IS TRUE OR T1.C1<>10)
     AND T1.C1=T2.C1

7. Index-type plug-in dependent functions cannot be specified in CASE expressions and CAST specifications.
8. Predicates that include IS FALSE, IS UNKNOWN, or negation (NOT) cannot be specified for index-type plug-in-dependent functions.

Examples related to these restrictions are as follows.

Example 1

If the WHERE clause specifies a send function for passing inter-function values and that function is dependent on an index-type plug-in, the first argument cannot contain one query specification that specifies that send function, together with an index-type plug-in-dependent function that has a column from the same table.

SELECT C1,C2, score(SENTENCES) FROM T1
   WHERE contains(SENTENCES,...)IS TRUE
      AND contains_with_score(SENTENCES, ...) IS TRUE

Example 2

This example outer-joins tables T1 and T2 and retrieves data by specifying an index-type plug-in-dependent function in the WHERE clause.

SELECT T1.C1,T2.C2 FROM T1 LEFT OUTER JOIN T2
   ON T1.C1=T2.C1 WHERE contains(T1.C3, ...)IS TRUE

(a) Storing passing inter-function values to a list

When target records are narrowed hierarchically (a narrowed search is performed), the results of the receive function for passing inter-function values can be obtained quickly by storing the passing inter-function values to a list.

To store passing inter-function values to a list, use the ASSIGN LIST statement to specify a send function for passing inter-function values in the search conditions for creating a list from a base table. The send function for passing inter-function values must be able to store the passing inter-function values to a list. (However, only one send function for passing inter-function values that can store such values to a list can be specified in the ASSIGN LIST statement.)

For information about whether the functions provided by a plug-in can store passing inter-function values to a list, refer to the manual for that plug-in.

You can also use the ASSIGN LIST statement to store passing inter-function values from a list that stores such values to a new list.

(b) Getting passing inter-function values from a list

To get passing inter-function values stored to a list without specifying a send function for passing inter-function values, specify a receive function for passing inter-function values that can get such values from a list (receive function for passing inter-function values for lists) in the selection expression of the cursor specification for search via a list.

For information about whether the functions provided by a plug-in can get passing inter-function values from a list, refer to the manual for that plug-in.

If a receive function for passing inter-function values that can get such values from a list is specified in the selection expression of the cursor specification for search via a list, HiRDB gets those values without evaluating the type of send function for passing inter-function values that stored those values to the list. Therefore, be sure to specify a receive function for passing inter-function values that corresponds to the send function for passing inter-function values specified when the list was created.

(c) Methods of executing set operations between lists

If a set operation between lists is to be performed, the set operation execution method changes depending on the send function for passing inter-function values that was specified in the search conditions for list creation.

Table 5-4 shows the passing inter-function values in the set operation results for the following:

list-name-1 {AND | OR | AND NOT | ANDNOT} list-name-2

Table 5-4 Passing inter-function values in set operation results

Send function for passing inter-function values when list-name-1 is created1		Send function for passing inter-function values when list-name-2 is created1
		When passing inter-function values can be stored to a list		Other cases
		Passing inter-function values for narrowing used is specified	No set operation method is specified
When passing inter-function values can be stored to a list.	Passing inter-function values for narrowing used is specified.	N	N	Passing inter-function values of list-name-12
	No set operation method is specified.	N	N	N
Other cases		N	N	None

Legend:

N: Cannot be executed.

¹ For information about send functions for storing inter-function variables that allow a set operation method to be specified, refer to the manual of the individual plug-in.

² The set operation result becomes the null value if the OR operation results do not include passing inter-function values.