Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEIty-0002Zm-2t for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Sep 2020 21:05:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kEItw-0004K7-Tk for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Sep 2020 21:05:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEItw-0004K0-Ig for pgsql-hackers@lists.postgresql.org; Fri, 04 Sep 2020 21:05:40 +0000 Received: from forward2-smtp.messagingengine.com ([66.111.4.226]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEItp-0007bD-9H for pgsql-hackers@lists.postgresql.org; Fri, 04 Sep 2020 21:05:39 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailforward.nyi.internal (Postfix) with ESMTP id 07FAC1941391; Fri, 4 Sep 2020 17:05:32 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Fri, 04 Sep 2020 17:05:32 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; bh=sXPiBz xuVFuczq8Ggw2UyPqK/ogO9o5gff50NbH5KnU=; b=OyJph7hv5Ir7bjfzy1r7p/ T4VEUAK5B0/qIDDuwPFo+1mtSp8VDPfajkkIoz2Dh2wlZ3jGxP0SxMdpAV2dBpJT OrRImn6Gx+/ieDdDc3tnsYjg5GtoTAHlxOnGT3yqdXqMJGIKrS1KwKkpd97GvkNt GwQpd8nELK3/4Tf0rzgUzZDMijFuo6mEhzZqLdHRkck7FLcZh/4OA5OXmSVUAmY1 chX56biWiVCyIvkLv1aUzGgDbtPjwut5FQeI+tk6RNJRl0fUnS/WD+CMvYaSM/7F GWxCcGDd0jZVb8X4jE/SmaujWRLNdZvIFzUlt1+vwQ2qE2wMFKCrN1dy95whTkjg == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduiedrudegfedgudehjecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefuvfhfhfhokffffgggjggtsehmtderredtfeejnecuhfhrohhmpefrvght vghrucfgihhsvghnthhrrghuthcuoehpvghtvghrrdgvihhsvghnthhrrghuthesvdhnug hquhgrughrrghnthdrtghomheqnecuggftrfgrthhtvghrnhepudfgueevieeluefggeel udfghfeuhffhjeehfefgledufedtieevudfggeevgfejnecuffhomhgrihhnpedvnhguqh hurggurhgrnhhtrdgtohhmnecukfhppeeluddrudegrddvfeefrdeffeenucevlhhushht vghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehpvghtvghrrdgvihhsvg hnthhrrghuthesvdhnughquhgrughrrghnthdrtghomh X-ME-Proxy: Received: from april.pezone.net (p5b0ee921.dip0.t-ipconnect.de [91.14.233.33]) by mail.messagingengine.com (Postfix) with ESMTPA id 5BBED328005A; Fri, 4 Sep 2020 17:05:30 -0400 (EDT) Subject: Re: BUG #16419: wrong parsing BC year in to_date() function To: "David G. Johnston" , Bruce Momjian References: <16419-d8d9db0a7553f01b@postgresql.org> <0123b852ae3fe53eca11b9451d035411b06dafd5.camel@cybertec.at> <20200904012149.GA13932@momjian.us> Cc: Laurenz Albe , PostgreSQL Hackers From: Peter Eisentraut Organization: 2ndQuadrant Message-ID: <13c0992c-f15a-a0ca-d839-91d3efd965d9@2ndquadrant.com> Date: Fri, 4 Sep 2020 23:05:28 +0200 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------60B7F3D9EF2BCBC0D40540F6" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------60B7F3D9EF2BCBC0D40540F6 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 2020-09-04 21:45, David G. Johnston wrote: > On Thu, Sep 3, 2020 at 6:21 PM Bruce Momjian > wrote: > > On Wed, Jul 15, 2020 at 09:26:53AM -0700, David G. Johnston wrote: > > > Whether to actually change the behavior of to_date is up for > debate though I > > would presume it would not be back-patched. > > OK, so, looking at this thread, we have to_date() treating -1 as -2 BC, > make_date() treating -1 as 1 BC, and we have Oracle, which to_date() is > supposed to match, making -1 as 1 BC. > > Because we already have the to_date/make_date inconsistency, and the -1 > to -2 BC mapping is confusing, and doesn't match Oracle, I think the > clean solution is to change PG 14 to treat -1 as 1 BC, and document the > incompatibility in the release notes. > > > I agree that someone else should write another patch to fix the behavior > for v14.  Still suggest committing the proposed patch to master and all > supported versions to document the existing behavior correctly.  The fix > patch can work from that. Adding support for negative years in make_timestamp seems pretty straightforward; see attached patch. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------60B7F3D9EF2BCBC0D40540F6 Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="0001-Support-negative-years-in-make_timestamp.patch" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="0001-Support-negative-years-in-make_timestamp.patch" RnJvbSBlZjliMGNlZTRhZWU2MDI2OGQ0YzcyZTg0NTJlNzRlN2JiYzE4ZWQxIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBFaXNlbnRyYXV0IDxwZXRlckBlaXNlbnRy YXV0Lm9yZz4KRGF0ZTogRnJpLCA0IFNlcCAyMDIwIDIzOjAyOjA5ICswMjAwClN1YmplY3Q6 IFtQQVRDSF0gU3VwcG9ydCBuZWdhdGl2ZSB5ZWFycyBpbiBtYWtlX3RpbWVzdGFtcCgpCgpt YWtlX2RhdGUoKSBhbHJlYWR5IHN1cHBvcnRlZCBpdDsgYWRkaW5nIGl0IHdhcyB0cml2aWFs LgoKQnVnOiAjMTY0MTkKRGlzY3Vzc2lvbjogaHR0cHM6Ly93d3cucG9zdGdyZXNxbC5vcmcv bWVzc2FnZS1pZC9mbGF0LzE2NDE5LWQ4ZDlkYjBhNzU1M2YwMWJAcG9zdGdyZXNxbC5vcmcK LS0tCiBzcmMvYmFja2VuZC91dGlscy9hZHQvdGltZXN0YW1wLmMgICAgICAgfCAxNCArKysr KysrKystLS0tLQogc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9kYXRlLm91dCAgICAgIHwg IDIgKysKIHNyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvdGltZXN0YW1wLm91dCB8IDExICsr KysrKysrKystCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC9kYXRlLnNxbCAgICAgICAgICAgfCAg MSArCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC90aW1lc3RhbXAuc3FsICAgICAgfCAgNSArKysr LQogNSBmaWxlcyBjaGFuZ2VkLCAyNiBpbnNlcnRpb25zKCspLCA3IGRlbGV0aW9ucygtKQoK ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC90aW1lc3RhbXAuYyBiL3NyYy9i YWNrZW5kL3V0aWxzL2FkdC90aW1lc3RhbXAuYwppbmRleCA1ZmUzMDRjZWE3Li40MTI4ZTNh NzM5IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9hZHQvdGltZXN0YW1wLmMKKysr IGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3RpbWVzdGFtcC5jCkBAIC01NTYsMTcgKzU1Niwy MSBAQCBtYWtlX3RpbWVzdGFtcF9pbnRlcm5hbChpbnQgeWVhciwgaW50IG1vbnRoLCBpbnQg ZGF5LAogCVRpbWVPZmZzZXQJZGF0ZTsKIAlUaW1lT2Zmc2V0CXRpbWU7CiAJaW50CQkJZHRl cnI7CisJYm9vbAkJYmMgPSBmYWxzZTsKIAlUaW1lc3RhbXAJcmVzdWx0OwogCiAJdG0udG1f eWVhciA9IHllYXI7CiAJdG0udG1fbW9uID0gbW9udGg7CiAJdG0udG1fbWRheSA9IGRheTsK IAotCS8qCi0JICogTm90ZTogd2UnbGwgcmVqZWN0IHplcm8gb3IgbmVnYXRpdmUgeWVhciB2 YWx1ZXMuICBQZXJoYXBzIG5lZ2F0aXZlcwotCSAqIHNob3VsZCBiZSBhbGxvd2VkIHRvIHJl cHJlc2VudCBCQyB5ZWFycz8KLQkgKi8KLQlkdGVyciA9IFZhbGlkYXRlRGF0ZShEVEtfREFU RV9NLCBmYWxzZSwgZmFsc2UsIGZhbHNlLCAmdG0pOworCS8qIEhhbmRsZSBuZWdhdGl2ZSB5 ZWFycyBhcyBCQyAqLworCWlmICh0bS50bV95ZWFyIDwgMCkKKwl7CisJCWJjID0gdHJ1ZTsK KwkJdG0udG1feWVhciA9IC10bS50bV95ZWFyOworCX0KKworCWR0ZXJyID0gVmFsaWRhdGVE YXRlKERUS19EQVRFX00sIGZhbHNlLCBmYWxzZSwgYmMsICZ0bSk7CiAKIAlpZiAoZHRlcnIg IT0gMCkKIAkJZXJlcG9ydChFUlJPUiwKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mv ZXhwZWN0ZWQvZGF0ZS5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2RhdGUub3V0 CmluZGV4IDRjZGYxNjM1ZjIuLmMwMzMyOWZiNWEgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3Jl Z3Jlc3MvZXhwZWN0ZWQvZGF0ZS5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3Rl ZC9kYXRlLm91dApAQCAtMTQzOSw2ICsxNDM5LDggQEAgc2VsZWN0IG1ha2VfdGltZSg4LCAy MCwgMC4wKTsKICgxIHJvdykKIAogLS0gc2hvdWxkIGZhaWwKK3NlbGVjdCBtYWtlX2RhdGUo MCwgNywgMTUpOworRVJST1I6ICBkYXRlIGZpZWxkIHZhbHVlIG91dCBvZiByYW5nZTogMC0w Ny0xNQogc2VsZWN0IG1ha2VfZGF0ZSgyMDEzLCAyLCAzMCk7CiBFUlJPUjogIGRhdGUgZmll bGQgdmFsdWUgb3V0IG9mIHJhbmdlOiAyMDEzLTAyLTMwCiBzZWxlY3QgbWFrZV9kYXRlKDIw MTMsIDEzLCAxKTsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvdGlt ZXN0YW1wLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvdGltZXN0YW1wLm91dApp bmRleCA1Zjk3NTA1YTMwLi45NjU1MTE2MDkwIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdy ZXNzL2V4cGVjdGVkL3RpbWVzdGFtcC5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9leHBl Y3RlZC90aW1lc3RhbXAub3V0CkBAIC0xNzA0LDkgKzE3MDQsMTggQEAgU0VMRUNUICcnIEFT IHRvX2NoYXJfMTIsIHRvX2NoYXIoZCwgJ0ZGMSBGRjIgRkYzIEZGNCBGRjUgRkY2ICBmZjEg ZmYyIGZmMyBmZjQgZmYKICg0IHJvd3MpCiAKIC0tIHRpbWVzdGFtcCBudW1lcmljIGZpZWxk cyBjb25zdHJ1Y3RvcgotU0VMRUNUIG1ha2VfdGltZXN0YW1wKDIwMTQsMTIsMjgsNiwzMCw0 NS44ODcpOworU0VMRUNUIG1ha2VfdGltZXN0YW1wKDIwMTQsIDEyLCAyOCwgNiwgMzAsIDQ1 Ljg4Nyk7CiAgICAgICAgIG1ha2VfdGltZXN0YW1wICAgICAgICAKIC0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLQogIFN1biBEZWMgMjggMDY6MzA6NDUuODg3IDIwMTQKICgxIHJv dykKIAorU0VMRUNUIG1ha2VfdGltZXN0YW1wKC00NCwgMywgMTUsIDEyLCAzMCwgMTUpOwor ICAgICAgIG1ha2VfdGltZXN0YW1wICAgICAgICAKKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tCisgRnJpIE1hciAxNSAxMjozMDoxNSAwMDQ0IEJDCisoMSByb3cpCisKKy0tIHNo b3VsZCBmYWlsCitzZWxlY3QgbWFrZV90aW1lc3RhbXAoMCwgNywgMTUsIDEyLCAzMCwgMTUp OworRVJST1I6ICBkYXRlIGZpZWxkIHZhbHVlIG91dCBvZiByYW5nZTogMC0wNy0xNQpkaWZm IC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvZGF0ZS5zcWwgYi9zcmMvdGVzdC9yZWdy ZXNzL3NxbC9kYXRlLnNxbAppbmRleCAxYzNhZGY3MGNlLi43N2Y5NGVkMjdiIDEwMDY0NAot LS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9kYXRlLnNxbAorKysgYi9zcmMvdGVzdC9yZWdy ZXNzL3NxbC9kYXRlLnNxbApAQCAtMzM1LDYgKzMzNSw3IEBAIENSRUFURSBUQUJMRSBEQVRF X1RCTCAoZjEgZGF0ZSk7CiBzZWxlY3QgbWFrZV9kYXRlKC00NCwgMywgMTUpOwogc2VsZWN0 IG1ha2VfdGltZSg4LCAyMCwgMC4wKTsKIC0tIHNob3VsZCBmYWlsCitzZWxlY3QgbWFrZV9k YXRlKDAsIDcsIDE1KTsKIHNlbGVjdCBtYWtlX2RhdGUoMjAxMywgMiwgMzApOwogc2VsZWN0 IG1ha2VfZGF0ZSgyMDEzLCAxMywgMSk7CiBzZWxlY3QgbWFrZV9kYXRlKDIwMTMsIDExLCAt MSk7CmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC90aW1lc3RhbXAuc3FsIGIv c3JjL3Rlc3QvcmVncmVzcy9zcWwvdGltZXN0YW1wLnNxbAppbmRleCA3YjU4YzNjZmE1Li43 MjdlZTUwMDg0IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC90aW1lc3RhbXAu c3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3RpbWVzdGFtcC5zcWwKQEAgLTI0MCw0 ICsyNDAsNyBAQCBDUkVBVEUgVEFCTEUgVElNRVNUQU1QX1RCTCAoZDEgdGltZXN0YW1wKDIp IHdpdGhvdXQgdGltZSB6b25lKTsKICAgICkgZChkKTsKIAogLS0gdGltZXN0YW1wIG51bWVy aWMgZmllbGRzIGNvbnN0cnVjdG9yCi1TRUxFQ1QgbWFrZV90aW1lc3RhbXAoMjAxNCwxMiwy OCw2LDMwLDQ1Ljg4Nyk7CitTRUxFQ1QgbWFrZV90aW1lc3RhbXAoMjAxNCwgMTIsIDI4LCA2 LCAzMCwgNDUuODg3KTsKK1NFTEVDVCBtYWtlX3RpbWVzdGFtcCgtNDQsIDMsIDE1LCAxMiwg MzAsIDE1KTsKKy0tIHNob3VsZCBmYWlsCitzZWxlY3QgbWFrZV90aW1lc3RhbXAoMCwgNywg MTUsIDEyLCAzMCwgMTUpOwotLSAKMi4yOC4wCgo= --------------60B7F3D9EF2BCBC0D40540F6--