From: Tom Lane <tgl@sss.pgh.pa.us>
To: email@piotr-stefaniak.me
cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14479: Casting non-constant values to INTERVAL DAY TO MINUTE
 doesn't truncate the seconds field
In-reply-to: <20161227172307.10135.7747@wrigleys.postgresql.org>
References: <20161227172307.10135.7747@wrigleys.postgresql.org>
Comments: In-reply-to email@piotr-stefaniak.me
	message dated "Tue, 27 Dec 2016 17:23:07 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <26631.1482862368.1@sss.pgh.pa.us>
Date: Tue, 27 Dec 2016 13:12:48 -0500
Message-ID: <26632.1482862368@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

email@piotr-stefaniak.me writes:
> # SELECT x.y::INTERVAL DAY TO MINUTE, (interval '02:47:15.375721')::INTERVAL
> DAY TO MINUTE FROM (VALUES (interval '02:47:15.375721')) x (y);
>         y        | interval 
> -----------------+----------
>  02:47:15.375721 | 02:47:00

> I chose 9.4.10 as the version in the web form because that's what I have,
> but Andrew Gierth has said that this is the behavior of versions: 9.2, 9.3,
> and 9.4 but not versions 9.1, 9.5, and HEAD.

Thanks for the report.  It appears that interval_transform is broken:
it's deciding that the cast to INTERVAL DAY TO MINUTE is a no-op and
can be replaced by a RelabelType.

This has *not* been fixed in HEAD, it's just been masked in this
particular example by other changes.  You can show it's still broken with

regression=# create table tt (f1 interval);
CREATE TABLE
regression=# insert into tt values('02:47:15.375721');
INSERT 0 1
regression=# select f1 from tt;
       f1        
-----------------
 02:47:15.375721
(1 row)

regression=# select f1::INTERVAL DAY TO MINUTE from tt;
       f1        
-----------------
 02:47:15.375721
(1 row)

This case fails all the way back to 9.2 where transforms were introduced.
The reason your example happens not to fail in 9.5 and up is that those
versions know how to flatten a single-row VALUES construct, so that the
cast gets simplified to a constant (correctly) and interval_transform
is never applied.

I've not figured out exactly what's broken about interval_transform;
the bit-twiddling looks a bit fishy, but it may be that that's fine
and there's simply a thinko in the logic about which conversions
are no-ops.

			regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs