pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedCAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
8+ messages / 5 participants
[nested] [flat]
Commits from this thread:
2021-12-16 f9a8bc9f27 e1fd61c8ce 1acf345869 9c356f4b2d da0d8a4545 c43d72b245 Ensure casting to typmod -1 generates a RelabelType.
2021-08-06 e5f6493e35 5c056b0c25 183b3aceda f230614da2 914e54501b 923b7efc25 dede143997 Don't elide casting to typmod -1.
* CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-08-04 15:15 Ján Pecsők <jan.pecsok@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ján Pecsők @ 2021-08-04 15:15 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
I report unexpected behaviour on version
PostgreSQL 13.3 on powerpc64le-unknown-linux-gnu, compiled by gcc (GCC)
6.4.1 20180131 (Advance-Toolchain-at10.0) [revision 257243], 64-bit
Where conversion from numeric (18,3) to numeric didnt work as expected.
Example
CREATE TABLE public.test_conversion (
number_1 numeric(18,3) NOT NULL
)
;
create or replace view public.test_conversion_2
as
select
number_1::numeric
from public.test_conversion
create table public.test_conversion_3 as select * from
public.test_conversion_2
;
Expected behaviour is that public.test_conversion_3 has one column of type
numeric
Actual behaviour is that public.test_conversion_3 has one column of type
numeric (18,3)
With best regards
Jan Pecsok
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-08-05 01:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ján Pecsők <jan.pecsok@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-08-05 01:35 UTC (permalink / raw)
To: Ján Pecsők <jan.pecsok@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
=?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) download
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
--
* Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-08-05 09:06 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Dean Rasheed @ 2021-08-05 09:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ján Pecsők <jan.pecsok@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thu, 5 Aug 2021 at 02:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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.
+1 for fixing this.
> 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.
This isn't the first time someone has hit this [1]. The failing CTE
query there is quite interesting because if ::varchar[] with no typmod
wasn't a no-op, it could have been used to fix it. In fact, that's
what the error message and hint suggest doing.
So this fix is potentially more widely useful, though I couldn't say
whether anyone is relying on the current behaviour.
Regards,
Dean
[1] https://dba.stackexchange.com/questions/116218/surprising-results-for-data-types-with-type-modifier
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-08-05 16:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-08-05 16:18 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: Ján Pecsők <jan.pecsok@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On Thu, 5 Aug 2021 at 02:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> This isn't the first time someone has hit this [1]. The failing CTE
> query there is quite interesting because if ::varchar[] with no typmod
> wasn't a no-op, it could have been used to fix it. In fact, that's
> what the error message and hint suggest doing.
Hmm. Jan's example isn't that compelling, since you could always
create the table manually with the desired column type. However
the CTE application is more interesting, since there's no really
easy way to satisfy the type-matching rule there. (You could
cast to the more restrictive typmod, but that might be the Wrong
Thing for your query.)
So maybe we should back-patch, but I still feel nervous about that.
Anybody else have an opinion here?
With one eye on the calendar, I'm thinking that if we do decide
to back-patch we should wait a week, rather than shoving this in
just before a release wrap. OTOH, if it's v14 only it'd be better
to have it appear in beta3 rather than later. Maybe we should
push to v14 now, and consider back-patch in a few months if there
are not complaints about beta3?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-08-06 18:58 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2021-08-06 18:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>; Ján Pecsők <jan.pecsok@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thursday, August 5, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> So maybe we should back-patch, but I still feel nervous about that.
> Anybody else have an opinion here?
>
> With one eye on the calendar, I'm thinking that if we do decide
> to back-patch we should wait a week, rather than shoving this in
> just before a release wrap. OTOH, if it's v14 only it'd be better
> to have it appear in beta3 rather than later. Maybe we should
> push to v14 now, and consider back-patch in a few months if there
> are not complaints about beta3?
>
>
Here is a recent newbie help request I fielded in Discord:
WITH RECURSIVE fizz_buzz (sequence, modulo_3, modulo_5) AS (
SELECT 1, CAST('' AS CHAR(4)), CAST('' AS CHAR(4))
UNION ALL
SELECT sequence + 1,
CASE WHEN MOD(sequence + 1, 3) = 0 THEN 'Fizz'
ELSE '' END,
CASE WHEN MOD(sequence + 1, 5) = 0 THEN 'Buzz'
ELSE '' END
FROM fizz_buzz
WHERE sequence < 100
)
SELECT
CASE WHEN CONCAT(modulo_3, modulo_5) = '' THEN sequence
ELSE CONCAT(modulo_3, modulo_5) END AS fizzbuzz
FROM fizz_buzz;
LINE 2: SELECT 1, CAST('' AS CHAR(4)), CAST('' AS CHAR(4))
^
HINT: Cast the output of the non-recursive term to the correct type.
And the just posted Bug 17137:
https://www.postgresql.org/message-id/17137-3d3732d5a259612c%40postgresql.org
Are these related?
I would agree with doing this in v14 then back-patch with the next update.
But i don’t have a technical feel here.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-08-06 19:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-08-06 19:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>; Ján Pecsők <jan.pecsok@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Here is a recent newbie help request I fielded in Discord:
> WITH RECURSIVE fizz_buzz (sequence, modulo_3, modulo_5) AS (
> SELECT 1, CAST('' AS CHAR(4)), CAST('' AS CHAR(4))
> UNION ALL
> SELECT sequence + 1,
> CASE WHEN MOD(sequence + 1, 3) = 0 THEN 'Fizz'
> ELSE '' END,
> CASE WHEN MOD(sequence + 1, 5) = 0 THEN 'Buzz'
> ELSE '' END
> FROM fizz_buzz
> WHERE sequence < 100
> )
> SELECT
> CASE WHEN CONCAT(modulo_3, modulo_5) = '' THEN sequence
> ELSE CONCAT(modulo_3, modulo_5) END AS fizzbuzz
> FROM fizz_buzz;
I'd class that one as pilot error, in that the query author
isn't even trying to make the two sides of the union agree
type-wise. It's not the same as the point at hand, where
someone did try to cast to the correct type but the parser
ignored it.
> And the just posted Bug 17137:
> https://www.postgresql.org/message-id/17137-3d3732d5a259612c%40postgresql.org
> Are these related?
Right, that one is a dup of the current issue.
> I would agree with doing this in v14 then back-patch with the next update.
Yeah, I'm going to push to v14 shortly if I don't hear objections soon.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-12-16 18:38 John Naylor <john.naylor@enterprisedb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: John Naylor @ 2021-12-16 18:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
A customer reported a planning regression from 11.5 to 12.9. After
bisecting, it seems the fix for the thread subject here, commit
f230614da28, broke partition pruning in some cases. Here's a
reproducer:
drop table if exists test_pruning;
create table test_pruning (account_id character(16) primary key)
partition by hash(account_id);
create table p0_test_pruning partition of test_pruning
for values with (modulus 5, remainder 0);
create table p1_test_pruning partition of test_pruning
for values with (modulus 5, remainder 1);
create table p2_test_pruning partition of test_pruning
for values with (modulus 5, remainder 2);
create table p3_test_pruning partition of test_pruning
for values with (modulus 5, remainder 3);
create table p4_test_pruning partition of test_pruning
for values with (modulus 5, remainder 4);
insert into test_pruning select 'XY' || lpad(i::text, 14, '0') from
generate_series(1,1000000,1) as i;
-- explicit cast on both operands
EXPLAIN (ANALYZE)
SELECT COUNT(*) FROM test_pruning WHERE account_id::BPCHAR =
'XY99999999999999'::BPCHAR;
-- explicit cast only on const
EXPLAIN (ANALYZE)
SELECT COUNT(*) FROM test_pruning WHERE account_id = 'XY99999999999999'::BPCHAR;
These queries both allowed partition pruning before, but with this
commit only the latter does:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=12159.67..12159.68 rows=1 width=8) (actual
time=66.891..68.641 rows=1 loops=1)
-> Gather (cost=12159.46..12159.67 rows=2 width=8) (actual
time=66.784..68.635 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=11159.46..11159.47 rows=1
width=8) (actual time=56.140..56.142 rows=1 loops=3)
-> Parallel Append (cost=0.00..11156.64 rows=1128
width=0) (actual time=56.137..56.138 rows=0 loops=3)
-> Parallel Seq Scan on p2_test_pruning
(cost=0.00..2238.25 rows=320 width=0) (actual time=35.421..35.421
rows=0 loops=1)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 200799
-> Parallel Seq Scan on p3_test_pruning
(cost=0.00..2236.50 rows=319 width=0) (actual time=39.589..39.589
rows=0 loops=1)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 200523
-> Parallel Seq Scan on p4_test_pruning
(cost=0.00..2227.75 rows=318 width=0) (actual time=10.156..10.156
rows=0 loops=3)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 66599
-> Parallel Seq Scan on p0_test_pruning
(cost=0.00..2224.25 rows=318 width=0) (actual time=17.785..17.785
rows=0 loops=2)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 99715
-> Parallel Seq Scan on p1_test_pruning
(cost=0.00..2224.25 rows=318 width=0) (actual time=27.336..27.336
rows=0 loops=1)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 199452
Planning Time: 0.476 ms
Execution Time: 68.716 ms
(23 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.44..8.45 rows=1 width=8) (actual time=0.016..0.016
rows=1 loops=1)
-> Index Only Scan using p2_test_pruning_pkey on p2_test_pruning
(cost=0.42..8.44 rows=1 width=0) (actual time=0.014..0.014 rows=0
loops=1)
Index Cond: (account_id = 'XY99999999999999'::bpchar)
Heap Fetches: 0
Planning Time: 0.071 ms
Execution Time: 0.038 ms
(6 rows)
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
@ 2021-12-16 19:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Naylor <john.naylor@enterprisedb.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2021-12-16 19:44 UTC (permalink / raw)
To: John Naylor <john.naylor@enterprisedb.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
John Naylor <john.naylor@enterprisedb.com> writes:
> A customer reported a planning regression from 11.5 to 12.9. After
> bisecting, it seems the fix for the thread subject here, commit
> f230614da28, broke partition pruning in some cases.
[ facepalm... ] I didn't consider the possibility that
find_typmod_coercion_function would find something ...
so we're getting an actual call of bpchar(), not just
a RelabelType.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-12-16 19:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-04 15:15 CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3 Ján Pecsők <jan.pecsok@gmail.com>
2021-08-05 01:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-05 09:06 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2021-08-05 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-06 18:58 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-06 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-16 18:38 ` John Naylor <john.naylor@enterprisedb.com>
2021-12-16 19:44 ` Tom Lane <tgl@sss.pgh.pa.us>
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