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 1pIBoj-000138-FF for pgsql-hackers@arkaria.postgresql.org; Wed, 18 Jan 2023 17:01:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pIBoi-0004gO-Af for pgsql-hackers@arkaria.postgresql.org; Wed, 18 Jan 2023 17:01:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIBoi-0004gF-0F for pgsql-hackers@lists.postgresql.org; Wed, 18 Jan 2023 17:01:40 +0000 Received: from relay4-d.mail.gandi.net ([217.70.183.196]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIBof-0004zR-DL for pgsql-hackers@lists.postgresql.org; Wed, 18 Jan 2023 17:01:39 +0000 Received: (Authenticated sender: vik@postgresfriends.org) by mail.gandi.net (Postfix) with ESMTPSA id D34E8E0014; Wed, 18 Jan 2023 17:01:34 +0000 (UTC) Content-Type: multipart/mixed; boundary="------------LqR1zkqg6t0rWAJKQK2qiy7j" Message-ID: <538ac95d-f12e-92b8-0e22-85b6e35fdec9@postgresfriends.org> Date: Wed, 18 Jan 2023 17:01:34 +0000 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: ANY_VALUE aggregate Content-Language: en-US To: Peter Eisentraut , PostgreSQL Hackers References: <5cff866c-10a8-d2df-32cb-e9072e6b04a2@postgresfriends.org> <464ece4e-fabb-ddac-60da-897493c4476d@enterprisedb.com> From: Vik Fearing In-Reply-To: <464ece4e-fabb-ddac-60da-897493c4476d@enterprisedb.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------LqR1zkqg6t0rWAJKQK2qiy7j Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/18/23 16:06, Peter Eisentraut wrote: > On 05.12.22 21:18, Vik Fearing wrote: >> On 12/5/22 15:57, Vik Fearing wrote: >>> The SQL:2023 Standard defines a new aggregate named ANY_VALUE.  It >>> returns an implementation-dependent (i.e. non-deterministic) value >>> from the rows in its group. >>> >>> PFA an implementation of this aggregate. >> >> Here is v2 of this patch.  I had forgotten to update sql_features.txt. > > In your patch, the documentation says the definition is any_value("any") > but the catalog definitions are any_value(anyelement).  Please sort that > out. > > Since the transition function is declared strict, null values don't need > to be checked. Thank you for the review. Attached is a new version rebased to d540a02a72. -- Vik Fearing --------------LqR1zkqg6t0rWAJKQK2qiy7j Content-Type: text/x-patch; charset=UTF-8; name="0001-Implement-ANY_VALUE-aggregate.v03.patch" Content-Disposition: attachment; filename="0001-Implement-ANY_VALUE-aggregate.v03.patch" Content-Transfer-Encoding: base64 RnJvbSA5Y2YyYzViNTZlYTM4ZDMwODBjMGNiOWY4ZWY5ZTYyMjlkODY5NmI0IE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBFaXNlbnRyYXV0IDxwZXRlckBlaXNlbnRy YXV0Lm9yZz4KRGF0ZTogU2F0LCA5IEFwciAyMDIyIDAwOjA3OjM4ICswMjAwClN1YmplY3Q6 IFtQQVRDSF0gSW1wbGVtZW50IEFOWV9WQUxVRSBhZ2dyZWdhdGUKClNRTDoyMDIzIGRlZmlu ZXMgYW4gQU5ZX1ZBTFVFIGFnZ3JlZ2F0ZSB3aG9zZSBwdXJwb3NlIGlzIHRvIGVtaXQgYW4K aW1wbGVtZW50YXRpb24tZGVwZW5kZW50IChpLmUuIG5vbi1kZXRlcm1pbmlzdGljKSB2YWx1 ZSBmcm9tIHRoZQphZ2dyZWdhdGVkIHJvd3MuCi0tLQogZG9jL3NyYy9zZ21sL2Z1bmMuc2dt bCAgICAgICAgICAgICAgICAgICB8IDE0ICsrKysrKysrKysrKysrCiBzcmMvYmFja2VuZC9j YXRhbG9nL3NxbF9mZWF0dXJlcy50eHQgICAgIHwgIDEgKwogc3JjL2JhY2tlbmQvdXRpbHMv YWR0L21pc2MuYyAgICAgICAgICAgICB8IDEzICsrKysrKysrKysrKysKIHNyYy9pbmNsdWRl L2NhdGFsb2cvcGdfYWdncmVnYXRlLmRhdCAgICAgfCAgNCArKysrCiBzcmMvaW5jbHVkZS9j YXRhbG9nL3BnX3Byb2MuZGF0ICAgICAgICAgIHwgIDggKysrKysrKysKIHNyYy90ZXN0L3Jl Z3Jlc3MvZXhwZWN0ZWQvYWdncmVnYXRlcy5vdXQgfCAyNCArKysrKysrKysrKysrKysrKysr KysrKysKIHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2FnZ3JlZ2F0ZXMuc3FsICAgICAgfCAgNiAr KysrKysKIDcgZmlsZXMgY2hhbmdlZCwgNzAgaW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBh L2RvYy9zcmMvc2dtbC9mdW5jLnNnbWwgYi9kb2Mvc3JjL3NnbWwvZnVuYy5zZ21sCmluZGV4 IGI4ZGFjOWVmNDYuLjhmZjlkZWNmZWMgMTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9mdW5j LnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL2Z1bmMuc2dtbApAQCAtMTk3MzUsNiArMTk3MzUs MjAgQEAgU0VMRUNUIE5VTExJRih2YWx1ZSwgJyhub25lKScpIC4uLgogICAgICA8L3RoZWFk PgogCiAgICAgIDx0Ym9keT4KKyAgICAgIDxyb3c+CisgICAgICAgPGVudHJ5IHJvbGU9ImZ1 bmNfdGFibGVfZW50cnkiPjxwYXJhIHJvbGU9ImZ1bmNfc2lnbmF0dXJlIj4KKyAgICAgICAg PGluZGV4dGVybT4KKyAgICAgICAgIDxwcmltYXJ5PmFueV92YWx1ZTwvcHJpbWFyeT4KKyAg ICAgICAgPC9pbmRleHRlcm0+CisgICAgICAgIDxmdW5jdGlvbj5hbnlfdmFsdWU8L2Z1bmN0 aW9uPiAoIDx0eXBlPmFueWVsZW1lbnQ8L3R5cGU+ICkKKyAgICAgICAgPHJldHVybnZhbHVl PjxyZXBsYWNlYWJsZT5zYW1lIGFzIGlucHV0IHR5cGU8L3JlcGxhY2VhYmxlPjwvcmV0dXJu dmFsdWU+CisgICAgICAgPC9wYXJhPgorICAgICAgIDxwYXJhPgorICAgICAgICBDaG9vc2Vz IGEgbm9uLWRldGVybWluaXN0aWMgdmFsdWUgZnJvbSB0aGUgbm9uLW51bGwgaW5wdXQgdmFs dWVzLgorICAgICAgIDwvcGFyYT48L2VudHJ5PgorICAgICAgIDxlbnRyeT5ZZXM8L2VudHJ5 PgorICAgICAgPC9yb3c+CisKICAgICAgIDxyb3c+CiAgICAgICAgPGVudHJ5IHJvbGU9ImZ1 bmNfdGFibGVfZW50cnkiPjxwYXJhIHJvbGU9ImZ1bmNfc2lnbmF0dXJlIj4KICAgICAgICAg PGluZGV4dGVybT4KZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvc3FsX2ZlYXR1 cmVzLnR4dCBiL3NyYy9iYWNrZW5kL2NhdGFsb2cvc3FsX2ZlYXR1cmVzLnR4dAppbmRleCBh YmFkMjE2YjdlLi5kZmQzODgyODAxIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jYXRhbG9n L3NxbF9mZWF0dXJlcy50eHQKKysrIGIvc3JjL2JhY2tlbmQvY2F0YWxvZy9zcWxfZmVhdHVy ZXMudHh0CkBAIC01MjAsNiArNTIwLDcgQEAgVDYyMglUcmlnb25vbWV0cmljIGZ1bmN0aW9u cwkJCVlFUwogVDYyMwlHZW5lcmFsIGxvZ2FyaXRobSBmdW5jdGlvbnMJCQlZRVMJCiBUNjI0 CUNvbW1vbiBsb2dhcml0aG0gZnVuY3Rpb25zCQkJWUVTCQogVDYyNQlMSVNUQUdHCQkJTk8J CitUNjI2CUFOWV9WQUxVRQkJCVlFUwkKIFQ2MzEJSU4gcHJlZGljYXRlIHdpdGggb25lIGxp c3QgZWxlbWVudAkJCVlFUwkKIFQ2NDEJTXVsdGlwbGUgY29sdW1uIGFzc2lnbm1lbnQJCQlO Twlvbmx5IHNvbWUgc3ludGF4IHZhcmlhbnRzIHN1cHBvcnRlZAogVDY1MQlTUUwtc2NoZW1h IHN0YXRlbWVudHMgaW4gU1FMIHJvdXRpbmVzCQkJWUVTCQpkaWZmIC0tZ2l0IGEvc3JjL2Jh Y2tlbmQvdXRpbHMvYWR0L21pc2MuYyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9taXNjLmMK aW5kZXggMjIwZGRiOGMwMS4uYTkyNTFmOTc3ZSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQv dXRpbHMvYWR0L21pc2MuYworKysgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvbWlzYy5jCkBA IC0xMDQxLDMgKzEwNDEsMTYgQEAgcGdfZ2V0X3JlcGxpY2FfaWRlbnRpdHlfaW5kZXgoUEdf RlVOQ1RJT05fQVJHUykKIAllbHNlCiAJCVBHX1JFVFVSTl9OVUxMKCk7CiB9CisKKy8qCisg KiBUcmFuc2l0aW9uIGZ1bmN0aW9uIGZvciB0aGUgQU5ZX1ZBTFVFIGFnZ3JlZ2F0ZQorICoK KyAqIEN1cnJlbnRseSB0aGlzIGp1c3QgcmV0dXJucyB0aGUgZmlyc3QgdmFsdWUsIGJ1dCBp biB0aGUgZnV0dXJlIGl0IG1pZ2h0IGJlCisgKiBhYmxlIHRvIHNpZ25hbCB0byB0aGUgYWdn cmVnYXRlIHRoYXQgaXQgZG9lcyBub3QgbmVlZCB0byBiZSBjYWxsZWQgYW55bW9yZS4KKyAq LworRGF0dW0KK2FueV92YWx1ZV90cmFucyhQR19GVU5DVElPTl9BUkdTKQoreworCVBHX1JF VFVSTl9EQVRVTShQR19HRVRBUkdfREFUVU0oMCkpOworfQorCmRpZmYgLS1naXQgYS9zcmMv aW5jbHVkZS9jYXRhbG9nL3BnX2FnZ3JlZ2F0ZS5kYXQgYi9zcmMvaW5jbHVkZS9jYXRhbG9n L3BnX2FnZ3JlZ2F0ZS5kYXQKaW5kZXggOGM5NTc0MzdlYS4uYWFjNjBkZWU1OCAxMDA2NDQK LS0tIGEvc3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19hZ2dyZWdhdGUuZGF0CisrKyBiL3NyYy9p bmNsdWRlL2NhdGFsb2cvcGdfYWdncmVnYXRlLmRhdApAQCAtNjI1LDQgKzYyNSw4IEBACiAg IGFnZ2ZpbmFsZm4gPT4gJ2RlbnNlX3JhbmtfZmluYWwnLCBhZ2dmaW5hbGV4dHJhID0+ICd0 JywgYWdnZmluYWxtb2RpZnkgPT4gJ3cnLAogICBhZ2dtZmluYWxtb2RpZnkgPT4gJ3cnLCBh Z2d0cmFuc3R5cGUgPT4gJ2ludGVybmFsJyB9LAogCisjIGFueV92YWx1ZQoreyBhZ2dmbm9p ZCA9PiAnYW55X3ZhbHVlKGFueWVsZW1lbnQpJywgYWdndHJhbnNmbiA9PiAnYW55X3ZhbHVl X3RyYW5zJywKKyAgYWdnY29tYmluZWZuID0+ICdhbnlfdmFsdWVfdHJhbnMnLCBhZ2d0cmFu c3R5cGUgPT4gJ2FueWVsZW1lbnQnIH0sCisKIF0KZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRl L2NhdGFsb2cvcGdfcHJvYy5kYXQgYi9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3Byb2MuZGF0 CmluZGV4IDg2ZWI4ZThjNTguLjk1ZTc2MDQ0MGUgMTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRl L2NhdGFsb2cvcGdfcHJvYy5kYXQKKysrIGIvc3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19wcm9j LmRhdApAQCAtMTE4OTEsNCArMTE4OTEsMTIgQEAKICAgcHJvcmV0dHlwZSA9PiAnYnl0ZWEn LCBwcm9hcmd0eXBlcyA9PiAncGdfYnJpbl9taW5tYXhfbXVsdGlfc3VtbWFyeScsCiAgIHBy b3NyYyA9PiAnYnJpbl9taW5tYXhfbXVsdGlfc3VtbWFyeV9zZW5kJyB9LAogCit7IG9pZCA9 PiAnODk4MScsIGRlc2NyID0+ICdhcmJpdHJhcnkgdmFsdWUgZnJvbSBhbW9uZyBpbnB1dCB2 YWx1ZXMnLAorICBwcm9uYW1lID0+ICdhbnlfdmFsdWUnLCBwcm9raW5kID0+ICdhJywgcHJv aXNzdHJpY3QgPT4gJ2YnLAorICBwcm9yZXR0eXBlID0+ICdhbnllbGVtZW50JywgcHJvYXJn dHlwZXMgPT4gJ2FueWVsZW1lbnQnLAorICBwcm9zcmMgPT4gJ2FnZ3JlZ2F0ZV9kdW1teScg fSwKK3sgb2lkID0+ICc4OTgyJywgZGVzY3IgPT4gJ2FueV92YWx1ZSB0cmFuc2l0aW9uIGZ1 bmN0aW9uJywKKyAgcHJvbmFtZSA9PiAnYW55X3ZhbHVlX3RyYW5zJywgcHJvcmV0dHlwZSA9 PiAnYW55ZWxlbWVudCcsIHByb2FyZ3R5cGVzID0+ICdhbnllbGVtZW50IGFueWVsZW1lbnQn LAorICBwcm9zcmMgPT4gJ2FueV92YWx1ZV90cmFucycgfSwKKwogXQpkaWZmIC0tZ2l0IGEv c3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9hZ2dyZWdhdGVzLm91dCBiL3NyYy90ZXN0L3Jl Z3Jlc3MvZXhwZWN0ZWQvYWdncmVnYXRlcy5vdXQKaW5kZXggYWUzYjkwNTMzMS4uYjI0MGVm NTIyYiAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9hZ2dyZWdhdGVz Lm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2FnZ3JlZ2F0ZXMub3V0CkBA IC0yNSw2ICsyNSwyNCBAQCBTRUxFQ1QgYXZnKGEpIEFTIGF2Z18zMiBGUk9NIGFnZ3Rlc3Qg V0hFUkUgYSA8IDEwMDsKICAzMi42NjY2NjY2NjY2NjY2NjY3CiAoMSByb3cpCiAKK1NFTEVD VCBhbnlfdmFsdWUodikgRlJPTSAoVkFMVUVTICgxKSkgQVMgdiAodik7CisgYW55X3ZhbHVl IAorLS0tLS0tLS0tLS0KKyAgICAgICAgIDEKKygxIHJvdykKKworU0VMRUNUIGFueV92YWx1 ZSh2KSBGUk9NIChWQUxVRVMgKE5VTEwpKSBBUyB2ICh2KTsKKyBhbnlfdmFsdWUgCistLS0t LS0tLS0tLQorIAorKDEgcm93KQorCitTRUxFQ1QgYW55X3ZhbHVlKHYpIEZST00gKFZBTFVF UyAoYXJyYXlbJ2hlbGxvJywgJ3dvcmxkJ10pKSBBUyB2ICh2KTsKKyAgIGFueV92YWx1ZSAg IAorLS0tLS0tLS0tLS0tLS0tCisge2hlbGxvLHdvcmxkfQorKDEgcm93KQorCiAtLSBJbiA3 LjEsIGF2ZyhmbG9hdDQpIGlzIGNvbXB1dGVkIHVzaW5nIGZsb2F0OCBhcml0aG1ldGljLgog LS0gUm91bmQgdGhlIHJlc3VsdCB0byAzIGRpZ2l0cyB0byBhdm9pZCBwbGF0Zm9ybS1zcGVj aWZpYyByZXN1bHRzLgogU0VMRUNUIGF2ZyhiKTo6bnVtZXJpYygxMCwzKSBBUyBhdmdfMTA3 Xzk0MyBGUk9NIGFnZ3Rlc3Q7CkBAIC0xOTEwLDYgKzE5MjgsMTIgQEAgZnJvbSAodmFsdWVz ICgnYScsICdiJykpIEFTIHYoZm9vLGJhcik7CiAgYQogKDEgcm93KQogCitTRUxFQ1QgYW55 X3ZhbHVlKHYpIEZJTFRFUiAoV0hFUkUgdiA+IDIpIEZST00gKFZBTFVFUyAoMSksICgyKSwg KDMpKSBBUyB2ICh2KTsKKyBhbnlfdmFsdWUgCistLS0tLS0tLS0tLQorICAgICAgICAgMwor KDEgcm93KQorCiAtLSBvdXRlciByZWZlcmVuY2UgaW4gRklMVEVSIChQb3N0Z3JlU1FMIGV4 dGVuc2lvbikKIHNlbGVjdCAoc2VsZWN0IGNvdW50KCopCiAgICAgICAgIGZyb20gKHZhbHVl cyAoMSkpIHQwKGlubmVyX2MpKQpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwv YWdncmVnYXRlcy5zcWwgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9hZ2dyZWdhdGVzLnNxbApp bmRleCA1MTRlM2IyYjM5Li40YzBmZDBkNDUyIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdy ZXNzL3NxbC9hZ2dyZWdhdGVzLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9hZ2dy ZWdhdGVzLnNxbApAQCAtMjQsNiArMjQsMTAgQEAgU0VMRUNUIGF2Zyhmb3VyKSBBUyBhdmdf MSBGUk9NIG9uZWs7CiAKIFNFTEVDVCBhdmcoYSkgQVMgYXZnXzMyIEZST00gYWdndGVzdCBX SEVSRSBhIDwgMTAwOwogCitTRUxFQ1QgYW55X3ZhbHVlKHYpIEZST00gKFZBTFVFUyAoMSkp IEFTIHYgKHYpOworU0VMRUNUIGFueV92YWx1ZSh2KSBGUk9NIChWQUxVRVMgKE5VTEwpKSBB UyB2ICh2KTsKK1NFTEVDVCBhbnlfdmFsdWUodikgRlJPTSAoVkFMVUVTIChhcnJheVsnaGVs bG8nLCAnd29ybGQnXSkpIEFTIHYgKHYpOworCiAtLSBJbiA3LjEsIGF2ZyhmbG9hdDQpIGlz IGNvbXB1dGVkIHVzaW5nIGZsb2F0OCBhcml0aG1ldGljLgogLS0gUm91bmQgdGhlIHJlc3Vs dCB0byAzIGRpZ2l0cyB0byBhdm9pZCBwbGF0Zm9ybS1zcGVjaWZpYyByZXN1bHRzLgogCkBA IC03MzMsNiArNzM3LDggQEAgaGF2aW5nIGV4aXN0cyAoc2VsZWN0IDEgZnJvbSBvbmVrIGIg d2hlcmUgc3VtKGRpc3RpbmN0IGEuZm91cikgPSBiLmZvdXIpOwogc2VsZWN0IG1heChmb28g Q09MTEFURSAiQyIpIGZpbHRlciAod2hlcmUgKGJhciBjb2xsYXRlICJQT1NJWCIpID4gJzAn KQogZnJvbSAodmFsdWVzICgnYScsICdiJykpIEFTIHYoZm9vLGJhcik7CiAKK1NFTEVDVCBh bnlfdmFsdWUodikgRklMVEVSIChXSEVSRSB2ID4gMikgRlJPTSAoVkFMVUVTICgxKSwgKDIp LCAoMykpIEFTIHYgKHYpOworCiAtLSBvdXRlciByZWZlcmVuY2UgaW4gRklMVEVSIChQb3N0 Z3JlU1FMIGV4dGVuc2lvbikKIHNlbGVjdCAoc2VsZWN0IGNvdW50KCopCiAgICAgICAgIGZy b20gKHZhbHVlcyAoMSkpIHQwKGlubmVyX2MpKQotLSAKMi4zNC4xCgo= --------------LqR1zkqg6t0rWAJKQK2qiy7j--