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 1kagq3-0007PZ-NU for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Nov 2020 15:06:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kagq2-00014a-Mh for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Nov 2020 15:06:10 +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 1kagq2-00014S-FT for pgsql-bugs@lists.postgresql.org; Thu, 05 Nov 2020 15:06:10 +0000 Received: from dd27502.kasserver.com ([85.13.146.49]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kagpy-0006wU-Em for pgsql-bugs@lists.postgresql.org; Thu, 05 Nov 2020 15:06:10 +0000 Received: from [192.168.0.52] (ip-37-201-154-176.hsi13.unitymediagroup.de [37.201.154.176]) by dd27502.kasserver.com (Postfix) with ESMTPSA id 91C8B60C76EB for ; Thu, 5 Nov 2020 16:06:04 +0100 (CET) Subject: Re: Wrong result for comparing ROW(...) with IS NOT NULL To: pgsql-bugs@lists.postgresql.org References: <21ff8e9c-627a-f949-fb00-a41b9ddcc9d3@technowledgy.de> <2ba24707-e7ba-2ccb-7269-71202a400a70@technowledgy.de> <4a853592-a43b-b677-5026-212196b6e9c2@technowledgy.de> From: Wolfgang Walther Message-ID: Date: Thu, 5 Nov 2020 16:06:01 +0100 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:68.0) Gecko/20100101 Thunderbird/68.12.1 MIME-Version: 1.0 In-Reply-To: <4a853592-a43b-b677-5026-212196b6e9c2@technowledgy.de> Content-Type: multipart/mixed; boundary="------------0FDBBAF46043D08A362AE04B" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------0FDBBAF46043D08A362AE04B Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Wolfgang Walther: > Thank you, that explains it very well. > > When I realized there was something unexpected going on, I was looking > at all the ROW() syntax in the docs and I found this (as mentioned > upthread): > > https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS > > > It might be worth it to either add another example for IS NOT NULL, like > > SELECT ROW(table.*) IS NOT NULL FROM table;  -- detect all-non-null rows > > or add a link to section 9.2, that you mentioned. Or do both. > > > > Another thing that could be improved: > > Below that example there is a link to section 9.24. The link is: > > https://www.postgresql.org/docs/current/functions-comparisons.html > > The link you gave me to section 9.2 is (replaced 13 with current): > > https://www.postgresql.org/docs/current/functions-comparison.html > > Like really? The only difference is the "s" in comparison(s). That > confused me at first for a bit, because I thought I had read your link > already :) > > I think that link for 9.24 could be much better chosen. > row-array-comparisons.html would match the content. Attached are some patches. Best Wolfgang --------------0FDBBAF46043D08A362AE04B Content-Type: text/plain; charset=UTF-8; name="0001-doc-Add-IS-NOT-NULL-example-and-link-to-section-9.2-.patch" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename*0="0001-doc-Add-IS-NOT-NULL-example-and-link-to-section-9.2-.pa"; filename*1="tch" PkZyb20gNzU3MDRmNDE5NmUxZGQ3ZDkyZGY1OGNiOThhY2E4MDNlNjkxM2U1MSBNb24gU2Vw IDE3IDAwOjAwOjAwIDIwMDENCkZyb206IFdvbGZnYW5nIFdhbHRoZXIgPHdhbHRoZXJAdGVj aG5vd2xlZGd5LmRlPg0KRGF0ZTogVGh1LCA1IE5vdiAyMDIwIDE1OjQ1OjM0ICswMTAwDQpT dWJqZWN0OiBbUEFUQ0ggMS8yXSBkb2M6IEFkZCBJUyBOT1QgTlVMTCBleGFtcGxlIGFuZCBs aW5rIHRvIHNlY3Rpb24gOS4yIHRvDQogNC4yLjEzIFJvdyBDb25zdHJ1Y3RvcnMNCg0KLS0t DQogZG9jL3NyYy9zZ21sL3N5bnRheC5zZ21sIHwgNCArKystDQogMSBmaWxlIGNoYW5nZWQs IDMgaW5zZXJ0aW9ucygrKSwgMSBkZWxldGlvbigtKQ0KDQpkaWZmIC0tZ2l0IGEvZG9jL3Ny Yy9zZ21sL3N5bnRheC5zZ21sIGIvZG9jL3NyYy9zZ21sL3N5bnRheC5zZ21sDQppbmRleCAz ZmRkODc4MjNlLi44YjVhYTMwYWI0IDEwMDY0NA0KLS0tIGEvZG9jL3NyYy9zZ21sL3N5bnRh eC5zZ21sDQorKysgYi9kb2Mvc3JjL3NnbWwvc3ludGF4LnNnbWwNCkBAIC0yNDM0LDggKzI0 MzQsMTAgQEAgU0VMRUNUIGdldGYxKENBU1QoUk9XKDExLCd0aGlzIGlzIGEgdGVzdCcsMi41 KSBBUyBteXJvd3R5cGUpKTsNCiBTRUxFQ1QgUk9XKDEsMi41LCd0aGlzIGlzIGEgdGVzdCcp ID0gUk9XKDEsIDMsICdub3QgdGhlIHNhbWUnKTsNCiANCiBTRUxFQ1QgUk9XKHRhYmxlLiop IElTIE5VTEwgRlJPTSB0YWJsZTsgIC0tIGRldGVjdCBhbGwtbnVsbCByb3dzDQorDQorU0VM RUNUIFJPVyh0YWJsZS4qKSBJUyBOT1QgTlVMTCBGUk9NIHRhYmxlOyAtLSBkZXRlY3QgYWxs LW5vbi1udWxsIHJvd3MNCiA8L3Byb2dyYW1saXN0aW5nPg0KLSAgIEZvciBtb3JlIGRldGFp bCBzZWUgPHhyZWYgbGlua2VuZD0iZnVuY3Rpb25zLWNvbXBhcmlzb25zIi8+Lg0KKyAgIEZv ciBtb3JlIGRldGFpbCBzZWUgPHhyZWYgbGlua2VuZD0iZnVuY3Rpb25zLWNvbXBhcmlzb25z Ii8+IChjb21wYXJpc29uKSBhbmQgPHhyZWYgbGlua2VuZD0iZnVuY3Rpb25zLWNvbXBhcmlz b24iLz4gKDxsaXRlcmFsPklTIFtOT1RdIE5VTEw8L2xpdGVyYWw+KS4NCiAgICBSb3cgY29u c3RydWN0b3JzIGNhbiBhbHNvIGJlIHVzZWQgaW4gY29ubmVjdGlvbiB3aXRoIHN1YnF1ZXJp ZXMsDQogICAgYXMgZGlzY3Vzc2VkIGluIDx4cmVmIGxpbmtlbmQ9ImZ1bmN0aW9ucy1zdWJx dWVyeSIvPi4NCiAgIDwvcGFyYT4NCi0tIA0KMi4yOS4yDQoNCg== --------------0FDBBAF46043D08A362AE04B Content-Type: text/plain; charset=UTF-8; name="0002-doc-change-id-of-section-9.24-to-functions-row-array.patch" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename*0="0002-doc-change-id-of-section-9.24-to-functions-row-array.pa"; filename*1="tch" PkZyb20gYTdjNTVlYzhjOTNiMWI3YmNlODg1ZjA5OWI3Y2E3YzlhYWJmMmQ5MiBNb24gU2Vw IDE3IDAwOjAwOjAwIDIwMDENCkZyb206IFdvbGZnYW5nIFdhbHRoZXIgPHdhbHRoZXJAdGVj aG5vd2xlZGd5LmRlPg0KRGF0ZTogVGh1LCA1IE5vdiAyMDIwIDE1OjQ4OjI3ICswMTAwDQpT dWJqZWN0OiBbUEFUQ0ggMi8yXSBkb2M6IGNoYW5nZSBpZCBvZiBzZWN0aW9uIDkuMjQgdG8N CiBmdW5jdGlvbnMtcm93LWFycmF5LWNvbXBhcmlzb25zDQoNClNlY3Rpb24gOS4yIGFuZCA5 LjI0IGhhdmUgbmVhcmx5IGlkZW50aWNhbCBpZHMgaW4gZnVuY3Rpb25zLWNvbXBhcmlzb24N CmFuZCBmdW5jdGlvbnMtY29tcGFyaXNvbnMuDQotLS0NCiBkb2Mvc3JjL3NnbWwvYXJyYXku c2dtbCAgfCAyICstDQogZG9jL3NyYy9zZ21sL2Z1bmMuc2dtbCAgIHwgNCArKy0tDQogZG9j L3NyYy9zZ21sL3N5bnRheC5zZ21sIHwgMiArLQ0KIDMgZmlsZXMgY2hhbmdlZCwgNCBpbnNl cnRpb25zKCspLCA0IGRlbGV0aW9ucygtKQ0KDQpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21s L2FycmF5LnNnbWwgYi9kb2Mvc3JjL3NnbWwvYXJyYXkuc2dtbA0KaW5kZXggNTYxODViOWIw My4uNmQ4MWU1OGVmNCAxMDA2NDQNCi0tLSBhL2RvYy9zcmMvc2dtbC9hcnJheS5zZ21sDQor KysgYi9kb2Mvc3JjL3NnbWwvYXJyYXkuc2dtbA0KQEAgLTYxNiw3ICs2MTYsNyBAQCBTRUxF Q1QgKiBGUk9NIHNhbF9lbXAgV0hFUkUgcGF5X2J5X3F1YXJ0ZXJbMV0gPSAxMDAwMCBPUg0K IA0KICAgSG93ZXZlciwgdGhpcyBxdWlja2x5IGJlY29tZXMgdGVkaW91cyBmb3IgbGFyZ2Ug YXJyYXlzLCBhbmQgaXMgbm90DQogICBoZWxwZnVsIGlmIHRoZSBzaXplIG9mIHRoZSBhcnJh eSBpcyB1bmtub3duLiBBbiBhbHRlcm5hdGl2ZSBtZXRob2QgaXMNCi0gIGRlc2NyaWJlZCBp biA8eHJlZiBsaW5rZW5kPSJmdW5jdGlvbnMtY29tcGFyaXNvbnMiLz4uIFRoZSBhYm92ZQ0K KyAgZGVzY3JpYmVkIGluIDx4cmVmIGxpbmtlbmQ9ImZ1bmN0aW9ucy1yb3ctYXJyYXktY29t cGFyaXNvbnMiLz4uIFRoZSBhYm92ZQ0KICAgcXVlcnkgY291bGQgYmUgcmVwbGFjZWQgYnk6 DQogDQogPHByb2dyYW1saXN0aW5nPg0KZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9mdW5j LnNnbWwgYi9kb2Mvc3JjL3NnbWwvZnVuYy5zZ21sDQppbmRleCBiZjYwMDRmMzIxLi4zOWVj NzZjNDg3IDEwMDY0NA0KLS0tIGEvZG9jL3NyYy9zZ21sL2Z1bmMuc2dtbA0KKysrIGIvZG9j L3NyYy9zZ21sL2Z1bmMuc2dtbA0KQEAgLTIwMDk5LDcgKzIwMDk5LDcgQEAgV0hFUkUgRVhJ U1RTIChTRUxFQ1QgMSBGUk9NIHRhYjIgV0hFUkUgY29sMiA9IHRhYjEuY29sMik7DQogIDwv c2VjdDE+DQogDQogDQotIDxzZWN0MSBpZD0iZnVuY3Rpb25zLWNvbXBhcmlzb25zIj4NCisg PHNlY3QxIGlkPSJmdW5jdGlvbnMtcm93LWFycmF5LWNvbXBhcmlzb25zIj4NCiAgIDx0aXRs ZT5Sb3cgYW5kIEFycmF5IENvbXBhcmlzb25zPC90aXRsZT4NCiANCiAgIDxpbmRleHRlcm0+ DQpAQCAtMjAxNjEsNyArMjAxNjEsNyBAQCBXSEVSRSBFWElTVFMgKFNFTEVDVCAxIEZST00g dGFiMiBXSEVSRSBjb2wyID0gdGFiMS5jb2wyKTsNCiAgICBCb29sZWFuICh0cnVlL2ZhbHNl KSByZXN1bHRzLg0KICAgPC9wYXJhPg0KIA0KLSAgPHNlY3QyIGlkPSJmdW5jdGlvbnMtY29t cGFyaXNvbnMtaW4tc2NhbGFyIj4NCisgIDxzZWN0MiBpZD0iZnVuY3Rpb25zLXJvdy1hcnJh eS1jb21wYXJpc29ucy1pbi1zY2FsYXIiPg0KICAgIDx0aXRsZT48bGl0ZXJhbD5JTjwvbGl0 ZXJhbD48L3RpdGxlPg0KIA0KIDxzeW5vcHNpcz4NCmRpZmYgLS1naXQgYS9kb2Mvc3JjL3Nn bWwvc3ludGF4LnNnbWwgYi9kb2Mvc3JjL3NnbWwvc3ludGF4LnNnbWwNCmluZGV4IDhiNWFh MzBhYjQuLjMxYjFkYTgwMTEgMTAwNjQ0DQotLS0gYS9kb2Mvc3JjL3NnbWwvc3ludGF4LnNn bWwNCisrKyBiL2RvYy9zcmMvc2dtbC9zeW50YXguc2dtbA0KQEAgLTI0MzcsNyArMjQzNyw3 IEBAIFNFTEVDVCBST1codGFibGUuKikgSVMgTlVMTCBGUk9NIHRhYmxlOyAgLS0gZGV0ZWN0 IGFsbC1udWxsIHJvd3MNCiANCiBTRUxFQ1QgUk9XKHRhYmxlLiopIElTIE5PVCBOVUxMIEZS T00gdGFibGU7IC0tIGRldGVjdCBhbGwtbm9uLW51bGwgcm93cw0KIDwvcHJvZ3JhbWxpc3Rp bmc+DQotICAgRm9yIG1vcmUgZGV0YWlsIHNlZSA8eHJlZiBsaW5rZW5kPSJmdW5jdGlvbnMt Y29tcGFyaXNvbnMiLz4gKGNvbXBhcmlzb24pIGFuZCA8eHJlZiBsaW5rZW5kPSJmdW5jdGlv bnMtY29tcGFyaXNvbiIvPiAoPGxpdGVyYWw+SVMgW05PVF0gTlVMTDwvbGl0ZXJhbD4pLg0K KyAgIEZvciBtb3JlIGRldGFpbCBzZWUgPHhyZWYgbGlua2VuZD0iZnVuY3Rpb25zLXJvdy1h cnJheS1jb21wYXJpc29ucyIvPiAoY29tcGFyaXNvbikgYW5kIDx4cmVmIGxpbmtlbmQ9ImZ1 bmN0aW9ucy1jb21wYXJpc29uIi8+ICg8bGl0ZXJhbD5JUyBbTk9UXSBOVUxMPC9saXRlcmFs PikuDQogICAgUm93IGNvbnN0cnVjdG9ycyBjYW4gYWxzbyBiZSB1c2VkIGluIGNvbm5lY3Rp b24gd2l0aCBzdWJxdWVyaWVzLA0KICAgIGFzIGRpc2N1c3NlZCBpbiA8eHJlZiBsaW5rZW5k PSJmdW5jdGlvbnMtc3VicXVlcnkiLz4uDQogICA8L3BhcmE+DQotLSANCjIuMjkuMg0KDQo= --------------0FDBBAF46043D08A362AE04B--