From 31926911318f6061eee0c830e8bb26517f4f1b2c Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 15 Jul 2026 15:36:37 +0800 Subject: [PATCH v12 3/4] CAST(expr AS newtype DEFAULT expr ON CONVERSION ERROR) Introduce SQL-standard safe type cast syntax: CAST(expr AS newtype DEFAULT expr ON CONVERSION ERROR) This allow users to specify a default fallback expression if a type conversion fails, rather than raising an error. With this patchset, almost all of the cast functions in pg_cast.castfunc are now error-safe. CoerceViaIO and CoerceToDomain were already error-safe in the HEAD, see [0], this patch extends error-safe behavior to ArrayCoerceExpr. # Bumps catversion required [0]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274b Author: jian he Reviewed-by: Amul Sul Reviewed-by: Andrew Dunstan Reviewed-by: Corey Huinker Reviewed-by: Kirill Reshke Reviewed-by: Matheus Alcantara Reviewed-by: Henson Choi Discussion: https://postgr.es/m/CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com Commitfest: https://commitfest.postgresql.org/patch/5941 --- contrib/citext/expected/citext.out | 5 + contrib/citext/expected/citext_1.out | 5 + contrib/citext/sql/citext.sql | 2 + .../pg_stat_statements/expected/select.out | 24 +- contrib/pg_stat_statements/sql/select.sql | 5 + doc/src/sgml/syntax.sgml | 42 + src/backend/executor/execExpr.c | 97 +- src/backend/executor/execExprInterp.c | 35 + src/backend/jit/llvm/llvmjit_expr.c | 56 + src/backend/nodes/nodeFuncs.c | 53 + src/backend/optimizer/prep/prepagg.c | 15 + src/backend/optimizer/util/clauses.c | 126 +- src/backend/parser/gram.y | 21 + src/backend/parser/parse_agg.c | 10 + src/backend/parser/parse_coerce.c | 169 ++- src/backend/parser/parse_expr.c | 233 +++- src/backend/parser/parse_func.c | 3 + src/backend/parser/parse_target.c | 3 +- src/backend/parser/parse_type.c | 22 + src/backend/parser/parse_utilcmd.c | 3 +- src/backend/utils/adt/arrayfuncs.c | 9 + src/backend/utils/adt/ruleutils.c | 25 + src/include/executor/execExpr.h | 7 + src/include/executor/executor.h | 2 + src/include/nodes/execnodes.h | 21 + src/include/nodes/parsenodes.h | 1 + src/include/nodes/primnodes.h | 38 + src/include/optimizer/optimizer.h | 2 +- src/include/parser/parse_coerce.h | 16 +- src/include/parser/parse_node.h | 2 + src/include/parser/parse_type.h | 2 + src/test/regress/expected/cast.out | 1212 +++++++++++++++++ src/test/regress/expected/create_cast.out | 12 + src/test/regress/expected/equivclass.out | 19 + src/test/regress/expected/xml.out | 6 + src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/cast.sql | 458 +++++++ src/test/regress/sql/create_cast.sql | 3 + src/test/regress/sql/equivclass.sql | 5 + src/test/regress/sql/xml.sql | 2 + src/tools/pgindent/typedefs.list | 2 + 41 files changed, 2674 insertions(+), 101 deletions(-) create mode 100644 src/test/regress/expected/cast.out create mode 100644 src/test/regress/sql/cast.sql diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out index 8c0bf54f0f3..1dd383b8cdc 100644 --- a/contrib/citext/expected/citext.out +++ b/contrib/citext/expected/citext.out @@ -10,6 +10,11 @@ WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); --------+--------- (0 rows) +SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSION ERROR); -- error +ERROR: cannot cast type character to citext with DEFAULT expression in CAST ... ON CONVERSION ERROR +LINE 1: SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSI... + ^ +DETAIL: Explicit cast is defined but definition is not error safe. -- Test the operators and indexing functions -- Test = and <>. SELECT 'a'::citext = 'a'::citext AS t; diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out index c5e5f180f2b..1fede606a57 100644 --- a/contrib/citext/expected/citext_1.out +++ b/contrib/citext/expected/citext_1.out @@ -10,6 +10,11 @@ WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); --------+--------- (0 rows) +SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSION ERROR); -- error +ERROR: cannot cast type character to citext with DEFAULT expression in CAST ... ON CONVERSION ERROR +LINE 1: SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSI... + ^ +DETAIL: Explicit cast is defined but definition is not error safe. -- Test the operators and indexing functions -- Test = and <>. SELECT 'a'::citext = 'a'::citext AS t; diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql index aa1cf9abd5c..c820b0bd4d9 100644 --- a/contrib/citext/sql/citext.sql +++ b/contrib/citext/sql/citext.sql @@ -9,6 +9,8 @@ SELECT amname, opcname FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); +SELECT CAST('abc'::bpchar AS citext DEFAULT NULL ON CONVERSION ERROR); -- error + -- Test the operators and indexing functions -- Test = and <>. diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index a069119c790..84f10810f72 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -73,6 +73,25 @@ SELECT 1 AS "int" OFFSET 2 FETCH FIRST 3 ROW ONLY; ----- (0 rows) +-- error safe type cast +SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR); + int4 +------ + 2 +(1 row) + +SELECT CAST('12' AS numeric DEFAULT 2 ON CONVERSION ERROR); + numeric +--------- + 12 +(1 row) + +SELECT CAST('12' AS numeric(10) DEFAULT 2 ON CONVERSION ERROR); + numeric +--------- + 12 +(1 row) + -- DISTINCT and ORDER BY patterns -- Try some query permutations which once produced identical query IDs SELECT DISTINCT 1 AS "int"; @@ -222,6 +241,9 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 2 | 2 | SELECT $1 AS "int" ORDER BY 1 1 | 2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i 1 | 1 | SELECT $1 || $2 + 1 | 1 | SELECT CAST($1 AS int DEFAULT $2 ON CONVERSION ERROR) + 1 | 1 | SELECT CAST($1 AS numeric DEFAULT $2 ON CONVERSION ERROR) + 1 | 1 | SELECT CAST($1 AS numeric(10) DEFAULT $2 ON CONVERSION ERROR) 2 | 2 | SELECT DISTINCT $1 AS "int" 0 | 0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C" 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t @@ -230,7 +252,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; | | ) + | | SELECT f FROM t ORDER BY f 1 | 1 | select $1::jsonb ? $2 -(17 rows) +(20 rows) SELECT pg_stat_statements_reset() IS NOT NULL AS t; t diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql index a10d618c034..4be97977ba6 100644 --- a/contrib/pg_stat_statements/sql/select.sql +++ b/contrib/pg_stat_statements/sql/select.sql @@ -25,6 +25,11 @@ SELECT 1 AS "int" LIMIT 3 OFFSET 3; SELECT 1 AS "int" OFFSET 1 FETCH FIRST 2 ROW ONLY; SELECT 1 AS "int" OFFSET 2 FETCH FIRST 3 ROW ONLY; +-- error safe type cast +SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR); +SELECT CAST('12' AS numeric DEFAULT 2 ON CONVERSION ERROR); +SELECT CAST('12' AS numeric(10) DEFAULT 2 ON CONVERSION ERROR); + -- DISTINCT and ORDER BY patterns -- Try some query permutations which once produced identical query IDs SELECT DISTINCT 1 AS "int"; diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 67482996861..dd145b0a3c6 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -2125,6 +2125,48 @@ CAST ( expression AS type . + + + Safe Type Casts + + + A type cast may occasionally fail at run time. To guard against such + failures, you can provide an ON CONVERSION ERROR + clause to control what happens when a cast fails. The available forms + are: + +CAST ( expression AS type DEFAULT expression ON CONVERSION ERROR ) +CAST ( expression AS type ERROR ON CONVERSION ERROR ) + + With DEFAULT expression ON CONVERSION ERROR, + the default expression is evaluated and + returned instead. The ERROR ON CONVERSION ERROR + form raises an error on failure, which is also the default behavior + when no ON CONVERSION ERROR clause is specified. + Note: The DEFAULT expression + is itself evaluated normally, so it may also raise an error during that evaluation. + + + + User-defined type casts created with CREATE CAST + are also supported, provided that the corresponding cast + function in the pg_proc system catalog is ERROR SAFE. + A function is considered ERROR SAFE when its proerrorsafe + column is set to true. + + + + Some examples: + +SELECT CAST('not-a-date' AS date DEFAULT NULL ON CONVERSION ERROR); +Result: NULL +SELECT CAST('not-a-date' AS date DEFAULT '1970-01-01' ON CONVERSION ERROR); +Result: 1970-01-01 +SELECT CAST(TEXT 'error' AS integer DEFAULT 3 ON CONVERSION ERROR); +Result: 3 + + + diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index cfea7e160c2..327cb54444c 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -99,6 +99,9 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate, static void ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state, Datum *resv, bool *resnull, ExprEvalStep *scratch); +static void ExecInitSafeTypeCastExpr(SafeTypeCastExpr *stcexpr, ExprState *state, + Datum *resv, bool *resnull, + ExprEvalStep *scratch); static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning, ErrorSaveContext *escontext, bool omit_quotes, bool exists_coerce, @@ -141,6 +144,26 @@ static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning, */ ExprState * ExecInitExpr(Expr *node, PlanState *parent) +{ + return ExecInitExprWithContext(node, parent, NULL); +} + +/* + * ExecInitExprWithContext: same as ExecInitExpr, but with an optional + * ErrorSaveContext for soft error handling. + * + * When 'escontext' is non-NULL, expression nodes that support soft errors + * (currently CoerceToDomain's NOT NULL and CHECK constraint steps) will use + * errsave() instead of ereport(), allowing the caller to detect and handle + * failures without a transaction abort. + * + * The escontext must be provided at initialization time (not after), because + * it is copied into per-step data during expression compilation. + * + * Not all expression node types support soft errors. If in doubt, pass NULL. + */ +ExprState * +ExecInitExprWithContext(Expr *node, PlanState *parent, Node *escontext) { ExprState *state; ExprEvalStep scratch = {0}; @@ -154,6 +177,7 @@ ExecInitExpr(Expr *node, PlanState *parent) state->expr = node; state->parent = parent; state->ext_params = NULL; + state->escontext = (ErrorSaveContext *) escontext; /* Insert setup steps as needed */ ExecCreateExprSetupSteps(state, (Node *) node); @@ -763,6 +787,18 @@ ExecBuildUpdateProjection(List *targetList, */ ExprState * ExecPrepareExpr(Expr *node, EState *estate) +{ + return ExecPrepareExprWithContext(node, estate, NULL); +} + +/* + * ExecPrepareExprWithContext: same as ExecPrepareExpr, but with an optional + * ErrorSaveContext for soft error handling. + * + * See ExecInitExprWithContext for details on the escontext parameter. + */ +ExprState * +ExecPrepareExprWithContext(Expr *node, EState *estate, Node *escontext) { ExprState *result; MemoryContext oldcontext; @@ -771,7 +807,7 @@ ExecPrepareExpr(Expr *node, EState *estate) node = expression_planner(node); - result = ExecInitExpr(node, NULL); + result = ExecInitExprWithContext(node, NULL, escontext); MemoryContextSwitchTo(oldcontext); @@ -1701,6 +1737,7 @@ ExecInitExprRec(Expr *node, ExprState *state, elemstate->innermost_caseval = palloc_object(Datum); elemstate->innermost_casenull = palloc_object(bool); + elemstate->escontext = state->escontext; ExecInitExprRec(acoerce->elemexpr, elemstate, &elemstate->resvalue, &elemstate->resnull); @@ -2176,6 +2213,16 @@ ExecInitExprRec(Expr *node, ExprState *state, break; } + case T_SafeTypeCastExpr: + { + SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node); + + ExecInitSafeTypeCastExpr(stcexpr, state, resv, resnull, + &scratch); + + break; + } + case T_CoalesceExpr: { CoalesceExpr *coalesce = (CoalesceExpr *) node; @@ -2736,7 +2783,8 @@ ExecInitFunc(ExprEvalStep *scratch, Expr *node, List *args, Oid funcid, /* Initialize function call parameter structure too */ InitFunctionCallInfoData(*fcinfo, flinfo, - nargs, inputcollid, NULL, NULL); + nargs, inputcollid, + (Node *) state->escontext, NULL); /* Keep extra copies of this info to save an indirection at runtime */ scratch->d.func.fn_addr = flinfo->fn_addr; @@ -4737,6 +4785,51 @@ ExecBuildParamSetEqual(TupleDesc desc, return state; } +/* + * Push steps to evaluate a SafeTypeCastExpr and its various subsidiary + * expressions. + */ +static void +ExecInitSafeTypeCastExpr(SafeTypeCastExpr *stcexpr, ExprState *state, + Datum *resv, bool *resnull, + ExprEvalStep *scratch) +{ + /* + * If there's no cast expression (castexpr == NULL), only the DEFAULT + * expression needs to be evaluated; set it up and return early. + */ + if (stcexpr->castexpr == NULL) + { + ExecInitExprRec(stcexpr->defexpr, state, resv, resnull); + + return; + } + else + { + SafeTypeCastState *stcstate = palloc0_object(SafeTypeCastState); + ErrorSaveContext *saved_escontext = state->escontext; + + stcstate->stcexpr = stcexpr; + stcstate->escontext.type = T_ErrorSaveContext; + stcstate->escontext.error_occurred = false; + stcstate->escontext.details_wanted = false; + stcstate->escontext.error_data = NULL; + state->escontext = &stcstate->escontext; + + /* evaluate argument expression into step's result area */ + ExecInitExprRec(stcexpr->castexpr, state, resv, resnull); + scratch->opcode = EEOP_SAFETYPE_CAST; + scratch->d.stcexpr.stcstate = stcstate; + ExprEvalPushStep(state, scratch); + + /* evaluate DEFAULT expression using the prior state->escontext */ + state->escontext = saved_escontext; + ExecInitExprRec(stcstate->stcexpr->defexpr, state, resv, resnull); + + stcstate->jump_end = state->steps_len; + } +} + /* * Push steps to evaluate a JsonExpr and its various subsidiary expressions. */ diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index d45812c23aa..cf73e5b1ff3 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -578,6 +578,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) &&CASE_EEOP_XMLEXPR, &&CASE_EEOP_JSON_CONSTRUCTOR, &&CASE_EEOP_IS_JSON, + &&CASE_EEOP_SAFETYPE_CAST, &&CASE_EEOP_JSONEXPR_PATH, &&CASE_EEOP_JSONEXPR_COERCION, &&CASE_EEOP_JSONEXPR_COERCION_FINISH, @@ -1936,6 +1937,28 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_SAFETYPE_CAST) + { + SafeTypeCastState *stcstate = op->d.stcexpr.stcstate; + + if (!SOFT_ERROR_OCCURRED(&stcstate->escontext)) + EEO_JUMP(stcstate->jump_end); + else + { + *op->resvalue = (Datum) 0; + *op->resnull = true; + + /* + * Type cast error occurred. Reset the ErrorSaveContext so + * it's ready for the next coercion evaluation attempt. + */ + stcstate->escontext.error_occurred = false; + stcstate->escontext.details_wanted = false; + + EEO_NEXT(); + } + } + EEO_CASE(EEOP_JSONEXPR_PATH) { /* too complex for an inline implementation */ @@ -3654,6 +3677,18 @@ ExecEvalArrayCoerce(ExprState *state, ExprEvalStep *op, ExprContext *econtext) econtext, op->d.arraycoerce.resultelemtype, op->d.arraycoerce.amstate); + + if (SOFT_ERROR_OCCURRED(op->d.arraycoerce.elemexprstate->escontext)) + { + *op->resvalue = (Datum) 0; + *op->resnull = true; + + /* + * A soft error occurred. The caller may need to reset + * ExprState.ErrorSaveContext.error_occurred for the next evaluation. + * Currently, EEOP_SAFETYPE_CAST will do that. + */ + } } /* diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index 0e160b8502c..af93dd084ab 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -2258,6 +2258,62 @@ llvm_compile_expr(ExprState *state) LLVMBuildBr(b, opblocks[opno + 1]); break; + case EEOP_SAFETYPE_CAST: + { + SafeTypeCastState *stcstate = op->d.stcexpr.stcstate; + LLVMBasicBlockRef b_noerror; + LLVMBasicBlockRef b_error; + LLVMValueRef v_error_occurred_p; + LLVMValueRef v_details_wanted_p; + LLVMValueRef v_error_occurred; + + b_noerror = l_bb_before_v(opblocks[opno + 1], + "op.%d.noerror", opno); + b_error = l_bb_before_v(opblocks[opno + 1], + "op.%d.error", opno); + + /* Get pointer to error_occurred field */ + v_error_occurred_p = l_ptr_const(&stcstate->escontext.error_occurred, + l_ptr(TypeStorageBool)); + + /* Load error_occurred at runtime */ + v_error_occurred = l_load(b, TypeStorageBool, v_error_occurred_p, ""); + + /* Branch based on error_occurred: no error -> jump_end */ + LLVMBuildCondBr(b, + LLVMBuildICmp(b, LLVMIntEQ, v_error_occurred, + l_sbool_const(0), ""), + b_noerror, + b_error); + + /* No error: jump to end */ + LLVMPositionBuilderAtEnd(b, b_noerror); + LLVMBuildBr(b, opblocks[stcstate->jump_end]); + + /* Error occurred: set null, reset flags, evaluate default */ + LLVMPositionBuilderAtEnd(b, b_error); + + /* set resnull to true */ + LLVMBuildStore(b, l_sbool_const(1), v_resnullp); + + /* reset resvalue */ + LLVMBuildStore(b, l_datum_const(0), v_resvaluep); + + /* + * Reset for next use such as for catching errors when + * coercing a expression. + */ + LLVMBuildStore(b, l_sbool_const(0), v_error_occurred_p); + + v_details_wanted_p = l_ptr_const(&stcstate->escontext.details_wanted, + l_ptr(TypeStorageBool)); + LLVMBuildStore(b, l_sbool_const(0), v_details_wanted_p); + + LLVMBuildBr(b, opblocks[opno + 1]); + + break; + } + case EEOP_JSONEXPR_PATH: { JsonExprState *jsestate = op->d.jsonexpr.jsestate; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 2a2e00b372e..d46ef1a87e4 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -206,6 +206,9 @@ exprType(const Node *expr) case T_RowCompareExpr: type = BOOLOID; break; + case T_SafeTypeCastExpr: + type = ((const SafeTypeCastExpr *) expr)->resulttype; + break; case T_CoalesceExpr: type = ((const CoalesceExpr *) expr)->coalescetype; break; @@ -453,6 +456,8 @@ exprTypmod(const Node *expr) return typmod; } break; + case T_SafeTypeCastExpr: + return ((const SafeTypeCastExpr *) expr)->resulttypmod; case T_CoalesceExpr: { /* @@ -970,6 +975,9 @@ exprCollation(const Node *expr) /* RowCompareExpr's result is boolean ... */ coll = InvalidOid; /* ... so it has no collation */ break; + case T_SafeTypeCastExpr: + coll = ((const SafeTypeCastExpr *) expr)->resultcollid; + break; case T_CoalesceExpr: coll = ((const CoalesceExpr *) expr)->coalescecollid; break; @@ -1248,6 +1256,9 @@ exprSetCollation(Node *expr, Oid collation) /* RowCompareExpr's result is boolean ... */ Assert(!OidIsValid(collation)); /* ... so never set a collation */ break; + case T_SafeTypeCastExpr: + ((SafeTypeCastExpr *) expr)->resultcollid = collation; + break; case T_CoalesceExpr: ((CoalesceExpr *) expr)->coalescecollid = collation; break; @@ -1569,6 +1580,9 @@ exprLocation(const Node *expr) /* just use leftmost argument's location */ loc = exprLocation((Node *) ((const RowCompareExpr *) expr)->largs); break; + case T_SafeTypeCastExpr: + loc = ((const SafeTypeCastExpr *) expr)->location; + break; case T_CoalesceExpr: /* COALESCE keyword should always be the first thing */ loc = ((const CoalesceExpr *) expr)->location; @@ -2345,6 +2359,18 @@ expression_tree_walker_impl(Node *node, return true; } break; + case T_SafeTypeCastExpr: + { + SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node); + + if (WALK(stcexpr->source)) + return true; + if (WALK(stcexpr->castexpr)) + return true; + if (WALK(stcexpr->defexpr)) + return true; + } + break; case T_CoalesceExpr: return WALK(((CoalesceExpr *) node)->args); case T_MinMaxExpr: @@ -3404,6 +3430,19 @@ expression_tree_mutator_impl(Node *node, return (Node *) newnode; } break; + case T_SafeTypeCastExpr: + { + SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node); + SafeTypeCastExpr *newnode; + + FLATCOPY(newnode, stcexpr, SafeTypeCastExpr); + MUTATE(newnode->source, stcexpr->source, Expr *); + MUTATE(newnode->castexpr, stcexpr->castexpr, Expr *); + MUTATE(newnode->defexpr, stcexpr->defexpr, Expr *); + + return (Node *) newnode; + } + break; case T_CoalesceExpr: { CoalesceExpr *coalesceexpr = (CoalesceExpr *) node; @@ -4581,6 +4620,20 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(tc->typeName)) return true; + if (WALK(tc->defexpr)) + return true; + } + break; + case T_SafeTypeCastExpr: + { + SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node); + + if (WALK(stcexpr->source)) + return true; + if (WALK(stcexpr->castexpr)) + return true; + if (WALK(stcexpr->defexpr)) + return true; } break; case T_CollateClause: diff --git a/src/backend/optimizer/prep/prepagg.c b/src/backend/optimizer/prep/prepagg.c index 3737cc15ba1..beec75112db 100644 --- a/src/backend/optimizer/prep/prepagg.c +++ b/src/backend/optimizer/prep/prepagg.c @@ -358,6 +358,21 @@ preprocess_aggrefs_walker(Node *node, PlannerInfo *root) */ return false; } + if (IsA(node, SafeTypeCastExpr)) + { + SafeTypeCastExpr *castexpr = (SafeTypeCastExpr *) node; + + /* + * SafeTypeCastExpr->source may also contain an Aggref node, but since + * it is kept only for deparsing purposes, we must not recurse into it + * when processing aggregate nodes here. + * + * We already prevent defexpr from containing aggregate functions. + */ + return expression_tree_walker((Node *) castexpr->castexpr, + preprocess_aggrefs_walker, + root); + } Assert(!IsA(node, SubLink)); return expression_tree_walker(node, preprocess_aggrefs_walker, root); } diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index aa8886ec210..1be9bbdf787 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -71,6 +71,7 @@ typedef struct List *active_fns; Node *case_val; bool estimate; + ErrorSaveContext *escontext; } eval_const_expressions_context; typedef struct @@ -2510,6 +2511,10 @@ rowtype_field_matches(Oid rowtypeid, int fieldnum, * NOTE: another critical effect is that any function calls that require * default arguments will be expanded, and named-argument calls will be * converted to positional notation. The executor won't handle either. + * + * NOTE: If eval_const_expressions_context->escontext is not NULL, the + * expression is evaluated in an error-safe manner. In case of failure, it + * may return NULL instead of throwing an error. *-------------------- */ Node * @@ -2525,6 +2530,7 @@ eval_const_expressions(PlannerInfo *root, Node *node) context.active_fns = NIL; /* nothing being recursively simplified */ context.case_val = NULL; /* no CASE being examined */ context.estimate = false; /* safe transformations only */ + context.escontext = NULL; /* for error-safe expression evaluation */ return eval_const_expressions_mutator(node, &context); } @@ -2667,6 +2673,7 @@ estimate_expression_value(PlannerInfo *root, Node *node) context.active_fns = NIL; /* nothing being recursively simplified */ context.case_val = NULL; /* no CASE being examined */ context.estimate = true; /* unsafe transformations OK */ + context.escontext = NULL; /* for error-safe expression evaluation */ return eval_const_expressions_mutator(node, &context); } @@ -2692,11 +2699,12 @@ estimate_expression_value(PlannerInfo *root, Node *node) (!expression_tree_walker((Node *) (node), contain_non_const_walker, NULL)) /* Generic macro for applying evaluate_expr */ -#define ece_evaluate_expr(node) \ +#define ece_evaluate_expr(node, escontext) \ ((Node *) evaluate_expr((Expr *) (node), \ exprType((Node *) (node)), \ exprTypmod((Node *) (node)), \ - exprCollation((Node *) (node)))) + exprCollation((Node *) (node)), \ + (Node *) escontext)) /* * Recursive guts of eval_const_expressions/estimate_expression_value @@ -3157,7 +3165,7 @@ eval_const_expressions_mutator(Node *node, if (!has_nonconst_input && ece_function_is_safe(expr->opfuncid, context)) - return ece_evaluate_expr(expr); + return ece_evaluate_expr(expr, context->escontext); return (Node *) expr; } @@ -3177,7 +3185,7 @@ eval_const_expressions_mutator(Node *node, */ if (ece_all_arguments_const(saop) && ece_function_is_safe(saop->opfuncid, context)) - return ece_evaluate_expr(saop); + return ece_evaluate_expr(saop, context->escontext); return (Node *) saop; } case T_BoolExpr: @@ -3301,6 +3309,67 @@ eval_const_expressions_mutator(Node *node, context); } break; + + case T_SafeTypeCastExpr: + { + SafeTypeCastExpr *stc = castNode(SafeTypeCastExpr, node); + Node *castexpr = (Node *) stc->castexpr; + Node *defexpr = (Node *) stc->defexpr; + SafeTypeCastExpr *newexpr = makeNode(SafeTypeCastExpr); + + ErrorSaveContext *save_escontext; + + /* + * Set up a fresh escontext while processing castexpr, so that + * any soft errors raised while trying to const-fold it are + * left unfolded for the executor to handle at runtime (see + * ExecEvalSafeTypeCastExpr) rather than causing a hard ERROR + * here. We must save/restore the previous escontext rather + * than assuming it was NULL, since a SafeTypeCastExpr can be + * nested inside another one's castexpr; unconditionally + * clearing it to NULL (or leaving our own escontext behind) + * would corrupt the outer SafeTypeCastExpr's error-catching + * state for the remainder of its own castexpr processing. + */ + save_escontext = context->escontext; + + context->escontext = makeNode(ErrorSaveContext); + context->escontext->type = T_ErrorSaveContext; + context->escontext->error_occurred = false; + + castexpr = eval_const_expressions_mutator(castexpr, + context); + + /* + * defexpr is not allowed to fail softly: it's supposed to be + * a well-behaved default, so let any error in it propagate + * normally. Do this regardless of nesting; only after that + * do we restore whatever escontext the caller had active. + */ + context->escontext = NULL; + + defexpr = eval_const_expressions_mutator(defexpr, + context); + + context->escontext = save_escontext; + + /* + * No need to fold "source" to a constant. The executor does + * not use it, see ExecInitSafeTypeCastExpr. Additionally, + * castexpr expression tree may already contain the "source" + * node. + */ + newexpr->source = stc->source; + newexpr->castexpr = (Expr *) castexpr; + newexpr->defexpr = (Expr *) defexpr; + newexpr->resulttype = stc->resulttype; + newexpr->resulttypmod = stc->resulttypmod; + newexpr->resultcollid = stc->resultcollid; + newexpr->location = stc->location; + + return (Node *) newexpr; + } + case T_SubPlan: case T_AlternativeSubPlan: @@ -3418,6 +3487,7 @@ eval_const_expressions_mutator(Node *node, { ArrayCoerceExpr *ac = makeNode(ArrayCoerceExpr); Node *save_case_val; + Expr *simple = NULL; /* * Copy the node and const-simplify its arguments. We can't @@ -3425,9 +3495,10 @@ eval_const_expressions_mutator(Node *node, * with case_val only while processing the elemexpr. */ memcpy(ac, node, sizeof(ArrayCoerceExpr)); - ac->arg = (Expr *) - eval_const_expressions_mutator((Node *) ac->arg, - context); + simple = (Expr *) eval_const_expressions_mutator((Node *) ac->arg, + context); + if (simple) + ac->arg = simple; /* * Set up for the CaseTestExpr node contained in the elemexpr. @@ -3436,9 +3507,10 @@ eval_const_expressions_mutator(Node *node, save_case_val = context->case_val; context->case_val = NULL; - ac->elemexpr = (Expr *) - eval_const_expressions_mutator((Node *) ac->elemexpr, - context); + simple = (Expr *) eval_const_expressions_mutator((Node *) ac->elemexpr, + context); + if (simple) + ac->elemexpr = simple; context->case_val = save_case_val; @@ -3453,7 +3525,12 @@ eval_const_expressions_mutator(Node *node, if (ac->arg && IsA(ac->arg, Const) && ac->elemexpr && !IsA(ac->elemexpr, CoerceToDomain) && !contain_mutable_functions((Node *) ac->elemexpr)) - return ece_evaluate_expr(ac); + { + simple = (Expr *) ece_evaluate_expr(ac, context->escontext); + + if (simple) + return (Node *) simple; + } return (Node *) ac; } @@ -3649,7 +3726,7 @@ eval_const_expressions_mutator(Node *node, node = ece_generic_processing(node); /* If all arguments are Consts, we can fold to a constant */ if (ece_all_arguments_const(node)) - return ece_evaluate_expr(node); + return ece_evaluate_expr(node, context->escontext); return node; } case T_CoalesceExpr: @@ -3732,7 +3809,8 @@ eval_const_expressions_mutator(Node *node, return (Node *) evaluate_expr((Expr *) svf, svf->type, svf->typmod, - InvalidOid); + InvalidOid, + NULL); else return copyObject((Node *) svf); } @@ -3829,7 +3907,7 @@ eval_const_expressions_mutator(Node *node, newfselect->resulttype, newfselect->resulttypmod, newfselect->resultcollid)) - return ece_evaluate_expr(newfselect); + return ece_evaluate_expr(newfselect, context->escontext); } return (Node *) newfselect; } @@ -4159,7 +4237,7 @@ eval_const_expressions_mutator(Node *node, newcre->arg = (Expr *) arg; if (arg != NULL && IsA(arg, Const)) - return ece_evaluate_expr((Node *) newcre); + return ece_evaluate_expr((Node *) newcre, context->escontext); return (Node *) newcre; } default: @@ -4559,6 +4637,8 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod, args, funcvariadic, func_tuple, context); + Assert(!(SOFT_ERROR_OCCURRED(context->escontext) && newexpr != NULL)); + if (!newexpr && allow_non_const && OidIsValid(func_form->prosupport)) { /* @@ -5284,7 +5364,7 @@ evaluate_function(Oid funcid, Oid result_type, int32 result_typmod, newexpr->location = -1; return evaluate_expr((Expr *) newexpr, result_type, result_typmod, - result_collid); + result_collid, (Node *) context->escontext); } /* @@ -5738,10 +5818,13 @@ sql_inline_error_callback(void *arg) * * We use the executor's routine ExecEvalExpr() to avoid duplication of * code and ensure we get the same result as the executor would get. + * + * When escontext is non-NULL, safely evaluates the constant expression. + * Returns NULL on failure rather than throwing an error. */ Expr * evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod, - Oid result_collation) + Oid result_collation, Node *escontext) { EState *estate; ExprState *exprstate; @@ -5766,7 +5849,7 @@ evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod, * Prepare expr for execution. (Note: we can't use ExecPrepareExpr * because it'd result in recursively invoking eval_const_expressions.) */ - exprstate = ExecInitExpr(expr, NULL); + exprstate = ExecInitExprWithContext(expr, NULL, escontext); /* * And evaluate it. @@ -5786,6 +5869,13 @@ evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod, /* Get back to outer memory context */ MemoryContextSwitchTo(oldcontext); + if (SOFT_ERROR_OCCURRED(exprstate->escontext)) + { + FreeExecutorState(estate); + + return NULL; + } + /* * Must copy result out of sub-context used by expression eval. * diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a218935f582..21b6acb3b86 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -158,6 +158,8 @@ static void updateRawStmtEnd(RawStmt *rs, int end_location); static Node *makeColumnRef(char *colname, List *indirection, int location, core_yyscan_t yyscanner); static Node *makeTypeCast(Node *arg, TypeName *typename, int location); +static Node *makeTypeCastOpt(Node *arg, TypeName *typename, + Node *raw_default, int location); static Node *makeStringConstCast(char *str, int location, TypeName *typename); static Node *makeIntConst(int val, int location); static Node *makeFloatConst(char *str, int location); @@ -16880,6 +16882,10 @@ func_expr_common_subexpr: } | CAST '(' a_expr AS Typename ')' { $$ = makeTypeCast($3, $5, @1); } + | CAST '(' a_expr AS Typename ERROR_P ON CONVERSION_P ERROR_P ')' + { $$ = makeTypeCast($3, $5, @1); } + | CAST '(' a_expr AS Typename DEFAULT a_expr ON CONVERSION_P ERROR_P ')' + { $$ = makeTypeCastOpt($3, $5, $7, @1); } | EXTRACT '(' extract_list ')' { $$ = (Node *) makeFuncCall(SystemFuncName("extract"), @@ -20043,10 +20049,25 @@ makeTypeCast(Node *arg, TypeName *typename, int location) n->arg = arg; n->typeName = typename; + n->defexpr = NULL; n->location = location; return (Node *) n; } +static Node * +makeTypeCastOpt(Node *arg, TypeName *typename, + Node *defexpr, int location) +{ + TypeCast *n = makeNode(TypeCast); + + n->arg = arg; + n->typeName = typename; + n->defexpr = defexpr; + n->location = location; + + return (Node *) n; +} + static Node * makeStringConstCast(char *str, int location, TypeName *typename) { diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index acb933392de..face030d44c 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -600,6 +600,13 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) break; + case EXPR_KIND_TYPECAST_DEFAULT: + if (isAgg) + err = _("aggregate functions are not allowed in CAST DEFAULT expressions"); + else + err = _("grouping operations are not allowed in CAST DEFAULT expressions"); + break; + /* * There is intentionally no default: case here, so that the * compiler will warn if we add a new ParseExprKind without @@ -1045,6 +1052,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_FOR_PORTION: err = _("window functions are not allowed in FOR PORTION OF expressions"); break; + case EXPR_KIND_TYPECAST_DEFAULT: + err = _("window functions are not allowed in CAST DEFAULT expressions"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c index 3b92ae2a920..3f012ebaafd 100644 --- a/src/backend/parser/parse_coerce.c +++ b/src/backend/parser/parse_coerce.c @@ -37,14 +37,17 @@ static Node *coerce_type_typmod(Node *node, Oid targetTypeId, int32 targetTypMod, CoercionContext ccontext, CoercionForm cformat, int location, - bool hideInputCoercion); + bool hideInputCoercion, + Node *escontext); static void hide_coercion_node(Node *node); -static Node *build_coercion_expression(Node *node, +static Node *build_coercion_expression(ParseState *pstate, + Node *node, CoercionPathType pathtype, Oid funcId, Oid targetTypeId, int32 targetTypMod, CoercionContext ccontext, CoercionForm cformat, - int location); + int location, + Node *escontext); static Node *coerce_record_to_complex(ParseState *pstate, Node *node, Oid targetTypeId, CoercionContext ccontext, @@ -81,6 +84,28 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, CoercionContext ccontext, CoercionForm cformat, int location) +{ + return coerce_to_target_type_extended(pstate, expr, exprtype, + targettype, targettypmod, + ccontext, + cformat, + location, + NULL); +} + +/* + * escontext: If non-NULL, safely coerces 'expr' to the target type + * without raising an error. Returns NULL if the coercion fails. + * + * See 'coerce_to_target_type' above for details on other parameters. + */ +Node * +coerce_to_target_type_extended(ParseState *pstate, Node *expr, Oid exprtype, + Oid targettype, int32 targettypmod, + CoercionContext ccontext, + CoercionForm cformat, + int location, + Node *escontext) { Node *result; Node *origexpr; @@ -102,9 +127,15 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, while (expr && IsA(expr, CollateExpr)) expr = (Node *) ((CollateExpr *) expr)->arg; - result = coerce_type(pstate, expr, exprtype, - targettype, targettypmod, - ccontext, cformat, location); + result = coerce_type_extended(pstate, expr, exprtype, + targettype, targettypmod, + ccontext, + cformat, + location, + escontext); + + if (SOFT_ERROR_OCCURRED(escontext)) + return NULL; /* * If the target is a fixed-length type, it may need a length coercion as @@ -114,7 +145,11 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, result = coerce_type_typmod(result, targettype, targettypmod, ccontext, cformat, location, - (result != expr && !IsA(result, Const))); + (result != expr && !IsA(result, Const)), + escontext); + + if (result == NULL) /* shouldn't happen */ + elog(ERROR, "failed to coerce type modifier for type %u", targettype); if (expr != origexpr && type_is_collatable(targettype)) { @@ -158,6 +193,18 @@ Node * coerce_type(ParseState *pstate, Node *node, Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, CoercionContext ccontext, CoercionForm cformat, int location) +{ + return coerce_type_extended(pstate, node, + inputTypeId, targetTypeId, targetTypeMod, + ccontext, cformat, location, + NULL); +} + +Node * +coerce_type_extended(ParseState *pstate, Node *node, + Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, + CoercionContext ccontext, CoercionForm cformat, int location, + Node *escontext) { Node *result; CoercionPathType pathtype; @@ -256,6 +303,7 @@ coerce_type(ParseState *pstate, Node *node, int32 inputTypeMod; Type baseType; ParseCallbackState pcbstate; + char *string = NULL; /* * If the target type is a domain, we want to call its base type's @@ -309,13 +357,21 @@ coerce_type(ParseState *pstate, Node *node, * as CSTRING. */ if (!con->constisnull) - newcon->constvalue = stringTypeDatum(baseType, - DatumGetCString(con->constvalue), - inputTypeMod); - else - newcon->constvalue = stringTypeDatum(baseType, - NULL, - inputTypeMod); + string = DatumGetCString(con->constvalue); + + if (!stringTypeDatumSafe(baseType, + string, + inputTypeMod, + escontext, + &newcon->constvalue)) + { + /* UNKNOWN Const cannot coerce to targetType, exit now */ + cancel_parser_errposition_callback(&pcbstate); + + ReleaseSysCache(baseType); + + return NULL; + } /* * If it's a varlena value, force it to be in non-expanded @@ -364,7 +420,8 @@ coerce_type(ParseState *pstate, Node *node, baseTypeId, baseTypeMod, targetTypeId, ccontext, cformat, location, - false); + false, + escontext); ReleaseSysCache(baseType); @@ -397,9 +454,11 @@ coerce_type(ParseState *pstate, Node *node, */ CollateExpr *coll = (CollateExpr *) node; - result = coerce_type(pstate, (Node *) coll->arg, - inputTypeId, targetTypeId, targetTypeMod, - ccontext, cformat, location); + result = coerce_type_extended(pstate, (Node *) coll->arg, + inputTypeId, targetTypeId, targetTypeMod, + ccontext, cformat, location, + escontext); + if (type_is_collatable(targetTypeId)) { CollateExpr *newcoll = makeNode(CollateExpr); @@ -430,9 +489,10 @@ coerce_type(ParseState *pstate, Node *node, * and we need to extract the correct typmod to use from the * domain's typtypmod. */ - result = build_coercion_expression(node, pathtype, funcId, + result = build_coercion_expression(pstate, node, pathtype, funcId, baseTypeId, baseTypeMod, - ccontext, cformat, location); + ccontext, cformat, location, + escontext); /* * If domain, coerce to the domain type and relabel with domain @@ -442,7 +502,8 @@ coerce_type(ParseState *pstate, Node *node, result = coerce_to_domain(result, baseTypeId, baseTypeMod, targetTypeId, ccontext, cformat, location, - true); + true, + escontext); } else { @@ -458,7 +519,9 @@ coerce_type(ParseState *pstate, Node *node, result = coerce_to_domain(node, baseTypeId, baseTypeMod, targetTypeId, ccontext, cformat, location, - false); + false, + escontext); + if (result == node) { /* @@ -478,6 +541,14 @@ coerce_type(ParseState *pstate, Node *node, } return result; } + if (escontext != NULL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot cast type %s to %s with DEFAULT expression in CAST ... ON CONVERSION ERROR", + format_type_be(inputTypeId), + format_type_be(targetTypeId)), + errdetail("Explicit cast is defined but definition is not error safe."), + parser_errposition(pstate, exprLocation(node))); if (inputTypeId == RECORDOID && ISCOMPLEX(targetTypeId)) { @@ -675,7 +746,8 @@ can_coerce_type(int nargs, const Oid *input_typeids, const Oid *target_typeids, Node * coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod, Oid typeId, CoercionContext ccontext, CoercionForm cformat, int location, - bool hideInputCoercion) + bool hideInputCoercion, + Node *escontext) { CoerceToDomain *result; @@ -705,7 +777,10 @@ coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod, Oid typeId, */ arg = coerce_type_typmod(arg, baseTypeId, baseTypeMod, ccontext, COERCE_IMPLICIT_CAST, location, - false); + false, + escontext); + if (arg == NULL) /* shouldn't happen */ + elog(ERROR, "failed to coerce type modifier as expected"); /* * Now build the domain coercion node. This represents run-time checking @@ -752,7 +827,8 @@ static Node * coerce_type_typmod(Node *node, Oid targetTypeId, int32 targetTypMod, CoercionContext ccontext, CoercionForm cformat, int location, - bool hideInputCoercion) + bool hideInputCoercion, + Node *escontext) { CoercionPathType pathtype; Oid funcId; @@ -777,9 +853,10 @@ coerce_type_typmod(Node *node, Oid targetTypeId, int32 targetTypMod, if (pathtype != COERCION_PATH_NONE) { - node = build_coercion_expression(node, pathtype, funcId, + node = build_coercion_expression(NULL, node, pathtype, funcId, targetTypeId, targetTypMod, - ccontext, cformat, location); + ccontext, cformat, location, + escontext); } else { @@ -835,12 +912,14 @@ hide_coercion_node(Node *node) * since there is no difference in terms of the calling convention. */ static Node * -build_coercion_expression(Node *node, +build_coercion_expression(ParseState *pstate, + Node *node, CoercionPathType pathtype, Oid funcId, Oid targetTypeId, int32 targetTypMod, CoercionContext ccontext, CoercionForm cformat, - int location) + int location, + Node *escontext) { int nargs = 0; @@ -869,6 +948,15 @@ build_coercion_expression(Node *node, Assert(nargs < 2 || procstruct->proargtypes.values[1] == INT4OID); Assert(nargs < 3 || procstruct->proargtypes.values[2] == BOOLOID); + if (escontext != NULL && !procstruct->proerrorsafe) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot cast type %s to %s with DEFAULT expression in CAST ... ON CONVERSION ERROR", + format_type_be(exprType(node)), + format_type_be(targetTypeId)), + errdetail("Explicit cast is defined but definition is not error safe."), + parser_errposition(pstate, exprLocation(node))); + ReleaseSysCache(tp); } @@ -950,14 +1038,15 @@ build_coercion_expression(Node *node, targetElementType = get_element_type(targetTypeId); Assert(OidIsValid(targetElementType)); - elemexpr = coerce_to_target_type(NULL, - (Node *) ctest, - ctest->typeId, - targetElementType, - targetTypMod, - ccontext, - cformat, - location); + elemexpr = coerce_to_target_type_extended(pstate, + (Node *) ctest, + ctest->typeId, + targetElementType, + targetTypMod, + ccontext, + cformat, + location, + escontext); if (elemexpr == NULL) /* shouldn't happen */ elog(ERROR, "failed to coerce array element type as expected"); @@ -1139,7 +1228,8 @@ coerce_record_to_complex(ParseState *pstate, Node *node, baseTypeId, baseTypeMod, targetTypeId, ccontext, cformat, location, - false); + false, + NULL); } return (Node *) rowexpr; @@ -1294,7 +1384,8 @@ coerce_null_to_domain(Oid typid, int32 typmod, Oid collation, COERCION_IMPLICIT, COERCE_IMPLICIT_CAST, -1, - false); + false, + NULL); return result; } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index e6ea34a7809..a3275194fee 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -17,6 +17,7 @@ #include "access/htup_details.h" #include "catalog/pg_aggregate.h" +#include "catalog/pg_cast.h" #include "catalog/pg_type.h" #include "miscadmin.h" #include "nodes/makefuncs.h" @@ -38,6 +39,7 @@ #include "utils/date.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" +#include "utils/syscache.h" #include "utils/timestamp.h" #include "utils/typcache.h" #include "utils/xml.h" @@ -62,7 +64,8 @@ static Node *transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref); static Node *transformCaseExpr(ParseState *pstate, CaseExpr *c); static Node *transformSubLink(ParseState *pstate, SubLink *sublink); static Node *transformArrayExpr(ParseState *pstate, A_ArrayExpr *a, - Oid array_type, Oid element_type, int32 typmod); + Oid array_type, Oid element_type, int32 typmod, + Node *escontext); static Node *transformRowExpr(ParseState *pstate, RowExpr *r, bool allowDefault); static Node *transformCoalesceExpr(ParseState *pstate, CoalesceExpr *c); static Node *transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m); @@ -166,7 +169,7 @@ transformExprRecurse(ParseState *pstate, Node *expr) case T_A_ArrayExpr: result = transformArrayExpr(pstate, (A_ArrayExpr *) expr, - InvalidOid, InvalidOid, -1); + InvalidOid, InvalidOid, -1, NULL); break; case T_TypeCast: @@ -579,6 +582,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_GENERATED_COLUMN: case EXPR_KIND_CYCLE_MARK: case EXPR_KIND_PROPGRAPH_PROPERTY: + case EXPR_KIND_TYPECAST_DEFAULT: /* okay */ break; @@ -1892,6 +1896,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_FOR_PORTION: err = _("cannot use subquery in FOR PORTION OF expression"); break; + case EXPR_KIND_TYPECAST_DEFAULT: + err = _("cannot use subquery in CAST DEFAULT expression"); + break; /* * There is intentionally no default: case here, so that the @@ -2042,10 +2049,15 @@ transformSubLink(ParseState *pstate, SubLink *sublink) * If the caller specifies the target type, the resulting array will * be of exactly that type. Otherwise we try to infer a common type * for the elements using select_common_type(). + * + * escontext (ErrorSaveContext *) is typically NULL, except during parse analysis for + * CAST(... DEFAULT ... ON CONVERSION ERROR). When provided, + * the error_occurred field should be initialized to false. It is set + * to true if coercing array elements fails. */ static Node * transformArrayExpr(ParseState *pstate, A_ArrayExpr *a, - Oid array_type, Oid element_type, int32 typmod) + Oid array_type, Oid element_type, int32 typmod, Node *escontext) { ArrayExpr *newa = makeNode(ArrayExpr); List *newelems = NIL; @@ -2076,9 +2088,10 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a, (A_ArrayExpr *) e, array_type, element_type, - typmod); + typmod, + escontext); /* we certainly have an array here */ - Assert(array_type == InvalidOid || array_type == exprType(newe)); + Assert(escontext || array_type == InvalidOid || array_type == exprType(newe)); newa->multidims = true; } else @@ -2119,6 +2132,8 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a, } else { + Assert(escontext == NULL); + /* Can't handle an empty array without a target type */ if (newelems == NIL) ereport(ERROR, @@ -2169,24 +2184,55 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a, foreach(element, newelems) { Node *e = (Node *) lfirst(element); - Node *newe; + Node *newe = NULL; if (coerce_hard) { - newe = coerce_to_target_type(pstate, e, - exprType(e), - coerce_type, - typmod, - COERCION_EXPLICIT, - COERCE_EXPLICIT_CAST, - -1); - if (newe == NULL) - ereport(ERROR, - (errcode(ERRCODE_CANNOT_COERCE), - errmsg("cannot cast type %s to %s", - format_type_be(exprType(e)), - format_type_be(coerce_type)), - parser_errposition(pstate, exprLocation(e)))); + /* + * Cannot coerce, just append the transformed element expression + * to the list. + */ + if (SOFT_ERROR_OCCURRED(escontext)) + newe = e; + else + { + Node *ecopy = NULL; + + if (escontext) + ecopy = copyObject(e); + + newe = coerce_to_target_type_extended(pstate, e, + exprType(e), + coerce_type, + typmod, + COERCION_EXPLICIT, + COERCE_EXPLICIT_CAST, + -1, + escontext); + if (newe == NULL) + { + /* + * Cannot coerce. Raise an error or append the transformed + * element to the list. Also set error_occurred to true + * (in case coerce_to_target_type_extended didn't) so + * caller will notice array coercion failed. + */ + if (!escontext) + ereport(ERROR, + (errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast type %s to %s", + format_type_be(exprType(e)), + format_type_be(coerce_type)), + parser_errposition(pstate, exprLocation(e)))); + else + { + ErrorSaveContext *context = castNode(ErrorSaveContext, escontext); + + newe = ecopy; + context->error_occurred = true; + } + } + } } else newe = coerce_to_common_type(pstate, e, @@ -2735,17 +2781,65 @@ transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem, static Node * transformTypeCast(ParseState *pstate, TypeCast *tc) { - Node *result; + SafeTypeCastExpr *stc; + Node *castexpr = NULL; + Node *defexpr = NULL; Node *arg = tc->arg; + Node *source; Node *expr; Oid inputType; Oid targetType; int32 targetTypmod; int location; + ErrorSaveContext *escontext = NULL; + Oid targetTypecoll; /* Look up the type name first */ typenameTypeIdAndMod(pstate, tc->typeName, &targetType, &targetTypmod); + targetTypecoll = get_typcollation(targetType); + + /* looking at DEFAULT expression */ + if (tc->defexpr) + { + escontext = makeNode(ErrorSaveContext); + escontext->type = T_ErrorSaveContext; + escontext->error_occurred = false; + escontext->details_wanted = false; + + defexpr = transformExpr(pstate, tc->defexpr, EXPR_KIND_TYPECAST_DEFAULT); + + + defexpr = coerce_to_target_type(pstate, defexpr, exprType(defexpr), + targetType, targetTypmod, + COERCION_EXPLICIT, + COERCE_EXPLICIT_CAST, + exprLocation(defexpr)); + + assign_expr_collations(pstate, defexpr); + + if (defexpr == NULL) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot coerce %s expression to type %s", + "CAST DEFAULT", + format_type_be(targetType)), + parser_coercion_errposition(pstate, exprLocation(tc->defexpr), defexpr)); + + /* + * The collation of DEFAULT expression must match the collation of the + * target type. + */ + if (targetTypecoll != exprCollation(defexpr)) + ereport(ERROR, + errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("collation of CAST DEFAULT expression conflicts with target type collation"), + errdetail("\"%s\" versus \"%s\"", + get_collation_name(exprCollation(defexpr)), + get_collation_name(targetTypecoll)), + parser_errposition(pstate, exprLocation(defexpr))); + } + /* * If the subject of the typecast is an ARRAY[] construct and the target * type is an array type, we invoke transformArrayExpr() directly so that @@ -2774,7 +2868,8 @@ transformTypeCast(ParseState *pstate, TypeCast *tc) (A_ArrayExpr *) arg, targetBaseType, elementType, - targetBaseTypmod); + targetBaseTypmod, + (Node *) escontext); } else expr = transformExprRecurse(pstate, arg); @@ -2795,20 +2890,84 @@ transformTypeCast(ParseState *pstate, TypeCast *tc) if (location < 0) location = tc->typeName->location; - result = coerce_to_target_type(pstate, expr, inputType, - targetType, targetTypmod, - COERCION_EXPLICIT, - COERCE_EXPLICIT_CAST, - location); - if (result == NULL) - ereport(ERROR, - (errcode(ERRCODE_CANNOT_COERCE), - errmsg("cannot cast type %s to %s", - format_type_be(inputType), - format_type_be(targetType)), - parser_coercion_errposition(pstate, location, expr))); - - return result; + /* + * coerce_to_target_type_extended may modify the source expression, so we + * create a copy beforehand, source expression is used in deparsing. + */ + source = defexpr ? copyObject(expr) : expr; + + if (!SOFT_ERROR_OCCURRED(escontext)) + { + castexpr = coerce_to_target_type_extended(pstate, expr, inputType, + targetType, targetTypmod, + COERCION_EXPLICIT, + COERCE_EXPLICIT_CAST, + location, + (Node *) escontext); + + /* + * No DEFAULT expression, exit now or error out in case of coercion + * failure + */ + if (!defexpr && castexpr) + return castexpr; + + if (!defexpr && !castexpr) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast type %s to %s", + format_type_be(inputType), + format_type_be(targetType)), + parser_coercion_errposition(pstate, location, expr)); + } + + /* + * The source expression may contain an UNKNOWN Const, which isn't ideal, + * especially for deparsing purposes. Transform the source expression to + * avoid this; see also get_rule_expr. + */ + { + Node *origexpr = source; + bool stripped_collate; + + while (source && IsA(source, CollateExpr)) + source = (Node *) ((CollateExpr *) source)->arg; + + stripped_collate = (source != origexpr); + + if (exprType(source) == UNKNOWNOID && IsA(source, Const)) + source = coerce_type(pstate, + source, + UNKNOWNOID, + TEXTOID, + -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + exprLocation(source)); + + if (stripped_collate) + { + /* Reinstall top CollateExpr */ + CollateExpr *coll = (CollateExpr *) origexpr; + CollateExpr *newcoll = makeNode(CollateExpr); + + newcoll->arg = (Expr *) source; + newcoll->collOid = coll->collOid; + newcoll->location = coll->location; + source = (Node *) newcoll; + } + } + + stc = makeNode(SafeTypeCastExpr); + stc->source = (Expr *) source; + stc->castexpr = (Expr *) castexpr; + stc->defexpr = (Expr *) defexpr; + stc->resulttype = targetType; + stc->resulttypmod = targetTypmod; + stc->resultcollid = targetTypecoll; + stc->location = location; + + return (Node *) stc; } /* @@ -3255,6 +3414,8 @@ ParseExprKindName(ParseExprKind exprKind) return "property definition expression"; case EXPR_KIND_FOR_PORTION: return "FOR PORTION OF"; + case EXPR_KIND_TYPECAST_DEFAULT: + return "CAST DEFAULT"; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index a9b6be7203b..ce55d34a77b 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2793,6 +2793,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_FOR_PORTION: err = _("set-returning functions are not allowed in FOR PORTION OF expressions"); break; + case EXPR_KIND_TYPECAST_DEFAULT: + err = _("set-returning functions are not allowed in CAST DEFAULT expressions"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 0ea10f8e882..2c943ef4f49 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -845,7 +845,8 @@ transformAssignmentIndirection(ParseState *pstate, COERCION_IMPLICIT, COERCE_IMPLICIT_CAST, location, - false); + false, + NULL); return (Node *) fstore; } diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c index bb7eccde9fd..bbf39dac619 100644 --- a/src/backend/parser/parse_type.c +++ b/src/backend/parser/parse_type.c @@ -660,6 +660,28 @@ stringTypeDatum(Type tp, char *string, int32 atttypmod) return OidInputFunctionCall(typinput, string, typioparam, atttypmod); } +/* + * Error-safe version of stringTypeDatum. + * + * Returns true if "string" is a valid representation of type "tp". + * On success, the internal Datum representation is written to "result". + */ +bool +stringTypeDatumSafe(Type tp, char *string, int32 atttypmod, + Node *escontext, Datum *result) +{ + FmgrInfo flinfo; + + Form_pg_type typform = (Form_pg_type) GETSTRUCT(tp); + Oid typinput = typform->typinput; + Oid typioparam = getTypeIOParam(tp); + + fmgr_info(typinput, &flinfo); + + return InputFunctionCallSafe(&flinfo, string, typioparam, atttypmod, + escontext, result); +} + /* * Given a typeid, return the type's typrelid (associated relation), if any. * Returns InvalidOid if type is not a composite type. diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 58ccc7b68f2..2da3af40314 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -680,6 +680,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) castnode = makeNode(TypeCast); castnode->typeName = SystemTypeName("regclass"); castnode->arg = (Node *) snamenode; + castnode->defexpr = NULL; castnode->location = -1; funccallnode = makeFuncCall(SystemFuncName("nextval"), list_make1(castnode), @@ -5192,7 +5193,7 @@ transformPartitionBoundValue(ParseState *pstate, Node *val, assign_expr_collations(pstate, value); value = (Node *) expression_planner((Expr *) value); value = (Node *) evaluate_expr((Expr *) value, colType, colTypmod, - partCollation); + partCollation, NULL); if (!IsA(value, Const)) elog(ERROR, "could not evaluate partition bound expression"); } diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index 13eed22dd6a..70523248f56 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -3294,6 +3294,15 @@ array_map(Datum arrayd, /* Apply the given expression to source element */ values[i] = ExecEvalExpr(exprstate, econtext, &nulls[i]); + /* Exit early if evaluation failed */ + if (SOFT_ERROR_OCCURRED(exprstate->escontext)) + { + pfree(values); + pfree(nulls); + + return (Datum) 0; + } + if (nulls[i]) hasnulls = true; else diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 4150e6488dd..9c3c0e296b4 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11119,6 +11119,31 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_SafeTypeCastExpr: + { + SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node); + + /* + * Cannot deparse castexpr directly, becuase transformTypeCast + * may have already constant-folded the cast expression into a + * constant. Instead, use "source" and "defexpr" to + * reconstruct the clause. + */ + appendStringInfoString(buf, "CAST("); + get_rule_expr((Node *) stcexpr->source, context, showimplicit); + + appendStringInfo(buf, " AS %s ", + format_type_with_typemod(stcexpr->resulttype, + stcexpr->resulttypmod)); + + appendStringInfoString(buf, "DEFAULT "); + + get_rule_expr((Node *) stcexpr->defexpr, context, showimplicit); + + appendStringInfoString(buf, " ON CONVERSION ERROR)"); + } + break; + case T_JsonExpr: { JsonExpr *jexpr = (JsonExpr *) node; diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index c61b3d624d5..faf6976b040 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -265,6 +265,7 @@ typedef enum ExprEvalOp EEOP_XMLEXPR, EEOP_JSON_CONSTRUCTOR, EEOP_IS_JSON, + EEOP_SAFETYPE_CAST, EEOP_JSONEXPR_PATH, EEOP_JSONEXPR_COERCION, EEOP_JSONEXPR_COERCION_FINISH, @@ -754,6 +755,12 @@ typedef struct ExprEvalStep JsonIsPredicate *pred; /* original expression node */ } is_json; + /* for EEOP_SAFETYPE_CAST */ + struct + { + struct SafeTypeCastState *stcstate; + } stcexpr; + /* for EEOP_JSONEXPR_PATH */ struct { diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 1798e6027d4..c690b230b45 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -332,6 +332,7 @@ ExecProcNode(PlanState *node) * prototypes from functions in execExpr.c */ extern ExprState *ExecInitExpr(Expr *node, PlanState *parent); +extern ExprState *ExecInitExprWithContext(Expr *node, PlanState *parent, Node *escontext); extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params); extern ExprState *ExecInitQual(List *qual, PlanState *parent); extern ExprState *ExecInitCheck(List *qual, PlanState *parent); @@ -380,6 +381,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList, TupleTableSlot *slot, PlanState *parent); extern ExprState *ExecPrepareExpr(Expr *node, EState *estate); +extern ExprState *ExecPrepareExprWithContext(Expr *node, EState *estate, Node *escontext); extern ExprState *ExecPrepareQual(List *qual, EState *estate); extern ExprState *ExecPrepareCheck(List *qual, EState *estate); extern List *ExecPrepareExprList(List *nodes, EState *estate); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index e64fd8c7ea3..41c29f17e1e 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1096,6 +1096,27 @@ typedef struct DomainConstraintState ExprState *check_exprstate; /* check_expr's eval state, or NULL */ } DomainConstraintState; +typedef struct SafeTypeCastState +{ + SafeTypeCastExpr *stcexpr; + + /* + * The jump target used to skip all remaining steps when the default + * expression evaluation is bypassed. + */ + int jump_end; + + /* + * Error-safe context for type coercion evaluation. A pointer to this is + * passed to ExecInitExprRec() during type coercion expression + * initialization (see ExecInitSafeTypeCastExpr). + * + * At runtime, this is reset before each evaluation of EEOP_SAFETYPE_CAST. + */ + ErrorSaveContext escontext; + +} SafeTypeCastState; + /* * State for JsonExpr evaluation, too big to inline. * diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 77f69f94607..34b21f73060 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -402,6 +402,7 @@ typedef struct TypeCast NodeTag type; Node *arg; /* the expression being casted */ TypeName *typeName; /* the target type */ + Node *defexpr; /* DEFAULT expression */ ParseLoc location; /* token location, or -1 if unknown */ } TypeCast; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index cacef7d4151..e70fc635d76 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -760,6 +760,44 @@ typedef enum CoercionForm COERCE_SQL_SYNTAX, /* display with SQL-mandated special syntax */ } CoercionForm; +/* + * SafeTypeCastExpr - + * Transformed representation of + * CAST(expr AS typename DEFAULT expr ON CONVERSION ERROR) + */ +typedef struct SafeTypeCastExpr +{ + Expr xpr; + + /* + * The transformed source expression. + * + * Cases like ``CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR)`` where + * the castexpr evaluates to NULL, we need this field to reconstruct the + * original query. + */ + Expr *source; + + /* + * transformed cast expression, NULL means cannot coerce to target type. + * + * For query jumbling, ignore this node because castexpr expression may + * contain the "source" expression. Including it would cause the same + * expression to be jumbled twice. + */ + Expr *castexpr pg_node_attr(query_jumble_ignore); + /* Fall-back to the default expression if cast evaluation fails */ + Expr *defexpr; + /* target type Oid */ + Oid resulttype; + /* target type modifier */ + int32 resulttypmod; + /* OID of collation, or InvalidOid if none */ + Oid resultcollid; + /* token location, or -1 if unknown */ + ParseLoc location; +} SafeTypeCastExpr; + /* * FuncExpr - expression node for a function call */ diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h index cb6241e2bdd..fb81ed6e551 100644 --- a/src/include/optimizer/optimizer.h +++ b/src/include/optimizer/optimizer.h @@ -151,7 +151,7 @@ extern void convert_saop_to_hashed_saop(Node *node); extern Node *estimate_expression_value(PlannerInfo *root, Node *node); extern Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod, - Oid result_collation); + Oid result_collation, Node *escontext); extern bool var_is_nonnullable(PlannerInfo *root, Var *var, NotNullSource source); diff --git a/src/include/parser/parse_coerce.h b/src/include/parser/parse_coerce.h index aabacd49b65..12644f146bf 100644 --- a/src/include/parser/parse_coerce.h +++ b/src/include/parser/parse_coerce.h @@ -43,15 +43,29 @@ extern Node *coerce_to_target_type(ParseState *pstate, CoercionContext ccontext, CoercionForm cformat, int location); +extern Node *coerce_to_target_type_extended(ParseState *pstate, + Node *expr, + Oid exprtype, + Oid targettype, + int32 targettypmod, + CoercionContext ccontext, + CoercionForm cformat, + int location, + Node *escontext); extern bool can_coerce_type(int nargs, const Oid *input_typeids, const Oid *target_typeids, CoercionContext ccontext); extern Node *coerce_type(ParseState *pstate, Node *node, Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, CoercionContext ccontext, CoercionForm cformat, int location); +extern Node *coerce_type_extended(ParseState *pstate, Node *node, + Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, + CoercionContext ccontext, CoercionForm cformat, int location, + Node *escontext); extern Node *coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod, Oid typeId, CoercionContext ccontext, CoercionForm cformat, int location, - bool hideInputCoercion); + bool hideInputCoercion, + Node *escontext); extern Node *coerce_to_boolean(ParseState *pstate, Node *node, const char *constructName); diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index f7f4ba6c2a8..b23404bbf67 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -84,6 +84,8 @@ typedef enum ParseExprKind EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */ EXPR_KIND_CYCLE_MARK, /* cycle mark value */ EXPR_KIND_PROPGRAPH_PROPERTY, /* derived property expression */ + EXPR_KIND_TYPECAST_DEFAULT, /* default expression in CAST(expr AS type + * DEFAULT defexor ON CONVERSION ERROR) */ } ParseExprKind; diff --git a/src/include/parser/parse_type.h b/src/include/parser/parse_type.h index a335807b0b0..e303a1073c1 100644 --- a/src/include/parser/parse_type.h +++ b/src/include/parser/parse_type.h @@ -47,6 +47,8 @@ extern char *typeTypeName(Type t); extern Oid typeTypeRelid(Type typ); extern Oid typeTypeCollation(Type typ); extern Datum stringTypeDatum(Type tp, char *string, int32 atttypmod); +extern bool stringTypeDatumSafe(Type tp, char *string, int32 atttypmod, + Node *escontext, Datum *result); extern Oid typeidTypeRelid(Oid type_id); extern Oid typeOrDomainTypeRelid(Oid type_id); diff --git a/src/test/regress/expected/cast.out b/src/test/regress/expected/cast.out new file mode 100644 index 00000000000..d9d659c884c --- /dev/null +++ b/src/test/regress/expected/cast.out @@ -0,0 +1,1212 @@ +SET extra_float_digits = 0; +SET lc_monetary TO "C"; +-- CAST DEFAULT ON CONVERSION ERROR +SELECT CAST(B'01' AS date DEFAULT NULL ON CONVERSION ERROR); + date +------ + +(1 row) + +SELECT CAST(BIT'01' AS date DEFAULT NULL ON CONVERSION ERROR); + date +------ + +(1 row) + +SELECT CAST(TRUE AS date DEFAULT NULL ON CONVERSION ERROR); + date +------ + +(1 row) + +SELECT CAST(1.1 AS date DEFAULT NULL ON CONVERSION ERROR); + date +------ + +(1 row) + +SELECT CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR); + date +------ + +(1 row) + +SELECT CAST(1111 AS "char" DEFAULT 'A' ON CONVERSION ERROR); + char +------ + A +(1 row) + +SELECT CAST('def'::text AS integer DEFAULT NULL ON CONVERSION ERROR); + int4 +------ + +(1 row) + +SELECT CAST('2'::jsonb AS text DEFAULT 'fallback' ON CONVERSION ERROR); + text +------ + 2 +(1 row) + +SELECT CAST('1' COLLATE "C" || 'h' AS text DEFAULT 'fallback' ON CONVERSION ERROR); + text +------ + 1h +(1 row) + +SELECT CAST(concat('1' collate "C", 'h') AS int DEFAULT NULL ON CONVERSION ERROR); + concat +-------- + +(1 row) + +SELECT CAST(65536 AS int2 DEFAULT NULL ON CONVERSION ERROR); + int2 +------ + +(1 row) + +SELECT CAST(int8(65536) AS int2 DEFAULT NULL ON CONVERSION ERROR); + int8 +------ + +(1 row) + +SELECT CAST(12.111 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale; + to_numeric_scale +------------------ + 12.1 +(1 row) + +-- source expression is a unknown const +VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR)); -- error +ERROR: invalid input syntax for type integer: "error" +LINE 1: VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR)); + ^ +VALUES (CAST('error' AS integer DEFAULT NULL ON CONVERSION ERROR)); + column1 +--------- + +(1 row) + +VALUES (CAST('error' AS integer DEFAULT 42 ON CONVERSION ERROR)); + column1 +--------- + 42 +(1 row) + +SELECT CAST('a' as int DEFAULT 18 ON CONVERSION ERROR); + int4 +------ + 18 +(1 row) + +SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); -- error +ERROR: aggregate functions are not allowed in CAST DEFAULT expressions +LINE 1: SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); + ^ +SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); -- error +ERROR: window functions are not allowed in CAST DEFAULT expressions +LINE 1: SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION E... + ^ +SELECT CAST('a' as int DEFAULT (SELECT NULL) ON CONVERSION ERROR); -- error +ERROR: cannot use subquery in CAST DEFAULT expression +LINE 1: SELECT CAST('a' as int DEFAULT (SELECT NULL) ON CONVERSION E... + ^ +SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR); -- error +ERROR: invalid input syntax for type integer: "b" +LINE 1: SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR); + ^ +SELECT CAST('a'::int as int DEFAULT NULL ON CONVERSION ERROR); -- error +ERROR: invalid input syntax for type integer: "a" +LINE 1: SELECT CAST('a'::int as int DEFAULT NULL ON CONVERSION ERROR... + ^ +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + '70000'::int2::int4 AS int DEFAULT -1 ON CONVERSION ERROR); -- error +ERROR: value "70000" is out of range for type smallint +LINE 1: ...ST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + '70000'::i... + ^ +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + 70000::int2::int4 AS int DEFAULT -1 ON CONVERSION ERROR); -- ok + int4 +------ + -1 +(1 row) + +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + 70000::int2::int4 AS int DEFAULT 111111111111111 ON CONVERSION ERROR); -- error +ERROR: integer out of range +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) AS int DEFAULT 70000::int2::int4 ON CONVERSION ERROR); -- error +ERROR: smallint out of range +-- the default expression’s collation should match target type collation +VALUES (CAST('error' AS text DEFAULT '1' COLLATE "C" ON CONVERSION ERROR)); +ERROR: collation of CAST DEFAULT expression conflicts with target type collation +LINE 1: VALUES (CAST('error' AS text DEFAULT '1' COLLATE "C" ON CONV... + ^ +DETAIL: "C" versus "default" +VALUES (CAST('error' AS int2vector DEFAULT '1 3' ON CONVERSION ERROR)); + column1 +--------- + 1 3 +(1 row) + +VALUES (CAST('error' AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR)); + column1 +--------- + {"1 3"} +(1 row) + +-- source expression contain subquery +SELECT CAST((SELECT b FROM generate_series(1, 1), (VALUES('H')) s(b)) + AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR); + b +--------- + {"1 3"} +(1 row) + +SELECT CAST((SELECT ARRAY((SELECT b FROM generate_series(1, 2) g, (VALUES('H')) s(b)))) + AS INT[] + DEFAULT NULL ON CONVERSION ERROR); + array +------- + +(1 row) + +CREATE FUNCTION ret_int8() RETURNS BIGINT AS $$ BEGIN RETURN 2147483648; END; $$ LANGUAGE plpgsql IMMUTABLE; +SELECT CAST('a' as int DEFAULT ret_int8() ON CONVERSION ERROR); -- error +ERROR: integer out of range +SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERROR); -- error +ERROR: cannot coerce CAST DEFAULT expression to type date +LINE 1: SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERR... + ^ +-- DEFAULT expression cannot be set-returning +CREATE FUNCTION ret_setint() RETURNS SETOF integer AS +$$ +BEGIN RETURN QUERY EXECUTE 'select 1 union all select 1'; END; +$$ +LANGUAGE plpgsql IMMUTABLE; +CREATE TEMP TABLE tcast0(a INT, b int); +INSERT INTO tcast0 VALUES(1,2), (65536,3); +SET jit_above_cost = 0; +SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0; + a +--- + 1 + +(2 rows) + +SET jit_above_cost TO DEFAULT; +SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0; + a +--- + 1 + +(2 rows) + +SELECT CAST(ROW(1,2) AS tcast0 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0 as t; +ERROR: cannot cast type record to tcast0 with DEFAULT expression in CAST ... ON CONVERSION ERROR +LINE 1: SELECT CAST(ROW(1,2) AS tcast0 DEFAULT NULL ON CONVERSION ER... + ^ +DETAIL: Explicit cast is defined but definition is not error safe. +DROP TABLE tcast0; +CREATE TABLE tcast(a text[], b int GENERATED BY DEFAULT AS IDENTITY); +INSERT INTO tcast VALUES ('{12}'), ('{1,a, b}'), ('{{1,2}, {c,d}}'), ('{13}'); +SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) FROM tcast; -- error +ERROR: set-returning functions are not allowed in CAST DEFAULT expressions +LINE 1: SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ER... + ^ +SELECT CAST(t AS text[] DEFAULT '{21,22, ' || b || '}' ON CONVERSION ERROR) FROM tcast as t; + t +----------- + {21,22,1} + {21,22,2} + {21,22,3} + {21,22,4} +(4 rows) + +SELECT CAST(t.a AS int[] DEFAULT '{21,22}'::int[] || b ON CONVERSION ERROR) FROM tcast as t; + a +----------- + {12} + {21,22,2} + {21,22,3} + {13} +(4 rows) + +CREATE DOMAIN ddomain AS numeric CHECK ((1/ (VALUE - 1)) > 0); +SELECT CAST ('1' AS ddomain DEFAULT NULL ON CONVERSION ERROR); +ERROR: division by zero +SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR); +ERROR: division by zero +CREATE VIEW vddomain AS SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR); +\sv vddomain +CREATE OR REPLACE VIEW public.vddomain AS + SELECT CAST(1 AS ddomain DEFAULT NULL::numeric::ddomain ON CONVERSION ERROR) AS ddomain +DROP VIEW vddomain; +-- Test nested type casts; all sub-expressions will be evaluated in an error-safe manner too. +CREATE DOMAIN d_nota AS text CHECK (VALUE <> 'a'); +CREATE TABLE tcast1(a text, b text[], c int, d int8, e int8[]); +INSERT INTO tcast1(a, b, d, e) values('a', '{a}', 2147483648, '{2147483648}'); +SELECT CAST (CAST(a AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + a +--- + +(1 row) + +SELECT CAST (CAST('a' AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- error +ERROR: invalid input syntax for type integer: "a" +LINE 1: SELECT CAST (CAST('a' AS int) AS text DEFAULT NULL ON CONVER... + ^ +SELECT CAST (CAST('a'::text AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- ok + text +------ + +(1 row) + +SELECT CAST (CAST(a AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + a +--- + +(1 row) + +SELECT CAST (CAST('a' AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + text +------ + +(1 row) + +SELECT CAST (CAST(d AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + d +--- + +(1 row) + +SELECT CAST (CAST('2147483648'::int8 AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + text +------ + +(1 row) + +SELECT CAST (CAST('{a}'::text[] AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + text +------ + +(1 row) + +SELECT CAST (CAST(b AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + b +--- + +(1 row) + +SELECT CAST (CAST(b AS d_nota[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + b +--- + +(1 row) + +SELECT CAST (CAST(e AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + e +--- + +(1 row) + +-- test nested ArrayCoerceExpr constant folding +SELECT CAST('{a}'::text[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + int4 +------ + +(1 row) + +SELECT CAST(CAST('{a}'::text[] AS date[]) AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + int4 +------ + +(1 row) + +SELECT CAST(('{' || sum(1 + 2) || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub; + int4 +------ + {6} +(1 row) + +SELECT CAST(('{' || sum(1 + 2) OVER () || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub; + int4 +------ + {6} + {6} +(2 rows) + +-- test with user-defined type, domain, array over domain, domain over array +CREATE DOMAIN d_int42 as int check (value = 42) NOT NULL; +CREATE DOMAIN d_int42arr as d_int42[]; +CREATE DOMAIN d_int42arr1 as d_int42arr; +CREATE DOMAIN d_int8 as int8 check (value > 0); +CREATE DOMAIN d_int8arr as d_int8[]; +CREATE DOMAIN d_char3_not_null as char(3) NOT NULL; +CREATE DOMAIN d_varchar as varchar(3) NOT NULL; +CREATE DOMAIN d_int_arr as int[] check (value = '{41, 43}') NOT NULL; +CREATE TYPE comp_domain_with_typmod AS (a d_char3_not_null, b int); +CREATE TYPE comp2 AS (a d_varchar); +CREATE DOMAIN d_numeric as numeric(4,4); +SELECT CAST(1.0::float4 AS d_numeric DEFAULT NULL ON CONVERSION ERROR); + d_numeric +----------- + +(1 row) + +SELECT CAST('{1.0}' AS numeric(4,1)[] DEFAULT NULL ON CONVERSION ERROR); + numeric +--------- + {1.0} +(1 row) + +SELECT CAST('{0.1, 1.0}' AS d_numeric[] DEFAULT NULL ON CONVERSION ERROR); + d_numeric +----------- + +(1 row) + +SELECT CAST(11 AS d_int42 DEFAULT 41 ON CONVERSION ERROR); -- error +ERROR: value for domain d_int42 violates check constraint "d_int42_check" +SELECT CAST(NULL AS d_int42 DEFAULT NULL ON CONVERSION ERROR); -- error +ERROR: domain d_int42 does not allow null values +SELECT CAST(11 AS d_int42 DEFAULT 42 ON CONVERSION ERROR); + d_int42 +--------- + 42 +(1 row) + +SELECT CAST(NULL AS d_int42 DEFAULT 42 ON CONVERSION ERROR); + d_int42 +--------- + 42 +(1 row) + +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8 DEFAULT '1' ON CONVERSION ERROR); + array +------- + 1 +(1 row) + +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8[] DEFAULT '{1}' ON CONVERSION ERROR); + array +------- + {42} +(1 row) + +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr DEFAULT '{1}' ON CONVERSION ERROR); + array +------- + {42} +(1 row) + +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr[] DEFAULT NULL ON CONVERSION ERROR); + array +------- + +(1 row) + +SELECT CAST(ARRAY[42,41] AS d_int42[] DEFAULT '{42, 42}' ON CONVERSION ERROR); + array +--------- + {42,42} +(1 row) + +SELECT CAST(ARRAY[42,41] AS d_int_arr DEFAULT '{41, 43}' ON CONVERSION ERROR); + array +--------- + {41,43} +(1 row) + +SELECT CAST(ARRAY[42, 41]::d_int42arr1 AS d_int8arr DEFAULT '{1,2,3}' ON CONVERSION ERROR); + array +--------- + {1,2,3} +(1 row) + +SELECT CAST(ARRAY[42, 41] AS d_int42arr1 DEFAULT '{42, 42}' ON CONVERSION ERROR); + array +--------- + {42,42} +(1 row) + +SELECT CAST(CAST(ARRAY[CAST(ARRAY[42, 42] AS d_int42arr)] AS d_int42arr1[]) + AS d_int8arr[] + DEFAULT NULL ON CONVERSION ERROR); + array +------------- + {"{42,42}"} +(1 row) + +SELECT CAST('(NULL)' AS comp2 DEFAULT '(1232)' ON CONVERSION ERROR); -- error +ERROR: value too long for type character varying(3) +LINE 1: SELECT CAST('(NULL)' AS comp2 DEFAULT '(1232)' ON CONVERSION... + ^ +SELECT CAST('(NULL)' AS comp2 DEFAULT '(123)' ON CONVERSION ERROR); + comp2 +------- + (123) +(1 row) + +SELECT CAST('(,42)' AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR); + comp_domain_with_typmod +------------------------- + +(1 row) + +SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR); + comp_domain_with_typmod +------------------------- + ("1 ",2) +(1 row) + +SELECT CAST(A AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR) +FROM (VALUES('(NULL,42)'::text), ('(,42)')) sub(a); + a +----------- + ("1 ",2) + ("1 ",2) +(2 rows) + +SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)' ON CONVERSION ERROR); -- error +ERROR: value too long for type character(3) +LINE 1: ...ST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)'... + ^ +SELECT CAST(ROW(NULL,42) AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR); -- error +ERROR: cannot cast type record to comp_domain_with_typmod with DEFAULT expression in CAST ... ON CONVERSION ERROR +LINE 1: SELECT CAST(ROW(NULL,42) AS comp_domain_with_typmod DEFAULT ... + ^ +DETAIL: Explicit cast is defined but definition is not error safe. +-- test array coerce +SELECT CAST(array['a'::text] AS int[] DEFAULT NULL ON CONVERSION ERROR); + array +------- + +(1 row) + +SELECT CAST(array['a'] AS int[] DEFAULT ARRAY[1] ON CONVERSION ERROR); + array +------- + {1} +(1 row) + +SELECT CAST(array[11.12] AS date[] DEFAULT NULL ON CONVERSION ERROR); + array +------- + +(1 row) + +SELECT CAST(array[11111111111111111] AS int[] DEFAULT NULL ON CONVERSION ERROR); + array +------- + +(1 row) + +SELECT CAST(array[['abc'],[456]] AS int[] DEFAULT NULL ON CONVERSION ERROR); + array +------- + +(1 row) + +SELECT CAST('{123,abc,456}' AS int[] DEFAULT '{-789}' ON CONVERSION ERROR); + int4 +-------- + {-789} +(1 row) + +SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON CONVERSION ERROR); + int4 +--------- + {-1011} +(1 row) + +SELECT CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR); + array +------- + {1,2} +(1 row) + +SELECT CAST(ARRAY[['1', '2'], ['three', 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR); + array +--------- + {21,22} +(1 row) + +-- error: `three'::int` will fail earlier +SELECT CAST(ARRAY[['1', '2'], ['three'::int, 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR); +ERROR: invalid input syntax for type integer: "three" +LINE 1: SELECT CAST(ARRAY[['1', '2'], ['three'::int, 'a']] AS int[] ... + ^ +SELECT CAST(ARRAY[1, 'three'::int] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR); +ERROR: invalid input syntax for type integer: "three" +LINE 1: SELECT CAST(ARRAY[1, 'three'::int] AS int[] DEFAULT '{21,22}... + ^ +-- safe cast with geometry data type +SELECT CAST('(1,2)'::point AS box DEFAULT NULL ON CONVERSION ERROR); + box +------------- + (1,2),(1,2) +(1 row) + +SELECT CAST('[(NaN,1),(NaN,infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR); + point +---------------- + (NaN,Infinity) +(1 row) + +SELECT CAST('[(1e+300,Infinity),(1e+300,Infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR); + point +------------------- + (1e+300,Infinity) +(1 row) + +SELECT CAST('[(1,2),(3,4)]'::path as polygon DEFAULT NULL ON CONVERSION ERROR); + polygon +--------- + +(1 row) + +SELECT CAST('(NaN,1.0,NaN,infinity)'::box AS point DEFAULT NULL ON CONVERSION ERROR); + point +---------------- + (NaN,Infinity) +(1 row) + +SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS lseg DEFAULT NULL ON CONVERSION ERROR); + lseg +--------------- + [(2,2),(0,0)] +(1 row) + +SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS polygon DEFAULT NULL ON CONVERSION ERROR); + polygon +--------------------------- + ((0,0),(0,2),(2,2),(2,0)) +(1 row) + +SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS path DEFAULT NULL ON CONVERSION ERROR); + path +------ + +(1 row) + +SELECT CAST('(2.0,infinity,NaN,infinity)'::box AS circle DEFAULT NULL ON CONVERSION ERROR); + circle +---------------------- + <(NaN,Infinity),NaN> +(1 row) + +SELECT CAST('(NaN,0.0),(2.0,4.0),(0.0,infinity)'::polygon AS point DEFAULT NULL ON CONVERSION ERROR); + point +---------------- + (NaN,Infinity) +(1 row) + +SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS path DEFAULT NULL ON CONVERSION ERROR); + path +--------------------- + ((2,0),(2,4),(0,0)) +(1 row) + +SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS box DEFAULT NULL ON CONVERSION ERROR); + box +------------- + (2,4),(0,0) +(1 row) + +SELECT CAST('(NaN,infinity),(2.0,4.0),(0.0,infinity)'::polygon AS circle DEFAULT NULL ON CONVERSION ERROR); + circle +---------------------- + <(NaN,Infinity),NaN> +(1 row) + +SELECT CAST('<(5,1),3>'::circle AS point DEFAULT NULL ON CONVERSION ERROR); + point +------- + (5,1) +(1 row) + +SELECT CAST('<(3,5),0>'::circle as box DEFAULT NULL ON CONVERSION ERROR); + box +------------- + (3,5),(3,5) +(1 row) + +SELECT CAST('<(3,5),0>'::circle as polygon DEFAULT NULL ON CONVERSION ERROR); + polygon +--------- + +(1 row) + +-- safe cast with money data type +-- cast from money to other type is not error safe +SELECT CAST('123456789012345678'::numeric AS MONEY DEFAULT NULL ON CONVERSION ERROR); + money +------- + +(1 row) + +SELECT CAST('123456789012345678'::int8 AS MONEY DEFAULT NULL ON CONVERSION ERROR); + money +------- + +(1 row) + +SELECT CAST('2147483647'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR); + money +------------------- + $2,147,483,647.00 +(1 row) + +SELECT CAST('-2147483648'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR); + money +-------------------- + -$2,147,483,648.00 +(1 row) + +SELECT CAST('-0'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR); + money +------- + $0.00 +(1 row) + +SELECT CAST(NULL::money AS numeric DEFAULT NULL ON CONVERSION ERROR); +ERROR: cannot cast type money to numeric with DEFAULT expression in CAST ... ON CONVERSION ERROR +LINE 1: SELECT CAST(NULL::money AS numeric DEFAULT NULL ON CONVERSIO... + ^ +DETAIL: Explicit cast is defined but definition is not error safe. +SELECT CAST(NULL::money[] AS numeric[] DEFAULT NULL ON CONVERSION ERROR); +ERROR: cannot cast type money to numeric with DEFAULT expression in CAST ... ON CONVERSION ERROR +DETAIL: Explicit cast is defined but definition is not error safe. +-- safe cast from boolean type to other data types +SELECT CAST(true AS numeric DEFAULT NULL ON CONVERSION ERROR); + numeric +--------- + +(1 row) + +SELECT CAST(false AS int DEFAULT NULL ON CONVERSION ERROR); + int4 +------ + 0 +(1 row) + +SELECT CAST(false AS text DEFAULT NULL ON CONVERSION ERROR); + text +------- + false +(1 row) + +-- safe cast from bytea type to other data types +SELECT CAST ('\x112233445566778899'::bytea AS int8 DEFAULT 19 ON CONVERSION ERROR); + int8 +------ + 19 +(1 row) + +SELECT CAST('\x123456789A'::bytea AS int4 DEFAULT 20 ON CONVERSION ERROR); + int4 +------ + 20 +(1 row) + +SELECT CAST('\x123456'::bytea AS int2 DEFAULT 21 ON CONVERSION ERROR); + int2 +------ + 21 +(1 row) + +SELECT CAST('\x1234567890abcdef'::bytea AS uuid DEFAULT NULL ON CONVERSION ERROR); + uuid +------ + +(1 row) + +-- safe cast from uuid type to other data types +SELECT CAST('5b35380a-7143-4912-9b55-f322699c6770'::uuid AS bytea DEFAULT NULL ON CONVERSION ERROR); + bytea +------------------------------------ + \x5b35380a714349129b55f322699c6770 +(1 row) + +-- safe cast related to network address data type +SELECT CAST('192.168.1.x' as inet DEFAULT NULL ON CONVERSION ERROR); + inet +------ + +(1 row) + +SELECT CAST('192.168.1.2/30' as cidr DEFAULT NULL ON CONVERSION ERROR); + cidr +------ + +(1 row) + +SELECT CAST('22:00:5c:08:55:08:01:02'::macaddr8 as macaddr DEFAULT NULL ON CONVERSION ERROR); + macaddr +--------- + +(1 row) + +-- safe cast from bit type to other data types +SELECT CAST('111111111100001'::bit(100) AS INT DEFAULT 22 ON CONVERSION ERROR); + int4 +------ + 22 +(1 row) + +SELECT CAST ('111111111100001'::bit(100) AS INT8 DEFAULT 23 ON CONVERSION ERROR); + int8 +------ + 23 +(1 row) + +-- safe cast related to oid data type +SELECT CAST(12345678901::bigint AS oid DEFAULT NULL ON CONVERSION ERROR); + oid +----- + +(1 row) + +SELECT CAST(12345678901::bigint AS regproc DEFAULT NULL ON CONVERSION ERROR); + regproc +--------- + +(1 row) + +SELECT CAST(12345678901::bigint AS regprocedure DEFAULT NULL ON CONVERSION ERROR); + regprocedure +-------------- + +(1 row) + +SELECT CAST(12345678901::bigint AS regclass DEFAULT NULL ON CONVERSION ERROR); + regclass +---------- + +(1 row) + +SELECT CAST(1234567890::bigint AS oid DEFAULT NULL ON CONVERSION ERROR); + oid +------------ + 1234567890 +(1 row) + +SELECT CAST(1234567890::bigint AS regrole DEFAULT NULL ON CONVERSION ERROR); + regrole +------------ + 1234567890 +(1 row) + +SELECT CAST(1234567890::bigint AS regnamespace DEFAULT NULL ON CONVERSION ERROR); + regnamespace +-------------- + 1234567890 +(1 row) + +SELECT CAST(1234567890::bigint AS regdatabase DEFAULT NULL ON CONVERSION ERROR); + regdatabase +------------- + 1234567890 +(1 row) + +-- safe cast from text type to other data types +SELECT CAST('pg_temp_1.foo'::text AS regclass DEFAULT NULL ON CONVERSION ERROR); + regclass +---------- + +(1 row) + +SELECT CAST('a.b.c.d'::text as regclass DEFAULT NULL ON CONVERSION ERROR); + regclass +---------- + +(1 row) + +CREATE TABLE test_safecast0(col0 text, col1 jsonb); +INSERT INTO test_safecast0 VALUES ('oneoneone1, max=>1) ON CONVERSION ERROR))); -- error +ERROR: functions in index expression must be marked IMMUTABLE +LINE 1: CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as ... + ^ +CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as xid DEFAULT NULL ON CONVERSION ERROR))); -- error +ERROR: data type xid has no default operator class for access method "btree" +HINT: You must specify an operator class for the index or define a default operator class for the data type. +CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as int DEFAULT NULL ON CONVERSION ERROR))); -- ok +SELECT pg_get_indexdef('cast_error_idx'::regclass); + pg_get_indexdef +-------------------------------------------------------------------------------------------------------------------------------------- + CREATE INDEX cast_error_idx ON public.test_safecast0 USING btree ((CAST(col1 AS integer DEFAULT NULL::integer ON CONVERSION ERROR))) +(1 row) + +-- safe cast betweeen range data types +SELECT CAST('[1,2]'::int4range AS int4multirange DEFAULT NULL ON CONVERSION ERROR); + int4multirange +---------------- + {[1,3)} +(1 row) + +SELECT CAST('[1,2]'::int8range AS int8multirange DEFAULT NULL ON CONVERSION ERROR); + int8multirange +---------------- + {[1,3)} +(1 row) + +SELECT CAST('[1,2]'::numrange AS nummultirange DEFAULT NULL ON CONVERSION ERROR); + nummultirange +--------------- + {[1,2]} +(1 row) + +SELECT CAST('[-infinity,infinity]'::daterange AS datemultirange DEFAULT NULL ON CONVERSION ERROR); + datemultirange +------------------------ + {[-infinity,infinity]} +(1 row) + +SELECT CAST('[-infinity,infinity]'::tsrange AS tsmultirange DEFAULT NULL ON CONVERSION ERROR); + tsmultirange +------------------------ + {[-infinity,infinity]} +(1 row) + +SELECT CAST('[-infinity,infinity]'::tstzrange AS tstzmultirange DEFAULT NULL ON CONVERSION ERROR); + tstzmultirange +------------------------ + {[-infinity,infinity]} +(1 row) + +-- safe cast betweeen numeric data types +CREATE TABLE test_safecast1( + col1 float4, col2 float8, col3 numeric, col4 numeric[], + col5 int2 default 32767, + col6 int4 default 32768, + col7 int8 default 4294967296); +INSERT INTO test_safecast1 VALUES('11.1234', '11.1234', '9223372036854775808', '{11.1234}'::numeric[]); +INSERT INTO test_safecast1 VALUES('inf', 'inf', 'inf', '{11.1234, 12, inf, NaN}'::numeric[]); +INSERT INTO test_safecast1 VALUES('-inf', '-inf', '-inf', '{11.1234, 12, -inf, NaN}'::numeric[]); +INSERT INTO test_safecast1 VALUES('NaN', 'NaN', 'NaN', '{11.1234, 12, -inf, NaN}'::numeric[]); +SELECT col5 as int2, + CAST(col5 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col5 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col5 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col5 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col5 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col5 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col5 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col5 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + int2 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale +-------+---------+---------+--------+---------+-----------+-----------+------------+------------------ + 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0 + 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0 + 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0 + 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767 | 32767.0 +(4 rows) + +SELECT col6 as int4, + CAST(col6 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col6 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col6 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col6 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col6 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col6 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col6 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col6 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + int4 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale +-------+---------+---------+--------+---------+-----------+-----------+------------+------------------ + 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0 + 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0 + 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0 + 32768 | | 32768 | 32768 | 32768 | 32768 | 32768 | 32768 | 32768.0 +(4 rows) + +SELECT col7 as int8, + CAST(col7 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col7 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col7 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col7 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col7 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col7 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col7 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col7 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + int8 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale +------------+---------+---------+--------+------------+-------------+------------+------------+------------------ + 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 | + 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 | + 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 | + 4294967296 | | | | 4294967296 | 4.29497e+09 | 4294967296 | 4294967296 | +(4 rows) + +SELECT col3 as numeric, + CAST(col3 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col3 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col3 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col3 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col3 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col3 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col3 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col3 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + numeric | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale +---------------------+---------+---------+--------+---------+-------------+----------------------+---------------------+------------------ + 9223372036854775808 | | | | | 9.22337e+18 | 9.22337203685478e+18 | 9223372036854775808 | + Infinity | | | | | Infinity | Infinity | Infinity | + -Infinity | | | | | -Infinity | -Infinity | -Infinity | + NaN | | | | | NaN | NaN | NaN | NaN +(4 rows) + +SELECT col4 as num_arr, + CAST(col4 AS int2[] DEFAULT NULL ON CONVERSION ERROR) as int2arr, + CAST(col4 AS int4[] DEFAULT NULL ON CONVERSION ERROR) as int4arr, + CAST(col4 as int8[] DEFAULT NULL ON CONVERSION ERROR) as int8arr, + CAST(col4 as float4[] DEFAULT NULL ON CONVERSION ERROR) as f4arr, + CAST(col4 as float8[] DEFAULT NULL ON CONVERSION ERROR) as f8arr, + CAST(col4 as numeric(10,1)[] DEFAULT NULL ON CONVERSION ERROR) as numarr +FROM test_safecast1; + num_arr | int2arr | int4arr | int8arr | f4arr | f8arr | numarr +----------------------------+---------+---------+---------+----------------------------+----------------------------+-------- + {11.1234} | {11} | {11} | {11} | {11.1234} | {11.1234} | {11.1} + {11.1234,12,Infinity,NaN} | | | | {11.1234,12,Infinity,NaN} | {11.1234,12,Infinity,NaN} | + {11.1234,12,-Infinity,NaN} | | | | {11.1234,12,-Infinity,NaN} | {11.1234,12,-Infinity,NaN} | + {11.1234,12,-Infinity,NaN} | | | | {11.1234,12,-Infinity,NaN} | {11.1234,12,-Infinity,NaN} | +(4 rows) + +SELECT col1 as float4, + CAST(col1 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col1 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col1 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col1 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col1 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col1 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col1 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col1 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + float4 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale +-----------+---------+---------+--------+---------+-----------+------------------+------------+------------------ + 11.1234 | 11 | 11 | | 11 | 11.1234 | 11.1233997344971 | 11.1234 | 11.1 + Infinity | | | | | Infinity | Infinity | Infinity | + -Infinity | | | | | -Infinity | -Infinity | -Infinity | + NaN | | | | | NaN | NaN | NaN | NaN +(4 rows) + +SELECT col2 as float8, + CAST(col2 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col2 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col2 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col2 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col2 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col2 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col2 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col2 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + float8 | to_int2 | to_int4 | to_oid | to_int8 | to_float4 | to_float8 | to_numeric | to_numeric_scale +-----------+---------+---------+--------+---------+-----------+-----------+------------+------------------ + 11.1234 | 11 | 11 | | 11 | 11.1234 | 11.1234 | 11.1234 | 11.1 + Infinity | | | | | Infinity | Infinity | Infinity | + -Infinity | | | | | -Infinity | -Infinity | -Infinity | + NaN | | | | | NaN | NaN | NaN | NaN +(4 rows) + +-- safe type cast for type date/timestamp/timestamptz/interval +CREATE TABLE test_safecast2( + col0 date, col1 timestamp, col2 timestamptz, + col3 interval, col4 time, col5 timetz); +INSERT INTO test_safecast2 VALUES +('-infinity', '-infinity', '-infinity', '-infinity', + '2003-03-07 15:36:39 America/New_York', '2003-07-07 15:36:39 America/New_York'), +('-infinity', 'infinity', 'infinity', 'infinity', '11:59:59.99 PM', '11:59:59.99 PM PDT'); +SELECT col0 as date, + CAST(col0 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col0 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col0 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col0 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col0 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale +FROM test_safecast2; + date | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale +-----------+----------------+-----------+----------+-----------+-------------------- + -infinity | -infinity | -infinity | | | -infinity + -infinity | -infinity | -infinity | | | -infinity +(2 rows) + +SELECT col1 as timestamp, + CAST(col1 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col1 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col1 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col1 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col1 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale +FROM test_safecast2; + timestamp | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale +-----------+----------------+-----------+----------+-----------+-------------------- + -infinity | -infinity | -infinity | | | -infinity + infinity | infinity | infinity | | | infinity +(2 rows) + +SELECT col2 as timestamptz, + CAST(col2 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col2 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col2 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col2 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col2 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale +FROM test_safecast2; + timestamptz | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale +-------------+----------------+-----------+----------+-----------+-------------------- + -infinity | -infinity | -infinity | | | -infinity + infinity | infinity | infinity | | | infinity +(2 rows) + +SELECT col3 as interval, + CAST(col3 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col3 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col3 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col3 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col3 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale, + CAST(col3 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale +FROM test_safecast2; + interval | to_timestamptz | to_date | to_times | to_timetz | to_timestamp_scale | to_interval_scale +-----------+----------------+---------+----------+-----------+--------------------+------------------- + -infinity | | | | | | -infinity + infinity | | | | | | infinity +(2 rows) + +SELECT col4 as time, + CAST(col4 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col4 AS timetz DEFAULT NULL ON CONVERSION ERROR) IS NOT NULL as to_timetz, + CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval, + CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale +FROM test_safecast2; + time | to_times | to_timetz | to_interval | to_interval_scale +-------------+-------------+-----------+-------------------------------+------------------------------- + 15:36:39 | 15:36:39 | t | @ 15 hours 36 mins 39 secs | @ 15 hours 36 mins 39 secs + 23:59:59.99 | 23:59:59.99 | t | @ 23 hours 59 mins 59.99 secs | @ 23 hours 59 mins 59.99 secs +(2 rows) + +SELECT col5 as timetz, + CAST(col5 AS time DEFAULT NULL ON CONVERSION ERROR) as to_time, + CAST(col5 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_time_scale, + CAST(col5 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col5 AS timetz(6) DEFAULT NULL ON CONVERSION ERROR) as to_timetz_scale, + CAST(col5 AS interval DEFAULT NULL ON CONVERSION ERROR) as to_interval, + CAST(col5 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale +FROM test_safecast2; + timetz | to_time | to_time_scale | to_timetz | to_timetz_scale | to_interval | to_interval_scale +----------------+-------------+---------------+----------------+-----------------+-------------+------------------- + 15:36:39-04 | 15:36:39 | 15:36:39 | 15:36:39-04 | 15:36:39-04 | | + 23:59:59.99-07 | 23:59:59.99 | 23:59:59.99 | 23:59:59.99-07 | 23:59:59.99-07 | | +(2 rows) + +-- test deparse +SET datestyle TO ISO, YMD; +CREATE VIEW safecastview AS +SELECT + CAST('2022-01-01' COLLATE "C" COLLATE "POSIX" as DATE DEFAULT NULL ON CONVERSION ERROR) as cast, + CAST('1234' as char(3) DEFAULT -1111 ON CONVERSION ERROR) as cast0, + CAST(1 as date DEFAULT (('2025-Dec-06'::date + random(min=>1, max=>1::int))) ON CONVERSION ERROR) as cast1, + CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast2, + CAST(ARRAY[['1', '2'], ['three', 'a']] AS date[] DEFAULT NULL ON CONVERSION ERROR) as cast3, + CAST(ARRAY['three'] AS INT[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast4, + CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + 70000::int2::int4 AS int DEFAULT -1 ON CONVERSION ERROR) as cast5; +\sv safecastview +CREATE OR REPLACE VIEW public.safecastview AS + SELECT CAST('2022-01-01'::text COLLATE "POSIX" AS date DEFAULT NULL::date ON CONVERSION ERROR) AS "cast", + CAST('1234'::text AS character(3) DEFAULT '-1111'::integer::character(3) ON CONVERSION ERROR) AS cast0, + CAST(1 AS date DEFAULT '2025-12-06'::date + random(min => 1, max => 1) ON CONVERSION ERROR) AS cast1, + CAST(ARRAY[ARRAY[1], ARRAY['three'], ARRAY['a']] AS integer[] DEFAULT '{1,2}'::integer[] ON CONVERSION ERROR) AS cast2, + CAST(ARRAY[ARRAY['1', '2'], ARRAY['three', 'a']] AS date[] DEFAULT NULL::date[] ON CONVERSION ERROR) AS cast3, + CAST(ARRAY['three'] AS integer[] DEFAULT '{1,2}'::integer[] ON CONVERSION ERROR) AS cast4, + CAST((CAST(1 AS integer DEFAULT 0 ON CONVERSION ERROR)) + 70000::smallint::integer AS integer DEFAULT '-1'::integer ON CONVERSION ERROR) AS cast5 +SELECT * FROM safecastview; + cast | cast0 | cast1 | cast2 | cast3 | cast4 | cast5 +------------+-------+------------+-------+-------+-------+------- + 2022-01-01 | 123 | 2025-12-07 | {1,2} | | {1,2} | -1 +(1 row) + +CREATE VIEW safecastview1 AS +SELECT CAST(ARRAY[['1'], ['three'],['a']] AS d_int_arr + DEFAULT '{41,43}' ON CONVERSION ERROR) as cast1, + CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr + DEFAULT '{41,43}' ON CONVERSION ERROR) as cast2; +\sv safecastview1 +CREATE OR REPLACE VIEW public.safecastview1 AS + SELECT CAST(ARRAY[ARRAY[1], ARRAY['three'], ARRAY['a']] AS d_int_arr DEFAULT '{41,43}'::integer[]::d_int_arr ON CONVERSION ERROR) AS cast1, + CAST(ARRAY[ARRAY[1, 2, 1.1::integer], ARRAY['three', true, '01'::"bit"]] AS d_int_arr DEFAULT '{41,43}'::integer[]::d_int_arr ON CONVERSION ERROR) AS cast2 +SELECT * FROM safecastview1; + cast1 | cast2 +---------+--------- + {41,43} | {41,43} +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('1' || 'a' AS int DEFAULT 2 ON CONVERSION ERROR); + QUERY PLAN +----------------------------------------------------------------------------------- + Result + Output: CAST(('1'::text || 'a'::text) AS integer DEFAULT 2 ON CONVERSION ERROR) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR); + QUERY PLAN +-------------------------------------------------------------------- + Result + Output: CAST('a'::text AS integer DEFAULT 2 ON CONVERSION ERROR) +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr DEFAULT NULL ON CONVERSION ERROR); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------- + Result + Output: CAST(ARRAY[ARRAY[1, 2, (1.1)::integer], ARRAY['three', true, '01'::"bit"]] AS d_int_arr DEFAULT (NULL::integer[])::d_int_arr ON CONVERSION ERROR) +(2 rows) + +RESET datestyle; +DROP VIEW safecastview; +DROP VIEW safecastview1; +DROP TABLE test_safecast0; +DROP TABLE test_safecast1; +DROP TABLE test_safecast2; +DROP TABLE tcast; +DROP TABLE tcast1; +DROP FUNCTION ret_int8; +DROP FUNCTION ret_setint; +DROP TYPE comp_domain_with_typmod; +DROP TYPE comp2; +DROP DOMAIN d_nota; +DROP DOMAIN d_varchar; +DROP DOMAIN d_int8arr; +DROP DOMAIN d_int8; +DROP DOMAIN d_int42arr1; +DROP DOMAIN d_int42arr; +DROP DOMAIN d_int42; +DROP DOMAIN d_char3_not_null; +DROP DOMAIN d_numeric; +DROP DOMAIN ddomain; +RESET extra_float_digits; +RESET lc_monetary; diff --git a/src/test/regress/expected/create_cast.out b/src/test/regress/expected/create_cast.out index 0e69644bca2..27f079259d5 100644 --- a/src/test/regress/expected/create_cast.out +++ b/src/test/regress/expected/create_cast.out @@ -88,6 +88,18 @@ SELECT 1234::int4::casttesttype; -- Should work now bar1234 (1 row) +SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVERSION ERROR); -- error +ERROR: cannot cast type integer to casttesttype with DEFAULT expression in CAST ... ON CONVERSION ERROR +LINE 1: SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVE... + ^ +DETAIL: Explicit cast is defined but definition is not error safe. +ALTER FUNCTION bar_int4_text(int4) ERROR SAFE; +SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVERSION ERROR); -- ok + casttesttype +-------------- + bar1234 +(1 row) + -- check dependencies generated for that SELECT pg_describe_object(classid, objid, objsubid) as obj, pg_describe_object(refclassid, refobjid, refobjsubid) as objref, diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out index ad8ab294ff6..3af737c220a 100644 --- a/src/test/regress/expected/equivclass.out +++ b/src/test/regress/expected/equivclass.out @@ -95,6 +95,25 @@ create function int8alias1cmp(int8, int8alias1) returns int strict immutable language internal as 'btint8cmp'; alter operator family integer_ops using btree add function 1 int8alias1cmp (int8, int8alias1); +-- int8alias2 binary-coercible to int8, this should not fail +select cast('1'::int8 as int8alias2 default null on conversion error); + int8alias2 +------------ + 1 +(1 row) + +select cast('1'::int8alias2 as int8 default null on conversion error); + int8 +------ + 1 +(1 row) + +select cast('a' as int8alias2 default null on conversion error); + int8alias2 +------------ + +(1 row) + create table ec0 (ff int8 primary key, f1 int8, f2 int8); create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2); create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2); diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out index fb3e0ec41b2..f4fc64c1a92 100644 --- a/src/test/regress/expected/xml.out +++ b/src/test/regress/expected/xml.out @@ -24,6 +24,12 @@ SELECT * FROM xmltest; 2 | two (2 rows) +SELECT CAST('one', 'xml'); pg_input_is_valid diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 8fa0a6c47fb..b6ef7ccecb2 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -115,7 +115,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson # NB: temp.sql does reconnects which transiently uses 2 connections, # so keep this parallel group to at most 19 tests # ---------- -test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml +test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml cast # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/cast.sql b/src/test/regress/sql/cast.sql new file mode 100644 index 00000000000..fb0cd3b8fe1 --- /dev/null +++ b/src/test/regress/sql/cast.sql @@ -0,0 +1,458 @@ +SET extra_float_digits = 0; +SET lc_monetary TO "C"; + +-- CAST DEFAULT ON CONVERSION ERROR +SELECT CAST(B'01' AS date DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(BIT'01' AS date DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(TRUE AS date DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(1.1 AS date DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(1111 AS "char" DEFAULT 'A' ON CONVERSION ERROR); +SELECT CAST('def'::text AS integer DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('2'::jsonb AS text DEFAULT 'fallback' ON CONVERSION ERROR); +SELECT CAST('1' COLLATE "C" || 'h' AS text DEFAULT 'fallback' ON CONVERSION ERROR); +SELECT CAST(concat('1' collate "C", 'h') AS int DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(65536 AS int2 DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(int8(65536) AS int2 DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(12.111 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale; + +-- source expression is a unknown const +VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR)); -- error +VALUES (CAST('error' AS integer DEFAULT NULL ON CONVERSION ERROR)); +VALUES (CAST('error' AS integer DEFAULT 42 ON CONVERSION ERROR)); +SELECT CAST('a' as int DEFAULT 18 ON CONVERSION ERROR); +SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); -- error +SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); -- error +SELECT CAST('a' as int DEFAULT (SELECT NULL) ON CONVERSION ERROR); -- error +SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR); -- error +SELECT CAST('a'::int as int DEFAULT NULL ON CONVERSION ERROR); -- error + +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + '70000'::int2::int4 AS int DEFAULT -1 ON CONVERSION ERROR); -- error +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + 70000::int2::int4 AS int DEFAULT -1 ON CONVERSION ERROR); -- ok +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + 70000::int2::int4 AS int DEFAULT 111111111111111 ON CONVERSION ERROR); -- error +SELECT CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) AS int DEFAULT 70000::int2::int4 ON CONVERSION ERROR); -- error + +-- the default expression’s collation should match target type collation +VALUES (CAST('error' AS text DEFAULT '1' COLLATE "C" ON CONVERSION ERROR)); + +VALUES (CAST('error' AS int2vector DEFAULT '1 3' ON CONVERSION ERROR)); +VALUES (CAST('error' AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR)); + +-- source expression contain subquery +SELECT CAST((SELECT b FROM generate_series(1, 1), (VALUES('H')) s(b)) + AS int2vector[] DEFAULT '{1 3}' ON CONVERSION ERROR); +SELECT CAST((SELECT ARRAY((SELECT b FROM generate_series(1, 2) g, (VALUES('H')) s(b)))) + AS INT[] + DEFAULT NULL ON CONVERSION ERROR); + +CREATE FUNCTION ret_int8() RETURNS BIGINT AS $$ BEGIN RETURN 2147483648; END; $$ LANGUAGE plpgsql IMMUTABLE; +SELECT CAST('a' as int DEFAULT ret_int8() ON CONVERSION ERROR); -- error +SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERROR); -- error + +-- DEFAULT expression cannot be set-returning +CREATE FUNCTION ret_setint() RETURNS SETOF integer AS +$$ +BEGIN RETURN QUERY EXECUTE 'select 1 union all select 1'; END; +$$ +LANGUAGE plpgsql IMMUTABLE; + +CREATE TEMP TABLE tcast0(a INT, b int); +INSERT INTO tcast0 VALUES(1,2), (65536,3); +SET jit_above_cost = 0; +SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0; +SET jit_above_cost TO DEFAULT; +SELECT CAST(a AS int2 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0; +SELECT CAST(ROW(1,2) AS tcast0 DEFAULT NULL ON CONVERSION ERROR) FROM tcast0 as t; +DROP TABLE tcast0; + +CREATE TABLE tcast(a text[], b int GENERATED BY DEFAULT AS IDENTITY); +INSERT INTO tcast VALUES ('{12}'), ('{1,a, b}'), ('{{1,2}, {c,d}}'), ('{13}'); +SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) FROM tcast; -- error +SELECT CAST(t AS text[] DEFAULT '{21,22, ' || b || '}' ON CONVERSION ERROR) FROM tcast as t; +SELECT CAST(t.a AS int[] DEFAULT '{21,22}'::int[] || b ON CONVERSION ERROR) FROM tcast as t; + +CREATE DOMAIN ddomain AS numeric CHECK ((1/ (VALUE - 1)) > 0); +SELECT CAST ('1' AS ddomain DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR); +CREATE VIEW vddomain AS SELECT CAST (1 AS ddomain DEFAULT NULL ON CONVERSION ERROR); +\sv vddomain +DROP VIEW vddomain; + +-- Test nested type casts; all sub-expressions will be evaluated in an error-safe manner too. +CREATE DOMAIN d_nota AS text CHECK (VALUE <> 'a'); +CREATE TABLE tcast1(a text, b text[], c int, d int8, e int8[]); +INSERT INTO tcast1(a, b, d, e) values('a', '{a}', 2147483648, '{2147483648}'); +SELECT CAST (CAST(a AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST (CAST('a' AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- error +SELECT CAST (CAST('a'::text AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; -- ok +SELECT CAST (CAST(a AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST (CAST('a' AS d_nota) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + +SELECT CAST (CAST(d AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST (CAST('2147483648'::int8 AS int) AS text DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + +SELECT CAST (CAST('{a}'::text[] AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST (CAST(b AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST (CAST(b AS d_nota[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST (CAST(e AS int[]) AS text[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; + +-- test nested ArrayCoerceExpr constant folding +SELECT CAST('{a}'::text[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST(CAST('{a}'::text[] AS date[]) AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM tcast1; +SELECT CAST(('{' || sum(1 + 2) || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub; +SELECT CAST(('{' || sum(1 + 2) OVER () || '}')::int[] AS int[] DEFAULT NULL ON CONVERSION ERROR) FROM (VALUES (1), (2)) sub; + +-- test with user-defined type, domain, array over domain, domain over array +CREATE DOMAIN d_int42 as int check (value = 42) NOT NULL; +CREATE DOMAIN d_int42arr as d_int42[]; +CREATE DOMAIN d_int42arr1 as d_int42arr; +CREATE DOMAIN d_int8 as int8 check (value > 0); +CREATE DOMAIN d_int8arr as d_int8[]; +CREATE DOMAIN d_char3_not_null as char(3) NOT NULL; +CREATE DOMAIN d_varchar as varchar(3) NOT NULL; +CREATE DOMAIN d_int_arr as int[] check (value = '{41, 43}') NOT NULL; +CREATE TYPE comp_domain_with_typmod AS (a d_char3_not_null, b int); +CREATE TYPE comp2 AS (a d_varchar); +CREATE DOMAIN d_numeric as numeric(4,4); + +SELECT CAST(1.0::float4 AS d_numeric DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('{1.0}' AS numeric(4,1)[] DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('{0.1, 1.0}' AS d_numeric[] DEFAULT NULL ON CONVERSION ERROR); + +SELECT CAST(11 AS d_int42 DEFAULT 41 ON CONVERSION ERROR); -- error +SELECT CAST(NULL AS d_int42 DEFAULT NULL ON CONVERSION ERROR); -- error +SELECT CAST(11 AS d_int42 DEFAULT 42 ON CONVERSION ERROR); +SELECT CAST(NULL AS d_int42 DEFAULT 42 ON CONVERSION ERROR); + +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8 DEFAULT '1' ON CONVERSION ERROR); +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8[] DEFAULT '{1}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr DEFAULT '{1}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[42]::d_int42arr1 AS d_int8arr[] DEFAULT NULL ON CONVERSION ERROR); + +SELECT CAST(ARRAY[42,41] AS d_int42[] DEFAULT '{42, 42}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[42,41] AS d_int_arr DEFAULT '{41, 43}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[42, 41]::d_int42arr1 AS d_int8arr DEFAULT '{1,2,3}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[42, 41] AS d_int42arr1 DEFAULT '{42, 42}' ON CONVERSION ERROR); +SELECT CAST(CAST(ARRAY[CAST(ARRAY[42, 42] AS d_int42arr)] AS d_int42arr1[]) + AS d_int8arr[] + DEFAULT NULL ON CONVERSION ERROR); + +SELECT CAST('(NULL)' AS comp2 DEFAULT '(1232)' ON CONVERSION ERROR); -- error +SELECT CAST('(NULL)' AS comp2 DEFAULT '(123)' ON CONVERSION ERROR); +SELECT CAST('(,42)' AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR); +SELECT CAST(A AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR) +FROM (VALUES('(NULL,42)'::text), ('(,42)')) sub(a); +SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)' ON CONVERSION ERROR); -- error +SELECT CAST(ROW(NULL,42) AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR); -- error + +-- test array coerce +SELECT CAST(array['a'::text] AS int[] DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(array['a'] AS int[] DEFAULT ARRAY[1] ON CONVERSION ERROR); +SELECT CAST(array[11.12] AS date[] DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(array[11111111111111111] AS int[] DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(array[['abc'],[456]] AS int[] DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('{123,abc,456}' AS int[] DEFAULT '{-789}' ON CONVERSION ERROR); +SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[['1', '2'], ['three', 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR); +-- error: `three'::int` will fail earlier +SELECT CAST(ARRAY[['1', '2'], ['three'::int, 'a']] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR); +SELECT CAST(ARRAY[1, 'three'::int] AS int[] DEFAULT '{21,22}' ON CONVERSION ERROR); + +-- safe cast with geometry data type +SELECT CAST('(1,2)'::point AS box DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[(NaN,1),(NaN,infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[(1e+300,Infinity),(1e+300,Infinity)]'::lseg AS point DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[(1,2),(3,4)]'::path as polygon DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(NaN,1.0,NaN,infinity)'::box AS point DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS lseg DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS polygon DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(2.0,2.0,0.0,0.0)'::box AS path DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(2.0,infinity,NaN,infinity)'::box AS circle DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(NaN,0.0),(2.0,4.0),(0.0,infinity)'::polygon AS point DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS path DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(2.0,0.0),(2.0,4.0),(0.0,0.0)'::polygon AS box DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('(NaN,infinity),(2.0,4.0),(0.0,infinity)'::polygon AS circle DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('<(5,1),3>'::circle AS point DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('<(3,5),0>'::circle as box DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('<(3,5),0>'::circle as polygon DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast with money data type +-- cast from money to other type is not error safe +SELECT CAST('123456789012345678'::numeric AS MONEY DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('123456789012345678'::int8 AS MONEY DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('2147483647'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('-2147483648'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('-0'::int4 AS MONEY DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(NULL::money AS numeric DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(NULL::money[] AS numeric[] DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast from boolean type to other data types +SELECT CAST(true AS numeric DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(false AS int DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(false AS text DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast from bytea type to other data types +SELECT CAST ('\x112233445566778899'::bytea AS int8 DEFAULT 19 ON CONVERSION ERROR); +SELECT CAST('\x123456789A'::bytea AS int4 DEFAULT 20 ON CONVERSION ERROR); +SELECT CAST('\x123456'::bytea AS int2 DEFAULT 21 ON CONVERSION ERROR); +SELECT CAST('\x1234567890abcdef'::bytea AS uuid DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast from uuid type to other data types +SELECT CAST('5b35380a-7143-4912-9b55-f322699c6770'::uuid AS bytea DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast related to network address data type +SELECT CAST('192.168.1.x' as inet DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('192.168.1.2/30' as cidr DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('22:00:5c:08:55:08:01:02'::macaddr8 as macaddr DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast from bit type to other data types +SELECT CAST('111111111100001'::bit(100) AS INT DEFAULT 22 ON CONVERSION ERROR); +SELECT CAST ('111111111100001'::bit(100) AS INT8 DEFAULT 23 ON CONVERSION ERROR); + +-- safe cast related to oid data type +SELECT CAST(12345678901::bigint AS oid DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(12345678901::bigint AS regproc DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(12345678901::bigint AS regprocedure DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(12345678901::bigint AS regclass DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(1234567890::bigint AS oid DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(1234567890::bigint AS regrole DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(1234567890::bigint AS regnamespace DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST(1234567890::bigint AS regdatabase DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast from text type to other data types +SELECT CAST('pg_temp_1.foo'::text AS regclass DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('a.b.c.d'::text as regclass DEFAULT NULL ON CONVERSION ERROR); +CREATE TABLE test_safecast0(col0 text, col1 jsonb); +INSERT INTO test_safecast0 VALUES ('one1, max=>1) ON CONVERSION ERROR))); -- error +CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as xid DEFAULT NULL ON CONVERSION ERROR))); -- error +CREATE INDEX cast_error_idx ON test_safecast0((CAST(col1 as int DEFAULT NULL ON CONVERSION ERROR))); -- ok +SELECT pg_get_indexdef('cast_error_idx'::regclass); + +-- safe cast betweeen range data types +SELECT CAST('[1,2]'::int4range AS int4multirange DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[1,2]'::int8range AS int8multirange DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[1,2]'::numrange AS nummultirange DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[-infinity,infinity]'::daterange AS datemultirange DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[-infinity,infinity]'::tsrange AS tsmultirange DEFAULT NULL ON CONVERSION ERROR); +SELECT CAST('[-infinity,infinity]'::tstzrange AS tstzmultirange DEFAULT NULL ON CONVERSION ERROR); + +-- safe cast betweeen numeric data types +CREATE TABLE test_safecast1( + col1 float4, col2 float8, col3 numeric, col4 numeric[], + col5 int2 default 32767, + col6 int4 default 32768, + col7 int8 default 4294967296); +INSERT INTO test_safecast1 VALUES('11.1234', '11.1234', '9223372036854775808', '{11.1234}'::numeric[]); +INSERT INTO test_safecast1 VALUES('inf', 'inf', 'inf', '{11.1234, 12, inf, NaN}'::numeric[]); +INSERT INTO test_safecast1 VALUES('-inf', '-inf', '-inf', '{11.1234, 12, -inf, NaN}'::numeric[]); +INSERT INTO test_safecast1 VALUES('NaN', 'NaN', 'NaN', '{11.1234, 12, -inf, NaN}'::numeric[]); + +SELECT col5 as int2, + CAST(col5 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col5 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col5 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col5 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col5 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col5 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col5 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col5 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + +SELECT col6 as int4, + CAST(col6 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col6 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col6 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col6 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col6 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col6 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col6 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col6 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + +SELECT col7 as int8, + CAST(col7 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col7 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col7 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col7 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col7 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col7 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col7 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col7 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + +SELECT col3 as numeric, + CAST(col3 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col3 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col3 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col3 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col3 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col3 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col3 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col3 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + +SELECT col4 as num_arr, + CAST(col4 AS int2[] DEFAULT NULL ON CONVERSION ERROR) as int2arr, + CAST(col4 AS int4[] DEFAULT NULL ON CONVERSION ERROR) as int4arr, + CAST(col4 as int8[] DEFAULT NULL ON CONVERSION ERROR) as int8arr, + CAST(col4 as float4[] DEFAULT NULL ON CONVERSION ERROR) as f4arr, + CAST(col4 as float8[] DEFAULT NULL ON CONVERSION ERROR) as f8arr, + CAST(col4 as numeric(10,1)[] DEFAULT NULL ON CONVERSION ERROR) as numarr +FROM test_safecast1; + +SELECT col1 as float4, + CAST(col1 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col1 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col1 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col1 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col1 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col1 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col1 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col1 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + +SELECT col2 as float8, + CAST(col2 AS int2 DEFAULT NULL ON CONVERSION ERROR) as to_int2, + CAST(col2 AS int4 DEFAULT NULL ON CONVERSION ERROR) as to_int4, + CAST(col2 AS oid DEFAULT NULL ON CONVERSION ERROR) as to_oid, + CAST(col2 as int8 DEFAULT NULL ON CONVERSION ERROR) as to_int8, + CAST(col2 as float4 DEFAULT NULL ON CONVERSION ERROR) as to_float4, + CAST(col2 as float8 DEFAULT NULL ON CONVERSION ERROR) as to_float8, + CAST(col2 as numeric DEFAULT NULL ON CONVERSION ERROR) as to_numeric, + CAST(col2 as numeric(10,1) DEFAULT NULL ON CONVERSION ERROR) as to_numeric_scale +FROM test_safecast1; + +-- safe type cast for type date/timestamp/timestamptz/interval +CREATE TABLE test_safecast2( + col0 date, col1 timestamp, col2 timestamptz, + col3 interval, col4 time, col5 timetz); +INSERT INTO test_safecast2 VALUES +('-infinity', '-infinity', '-infinity', '-infinity', + '2003-03-07 15:36:39 America/New_York', '2003-07-07 15:36:39 America/New_York'), +('-infinity', 'infinity', 'infinity', 'infinity', '11:59:59.99 PM', '11:59:59.99 PM PDT'); + +SELECT col0 as date, + CAST(col0 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col0 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col0 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col0 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col0 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale +FROM test_safecast2; + +SELECT col1 as timestamp, + CAST(col1 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col1 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col1 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col1 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col1 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale +FROM test_safecast2; + +SELECT col2 as timestamptz, + CAST(col2 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col2 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col2 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col2 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col2 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale +FROM test_safecast2; + +SELECT col3 as interval, + CAST(col3 AS timestamptz DEFAULT NULL ON CONVERSION ERROR) as to_timestamptz, + CAST(col3 AS date DEFAULT NULL ON CONVERSION ERROR) as to_date, + CAST(col3 AS time DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col3 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col3 AS timestamp(2) DEFAULT NULL ON CONVERSION ERROR) as to_timestamp_scale, + CAST(col3 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale +FROM test_safecast2; + +SELECT col4 as time, + CAST(col4 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_times, + CAST(col4 AS timetz DEFAULT NULL ON CONVERSION ERROR) IS NOT NULL as to_timetz, + CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval, + CAST(col4 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale +FROM test_safecast2; + +SELECT col5 as timetz, + CAST(col5 AS time DEFAULT NULL ON CONVERSION ERROR) as to_time, + CAST(col5 AS time(2) DEFAULT NULL ON CONVERSION ERROR) as to_time_scale, + CAST(col5 AS timetz DEFAULT NULL ON CONVERSION ERROR) as to_timetz, + CAST(col5 AS timetz(6) DEFAULT NULL ON CONVERSION ERROR) as to_timetz_scale, + CAST(col5 AS interval DEFAULT NULL ON CONVERSION ERROR) as to_interval, + CAST(col5 AS interval(2) DEFAULT NULL ON CONVERSION ERROR) as to_interval_scale +FROM test_safecast2; + +-- test deparse +SET datestyle TO ISO, YMD; +CREATE VIEW safecastview AS +SELECT + CAST('2022-01-01' COLLATE "C" COLLATE "POSIX" as DATE DEFAULT NULL ON CONVERSION ERROR) as cast, + CAST('1234' as char(3) DEFAULT -1111 ON CONVERSION ERROR) as cast0, + CAST(1 as date DEFAULT (('2025-Dec-06'::date + random(min=>1, max=>1::int))) ON CONVERSION ERROR) as cast1, + CAST(ARRAY[['1'], ['three'],['a']] AS int[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast2, + CAST(ARRAY[['1', '2'], ['three', 'a']] AS date[] DEFAULT NULL ON CONVERSION ERROR) as cast3, + CAST(ARRAY['three'] AS INT[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast4, + CAST(CAST(1 AS int DEFAULT 0 ON CONVERSION ERROR) + 70000::int2::int4 AS int DEFAULT -1 ON CONVERSION ERROR) as cast5; +\sv safecastview +SELECT * FROM safecastview; + +CREATE VIEW safecastview1 AS +SELECT CAST(ARRAY[['1'], ['three'],['a']] AS d_int_arr + DEFAULT '{41,43}' ON CONVERSION ERROR) as cast1, + CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr + DEFAULT '{41,43}' ON CONVERSION ERROR) as cast2; +\sv safecastview1 +SELECT * FROM safecastview1; + +EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('1' || 'a' AS int DEFAULT 2 ON CONVERSION ERROR); +EXPLAIN (VERBOSE, COSTS OFF) SELECT CAST('a' AS int DEFAULT 2 ON CONVERSION ERROR); + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT CAST(ARRAY[['1', '2', 1.1], ['three', true, B'01']] AS d_int_arr DEFAULT NULL ON CONVERSION ERROR); + +RESET datestyle; + +DROP VIEW safecastview; +DROP VIEW safecastview1; +DROP TABLE test_safecast0; +DROP TABLE test_safecast1; +DROP TABLE test_safecast2; +DROP TABLE tcast; +DROP TABLE tcast1; +DROP FUNCTION ret_int8; +DROP FUNCTION ret_setint; +DROP TYPE comp_domain_with_typmod; +DROP TYPE comp2; +DROP DOMAIN d_nota; +DROP DOMAIN d_varchar; +DROP DOMAIN d_int8arr; +DROP DOMAIN d_int8; +DROP DOMAIN d_int42arr1; +DROP DOMAIN d_int42arr; +DROP DOMAIN d_int42; +DROP DOMAIN d_char3_not_null; +DROP DOMAIN d_numeric; +DROP DOMAIN ddomain; +RESET extra_float_digits; +RESET lc_monetary; diff --git a/src/test/regress/sql/create_cast.sql b/src/test/regress/sql/create_cast.sql index 32187853cc7..88377a7b467 100644 --- a/src/test/regress/sql/create_cast.sql +++ b/src/test/regress/sql/create_cast.sql @@ -62,6 +62,9 @@ $$ SELECT ('bar'::text || $1::text); $$; CREATE CAST (int4 AS casttesttype) WITH FUNCTION bar_int4_text(int4) AS IMPLICIT; SELECT 1234::int4::casttesttype; -- Should work now +SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVERSION ERROR); -- error +ALTER FUNCTION bar_int4_text(int4) ERROR SAFE; +SELECT CAST(1234::int4 AS casttesttype DEFAULT NULL ON CONVERSION ERROR); -- ok -- check dependencies generated for that SELECT pg_describe_object(classid, objid, objsubid) as obj, diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql index 7fc2159349b..96e6f73c7b6 100644 --- a/src/test/regress/sql/equivclass.sql +++ b/src/test/regress/sql/equivclass.sql @@ -98,6 +98,11 @@ create function int8alias1cmp(int8, int8alias1) returns int alter operator family integer_ops using btree add function 1 int8alias1cmp (int8, int8alias1); +-- int8alias2 binary-coercible to int8, this should not fail +select cast('1'::int8 as int8alias2 default null on conversion error); +select cast('1'::int8alias2 as int8 default null on conversion error); +select cast('a' as int8alias2 default null on conversion error); + create table ec0 (ff int8 primary key, f1 int8, f2 int8); create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2); create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2); diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql index aafd39433a6..dbfec20a05e 100644 --- a/src/test/regress/sql/xml.sql +++ b/src/test/regress/sql/xml.sql @@ -16,6 +16,8 @@ SELECT count(*) = 0 AS skip_test FROM xmltest \gset SELECT * FROM xmltest; +SELECT CAST('one', 'xml'); SELECT pg_input_is_valid('one