pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17371: Immutable INTERVAL to TEXT cast can cause incorrect query results
2+ messages / 2 participants
[nested] [flat]
* BUG #17371: Immutable INTERVAL to TEXT cast can cause incorrect query results
@ 2022-01-19 21:06 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2022-01-19 21:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: marcus@cockroachlabs.com
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';
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17371: Immutable INTERVAL to TEXT cast can cause incorrect query results
@ 2022-01-19 21:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-01-19 21:42 UTC (permalink / raw)
To: marcus@cockroachlabs.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-01-19 21:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-19 21:06 BUG #17371: Immutable INTERVAL to TEXT cast can cause incorrect query results PG Bug reporting form <noreply@postgresql.org>
2022-01-19 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
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