Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mBSIm-00085f-01 for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Aug 2021 01:36:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mBSID-0000Sr-Tv for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Aug 2021 01:35:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mBSID-0000SE-Mr for pgsql-bugs@lists.postgresql.org; Thu, 05 Aug 2021 01:35:29 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mBSI9-0004uG-OU for pgsql-bugs@lists.postgresql.org; Thu, 05 Aug 2021 01:35:28 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 1751ZMNB2352035; Wed, 4 Aug 2021 21:35:22 -0400 From: Tom Lane To: =?UTF-8?B?SsOhbiBQZWNzxZFr?= cc: pgsql-bugs@lists.postgresql.org Subject: Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3 In-reply-to: References: Comments: In-reply-to =?UTF-8?B?SsOhbiBQZWNzxZFr?= message dated "Wed, 04 Aug 2021 17:15:42 +0200" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <2351973.1628127234.0@sss.pgh.pa.us> Date: Wed, 04 Aug 2021 21:35:22 -0400 Message-ID: <2352034.1628127322@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2351973.1628127234.1@sss.pgh.pa.us> =?UTF-8?B?SsOhbiBQZWNzxZFr?= 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="label-cast-to-minus-one-typmod-correctly.patch"; charset="us-ascii" Content-ID: <2351973.1628127234.2@sss.pgh.pa.us> Content-Description: label-cast-to-minus-one-typmod-correctly.patch Content-Transfer-Encoding: quoted-printable 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, int= 32 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 =3D=3D exprTypmod(node)) + /* Skip coercion if already done */ + if (targetTypMod =3D=3D exprTypmod(node)) return node; = + /* Suppress display of nested coercion steps */ + if (hideInputCoercion) + hide_coercion_node(node); + pathtype =3D find_typmod_coercion_function(targetTypeId, &funcId); = if (pathtype !=3D COERCION_PATH_NONE) { - /* Suppress display of nested coercion steps */ - if (hideInputCoercion) - hide_coercion_node(node); - node =3D 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 =3D 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 | Desc= ription = +--------+---------------+-----------+----------+---------+---------+-----= -------- + 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; + QUE= RY 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/e= xpressions.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 -- ------- =_aaaaaaaaaa0--