PartiQL Functions

[yliuuuu]

PartiQL Functions

This document gives answer to following questions informed by PartiQL Kotlin's implementation of a resolved query plan.

Questions

• What is a function; what is an operator?
• How do we define functions and operators?
• What is SPECIFIC?
• What are type coercions?
• What is the operator precedence?
• How are functions resolved?
• How are NULL and MISSING handled in functions?
• How are ANY parameters matched?

Proposals

Q1. What is a function; what is an operator?

A function is defined by its name and a set of operators. Operators are defined by input types and an output type.
For example, we way define a function called "foo" which has the given operators:

(T₁, T₁) -> T₁

(T₂, T₂) -> T₂

That is, the function "foo" has an operator defined for input types (T₁, T₁) with output type T₁, as well as an
operator with input types (T₂, T₂) with output type T₂.

Given a function name and list of arguments, the PartiQL compiler must resolve the appropriate operator.

Q2. How do we define functions and operators?

Functions are defined by its operators. We use the SQL CREATE FUNCTION syntax to defined operators introduced in SQL-99 4.8. The basic syntax is as follows:

<fn definition> ::= CREATE FUNCTION <quoted identitifer> (
                            <fn params> )
                            <fn return type>
                            <fn specific>
                            <fn return>

<fn params> := '(' <fn param> (, <fn param>)+ ')'

<fn param> ::= <quoted identifier> <type>

<fn return type> ::= RETURNS <type>

<fn specific> ::= SPECIFIC <unquoted identifier>

<fn return> ::= <fn call>

This is a sketch of the syntax and not a full EBNF. Here are some examples:

CREATE FUNCTION "CHAR_LENGTH" (
    S1 BIT VARYING ( BML ) )
    RETURNS NUMERIC ( P2, 0 )
    SPECIFIC CHAR_LENGTH4
    RETURN CHAR_LENGTH ( S1 ) ;

CREATE FUNCTION "CHARACTER_LENGTH" (
    S1 CHARACTER ( CML ) )
    RETURNS NUMERIC ( P2, 0 )
    SPECIFIC CHARACTER_LENGTH1
    RETURN CHARACTER_LENGTH ( S1 ) ;

For a complete list of the SQL-99 builtins, see section 20.70 Definition of SQL built-in functions.

Q3. What is SPECIFIC?

The "SPECIFIC" is a unique identifier for this operator. For now, I am thinking of this as the operator name. I have
generated specific names for the PartiQL builtins with some string mangling. The rule is to combine the function name,
input types, and output type joining with double underscore. The input types are joined with a single underscore.

For example,

CREATE FUNCTION "LTE" (
    LHS INT,
    RHS INT )
    RETURNS BOOL
    SPECIFIC LTE__INT_INT__BOOL  -- operator name
    RETURN LTE ( LHS, RHS ) ;

NOTE: foo(x,y) -> z mangled to FOO\__X_Y__Z.

Q4. What are type coercions?

Type coercions are the implicit casting behavior defined by SQL. The planner is responsible for leverage coercions to
resolve functions and inserting an explicit cast operator where the coercion was matched.

For example, here are two operators for the function LTE

CREATE FUNCTION "LTE" (
    LHS INT8,
    RHS INT8 )
    RETURNS BOOL
    SPECIFIC LTE__INT8_INT8__BOOL
    RETURN LTE ( LHS, RHS ) ;

CREATE FUNCTION "LTE" (
    LHS INT16,
    RHS INT16 )
    RETURNS BOOL
    SPECIFIC LTE__INT16_INT16__BOOL
    RETURN LTE ( LHS, RHS ) ;

Given the function name "LTE" and arguments of types (INT8, INT16) the planner would match to the second operator with
an explicit cast. The result would be:

LTE(CAST(LHS AS INT16), RHS)

Q5. What is the operator precedence?

Operators of a function are ordered by the following rules

• Fewest args first
• Parameter type precedence left-to-right

For now, the following type ordering is used:

NULL
MISSING
BOOL
INT8
INT16
INT32
INT64
INT
DECIMAL
FLOAT32
FLOAT64
CHAR
STRING
CLOB
SYMBOL
BINARY
BYTE
BLOB
DATE
TIME
TIMESTAMP
INTERVAL
BAG
LIST
SEXP
STRUCT
ANY

For example, these operators (shown simply as a list of parameter types) are are sorted

(INT8)
(INT16)
(INT32)
(INT8,INT8)
(INT8,INT16)
(INT8,INT32)
(INT16,INT8)
(INT16,INT16)
(INT16,INT32)

This is implemented in the PartiQL Kotlin header.

Q6. How are functions resolved?

Functions are resolved quite simply,

• Type all arguments
• Lookup all operators
• Check arguments arg_i to param_i left to right
  • If arg_i is the same type as param_i then continue to next arg
  • If arg_i is coerceable to param_i then insert explicit CAST and continue to next arg
  • Else check next operator

We return the first match. See the Kotlin implementation for more details.

/**
 * Attempt to match arguments to the parameters; return the explicit CASTS if necessary.
 */
public fun match(signature: FunctionSignature, args: Args): Mapping? {
    if (signature.parameters.size != args.size) {
        return null
    }
    val mapping = ArrayList<FunctionSignature?>(args.size)
    for (i in args.indices) {
        val a = args[i]
        val p = signature.parameters[i]
        when {
            // 1. Exact match
            a.type == p.type -> mapping.add(null)
            // 2. Match ANY, no coercion needed
            p.type == PartiQLValueType.ANY -> mapping.add(null)
            // 3. Check for a coercion
            else -> {
                val cast = header.lookupCast(a.type, p.type)
                when (cast) {
                    null -> return null // short-circuit
                    else -> mapping.add(cast)
                }
            }
        }
    }
    // we made a match
    return mapping
}

Note that if a parameter type is ANY, it will match every argument. We don't actually want this behavior; see Q8 for a brief proposal on handling this.

Q7. How are NULL and MISSING handled in functions?

Function evaluation is determined by the method specification, see 21.24 Method Specifications Base Table. The important specification here is the property IS_NULL_CALL.

If a function is a null-call, it is evaluated as follows:

• First, evaluated all arguments.
• If any argument is NULL, return NULL.
• If any argument is MISSING, return MISSING in permissive mode and error in strict mode.
• Finally, invoke the resolved operator with all the non-null values from the evaluated arguments.

A null-call function is an SQL-invoked function that is defined to return the null value if any of its
input arguments is the null value. A null-call function is an SQL-invoked function whose specifies ‘RETURNS NULL ON NULL INPUT’.

If a function is NOT a null-call, it is evaluated as follows:

• First, evaluated all arguments.
• If in strict mode evaluation and any argument is MISSING, then error.
• Finally, invoke the resolved operator with all values from the evaluated arguments.

SQL functions are called on NULL input by default.

If is not specified, then CALLED ON NULL INPUT is implicit — SQL-99 11.49.f

Q8. How are ANY parameters matched?

In PartiQL, we wish to resolve operators when the types of arguments may not be known. For example, consider the given environment (defined as a binding tuple < ... >) and query.

-- <
--   T: { 
--        type: "struct",
--        constraints: [ OPEN ],
--        fields: [ { name: "a", type: "int" } ]
--   }
-- >

SELECT t.a + t.b FROM T as t

How do we resolve the appropriate + operator when the type of t.b is unknown? PartiQL defines additional operators which take ANY type parameters. So we would resolve to this operator:

CREATE FUNCTION "PLUS" (
  ARG_0 ANY,
  ARG_1 ANY )
  RETURNS ANY                    -- ANY return type ?? not exactly!
  SPECIFIC PLUS__ANY_ANY__ANY
  DETERMINISTIC
  CALLED ON NULL INPUT
  RETURN PLUS ( ARG_0, ARG_1 ) ;

This is fine, but we don't want to always match on ANY. Consider this,

-- <
--   T: { 
--        type: "struct",
--        constraints: [ CLOSED ],
--        fields: [ { name: "a", type: "int" }, { name: "b", type: "string" } ]
--   }
-- >

SELECT t.a + t.b FROM T as t

