From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ján Pecsők <jan.pecsok@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
Date: Wed, 04 Aug 2021 21:35:22 -0400
Message-ID: <2352034.1628127322@sss.pgh.pa.us> (raw)
In-Reply-To: <CABNQVagu3bZGqiTjb31a8D5Od3fUMs7Oh3gmZMQZVHZ=uWWWfQ@mail.gmail.com>
References: <CABNQVagu3bZGqiTjb31a8D5Od3fUMs7Oh3gmZMQZVHZ=uWWWfQ@mail.gmail.com>
=?UTF-8?B?SsOhbiBQZWNzxZFr?= <jan.pecsok@gmail.com> writes:
> Where conversion from numeric (18,3) to numeric didnt work as expected.
Hmm, interesting. The cause of this is that coerce_type_typmod
supposes that casting to a -1 typmod is a total no-op. Which
is true so far as the run-time behavior is concerned, but not
if something inspects the exposed type of the expression.
The attached seems to be enough to fix it, though I wonder if
we've made the same assumption anywhere else.
Although this changes no existing regression test results,
I'm still a bit hesitant to back-patch it, because I am not
real sure that nothing out there is depending on the current
behavior (which has stood for decades). I think it'd be all
right to put into HEAD, and maybe it's not too late for v14.
regards, tom lane
Attachments:label-cast-to-minus-one-typmod-correctly.patch (text/x-diff, 4.3K) downloaddiff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.cindex 5da72a4c36..78194afedf 100644--- a/src/backend/parser/parse_coerce.c+++ b/src/backend/parser/parse_coerce.c@@ -758,25 +758,33 @@ coerce_type_typmod(Node *node, Oid targetTypeId, int32 targetTypMod,
CoercionPathType pathtype;
Oid funcId;
- /*- * A negative typmod is assumed to mean that no coercion is wanted. Also,- * skip coercion if already done.- */- if (targetTypMod < 0 || targetTypMod == exprTypmod(node))+ /* Skip coercion if already done */+ if (targetTypMod == exprTypmod(node))
return node;
+ /* Suppress display of nested coercion steps */+ if (hideInputCoercion)+ hide_coercion_node(node);+
pathtype = find_typmod_coercion_function(targetTypeId, &funcId);
if (pathtype != COERCION_PATH_NONE)
{
- /* Suppress display of nested coercion steps */- if (hideInputCoercion)- hide_coercion_node(node);-
node = build_coercion_expression(node, pathtype, funcId,
targetTypeId, targetTypMod,
ccontext, cformat, location);
}
+ else+ {+ /*+ * We don't need to perform any actual coercion step, but we should+ * apply a RelabelType to ensure that the expression exposes the+ * intended typmod.+ */+ node = applyRelabelType(node, targetTypeId, targetTypMod,+ exprCollation(node),+ cformat, location, false);+ }
return node;
}
diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.outindex 84159cb21f..7b6b0bb4f9 100644--- a/src/test/regress/expected/expressions.out+++ b/src/test/regress/expected/expressions.out@@ -158,6 +158,43 @@ select count(*) from date_tbl
13
(1 row)
+--+-- Test parsing of a no-op cast to a type with unspecified typmod+--+begin;+create table numeric_tbl (f1 numeric(18,3), f2 numeric);+create view numeric_view as+ select+ f1, f1::numeric(16,4) as f1164, f1::numeric as f1n,+ f2, f2::numeric(16,4) as f2164, f2::numeric as f2n+ from numeric_tbl;+\d+ numeric_view+ View "public.numeric_view"+ Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------------+-----------+----------+---------+---------+-------------+ f1 | numeric(18,3) | | | | main | + f1164 | numeric(16,4) | | | | main | + f1n | numeric | | | | main | + f2 | numeric | | | | main | + f2164 | numeric(16,4) | | | | main | + f2n | numeric | | | | main | +View definition:+ SELECT numeric_tbl.f1,+ numeric_tbl.f1::numeric(16,4) AS f1164,+ numeric_tbl.f1::numeric AS f1n,+ numeric_tbl.f2,+ numeric_tbl.f2::numeric(16,4) AS f2164,+ numeric_tbl.f2 AS f2n+ FROM numeric_tbl;++explain (verbose, costs off) select * from numeric_view;+ QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------+ Seq Scan on public.numeric_tbl+ Output: numeric_tbl.f1, (numeric_tbl.f1)::numeric(16,4), (numeric_tbl.f1)::numeric, numeric_tbl.f2, (numeric_tbl.f2)::numeric(16,4), numeric_tbl.f2+(2 rows)++rollback;
--
-- Tests for ScalarArrayOpExpr with a hashfn
--
diff --git a/src/test/regress/sql/expressions.sql b/src/test/regress/sql/expressions.sqlindex bf30f41505..e9aa1a0b28 100644--- a/src/test/regress/sql/expressions.sql+++ b/src/test/regress/sql/expressions.sql@@ -66,6 +66,27 @@ select count(*) from date_tbl
select count(*) from date_tbl
where f1 not between symmetric '1997-01-01' and '1998-01-01';
++--+-- Test parsing of a no-op cast to a type with unspecified typmod+--+begin;++create table numeric_tbl (f1 numeric(18,3), f2 numeric);++create view numeric_view as+ select+ f1, f1::numeric(16,4) as f1164, f1::numeric as f1n,+ f2, f2::numeric(16,4) as f2164, f2::numeric as f2n+ from numeric_tbl;++\d+ numeric_view++explain (verbose, costs off) select * from numeric_view;++rollback;++
--
-- Tests for ScalarArrayOpExpr with a hashfn
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jan.pecsok@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
In-Reply-To: <2352034.1628127322@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox