From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: marcus@cockroachlabs.com
Subject: BUG #17398: Casts from BYTEA to TEXT and FLOAT4/8 to TEXT should not be immutable
Date: Mon, 07 Feb 2022 20:40:15 +0000
Message-ID: <17398-8fb0c02a60e2e565@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17398
Logged by: Marcus Gartner
Email address: marcus@cockroachlabs.com
PostgreSQL version: 14.0
Operating system: macOS Monterey 12.2
Description:
Casts from BYTEA to TEXT should not be immutable because the result is
dependent on the bytea_output parameter. Casts from FLOAT4 and FLOAT8 to
TEXT should not be immutable because the result is dependent on the
extra_float_digits parameter. Incorrect results are possible because of
these incorrectly labeled cast volatilities. This bug is similar to BUG
#17371.
Example 1: BYTEA::TEXT
SET bytea_output = hex;
CREATE TABLE t (b BYTEA);
INSERT INTO t SELECT 'a' FROM generate_series(1, 10000) s(i);
INSERT INTO t VALUES ('b');
CREATE INDEX i ON t((b::TEXT), b);
SET bytea_output = escape;
-- Returns false.
SELECT b::TEXT = '\x62' FROM t WHERE b::TEXT = '\x62';
ANALYZE t;
-- Same query as above, now performing an index-only scan, returns true.
SELECT b::TEXT = '\x62' FROM t WHERE b::TEXT = '\x62';
DROP INDEX i;
-- Same query as above returns zero rows.
SELECT b::TEXT = '\x62' FROM t WHERE b::TEXT = '\x62';
Example 2: FLOAT::TEXT
SET extra_float_digits = 0;
CREATE TABLE t (f FLOAT);
INSERT INTO t SELECT 1 FROM generate_series(1, 10000) s(i);
INSERT INTO t VALUES (123.4567890123456789);
CREATE INDEX i ON t((f::TEXT), f);
SET extra_float_digits = 3;
-- Returns false.
SELECT f::TEXT = '123.456789012346' FROM t WHERE f::TEXT =
'123.456789012346';
ANALYZE t;
-- Same query as above, now performing an index-only scan, returns true.
SELECT f::TEXT = '123.456789012346' FROM t WHERE f::TEXT =
'123.456789012346';
DROP INDEX i;
-- Same query as above returns zero rows.
SELECT f::TEXT = '123.456789012346' FROM t WHERE f::TEXT =
'123.456789012346';
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, marcus@cockroachlabs.com
Subject: Re: BUG #17398: Casts from BYTEA to TEXT and FLOAT4/8 to TEXT should not be immutable
In-Reply-To: <17398-8fb0c02a60e2e565@postgresql.org>
* 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