Subject: Analytic Function Bug

From: Rumpi Gravenstein <[email-masked]>
Date: Thu, 29 Aug 2024 19:18:16 -0400

Experts,

I am running on

PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
20210514 (Red Hat 8.5.0-22), 64-bit

I have the following query which returns what I expect:

with

d(logical_partition_key, model_usage) as ( values( 'TEST_DATA' ,
'F(T61)(EXPORT)' )

)

,

usg_txt as (

SELECT DISTINCT logical_partition_key,

MODEL_USAGE as usage_text,

REPLACE ( REPLACE ( REPLACE ( REPLACE ( PIU.MODEL_USAGE::text, '<t
context="USAGE_TEXT">', ''), '<t context="FCN_NAME_MODFR">', ''), '<t
context="FCN_USAGE_MODFR">', ''), '</t>', '') AS txt

FROM d /*CAO_CALLOUT_GHOST_COMB_W*/ piu

)

,

parse( logical_partition_key, usage_text,txt, rpo_txt, indx ) as

(

select d.logical_partition_key, d.usage_text,d.txt, coalesce(a.rpo[1],a.rpo[
2]) as rpo_txt, a.pos

from usg_txt d

left join lateral regexp_matches( txt, '([ ,\-()/&])|([^ ,\-()/&]+)','g')
with ordinality as a(rpo,pos) on true

)

,

prv_nxt_token( logical_partition_key, usage_text,txt, rpo_txt, indx,
mx_indx, prev,nxt,nxt2, prv2,prv3) as

(

/* Get prior and next token to support later logic */

select p.logical_partition_key, p.usage_text,

p.txt, p.rpo_txt, indx,

max( indx) over ( partition by p.txt ) mx_indx,

lag( p.rpo_txt,1 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) prev,

lag( p.rpo_txt,-1 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) nxt,

lag( p.rpo_txt,-2) over ( partition by p.logical_partition_key,p.txt order
by indx ) nxt2,

lag( p.rpo_txt,2 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) prv2,

lag( p.rpo_txt,3 ) over ( partition by p.logical_partition_key,p.txt order
by indx ) prv3

from parse p

)

select * from prv_nxt_token;

logical_partition_keyusage_texttxtrpo_txtindxmx_indxprevnxtnxt2prv2prv3
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "F" "1" "7" "[NULL]" "(" "T61"
"[NULL]" "[NULL]"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "2" "7" "F" "T61" ")"
"[NULL]" "[NULL]"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "T61" "3" "7" "(" ")" "(" "F"
"[NULL]"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "4" "7" "T61" "(" "EXPORT"
"(" "F"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "(" "5" "7" ")" "EXPORT" ")"
"T61" "("
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" "EXPORT" "6" "7" "(" ")"
"[NULL]" ")" "T61"
"TEST_DATA" "F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "7" "7" "EXPORT" "[NULL]"
"[NULL]" "(" ")"

When I run the same statement, except this time using a large table instead
of a values statement, I get the wrong answer.  The difference in the SQL
statement that produced the following data is that the large table (10M
records) and there is a closing where condition used to limit the result to
what is shown:

select * from prv_nxt_token;
is replaced by:

select * from prv_nxt_token where logical_partition_key='TEST_DATA' and
usage_text='F(T61)(EXPORT)';

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?

-- 
Rumpi Gravenstein

---

From: Tom Lane <[email-masked]>
Date: Thu, 29 Aug 2024 20:07:13 -0400

> Is this a PostgreSQL bug?

Maybe, but you haven't provided enough information to let anyone else
reproduce the behavior.

Personally I'm suspicious that because your lag() calls are over

    partition by p.logical_partition_key, p.txt order by indx

but then you filter by

    where logical_partition_key='TEST_DATA' and usage_text='F(T61)(EXPORT)';

that the lag() functions are seeing some rows that don't show up in
the final output.  (This'd require that some output rows from "parse"
share txt values but not usage_text values, but that certainly looks
like it's possible.)  So IMO you have not actually demonstrated that
there is any bug.

			regards, tom lane

---

From: David Rowley <[email-masked]>
Date: Fri, 30 Aug 2024 13:24:17 +1200

On Fri, 30 Aug 2024 at 11:18, Rumpi Gravenstein <[email-masked]> wrote:

> "F(T61)(EXPORT)" "F(T61)(EXPORT)" ")" "7" "7" ")" "[NULL]" "[NULL]"
> "EXPORT" "EXPORT"

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

---

From: Rumpi Gravenstein <[email-masked]>
Date: Fri, 30 Aug 2024 07:37:15 -0400

<snip>
the final output.
</snip>

I'm under the impression that the predicate filter is applied before the
analytic is evaluated.  Are you suggesting that I have this wrong -- the
analytic is evaluated and then the filter is applied?

-- 
Rumpi Gravenstein

---

From: Rumpi Gravenstein <[email-masked]>
Date: Fri, 30 Aug 2024 07:45:29 -0400

Here's the output of the script you requested.

-- 
Rumpi Gravenstein

---

From: Rumpi Gravenstein <[email-masked]>
Date: Fri, 30 Aug 2024 07:55:40 -0400

Wait...I see my issue.  Duh!  The where clause is applied after the CTE is
evaluated

-- 
Rumpi Gravenstein

---

From: David Rowley <[email-masked]>
Date: Sat, 31 Aug 2024 01:08:24 +1200

> Here's the output of the script you requested.

These results look correct to me. Not the same as the ones you originally
reported.

David