Thread: BUG #14202: pg_trgm: % uses incorrect comparison of similarity with the limit

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDIwMgpMb2dnZWQgYnk6ICAg
ICAgICAgIEdyZWcgTmF2aXMKRW1haWwgYWRkcmVzczogICAgICBjb250YWN0
QGdyZWduYXZpcy5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjZiZXRhMQpP
cGVyYXRpbmcgc3lzdGVtOiAgIEZlZG9yYSAyMyAoTGludXggNC41LjYtMjAw
LmZjMjMueDg2XzY0KQpEZXNjcmlwdGlvbjogICAgICAgIAoKIyBTdW1tYXJ5
DQoNCkR1ZSB0byBhIGJ1ZyBndHJnbV9jb25zaXN0ZW50LCB1c2luZyBhIEdp
U1Qgb3IgR0lOIHRyaWdyYW0gaW5kZXggY2FuIHJldHVybgpleHRyYW5lb3Vz
IHJvd3Mgd2hvc2UgdHJpZ3JhbS1zaW1pbGFyaXR5IHdhcyBiZWxvdyBzZXRf
bGltaXQoKS4NCg0KIyBWZXJzaW9uIHRlc3RlZA0KDQpQb3N0Z3JlU1FMIEdp
dCBjb21taXQ6IDRkNDhhZGMNCg0KJCBnY2MgLS12ZXJzaW9uDQpnY2MgKEdD
QykgNS4zLjEgMjAxNjA0MDYgKFJlZCBIYXQgNS4zLjEtNikNCkNvcHlyaWdo
dCAoQykgMjAxNSBGcmVlIFNvZnR3YXJlIEZvdW5kYXRpb24sIEluYy4NClRo
aXMgaXMgZnJlZSBzb2Z0d2FyZTsgc2VlIHRoZSBzb3VyY2UgZm9yIGNvcHlp
bmcgY29uZGl0aW9ucy4gIFRoZXJlIGlzIE5PDQp3YXJyYW50eTsgbm90IGV2
ZW4gZm9yIE1FUkNIQU5UQUJJTElUWSBvciBGSVRORVNTIEZPUiBBIFBBUlRJ
Q1VMQVIKUFVSUE9TRS4NCg0KJCB1bmFtZSAtYQ0KTGludXggbG9jYWxob3N0
LmxvY2FsZG9tYWluIDQuNS42LTIwMC5mYzIzLng4Nl82NCAjMSBTTVAgV2Vk
IEp1biAxIDIxOjI4OjIwClVUQyAyMDE2IHg4Nl82NCB4ODZfNjQgeDg2XzY0
IEdOVS9MaW51eA0KDQojIEhvdyB0byByZXByb2R1Y2UNCg0KU2F2ZSB0aGUg
YmVsb3cgdG8gdHJnbV9idWcuc3FsOg0KDQotLSBDbGVhbiB1cCBhZnRlciBw
cmV2aW91cyBydW4uDQpEUk9QIFRBQkxFIElGIEVYSVNUUyByZXN0YXVyYW50
czsNCkRST1AgRVhURU5TSU9OIElGIEVYSVNUUyBwZ190cmdtIENBU0NBREU7
DQoNCi0tIENyZWF0ZSB0aGUgdGFibGUuDQpDUkVBVEUgRVhURU5TSU9OIHBn
X3RyZ207DQpDUkVBVEUgVEFCTEUgcmVzdGF1cmFudHMoY2l0eSBWQVJDSEFS
KDI1NSkgTk9UIE5VTEwpOw0KQ1JFQVRFIElOREVYIHJlc3RhdXJhbnRzX29u
X2NpdHlfaWR4IE9OIHJlc3RhdXJhbnRzIFVTSU5HIGdpc3QoY2l0eQpnaXN0
X3RyZ21fb3BzKTsNCg0KLS0gSW5zZXJ0IDEwMDAwIHJvd3MgdG8gdHJpZ2dl
ciBpbmRleCB1c2UuDQpJTlNFUlQgSU5UTyByZXN0YXVyYW50cyBTRUxFQ1Qg
J1dhcnNhdycgRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDApOw0KSU5T
RVJUIElOVE8gcmVzdGF1cmFudHMgU0VMRUNUICdTemN6ZWNpbicgRlJPTSBn
ZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDApOw0KDQotLSBTaW1pbGFyaXR5IG9m
IHRoZSB0d28gbmFtZXMgKGZvciByZWZlcmVuY2UpLg0KU0VMRUNUIHNpbWls
YXJpdHkoJ1N6Y3plY2luJywgJ1dhcnNhdycpOw0KDQotLSBTZXQgdGhlIGxp
bWl0IHRvIDAuMy4gT25seSBXYXJzYXcgaXMgcmV0dXJuZWQgKGFzIGl0IHNo
b3VsZCkuDQpTRUxFQ1Qgc2V0X2xpbWl0KDAuMyk7DQpTRUxFQ1QgRElTVElO
Q1QgY2l0eSwgc2ltaWxhcml0eShjaXR5LCAnV2Fyc2F3JyksIHNob3dfbGlt
aXQoKSBGUk9NCnJlc3RhdXJhbnRzIFdIRVJFIGNpdHkgJSAnV2Fyc2F3JzsN
Cg0KLS0gUmFpc2UgdGhlIGxpbWl0IHRvIDAuNS4gTm93IF9ib3RoXyBXYXJz
YXcgYW5kIFN6Y3plY2luIGFyZSByZXR1cm5lZC4NClNFTEVDVCBzZXRfbGlt
aXQoMC41KTsNClNFTEVDVCBESVNUSU5DVCBjaXR5LCBzaW1pbGFyaXR5KGNp
dHksICdXYXJzYXcnKSwgc2hvd19saW1pdCgpIEZST00KcmVzdGF1cmFudHMg
V0hFUkUgY2l0eSAlICdXYXJzYXcnOw0KDQphbmQgcnVuICJwc3FsIC1mIHRy
Z21fYnVnLnNxbCIuDQoNCiMgUmVzdWx0cw0KDQpUaGUgc2NyaXB0IHJldHVy
bnMgdGhlIHJpZ2h0IHJlc3VsdCB3aGVuIHRoZSB0aHJlc2hvbGQgaXMgc2V0
IHRvIDAuMzoNCg0KICBjaXR5ICB8IHNpbWlsYXJpdHkgfCBzaG93X2xpbWl0
IA0KLS0tLS0tLS0rLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLQ0KIFdhcnNh
dyB8ICAgICAgICAgIDEgfCAgICAgICAgMC4zDQooMSByb3cpDQoNCkhvd2V2
ZXIsIGl0IHJldHVybnMgX2JvdGggY2l0aWVzIGFmdGVyIHJpc2luZyB0aGUg
dGhyZXNob2xkIHRvIDAuNV86DQoNCiAgIGNpdHkgICB8IHNpbWlsYXJpdHkg
fCBzaG93X2xpbWl0IA0KLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0rLS0tLS0t
LS0tLS0tDQogU3pjemVjaW4gfCAgICAgICAgICAwIHwgICAgICAgIDAuNQ0K
IFdhcnNhdyAgIHwgICAgICAgICAgMSB8ICAgICAgICAwLjUNCg0KIyBSb290
IGNhdXNlDQoNClRoZSByb290IGNhdXNlIGlzIHRoaXMgbGluZSBpbiBjb250
cmliL3BnX3RyZ20vdHJnbV9naXN0LmM6DQoNCi8qIHN0cmFuZ2UgYnVnIGF0
IGZyZWVic2QgNS4yLjEgYW5kIGdjYyAzLjMuMyAqLw0KcmVzID0gKCooaW50
ICopICZ0bXBzbWwgPT0gKihpbnQgKikgJm5saW1pdCB8fCB0bXBzbWwgPiBu
bGltaXQpOw0KDQpubGltaXQgaXMgb2YgdHlwZSBkb3VibGUuIHRtcHNtbCBp
cyBvZiB0eXBlIGZsb2F0NC4gT24gbXkgc3lzdGVtCnNpemVvZihmbG9hdDQp
ID09IDQgYW5kIHNpemVvZihkb3VibGUpID09IDguIEFmdGVyIGFkZGluZzoN
Cg0KcHJpbnRmKCJ0bXBzbWwgPSAlZiB0bXBzbWw6aW50ID0gJWkgbmxpbWl0
ID0gJWYgbmxpbWl0OmludCA9ICVpIHJlcyA9ICVpXG4iLAp0bXBzbWwsICoo
aW50ICopJnRtcHNtbCwgbmxpbWl0LCAqKGludCAqKSZubGltaXQsIHJlcyk7
DQoNCmFmdGVyIHRoZSBvZmZlbmRpbmcgbGluZSBJIGNhbiBzZWUgdGhlIGZv
bGxvd2luZyBvdXRwdXQgKGFmdGVyIHBhc3NpbmcKdGhyb3VnaCAic29ydCB8
IHVuaXEiKToNCg0KdG1wc21sID0gMC4wMDAwMDAgdG1wc21sOmludCA9IDAg
bmxpbWl0ID0gMC4zMDAwMDAgbmxpbWl0OmludCA9IDg1ODk5MzQ1OQpyZXMg
PSAwDQp0bXBzbWwgPSAwLjAwMDAwMCB0bXBzbWw6aW50ID0gMCBubGltaXQg
PSAwLjUwMDAwMCBubGltaXQ6aW50ID0gMCByZXMgPSAxDQp0bXBzbWwgPSAx
LjAwMDAwMCB0bXBzbWw6aW50ID0gMTA2NTM1MzIxNiBubGltaXQgPSAwLjMw
MDAwMCBubGltaXQ6aW50ID0KODU4OTkzNDU5IHJlcyA9IDENCnRtcHNtbCA9
IDEuMDAwMDAwIHRtcHNtbDppbnQgPSAxMDY1MzUzMjE2IG5saW1pdCA9IDAu
NTAwMDAwIG5saW1pdDppbnQgPSAwCnJlcyA9IDENCg0KT24gbGluZSAyLCB3
ZSBjYW4gc2VlIHRoYXQgbmxpbWl0ID0gMC41IHdhcyBjb252ZXJ0ZWQgdG8g
MCB3aGljaCB0cmlnZ2VyZWQKdGhlIGVycm9yLg0KDQojIFBvc3NpYmxlIGZp
eA0KDQpJZiB3ZSdyZSBva2F5IHdpdGggYWJhbmRvbmluZyB0aGUgYnVnIHdv
cmstYXJvdW5kIGZvciBGcmVlQlNEIDUuMi4xIC8gZ2NjCjMuMy4zIHRoZW4g
SSBzdWdnZXN0IHJlcGxhY2luZw0KDQpyZXMgPSAoKihpbnQgKikgJnRtcHNt
bCA9PSAqKGludCAqKSAmbmxpbWl0IHx8IHRtcHNtbCA+IG5saW1pdCk7DQoN
CndpdGgNCg0KcmVzID0gdG1wc21sID49IG5saW1pdDsNCg0KVGhpcyByZXNv
bHZlcyB0aGUgaXNzdWUuDQoKCg==

contact@gregnavis.com writes:
> Due to a bug gtrgm_consistent, using a GiST or GIN trigram index can return
> extraneous rows whose trigram-similarity was below set_limit().
> ...
> The root cause is this line in contrib/pg_trgm/trgm_gist.c:

> /* strange bug at freebsd 5.2.1 and gcc 3.3.3 */
> res = (*(int *) &tmpsml == *(int *) &nlimit || tmpsml > nlimit);

> nlimit is of type double. tmpsml is of type float4.

Ugh, yeah, that's completely broken now isn't it.

I suspect that changing tmpsml to double would be enough to get around the
alleged gcc bug and let us write the comparison naturally, ie the whole
thing was likely an artifact of using float4.  Will try it that way.

Thanks for the report!

            regards, tom lane