Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sjqNQ-004fUc-Dd for pgsql-general@arkaria.postgresql.org; Fri, 30 Aug 2024 01:24:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sjqNO-00BJ9m-F0 for pgsql-general@arkaria.postgresql.org; Fri, 30 Aug 2024 01:24:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sjqNN-00BJ9J-RL for pgsql-general@lists.postgresql.org; Fri, 30 Aug 2024 01:24:34 +0000 Received: from mail-lf1-x132.google.com ([2a00:1450:4864:20::132]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sjqNL-0026GH-G3 for pgsql-general@lists.postgresql.org; Fri, 30 Aug 2024 01:24:32 +0000 Received: by mail-lf1-x132.google.com with SMTP id 2adb3069b0e04-533488ffaf7so1752807e87.0 for ; Thu, 29 Aug 2024 18:24:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724981070; x=1725585870; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=C2MPG/THsn8LWj/Br+kSDwXcPOGJXZ+7LwYk9Ewb5LI=; b=TD/RmvPPMCE3hMl2UiSnvdQ4sD11kREVlKoCq35/2sWAFaaVIxh8JhjLKW2sbKKNnj L7v7/2k/xkCCGVE539ymHFR2xxVV8VMYVq9UHNdFRxrsAzz+PSBUH5Pepv8F42GSjgYP f+5AZEwTy/t5SO+cF4wzsb+rFO3FF2L1V1wqwqUqtcYicG3gUp5ag5MQ7ADoVNnARTVW IVf7j7bCqwULBo3WLel/DcV0jgTN8IZ/8t5rxtA9y8cP3GYNYxpMt/UsD1uiQYIYOwD6 DU+Kt7m6HFKYCGFCGfeDa1X7mmBiRjZW/0htF9WDbpDf10pc+J83hI3MgBn6jhKR8aR0 MppA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724981070; x=1725585870; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=C2MPG/THsn8LWj/Br+kSDwXcPOGJXZ+7LwYk9Ewb5LI=; b=MDUIzBhcWauTGsIKVQUwZGldfQanTlBk0EII4/pg7fmAPD31tO5snepnbg1UT/QYC0 OgqoVXd5oR8i7BRYHNROMjpTX/VA7pw886LfJBM4+jJpfiNyGwAIPAkkshfo9P6E1+Io 31+V+lAYdDxi16MI2DgBY448de1vPXI9jf181Key2rJmnCeF3o1gGJleu6x+bwYHQMiP /YmVJC2pZXlioJ6sAXCR4GvzsMDFZ1C1jk00Pi8xorc0CX37ZxZQFuzuqDHzDFdOfKj1 euLo1lYZDONRoVbaIzSiMfuO7U++XRtS4X/pZYQV6phZiicIeOaaa1onTvzUk4+wvk0K Q3tQ== X-Gm-Message-State: AOJu0Yx8OfqmARC9hpmbVdH+KTHwK3DevbwnW5+lx2Y0KjlXxt092PEU thhMVHed5wgXTzfQH8WwHv+lNC7z4V/FM9DOUXvvp58HMwzNt8IBD0wcbZ1E4mOHSXQ9AYrbyWx F1eDyl0aFAifSST9WjW/zExaWJOs= X-Google-Smtp-Source: AGHT+IGCgJ4puwelvf6GkpxJbt0TTNdViC5IQMRoM2U8vZSC94STWDaed/njngegmP333/AAsZBk294m9NDu32vcTog= X-Received: by 2002:a05:6512:2521:b0:533:41ec:bff1 with SMTP id 2adb3069b0e04-53546bb3d58mr280363e87.52.1724981069293; Thu, 29 Aug 2024 18:24:29 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: David Rowley Date: Fri, 30 Aug 2024 13:24:17 +1200 Message-ID: Subject: Re: Analytic Function Bug To: Rumpi Gravenstein Cc: PostgreSQL Content-Type: multipart/mixed; boundary="000000000000e1524f0620dc73f6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e1524f0620dc73f6 Content-Type: multipart/alternative; boundary="000000000000e1524d0620dc73f4" --000000000000e1524d0620dc73f4 Content-Type: text/plain; charset="UTF-8" On Fri, 30 Aug 2024 at 11:18, Rumpi Gravenstein wrote: > > Which returns: > logical_partition_keyusage_texttxtrpo_txtindxmx_indxprvnxtnxt2prv2prv3 > "TEST_DATA" > "F(T61)(EXPORT)" "F(T61)(EXPORT)" "F" "1" "7" *"F"* "(" "(" "[NULL]" > "[NULL]" > "TEST_DATA" > "F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "2" "7" "F" "(" "T61" "F" "[NULL]" > "TEST_DATA" > "F(T61)(EXPORT)" "F(T61)(EXPORT)" "T61" "3" "7" *"T61"* ")" ")" "(" "(" > "TEST_DATA" > "F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "4" "7" "T61" ")" "(" "T61" "(" > "TEST_DATA" > "F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "5" "7" "*("* "EXPORT" "EXPORT" ")" > ")" > "TEST_DATA" > "F(T61)(EXPORT)" "F(T61)(EXPORT)" "EXPORT" "6" "7" "(" "EXPORT" ")" "(" > ")" > "TEST_DATA" > "F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "7" "7" ")" "[NULL]" "[NULL]" > "EXPORT" "EXPORT" > Notice that the prv column (lag - 1 ) is just wrong. I've highlighted > obvious bad values. Other columns are wrong as well. > > Is this a PostgreSQL bug? > FWIW, these aren't the results I see when executing your query on 14.13. I'm not sure what interface you're using to get those tables, but let's eliminate some buggy GUI and use psql instead. I've attached a script, could you send us back the output of it with something like?: psql -f lag_query.sql > lagscript.txt I've included a query to give us the EXPLAIN ANALYZE output too. The WHERE clause quals shouldn't get pushed down since the PARTITION BY clause does not contain all of those fields. David --000000000000e1524d0620dc73f4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, 30 Aug 2024 at 11:18, Rumpi Grave= nstein <rgravens= @gmail.com> wrote:

Which returns:
logical_partition_key<= th>indx= = <= td>"6""EXPORT"
usage_texttxtrpo_txtmx_indxprvnxtnxt2prv2prv3
"TEST_DATA"
 "F(T61)(EX= PORT)" "F(T61)(EXPORT)" "F""1" "7" "F" "(" "(" "[NULL]" "[NULL]"
"TEST_DATA"
 "F(T61)(EXPORT)&q= uot; "F(T61)(EXPORT)" "(" &q= uot;2" "7" "F" "(&= quot; "T61" "F" "[NULL]= "
"TEST_DATA"
 "F(T61)(EXPORT)&q= uot; "F(T61)(EXPORT)" "T61" = "3" "7" &q= uot;T61" ")" ")""(" "("
"TEST_DATA"
 "F(T61)(EXPORT)&q= uot; "F(T61)(EXPORT)" ")" &q= uot;4" "7" "T61" "= )" "(" "T61" "(&qu= ot;
"TEST_DATA"
 "F(T61)(EXPORT)&q= uot; "F(T61)(EXPORT)" "(" &q= uot;5" "7" "<= b>(" "EXPORT" "EXPORT"= ; ")" ")"
"TEST_DATA"
 "F(T61)(EXPORT)&q= uot; "F(T61)(EXPORT)" "EXPORT" "7" "(" &qu= ot;EXPORT" ")" "(" &quo= t;)"
"TEST_DATA"
 "F(T61)(EXPORT)&q= uot; "F(T61)(EXPORT)" ")" &q= uot;7" "7" ")" "[N= ULL]" "[NULL]" "EXPORT"

Notice that the prv column (lag - 1 ) is just wrong.= =C2=A0 I've highlighted obvious bad values.=C2=A0 Other columns are wro= ng as well.

Is this a PostgreSQL bug?
<= /blockquote>

FWIW, these aren't the results I see wh= en executing your query on 14.13. I'm not sure what interface you'r= e using to get those tables, but let's eliminate some buggy GUI and use= psql instead.

I've attached a script, could y= ou send us back the output of it with something like?: psql -f lag_query.sq= l > lagscript.txt

I've included a query to = give us the EXPLAIN ANALYZE output too. The WHERE clause quals shouldn'= t get pushed down since the PARTITION BY clause does not contain all of tho= se fields.=C2=A0

David
--000000000000e1524d0620dc73f4-- --000000000000e1524f0620dc73f6 Content-Type: application/octet-stream; name="lag_query.sql" Content-Disposition: attachment; filename="lag_query.sql" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_m0g1210g0 XHBzZXQgbnVsbCAobnVsbCkKd2l0aCAKZChsb2dpY2FsX3BhcnRpdGlvbl9rZXksIG1vZGVsX3Vz YWdlKSBhcyAoIHZhbHVlcyggJ1RFU1RfREFUQScgLCdGKFQ2MSkoRVhQT1JUKScgKQopLAp1c2df dHh0IGFzICgKIFNFTEVDVCBESVNUSU5DVCBsb2dpY2FsX3BhcnRpdGlvbl9rZXksCiAgICAgIE1P REVMX1VTQUdFIGFzIHVzYWdlX3RleHQsCiAgICAgIFJFUExBQ0UgKCBSRVBMQUNFICggUkVQTEFD RSAoIFJFUExBQ0UgKCBQSVUuTU9ERUxfVVNBR0U6OnRleHQsICc8dCBjb250ZXh0PSJVU0FHRV9U RVhUIj4nLCAnJyksICc8dCBjb250ZXh0PSJGQ05fTkFNRV9NT0RGUiI+JywgJycpLCAnPHQgY29u dGV4dD0iRkNOX1VTQUdFX01PREZSIj4nLCAnJyksICc8L3Q+JywgJycpIEFTIHR4dAogICAgRlJP TSBkIC8qQ0FPX0NBTExPVVRfR0hPU1RfQ09NQl9XKi8gcGl1CiAgICApCiwKcGFyc2UoIGxvZ2lj YWxfcGFydGl0aW9uX2tleSwgdXNhZ2VfdGV4dCx0eHQsIHJwb190eHQsIGluZHggKSBhcyAKKApz ZWxlY3QgIGQubG9naWNhbF9wYXJ0aXRpb25fa2V5LCBkLnVzYWdlX3RleHQsZC50eHQsIGNvYWxl c2NlKGEucnBvWzFdLGEucnBvWzJdKSBhcyBycG9fdHh0LCBhLnBvcyAKZnJvbSB1c2dfdHh0IGQK ICAgICBsZWZ0IGpvaW4gbGF0ZXJhbCByZWdleHBfbWF0Y2hlcyggdHh0LCAnKFsgLFwtKCkvJl0p fChbXiAsXC0oKS8mXSspJywnZycpIHdpdGggb3JkaW5hbGl0eSBhcyBhKHJwbyxwb3MpIG9uIHRy dWUKKQosIApwcnZfbnh0X3Rva2VuKCBsb2dpY2FsX3BhcnRpdGlvbl9rZXksIHVzYWdlX3RleHQs dHh0LCBycG9fdHh0LCBpbmR4LCBteF9pbmR4LCBwcmV2LG54dCxueHQyLCBwcnYyLHBydjMpIGFz IAooICAgICAKLyogR2V0IHByaW9yIGFuZCBuZXh0IHRva2VuIHRvIHN1cHBvcnQgbGF0ZXIgbG9n aWMgKi8Kc2VsZWN0ICBwLmxvZ2ljYWxfcGFydGl0aW9uX2tleSwgcC51c2FnZV90ZXh0LAogICAg ICAgcC50eHQsIHAucnBvX3R4dCwgaW5keCwKICAgICAgIG1heCggaW5keCkgb3ZlciAoIHBhcnRp dGlvbiBieSBwLnR4dCApIG14X2luZHgsCiAgICAgICBsYWcoIHAucnBvX3R4dCwxICkgb3ZlciAo IHBhcnRpdGlvbiBieSBwLmxvZ2ljYWxfcGFydGl0aW9uX2tleSxwLnR4dCBvcmRlciBieSBpbmR4 ICkgcHJldiwKICAgICAgIGxhZyggcC5ycG9fdHh0LC0xICkgb3ZlciAoIHBhcnRpdGlvbiBieSBw LmxvZ2ljYWxfcGFydGl0aW9uX2tleSxwLnR4dCBvcmRlciBieSBpbmR4ICkgbnh0LAogICAgICAg bGFnKCBwLnJwb190eHQsLTIpIG92ZXIgKCBwYXJ0aXRpb24gYnkgcC5sb2dpY2FsX3BhcnRpdGlv bl9rZXkscC50eHQgb3JkZXIgYnkgaW5keCApIG54dDIsCQkJCQkJCQkJCQkJCQkJICAgCiAgICAg ICBsYWcoIHAucnBvX3R4dCwyICkgb3ZlciAoIHBhcnRpdGlvbiBieSBwLmxvZ2ljYWxfcGFydGl0 aW9uX2tleSxwLnR4dCBvcmRlciBieSBpbmR4ICkgcHJ2MiwKICAgICAgIGxhZyggcC5ycG9fdHh0 LDMgKSBvdmVyICggcGFydGl0aW9uIGJ5IHAubG9naWNhbF9wYXJ0aXRpb25fa2V5LHAudHh0IG9y ZGVyIGJ5IGluZHggKSBwcnYzCiBmcm9tIHBhcnNlIHAgCiApCiBzZWxlY3QgKiBmcm9tIHBydl9u eHRfdG9rZW4gd2hlcmUgbG9naWNhbF9wYXJ0aXRpb25fa2V5PSdURVNUX0RBVEEnIGFuZCAgdXNh Z2VfdGV4dD0nRihUNjEpKEVYUE9SVCknOwoKZXhwbGFpbiBhbmFseXplIHdpdGggCmQobG9naWNh bF9wYXJ0aXRpb25fa2V5LCBtb2RlbF91c2FnZSkgYXMgKCB2YWx1ZXMoICdURVNUX0RBVEEnICwn RihUNjEpKEVYUE9SVCknICkKKSwKdXNnX3R4dCBhcyAoCiBTRUxFQ1QgRElTVElOQ1QgbG9naWNh bF9wYXJ0aXRpb25fa2V5LAogICAgICBNT0RFTF9VU0FHRSBhcyB1c2FnZV90ZXh0LAogICAgICBS RVBMQUNFICggUkVQTEFDRSAoIFJFUExBQ0UgKCBSRVBMQUNFICggUElVLk1PREVMX1VTQUdFOjp0 ZXh0LCAnPHQgY29udGV4dD0iVVNBR0VfVEVYVCI+JywgJycpLCAnPHQgY29udGV4dD0iRkNOX05B TUVfTU9ERlIiPicsICcnKSwgJzx0IGNvbnRleHQ9IkZDTl9VU0FHRV9NT0RGUiI+JywgJycpLCAn PC90PicsICcnKSBBUyB0eHQKICAgIEZST00gZCAvKkNBT19DQUxMT1VUX0dIT1NUX0NPTUJfVyov IHBpdQogICAgKQosCnBhcnNlKCBsb2dpY2FsX3BhcnRpdGlvbl9rZXksIHVzYWdlX3RleHQsdHh0 LCBycG9fdHh0LCBpbmR4ICkgYXMgCigKc2VsZWN0ICBkLmxvZ2ljYWxfcGFydGl0aW9uX2tleSwg ZC51c2FnZV90ZXh0LGQudHh0LCBjb2FsZXNjZShhLnJwb1sxXSxhLnJwb1syXSkgYXMgcnBvX3R4 dCwgYS5wb3MgCmZyb20gdXNnX3R4dCBkCiAgICAgbGVmdCBqb2luIGxhdGVyYWwgcmVnZXhwX21h dGNoZXMoIHR4dCwgJyhbICxcLSgpLyZdKXwoW14gLFwtKCkvJl0rKScsJ2cnKSB3aXRoIG9yZGlu YWxpdHkgYXMgYShycG8scG9zKSBvbiB0cnVlCikKLCAKcHJ2X254dF90b2tlbiggbG9naWNhbF9w YXJ0aXRpb25fa2V5LCB1c2FnZV90ZXh0LHR4dCwgcnBvX3R4dCwgaW5keCwgbXhfaW5keCwgcHJl dixueHQsbnh0MiwgcHJ2MixwcnYzKSBhcyAKKCAgICAgCi8qIEdldCBwcmlvciBhbmQgbmV4dCB0 b2tlbiB0byBzdXBwb3J0IGxhdGVyIGxvZ2ljICovCnNlbGVjdCAgcC5sb2dpY2FsX3BhcnRpdGlv bl9rZXksIHAudXNhZ2VfdGV4dCwKICAgICAgIHAudHh0LCBwLnJwb190eHQsIGluZHgsCiAgICAg ICBtYXgoIGluZHgpIG92ZXIgKCBwYXJ0aXRpb24gYnkgcC50eHQgKSBteF9pbmR4LAogICAgICAg bGFnKCBwLnJwb190eHQsMSApIG92ZXIgKCBwYXJ0aXRpb24gYnkgcC5sb2dpY2FsX3BhcnRpdGlv bl9rZXkscC50eHQgb3JkZXIgYnkgaW5keCApIHByZXYsCiAgICAgICBsYWcoIHAucnBvX3R4dCwt MSApIG92ZXIgKCBwYXJ0aXRpb24gYnkgcC5sb2dpY2FsX3BhcnRpdGlvbl9rZXkscC50eHQgb3Jk ZXIgYnkgaW5keCApIG54dCwKICAgICAgIGxhZyggcC5ycG9fdHh0LC0yKSBvdmVyICggcGFydGl0 aW9uIGJ5IHAubG9naWNhbF9wYXJ0aXRpb25fa2V5LHAudHh0IG9yZGVyIGJ5IGluZHggKSBueHQy LAogICAgICAgbGFnKCBwLnJwb190eHQsMiApIG92ZXIgKCBwYXJ0aXRpb24gYnkgcC5sb2dpY2Fs X3BhcnRpdGlvbl9rZXkscC50eHQgb3JkZXIgYnkgaW5keCApIHBydjIsCiAgICAgICBsYWcoIHAu cnBvX3R4dCwzICkgb3ZlciAoIHBhcnRpdGlvbiBieSBwLmxvZ2ljYWxfcGFydGl0aW9uX2tleSxw LnR4dCBvcmRlciBieSBpbmR4ICkgcHJ2MwogZnJvbSBwYXJzZSBwIAogKQogc2VsZWN0ICogZnJv bSBwcnZfbnh0X3Rva2VuIHdoZXJlIGxvZ2ljYWxfcGFydGl0aW9uX2tleT0nVEVTVF9EQVRBJyBh bmQgIHVzYWdlX3RleHQ9J0YoVDYxKShFWFBPUlQpJzsKIApzZWxlY3QgdmVyc2lvbigpOwpzZWxl Y3QgKiBmcm9tIHBnX3NldHRpbmdzOwo= --000000000000e1524f0620dc73f6--