BUG #17137: Bug with datatypes definition in CTE

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: alexone07(at)mail(dot)ru
Subject: BUG #17137: Bug with datatypes definition in CTE
Date: 2021-08-06 18:12:28
Message-ID: 17137-3d3732d5a259612c@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17137
Logged by: Alex Zhuravlev
Email address: alexone07(at)mail(dot)ru
PostgreSQL version: 13.3
Operating system: Red Hat 8.4.1-1, 64-bit
Description:

SQL expression doesn't execute with error:
SQL Error [42804]: ERROR: recursive query "t" column 1 has type character
varying(20) in non-recursive term but type character varying overall
Help: Cast the output of the non-recursive term to the correct type.

*But it works with text datatype.

Some repro's:
First:
WITH RECURSIVE t AS (
SELECT a::varchar AS b
FROM (
SELECT 'a'::varchar(20) AS a
) AS x
UNION ALL
SELECT 'a'::varchar AS b
FROM t
)
SELECT *
FROM t;

The Second:
CREATE TABLE tm1(a varchar(20)); SELECT a, a::varchar FROM tm1 \gdesc

The third:
CREATE TABLE t(n) AS SELECT '5.5'::numeric(20, 2); SELECT n::numeric FROM t
\gdesc

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c3bfe57c351cc783399fad539d5690d0

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2021-08-06 18:58:45 CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3
Previous Message Andrey Borodin 2021-08-06 18:03:18 Re: CREATE INDEX CONCURRENTLY does not index prepared xact's data