BUG #17371: Immutable INTERVAL to TEXT cast can cause incorrect query results - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17371
Logged by:          Marcus Gartner
Email address:      marcus@cockroachlabs.com
PostgreSQL version: 14.0
Operating system:   macOS Big Sur 11.6
Description:

I believe casts from INTERVAL to TEXT (and other string-like types such as
CHAR, NAME, VARCHAR, and "char") should have a volatility of stable, not
immutable, because the results of these casts depend on IntervalStyle. As an
immutable cast, INTERVAL to TEXT casts are allowed in computed columns and
expression indexes, which can cause incorrect query results. For example:


SET IntervalStyle = 'postgres';

CREATE TABLE t (i INTERVAL);

INSERT INTO t SELECT i * '1 day'::INTERVAL FROM generate_series(1, 10000)
s(i);

CREATE INDEX i ON t((i::TEXT), i);

SET IntervalStyle = 'sql_standard';

-- Returns false.
SELECT i::TEXT = '5 days' FROM t WHERE i::TEXT = '5 days';

ANALYZE t;

-- Same query as above, now performing an index-only scan, returns true.
SELECT i::TEXT = '5 days' FROM t WHERE i::TEXT = '5 days';

DROP INDEX i;

-- Same query as above returns zero rows.
SELECT i::TEXT = '5 days' FROM t WHERE i::TEXT = '5 days';