Analytic Function Bug
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
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
On Fri, 30 Aug 2024 at 11:18, Rumpi Gravenstein <rgravens@gmail.com> 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
Attachments:
<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.
On Thu, Aug 29, 2024 at 9:24 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Fri, 30 Aug 2024 at 11:18, Rumpi Gravenstein <rgravens@gmail.com>
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.txtI'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
--
Rumpi Gravenstein
Attachments:
lagscript.txttext/plain; charset=US-ASCII; name=lagscript.txtDownload
Wait...I see my issue. Duh! The where clause is applied after the CTE is
evaluated
On Fri, Aug 30, 2024 at 7:37 AM Rumpi Gravenstein <rgravens@gmail.com>
wrote:
<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
--
Rumpi Gravenstein