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 1oltsQ-0005Dh-Bq for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Oct 2022 15:24:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oltsN-0005gy-8w for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Oct 2022 15:23:59 +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 1oltsL-0005gj-91 for pgsql-hackers@lists.postgresql.org; Fri, 21 Oct 2022 15:23:58 +0000 Received: from mail-wr1-x42c.google.com ([2a00:1450:4864:20::42c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oltsD-0004Ge-ML for pgsql-hackers@lists.postgresql.org; Fri, 21 Oct 2022 15:23:55 +0000 Received: by mail-wr1-x42c.google.com with SMTP id n12so5313163wrp.10 for ; Fri, 21 Oct 2022 08:23:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=TO5TX39ZRGIwaHIzp5ss8bCu9QwJkO9Hny5xa22ZHp0=; b=WAnB4yapDF3awr6qn/RyZBZZi0KYvVCW2mLOWk6hvVCF0CoiW4iOtRrw92Pa3+83N9 6vYyy2niG/kpTdhPMztCf4xySJr3aETv1Ijdf/lTX77HJpLfq2VLYLsG1KSWeEYq76D9 hxFg1lPp7pG+YKKnFrWcY1AUrv84wxVaXl1Y+LBIGfd1DASc6z8uY/XLvyEhfahUPOke IWNf8cc4TDPSFGZsDufWD4GttLnWdTFGvcQJ27QvPrpXivFFsXk3+7iXTJBLD2ogKTrF t/yrE1Ab8NpG0H6DVm0e+OvG9oJc1Vd6+8WorJpYPmjlKYJ/VAZ1nUO79DqkEeia0OG3 0kyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=TO5TX39ZRGIwaHIzp5ss8bCu9QwJkO9Hny5xa22ZHp0=; b=7v//C0rgaeYdPzFpr6idAFIz6YpWlOqq0OPJt7opjdVF+BZ3cE/FJXGY9XziyvKYB1 lTqfuGXw6hAnj40UZrSkTa+d++LhR6oP76FWacLfVkJqNkcuZ2WYy8kjDp5lwvoCAhyn 3x20+ULCjjfqAlpaDD72Y3tgkpIdgTs1t8SAREVROchLvUPSZx0Q7Y9ngjJiyTpGmzza Zqbrv5sKvt0TFHTqt6ViyEfmhzbWSs6RsDISD5yXNPSGB9hLdO2HC7PtbgpXoL8dQxYy hPcoo3gs95+ofd4odhCuHxR2Qeqrtg684ViyP3ZJzVIn+WYVMJ6eL+vhYNda3txAGqc3 SOTw== X-Gm-Message-State: ACrzQf0XmLK4B6qXw1hxz/qEn20xelHCpV2TaxHu7wFw3fzZoIVGecna e6ODZKVdJDNY3VrLfKrqcMJIPAopn116PBzRvuKyCKp9/uJ1jNP6LxCxDMsOEZluGfG4jzDl1dp zEP9lC9BQiaLy9MNkbepMUqcmRnJB/JE5yE16m8ZSi0gbE2LZREOyROzkozAW7XChv4Wjy6ANEV NI5T5WRRMhKn+P9Q2ghfqMs1SBvzm0/VyeJhUd4UUyEIDZvFyhGSv1et9w+7Yoi6SRGux87F0Ln 5fz0WBO0JCBQz+GpQvjSsy7KbsI9LRO+JK1JYxc3wtHDFELjBb40QvCmTttHRFOSy2S2vWR9QWe qWIwqcrljIb6j6FXuJMXQMhaU3WS4h//0Blk7hz4MKKWnFp+dJjFyMrkWw== X-Google-Smtp-Source: AMsMyM5y99W0ggwfSDVlDrIYFE1K0nkSL8ifpT2MZF42mp5ruubGP1p1PiC8etbzSCqTOK0VlHDz9A== X-Received: by 2002:a05:6000:90b:b0:22c:beec:5b26 with SMTP id bz11-20020a056000090b00b0022cbeec5b26mr12553781wrb.490.1666365827567; Fri, 21 Oct 2022 08:23:47 -0700 (PDT) Received: from [10.137.0.17] (ip-86-49-228-162.bb.vodafone.cz. [86.49.228.162]) by smtp.gmail.com with ESMTPSA id j30-20020adfa55e000000b0021e51c039c5sm20061356wrb.80.2022.10.21.08.23.46 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 21 Oct 2022 08:23:46 -0700 (PDT) Content-Type: multipart/mixed; boundary="------------nIVBtoh1wa19tseATZ06ngi7" Message-ID: <402430e4-7d9d-6cf1-09ef-464d80afff3b@enterprisedb.com> Date: Fri, 21 Oct 2022 17:23:45 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.3.1 Content-Language: en-US To: PostgreSQL Hackers From: Tomas Vondra Subject: Missing update of all_hasnulls in BRIN opclasses X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 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. --------------nIVBtoh1wa19tseATZ06ngi7 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Hi, While working on some BRIN code, I discovered a bug in handling NULL values - when inserting a non-NULL value into a NULL-only range, we reset the all_nulls flag but don't update the has_nulls flag. And because of that we then fail to return the range for IS NULL ranges. Reproducing this is trivial: create table t (a int); create index on t using brin (a); insert into t values (null); insert into t values (1); set enable_seqscan = off; select * from t where a is null; This should return 1 row, but actually it returns no rows. Attached is a patch fixing this by properly updating the has_nulls flag. I reproduced this all the way back to 9.5, so it's a long-standing bug. It's interesting no one noticed / reported it so far, it doesn't seem like a particularly rare corner case. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --------------nIVBtoh1wa19tseATZ06ngi7 Content-Type: text/x-patch; charset=UTF-8; name="brin-hasnulls-fix.patch" Content-Disposition: attachment; filename="brin-hasnulls-fix.patch" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9icmluL2JyaW5fYmxvb20uYyBiL3Ny Yy9iYWNrZW5kL2FjY2Vzcy9icmluL2JyaW5fYmxvb20uYwppbmRleCA2YjBhZjcyNjdkNS4u NjAzMTU0NTBiNDEgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9icmluL2JyaW5f Ymxvb20uYworKysgYi9zcmMvYmFja2VuZC9hY2Nlc3MvYnJpbi9icmluX2Jsb29tLmMKQEAg LTUzOSw2ICs1MzksNyBAQCBicmluX2Jsb29tX2FkZF92YWx1ZShQR19GVU5DVElPTl9BUkdT KQogCQkJCQkJCUJsb29tR2V0RmFsc2VQb3NpdGl2ZVJhdGUob3B0cykpOwogCQljb2x1bW4t PmJ2X3ZhbHVlc1swXSA9IFBvaW50ZXJHZXREYXR1bShmaWx0ZXIpOwogCQljb2x1bW4tPmJ2 X2FsbG51bGxzID0gZmFsc2U7CisJCWNvbHVtbi0+YnZfaGFzbnVsbHMgPSB0cnVlOwogCQl1 cGRhdGVkID0gdHJ1ZTsKIAl9CiAJZWxzZQpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvYWNj ZXNzL2JyaW4vYnJpbl9pbmNsdXNpb24uYyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy9icmluL2Jy aW5faW5jbHVzaW9uLmMKaW5kZXggNGIwMmQzNzRmMjMuLmUwZjQ0ZDNlNjJjIDEwMDY0NAot LS0gYS9zcmMvYmFja2VuZC9hY2Nlc3MvYnJpbi9icmluX2luY2x1c2lvbi5jCisrKyBiL3Ny Yy9iYWNrZW5kL2FjY2Vzcy9icmluL2JyaW5faW5jbHVzaW9uLmMKQEAgLTE2NCw2ICsxNjQs NyBAQCBicmluX2luY2x1c2lvbl9hZGRfdmFsdWUoUEdfRlVOQ1RJT05fQVJHUykKIAkJY29s dW1uLT5idl92YWx1ZXNbSU5DTFVTSU9OX1VOTUVSR0VBQkxFXSA9IEJvb2xHZXREYXR1bShm YWxzZSk7CiAJCWNvbHVtbi0+YnZfdmFsdWVzW0lOQ0xVU0lPTl9DT05UQUlOU19FTVBUWV0g PSBCb29sR2V0RGF0dW0oZmFsc2UpOwogCQljb2x1bW4tPmJ2X2FsbG51bGxzID0gZmFsc2U7 CisJCWNvbHVtbi0+YnZfaGFzbnVsbHMgPSB0cnVlOwogCQluZXcgPSB0cnVlOwogCX0KIApk aWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvYWNjZXNzL2JyaW4vYnJpbl9taW5tYXguYyBiL3Ny Yy9iYWNrZW5kL2FjY2Vzcy9icmluL2JyaW5fbWlubWF4LmMKaW5kZXggOWU4YThlMDU2Y2Mu LjhhNTY2MWE4OTUyIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9hY2Nlc3MvYnJpbi9icmlu X21pbm1heC5jCisrKyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy9icmluL2JyaW5fbWlubWF4LmMK QEAgLTkwLDYgKzkwLDcgQEAgYnJpbl9taW5tYXhfYWRkX3ZhbHVlKFBHX0ZVTkNUSU9OX0FS R1MpCiAJCWNvbHVtbi0+YnZfdmFsdWVzWzBdID0gZGF0dW1Db3B5KG5ld3ZhbCwgYXR0ci0+ YXR0Ynl2YWwsIGF0dHItPmF0dGxlbik7CiAJCWNvbHVtbi0+YnZfdmFsdWVzWzFdID0gZGF0 dW1Db3B5KG5ld3ZhbCwgYXR0ci0+YXR0Ynl2YWwsIGF0dHItPmF0dGxlbik7CiAJCWNvbHVt bi0+YnZfYWxsbnVsbHMgPSBmYWxzZTsKKwkJY29sdW1uLT5idl9oYXNudWxscyA9IHRydWU7 CiAJCVBHX1JFVFVSTl9CT09MKHRydWUpOwogCX0KIApkaWZmIC0tZ2l0IGEvc3JjL2JhY2tl bmQvYWNjZXNzL2JyaW4vYnJpbl9taW5tYXhfbXVsdGkuYyBiL3NyYy9iYWNrZW5kL2FjY2Vz cy9icmluL2JyaW5fbWlubWF4X211bHRpLmMKaW5kZXggOWEwYmNmNjY5OGQuLjRlNzExOWUy ZDc4IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9hY2Nlc3MvYnJpbi9icmluX21pbm1heF9t dWx0aS5jCisrKyBiL3NyYy9iYWNrZW5kL2FjY2Vzcy9icmluL2JyaW5fbWlubWF4X211bHRp LmMKQEAgLTI1MDAsNiArMjUwMCw3IEBAIGJyaW5fbWlubWF4X211bHRpX2FkZF92YWx1ZShQ R19GVU5DVElPTl9BUkdTKQogCQlNZW1vcnlDb250ZXh0U3dpdGNoVG8ob2xkY3R4KTsKIAog CQljb2x1bW4tPmJ2X2FsbG51bGxzID0gZmFsc2U7CisJCWNvbHVtbi0+YnZfaGFzbnVsbHMg PSB0cnVlOwogCQltb2RpZmllZCA9IHRydWU7CiAKIAkJY29sdW1uLT5idl9tZW1fdmFsdWUg PSBQb2ludGVyR2V0RGF0dW0ocmFuZ2VzKTsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jl c3MvZXhwZWN0ZWQvYnJpbi5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2JyaW4u b3V0CmluZGV4IDczZmEzODM5NmU0Li5jYzg5NmMyZDlkNCAxMDA2NDQKLS0tIGEvc3JjL3Rl c3QvcmVncmVzcy9leHBlY3RlZC9icmluLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4 cGVjdGVkL2JyaW4ub3V0CkBAIC01NzIsMyArNTcyLDM5IEBAIENSRUFURSBVTkxPR0dFRCBU QUJMRSBicmludGVzdF91bmxvZ2dlZCAobiBudW1yYW5nZSk7CiBDUkVBVEUgSU5ERVggYnJp bmlkeF91bmxvZ2dlZCBPTiBicmludGVzdF91bmxvZ2dlZCBVU0lORyBicmluIChuKTsKIElO U0VSVCBJTlRPIGJyaW50ZXN0X3VubG9nZ2VkIFZBTFVFUyAobnVtcmFuZ2UoMCwgMl4xMDAw OjpudW1lcmljKSk7CiBEUk9QIFRBQkxFIGJyaW50ZXN0X3VubG9nZ2VkOworLS0gdGVzdCB0 aGF0IHdlIHByb3Blcmx5IHVwZGF0ZSBoYXNfbnVsbHMgd2hlbiBpbnNlcnRpbmcgc29tZXRo aW5nIGludG8KKy0tIGEgcmFuZ2UgdGhhdCBvbmx5IGhhZCBOVUxMcyBiZWZvcmUKK0NSRUFU RSBUQUJMRSBicmludGVzdF80IChhIElOVCwgYiBJTlQsIGMgSU5ULCBkIElORVQpOworQ1JF QVRFIElOREVYIGJyaW50ZXN0XzRfaWR4IE9OIGJyaW50ZXN0XzQgVVNJTkcgYnJpbiAoYSwg YiBpbnQ0X21pbm1heF9tdWx0aV9vcHMsIGMgaW50NF9ibG9vbV9vcHMsIGQgaW5ldF9pbmNs dXNpb25fb3BzKTsKKy0tIGluc2VydCBhIE5VTEwgdmFsdWUsIHNvIHRoYXQgd2UgZ2V0IGFu IGFsbC1udWxscyByYW5nZQorSU5TRVJUIElOVE8gYnJpbnRlc3RfNCBWQUxVRVMgKE5VTEws IE5VTEwsIE5VTEwsIE5VTEwpOworLS0gbm93IGluc2VydCBhIG5vbi1OVUxMIHZhbHVlCitJ TlNFUlQgSU5UTyBicmludGVzdF80IFZBTFVFUyAoMSwgMSwgMSwgJzEyNy4wLjAuMScpOwor LS0gc2VlIHRoYXQgd2UgY2FuIHN0aWxsIG1hdGNoIHRoZSB2YWx1ZSB3aGVuIHVzaW5nIHRo ZSBicmluIGluZGV4CitTRVQgZW5hYmxlX3NlcXNjYW4gPSBvZmY7CitTRUxFQ1QgKiBGUk9N IGJyaW50ZXN0XzQgV0hFUkUgYSBJUyBOVUxMOworIGEgfCBiIHwgYyB8IGQgCistLS0rLS0t Ky0tLSstLS0KKyAgIHwgICB8ICAgfCAKKygxIHJvdykKKworU0VMRUNUICogRlJPTSBicmlu dGVzdF80IFdIRVJFIGIgSVMgTlVMTDsKKyBhIHwgYiB8IGMgfCBkIAorLS0tKy0tLSstLS0r LS0tCisgICB8ICAgfCAgIHwgCisoMSByb3cpCisKK1NFTEVDVCAqIEZST00gYnJpbnRlc3Rf NCBXSEVSRSBjIElTIE5VTEw7CisgYSB8IGIgfCBjIHwgZCAKKy0tLSstLS0rLS0tKy0tLQor ICAgfCAgIHwgICB8IAorKDEgcm93KQorCitTRUxFQ1QgKiBGUk9NIGJyaW50ZXN0XzQgV0hF UkUgZCBJUyBOVUxMOworIGEgfCBiIHwgYyB8IGQgCistLS0rLS0tKy0tLSstLS0KKyAgIHwg ICB8ICAgfCAKKygxIHJvdykKKworRFJPUCBUQUJMRSBicmludGVzdF80OworU0VUIGVuYWJs ZV9zZXFzY2FuID0gb2ZmOwpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvYnJp bi5zcWwgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9icmluLnNxbAppbmRleCBlNjhlOWUxOGRm NS4uMTdhMDFhNGI4MmYgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2JyaW4u c3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2JyaW4uc3FsCkBAIC01MTUsMyArNTE1 LDI0IEBAIENSRUFURSBVTkxPR0dFRCBUQUJMRSBicmludGVzdF91bmxvZ2dlZCAobiBudW1y YW5nZSk7CiBDUkVBVEUgSU5ERVggYnJpbmlkeF91bmxvZ2dlZCBPTiBicmludGVzdF91bmxv Z2dlZCBVU0lORyBicmluIChuKTsKIElOU0VSVCBJTlRPIGJyaW50ZXN0X3VubG9nZ2VkIFZB TFVFUyAobnVtcmFuZ2UoMCwgMl4xMDAwOjpudW1lcmljKSk7CiBEUk9QIFRBQkxFIGJyaW50 ZXN0X3VubG9nZ2VkOworCistLSB0ZXN0IHRoYXQgd2UgcHJvcGVybHkgdXBkYXRlIGhhc19u dWxscyB3aGVuIGluc2VydGluZyBzb21ldGhpbmcgaW50bworLS0gYSByYW5nZSB0aGF0IG9u bHkgaGFkIE5VTExzIGJlZm9yZQorQ1JFQVRFIFRBQkxFIGJyaW50ZXN0XzQgKGEgSU5ULCBi IElOVCwgYyBJTlQsIGQgSU5FVCk7CitDUkVBVEUgSU5ERVggYnJpbnRlc3RfNF9pZHggT04g YnJpbnRlc3RfNCBVU0lORyBicmluIChhLCBiIGludDRfbWlubWF4X211bHRpX29wcywgYyBp bnQ0X2Jsb29tX29wcywgZCBpbmV0X2luY2x1c2lvbl9vcHMpOworCistLSBpbnNlcnQgYSBO VUxMIHZhbHVlLCBzbyB0aGF0IHdlIGdldCBhbiBhbGwtbnVsbHMgcmFuZ2UKK0lOU0VSVCBJ TlRPIGJyaW50ZXN0XzQgVkFMVUVTIChOVUxMLCBOVUxMLCBOVUxMLCBOVUxMKTsKKworLS0g bm93IGluc2VydCBhIG5vbi1OVUxMIHZhbHVlCitJTlNFUlQgSU5UTyBicmludGVzdF80IFZB TFVFUyAoMSwgMSwgMSwgJzEyNy4wLjAuMScpOworCistLSBzZWUgdGhhdCB3ZSBjYW4gc3Rp bGwgbWF0Y2ggdGhlIHZhbHVlIHdoZW4gdXNpbmcgdGhlIGJyaW4gaW5kZXgKK1NFVCBlbmFi bGVfc2Vxc2NhbiA9IG9mZjsKK1NFTEVDVCAqIEZST00gYnJpbnRlc3RfNCBXSEVSRSBhIElT IE5VTEw7CitTRUxFQ1QgKiBGUk9NIGJyaW50ZXN0XzQgV0hFUkUgYiBJUyBOVUxMOworU0VM RUNUICogRlJPTSBicmludGVzdF80IFdIRVJFIGMgSVMgTlVMTDsKK1NFTEVDVCAqIEZST00g YnJpbnRlc3RfNCBXSEVSRSBkIElTIE5VTEw7CisKK0RST1AgVEFCTEUgYnJpbnRlc3RfNDsK K1NFVCBlbmFibGVfc2Vxc2NhbiA9IG9mZjsK --------------nIVBtoh1wa19tseATZ06ngi7--