Thread: Analytic Function Bug
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_key | usage_text | txt | rpo_txt | indx | mx_indx | prev | nxt | nxt2 | prv2 | prv3 |
---|---|---|---|---|---|---|---|---|---|---|
"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]" | "(" | ")" |
select * from prv_nxt_token where logical_partition_key='TEST_DATA' and usage_text='F(T61)(EXPORT)';
logical_partition_key | usage_text | txt | rpo_txt | indx | mx_indx | prv | nxt | nxt2 | prv2 | prv3 |
---|---|---|---|---|---|---|---|---|---|---|
"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" |
Rumpi Gravenstein <rgravens@gmail.com> writes: > 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
the final output.
Rumpi Gravenstein <rgravens@gmail.com> writes:
> 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
<snip>that the lag() functions are seeing some rows that don't show up in
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?On Thu, Aug 29, 2024 at 8:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:Rumpi Gravenstein <rgravens@gmail.com> writes:
> 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--Rumpi Gravenstein
Here's the output of the script you requested.
David