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';
