Thread: BUG #17371: Immutable INTERVAL to TEXT cast can cause incorrect query results

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

PG Bug reporting form <noreply@postgresql.org> writes:
> 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.

Ugh, right, interval_out is marked immutable and should not be.

The cost/benefit of trying to change this in released branches doesn't
seem attractive, but we can fix it for v15 and up.

(Oddly, interval_in is already correctly marked as STABLE.  I'm not
sure how it got to be that way with nobody noticing the mistake
for interval_out.)

Thanks for the report!

            regards, tom lane