MIME-Version: 1.0
References: <17502-281a7aaacfaa872a@postgresql.org>
 <CABUevEz7t3OWxaSSVSHOxa5KcYioOk=yRwoL3iMViyZQ8m2wAw@mail.gmail.com>
 <CAGckUK2GLF=d9J5ErEWgK5x8ECqCw4equnq3jEzrqtfJw+iHYw@mail.gmail.com>
 <CAApHDvoR4BXm7oxSrYhpEAFOZ-qMPkkYnn14y6sxtPf=5w5OOw@mail.gmail.com>
 <3722616.1653877139@sss.pgh.pa.us>
 <CAApHDvp5dapiG2PBSYz+Yps+LGEwr8bY_NoUr1+=rn9oL+NfPA@mail.gmail.com>
 <3727090.1653878700@sss.pgh.pa.us>
 <CAApHDvpacAbOTYeTPCgKnafUOvJT0=dFP0q+eVhOhB2Yaa8+QQ@mail.gmail.com>
 <3730546.1653879769@sss.pgh.pa.us>
In-Reply-To: <3730546.1653879769@sss.pgh.pa.us>
From: David Rowley <dgrowleyml@gmail.com>
Date: Mon, 30 May 2022 17:24:33 +1200
Message-ID: 
 <CAApHDvr94vSvxm4j_ykMBapFLXC8H_ntLg7oD5fhqfqR0x-snA@mail.gmail.com>
Subject: Re: BUG #17502: View based on window functions returns wrong results
 when queried
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: =?UTF-8?Q?Daniel_Farka=C5=A1?= <daniel.farkas@datoris.com>,
	Magnus Hagander <magnus@hagander.net>,
	PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000d4e90205e033dfec"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvr94vSvxm4j_ykMBapFLXC8H_ntLg7oD5fhqfqR0x-snA%40mail.gmail.com>
Precedence: bulk

--000000000000d4e90205e033dfec
Content-Type: text/plain; charset="UTF-8"

On Mon, 30 May 2022 at 15:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm not necessarily against adding the prohibition in the back
> branches.  However, if this has been wrong since 10.x (if not
> further back) then it seems like few people are tripping over
> the inconsistency.

Yeah, perhaps. But we have received a report from 1 person and I think
it seems strange to adopt a policy that we require multiple bug
reports for the same issue before we consider fixing.

FWIW, just to demonstrate what a fix could look like, I've attached a
patch. I'm not planning to commit it if you really think we shouldn't
be fixing it.

The patch also does nothing for the weirdness that I described in the
DISTINCT ON case earlier in this thread.

David

--000000000000d4e90205e033dfec
Content-Type: text/plain; charset="US-ASCII";
 name="dont_remove_srf_windowfuncs.patch"
Content-Disposition: attachment;
	filename="dont_remove_srf_windowfuncs.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_l3sa75b10>
