Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3 - Mailing list pgsql-bugs

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

diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 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.out
index 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.sql
index 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
 --