Thread: BUG #17398: Casts from BYTEA to TEXT and FLOAT4/8 to TEXT should not be immutable
BUG #17398: Casts from BYTEA to TEXT and FLOAT4/8 to TEXT should not be immutable
From
PG Bug reporting form
Date:
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';