Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cqKHp-0005DC-Ee for pgsql-general@arkaria.postgresql.org; Tue, 21 Mar 2017 13:57:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cqKHo-0001bl-Er for pgsql-general@arkaria.postgresql.org; Tue, 21 Mar 2017 13:57:20 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cqKHn-0001aw-4Y for pgsql-general@postgresql.org; Tue, 21 Mar 2017 13:57:19 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cqKHf-0006Gl-I1 for pgsql-general@postgresql.org; Tue, 21 Mar 2017 13:57:17 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 03BB320AA2 for ; Tue, 21 Mar 2017 09:57:10 -0400 (EDT) Received: from web6 ([10.202.2.216]) by compute3.internal (MEProxy); Tue, 21 Mar 2017 09:57:10 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= frazermclean.co.uk; h=content-transfer-encoding:content-type :date:from:message-id:mime-version:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=mesmtp; bh=t3Vp+L/uKPGH3hnkeYIPdEXE5C wEnxw1h7Ijm96rlw8=; b=Tv8UQWKFuWFCbugGFhtmpJsmjoCk7DIJlnTltIpgyY v4Yf7w5MX94Pp51MVIWXgHZTFspZJxAo9P34qtuFsSI/z5dhiFXIoKlz0NzYHPKu Y1puwED0cJFVuThHdYdICu0MouFsuEu4Ze2HHl3CBxlFscsv1G5GnwjesoSlMFNA I= DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:message-id:mime-version:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; bh=t3Vp+L/uKPGH3hnkeYIPdEXE5CwEn xw1h7Ijm96rlw8=; b=dhq8t77TkbSKcDqWpnoIQIEcbBeq3MPdy3nZy0linKao2 J/xQjyxiA04v5W09WJkmcWYMfTkForKq3xQt6Ut03hfqpFmwX3V3gmJhXLwcdY87 j2mi7pheopcJ5yuisFSrGhm7YeV/tHxw28FPGQxYMY7bUr5eIZCSRxIADR4FuYT/ ARxJ3bST138AEI54qLB8xCxtYbZeodcPw1XHkoB7eq0kSJMmDgofSw3ywE64Kiqd k4BuZv4HsdA4jM8se1yL4d0Wgc9Zw9+Mcynnpl6DOiA0QtSMaQ5Yn0pm4SbdmEiN asns6pSrom46WND6gEvNGxr3Pf1nxnqV1ZJg2Kiow== X-ME-Sender: Received: by mailuser.nyi.internal (Postfix, from userid 99) id CEDD748007; Tue, 21 Mar 2017 09:57:09 -0400 (EDT) Message-Id: <1490104629.422698.918452336.26FA96B7@webmail.messagingengine.com> From: Frazer McLean To: pgsql-general@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary="_----------=_14901046294226980" X-Mailer: MessagingEngine.com Webmail Interface - ajax-aac97429 Subject: Unexpected interval comparison Date: Tue, 21 Mar 2017 14:57:09 +0100 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org This is a multi-part message in MIME format. --_----------=_14901046294226980 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="utf-8" I came across an unexpected comparison (tested on PostgreSQL 9.4 and 9.6) for intervals with a large difference in magnitude. I narrowed it down to this example, where comparisons with this range give the wrong value: postgres=# SELECT '1 year'::interval > '3854933 years'::interval, '1 year'::interval > '3854934 years'::interval, '1 year'::interval > '32618664 years'::interval, '1 year'::interval > '32618665 years'::interval; ?column? | ?column? | ?column? | ?column? ----------+----------+----------+---------- f | t | t | f (1 row) Is this a bug? Should I not be comparing intervals? It would seem the interval type has enough information to give the correct answer here. Regards, Frazer McLean --_----------=_14901046294226980 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset="utf-8"
I came across an unexpected comparison (tested on PostgreSQL 9.4 and 9.6) for intervals with a large difference in magnitude.

I narrowed it down to this example, where comparisons with this range give the wrong value:

postgres=# SELECT
  '1 year'::interval > '3854933 years'::interval,
  '1 year'::interval > '3854934 years'::interval,
  '1 year'::interval > '32618664 years'::interval,
  '1 year'::interval > '32618665 years'::interval;

?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
f        | t        | t        | f
(1 row)

Is this a bug? Should I not be comparing intervals? It would seem the interval type has enough information to give the correct answer here.

Regards,

Frazer McLean
--_----------=_14901046294226980--