Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions - Mailing list pgsql-hackers

Hi hackers,

Continuing to check what ON CONVERSION ERROR should actually cover.  By
definition the clause is about a conversion error, and a reference
implementation (Oracle) likewise propagates operand errors and catches
only the conversion.  By that measure three behaviors are clear defects;
two others look like design/policy choices that have not been made.
(This is separate from the not-found soft/hard asymmetry I raised
earlier.)

All examples were built and run on the current WIP branch.


Clear defects
=============

A. An error in evaluating the operand is folded into the DEFAULT, and
   inconsistently so.

A numeric->text conversion cannot fail, yet an overflow while computing
the operand is swallowed and replaced by the DEFAULT:

    SELECT CAST(1e100000::numeric * 1e100000::numeric AS text
                DEFAULT 'oops' ON CONVERSION ERROR);
    -- oops

But a different operand error is not swallowed:

    SELECT CAST(1/0 AS text DEFAULT 'oops' ON CONVERSION ERROR);
    -- ERROR: division by zero

The only difference is that the numeric multiplication's overflow is
swallowed while the integer division's error is not.  Which operand
errors fold into the DEFAULT varies by operation, with no stated rule.

Oracle catches only the conversion; an error in the operand propagates:

    CAST(1e70*1e70 AS NUMBER DEFAULT -1 ON CONVERSION ERROR)  -> ORA-01426 (overflow)
    CAST(1/0       AS NUMBER DEFAULT -1 ON CONVERSION ERROR)  -> ORA-01476 (div by zero)
    CAST(CAST(99999 AS NUMBER(3)) AS NUMBER DEFAULT -1 ON CONVERSION ERROR)
                                                             -> ORA-01438 (inner cast)

whereas a genuine value-conversion failure is caught, as intended:

    CAST('abc' AS NUMBER DEFAULT -1 ON CONVERSION ERROR)     -> -1


B. The same operand swallowing diverges between plan time and run time,
   depending on constant folding.

It is the same operand swallowing as (A), but here the result of one
and the same valid query depends on whether the operand folds:

    -- operand is constant, folded at plan time, so the overflow propagates:
    SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + 70000::int2::int4
                AS int DEFAULT -1 ON CONVERSION ERROR);
    -- ERROR: smallint out of range

    -- same expression with the operand fed from a column, not folded:
    CREATE TABLE nums(x int);  INSERT INTO nums VALUES (70000);
    SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + x::int2::int4
                AS int DEFAULT -1 ON CONVERSION ERROR) FROM nums;
    -- -1   (swallowed)

Oracle has no such fold-dependent split, because an operand error
propagates either way.


C. A nested DEFAULT can turn a hard error into a silent wrong answer.

    CREATE TABLE t(a text, b text);
    INSERT INTO t VALUES ('foo', 'bar');

    SELECT CAST(CAST(a AS int DEFAULT b::int ON CONVERSION ERROR)
                AS int2 DEFAULT -1 ON CONVERSION ERROR) FROM t;
    -- -1

The inner DEFAULT b::int (b = 'bar') fails at run time.  At top level
the same failing DEFAULT raises:

    SELECT CAST('x' AS int DEFAULT ('zzz')::int ON CONVERSION ERROR);
    -- ERROR: invalid input syntax for type integer: "zzz"

Nested, it is instead taken for the outer cast's own conversion failure
and the outer DEFAULT (-1) is returned silently.  A hard error becoming
a silent value is the concern here, independently of the DEFAULT policy
in (D).


Look like policy decisions
==========================

D. May the DEFAULT be an arbitrary expression, or only a literal/bind?

We currently allow an arbitrary expression as the DEFAULT:

    SELECT CAST('x' AS int DEFAULT (length('abc') * 2) ON CONVERSION ERROR);
    -- 6

Oracle restricts the DEFAULT to a literal or bind variable:

    CAST(x AS NUMBER DEFAULT TO_NUMBER('bad') ON CONVERSION ERROR)
        -- ORA-43907: Argument in DEFAULT ... ON CONVERSION ERROR clause
        --            must be a literal or bind variable.

which structurally prevents (C).  This expression DEFAULT can itself
fail; if it is intended to stay, its evaluation scope needs to be
pinned down so the nested case in (C) cannot arise.


E. User-defined types reached via I/O coercion: reject, or support?

The documentation says only built-in casts are supported, and a
user-defined WITH FUNCTION cast is rejected at parse time:

    CREATE FUNCTION int_to_point(int) RETURNS point
       AS 'SELECT point($1, $1)' LANGUAGE sql IMMUTABLE;
    CREATE CAST (int AS point) WITH FUNCTION int_to_point(int);
    SELECT CAST(1 AS point DEFAULT NULL ON CONVERSION ERROR);
    -- ERROR: cannot cast type integer to point with DEFAULT expression
    --        in CAST ... ON CONVERSION ERROR
    -- DETAIL: Safe type casts for user-defined types are not yet supported.

But a user-defined type reached through I/O coercion (a WITH INOUT
cast, or a plain text->type explicit cast) is accepted, and a
hard-erroring input function then leaks straight through
ON CONVERSION ERROR instead of being rejected or folding into the
DEFAULT.  widget_in/widget_out below are the deliberately hard-erroring
I/O functions from the regression suite (src/test/regress/regress.c),
with :regresslib set to the regression shared library as in
test_setup.sql:

    CREATE TYPE hardtype;
    CREATE FUNCTION hardtype_in(cstring)  RETURNS hardtype
       AS :'regresslib', 'widget_in'  LANGUAGE C STRICT IMMUTABLE;
    CREATE FUNCTION hardtype_out(hardtype) RETURNS cstring
       AS :'regresslib', 'widget_out' LANGUAGE C STRICT IMMUTABLE;
    CREATE TYPE hardtype (internallength = 24, input = hardtype_in,
       output = hardtype_out, alignment = double);
    CREATE CAST (text AS hardtype) WITH INOUT;

    SELECT CAST('bad'::text AS hardtype DEFAULT NULL ON CONVERSION ERROR);
    -- ERROR: invalid input syntax for type widget: "bad"  (leaked, not DEFAULT/NULL;
    --   the error says "widget" because the reused widget_in reports its own name)

So the "not supported" rule is enforced for one path but not the other.


(A), (B), and (C) are clear defects.  In all three, an error that is
not the conversion itself -- raised while evaluating the operand or an
inner cast -- is caught by ON CONVERSION ERROR, so a valid query either
returns a silently wrong value or its result depends on constant
folding.  That contradicts the clause's definition, that ON CONVERSION
ERROR applies to the conversion only.  (D) and (E), by contrast, are
genuine design choices that have not been made.

Best regards,
Henson