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