Now we know that t.b is string, so we shouldn't match the PLUS operator because we know that PLUS operates on numeric types. Consider these three possibilities.

---

(ANY,ANY) -- match
(INT,ANY) -- match
(INT,STRING) -- ERR!

Let's consider modeling the allowable types of the parameters as constraints on the operator definition.

CREATE FUNCTION "PLUS" (
  ARG_0 ANY,
  ARG_1 ANY )
  RETURNS ANY
  SPECIFIC PLUS__ANY_ANY__ANY 
  DETERMINISTIC
  CALLED ON NULL INPUT
  WITH CONSTRAINTS
    CONSTRAINT arg_0_type TYPE ARG_0 ONEOF (INT8, INT16, INT32, INT64, INT, DECIMAL, FLOAT32, FLOAT64),
    CONSTRAINT arg_1_type TYPE ARG_1 ONEOF (INT8, INT16, INT32, INT64, INT, DECIMAL, FLOAT32, FLOAT64),
    CONSTRAINT return_type TYPE RETURNS ONEOF (INT8, INT16, INT32, INT64, INT, DECIMAL, FLOAT32, FLOAT64),
  RETURN PLUS ( ARG_0, ARG_1 ) ;

This is a bit verbose, but if we break it down what we are saying is that the type of ARG_0, ARG_1, and the RETURNS types are one of the types in the respective lists. We may be able to alias common lists of types such as NUMERIC in this case.

CREATE FUNCTION "PLUS" (
  ARG_0 ANY,
  ARG_1 ANY )
  RETURNS ANY
  SPECIFIC PLUS__ANY_ANY__ANY 
  DETERMINISTIC
  CALLED ON NULL INPUT
  WITH CONSTRAINTS
    arg_0_type TYPE ARG_0 ONEOF NUMERIC,
    arg_1_type TYPE ARG_1 ONEOF NUMERIC,
    return_type TYPE RETURNS ONEOF NUMERIC,
  RETURN PLUS ( ARG_0, ARG_1 ) ;

The function resolver would use these constraints to resolve (or fail to resolve) with given arguments. To be continued ...

[yliuuuu]

Appendix I — Type Coercion Table

NOTE: This is incomplete.

⬤ — Implicit CAST / Coercion
◯ — Safe explicit CAST

Cast Table

FROM \ TO	ANY	BOOL	INT8	INT16	INT32	INT64	INT	DECIMAL	FLOAT32	FLOAT64	CHAR	STRING	SYMBOL	BINARY	BYTE	BLOB	CLOB	DATE	TIME	TIMESTAMP	INTERVAL	BAG	LIST	SEXP	STRUCT	NULL	MISSING
ANY	⬤
BOOL	⬤	⬤	⬤	⬤	⬤	⬤	⬤	⬤	⬤	⬤	⬤	⬤	⬤
INT8	⬤	◯	⬤	⬤	⬤	⬤	⬤	⬤	⬤	⬤		◯	◯
INT16	⬤	◯		⬤	⬤	⬤	⬤	⬤	⬤	⬤		◯	◯
INT32	⬤	◯			⬤	⬤	⬤	⬤	⬤	⬤		◯	◯
INT64	⬤	◯				⬤	⬤	⬤	⬤	⬤		◯	◯
INT	⬤	◯					⬤	⬤	⬤	⬤		◯	◯
DECIMAL	⬤	◯						⬤	⬤	⬤		◯	◯
FLOAT32	⬤	◯							⬤	⬤		◯	◯
FLOAT64	⬤	◯								⬤		◯	◯
CHAR	⬤	◯									⬤	⬤	⬤
STRING	⬤	◯										⬤	⬤				⬤
SYMBOL	⬤	◯										⬤	⬤				⬤
BINARY
BYTE
BLOB
CLOB	⬤																⬤
DATE
TIME
TIMESTAMP
INTERVAL
BAG	⬤																					⬤
LIST	⬤																					⬤	⬤	⬤
SEXP	⬤																					⬤	⬤	⬤
STRUCT	⬤																								⬤
NULL																										⬤
MISSING																											⬤

[yliuuuu]

Accredit to @RCHowell, originally from partiql/partiql-docs#48