X-Attachment-Id: f_l3sa75b10

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2FsbHBhdGhzLmMgYi9zcmMv
YmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9hbGxwYXRocy5jCmluZGV4IDdhYzExNmE3OTEuLjVkYWI0
MGY2ZmYgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2FsbHBhdGhzLmMK
KysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvYWxscGF0aHMuYwpAQCAtMzkzMyw2ICsz
OTMzLDQ0IEBAIHJlY3Vyc2VfcHVzaF9xdWFsKE5vZGUgKnNldE9wLCBRdWVyeSAqdG9wcXVlcnks
CiAgKgkJCVNJTVBMSUZZSU5HIFNVQlFVRVJZIFRBUkdFVExJU1RTCiAgKioqKioqKioqKioqKioq
KioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq
KioqKiovCiAKKy8qCisgKiBoYXNfc2V0X3JldHVybmluZ193aW5kb3dfY2xhdXNlcworICoJCVJl
dHVybnMgdHJ1ZSBpZiAnbm9kZScgY29udGFpbnMgYW55IFdpbmRvd0Z1bmNzIHdoaWNoIHJlZmVy
ZW5jZSBhCisgKgkJV2luZG93Q2xhdXNlIHdoZXJlIHRoZSBQQVJUSVRJT04gQlkgb3IgT1JERVIg
QlkgY2xhdXNlIGNvbnRhaW5zIGFueQorICoJCXNldCByZXR1cm5pbmcgZnVuY3Rpb25zLgorICov
CitzdGF0aWMgYm9vbAoraGFzX3NldF9yZXR1cm5pbmdfd2luZG93X2NsYXVzZXMoUXVlcnkgKnN1
YnF1ZXJ5LCBOb2RlICpub2RlKQoreworCVdpbmRvd0Z1bmNMaXN0cyAqd2ZsaXN0czsKKworCXdm
bGlzdHMgPSBmaW5kX3dpbmRvd19mdW5jdGlvbnMobm9kZSwKKwkJCQkJCQkJCWxpc3RfbGVuZ3Ro
KHN1YnF1ZXJ5LT53aW5kb3dDbGF1c2UpKTsKKworCWlmICh3Zmxpc3RzLT5udW1XaW5kb3dGdW5j
cyA9PSAwKQorCQlyZXR1cm4gZmFsc2U7CisKKwlmb3IgKGludCBpID0gMTsgaSA8PSB3Zmxpc3Rz
LT5tYXhXaW5SZWY7IGkrKykKKwl7CisJCVdpbmRvd0NsYXVzZSAqd2NsYXVzZTsKKworCQlpZiAo
d2ZsaXN0cy0+d2luZG93RnVuY3NbaV0gPT0gTklMKQorCQkJY29udGludWU7CisKKwkJd2NsYXVz
ZSA9IChXaW5kb3dDbGF1c2UgKikgbGlzdF9udGgoc3VicXVlcnktPndpbmRvd0NsYXVzZSwgaSAt
IDEpOworCisJCWlmIChleHByZXNzaW9uX3JldHVybnNfc2V0KChOb2RlICopIGdldF9zb3J0Z3Jv
dXBsaXN0X2V4cHJzKHdjbGF1c2UtPnBhcnRpdGlvbkNsYXVzZSwKKwkJCQkJCQkJCQkJCQkJCQkJ
c3VicXVlcnktPnRhcmdldExpc3QpKSkKKwkJCXJldHVybiB0cnVlOworCisJCWlmIChleHByZXNz
aW9uX3JldHVybnNfc2V0KChOb2RlICopIGdldF9zb3J0Z3JvdXBsaXN0X2V4cHJzKHdjbGF1c2Ut
Pm9yZGVyQ2xhdXNlLAorCQkJCQkJCQkJCQkJCQkJCSAgIHN1YnF1ZXJ5LT50YXJnZXRMaXN0KSkp
CisJCQlyZXR1cm4gdHJ1ZTsKKwl9CisKKwlyZXR1cm4gZmFsc2U7Cit9CisKIC8qCiAgKiByZW1v
dmVfdW51c2VkX3N1YnF1ZXJ5X291dHB1dHMKICAqCQlSZW1vdmUgc3VicXVlcnkgdGFyZ2V0bGlz
dCBpdGVtcyB3ZSBkb24ndCBuZWVkCkBAIC00MDI4LDkgKzQwNjYsMjAgQEAgcmVtb3ZlX3VudXNl
ZF9zdWJxdWVyeV9vdXRwdXRzKFF1ZXJ5ICpzdWJxdWVyeSwgUmVsT3B0SW5mbyAqcmVsKQogCQkg
KiBJZiBpdCBjb250YWlucyBhIHNldC1yZXR1cm5pbmcgZnVuY3Rpb24sIHdlIGNhbid0IHJlbW92
ZSBpdCBzaW5jZQogCQkgKiB0aGF0IGNvdWxkIGNoYW5nZSB0aGUgbnVtYmVyIG9mIHJvd3MgcmV0
dXJuZWQgYnkgdGhlIHN1YnF1ZXJ5LgogCQkgKi8KLQkJaWYgKHN1YnF1ZXJ5LT5oYXNUYXJnZXRT
UkZzICYmCi0JCQlleHByZXNzaW9uX3JldHVybnNfc2V0KHRleHByKSkKLQkJCWNvbnRpbnVlOwor
CQlpZiAoc3VicXVlcnktPmhhc1RhcmdldFNSRnMpCisJCXsKKwkJCWlmIChleHByZXNzaW9uX3Jl
dHVybnNfc2V0KHRleHByKSkKKwkJCQljb250aW51ZTsKKworCQkJLyoKKwkJCSAqIENoZWNrIGlm
IHRoZSB0YXJnZXQgZW50cnkgY29udGFpbnMgYW55IFdpbmRvd0Z1bmMgcmVmZXJlbmNlcworCQkJ
ICogd2hpY2ggaGF2ZSBhIFdpbmRvd0NsYXVzZSB3aGVyZSB0aGUgUEFSVElUSU9OIEJZIG9yIE9S
REVSIEJZCisJCQkgKiBjbGF1c2UgY29udGFpbiBhbnkgU1JGcworCQkJICovCisJCQlpZiAoc3Vi
cXVlcnktPmhhc1dpbmRvd0Z1bmNzICYmCisJCQkJaGFzX3NldF9yZXR1cm5pbmdfd2luZG93X2Ns
YXVzZXMoc3VicXVlcnksIChOb2RlICopIHRleHByKSkKKwkJCQljb250aW51ZTsKKwkJfQogCiAJ
CS8qCiAJCSAqIElmIGl0IGNvbnRhaW5zIHZvbGF0aWxlIGZ1bmN0aW9ucywgd2UgZGFyZW4ndCBy
ZW1vdmUgaXQgZm9yIGZlYXIK
--000000000000d4e90205e033dfec--