Re: Analytic Function Bug

On Fri, 30 Aug 2024 at 11:18, Rumpi Gravenstein <rgravens(at)gmail(dot)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