Re: Failure assertion in GROUPS mode of window function in current HEAD

On Tue, Jul 10, 2018 at 7:24 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> writes:
>> I got an assertion failure when I use GROUPS mode and specifies offset
>> without ORDER BY clause. The reproduction steps and the backtrace I
>> got are following.
>
>> =# create table test as select 1 as c;
>> =# select sum(c) over (partition by c groups between 1 preceding and
>> current row) from test;
>> TRAP: FailedAssertion("!(ptr >= 0 && ptr < state->readptrcount)",
>> File: "tuplestore.c", Line: 478)
>
> Hm. Shouldn't we reject this case? I don't see how GROUPS mode makes
> any sense with no ORDER BY. Maybe you could define it as working with
> "groups" of one row each, but the standard seems to think not:
>
> c) If GROUPS is specified, then:
>
> i) Either WDEF shall contain a <window order clause>, or WDEF shall
> specify an <existing window name> that identifies a window structure
> descriptor that includes a window ordering clause.
>
> The fact that you got an assertion failure is not very nice, maybe we
> need some more code defenses there; but probably the whole thing
> should be rejected at parse time.
>

Agreed. So should we reject only GROUPS offset mode without ORDER BY?
Although since I don't have the standard I'm not sure the ideal
behavior exactly there is the code that considers GROUPS non-offset
mode without ORDER BY.

BTW, I found an another but related issue. We can got an assertion
failure also by the following query.

=# select sum(c) over (partition by c order by c groups between 1
preceding and current row) from test;

The cause of this assertion failure is similar to the first problem I
reported; in the above case, since node->ordNumCols will be set to 0
we don't allocate both the frame head and tail pointer. The proposed
patch seems to fix the both problems but If we fix the first problem
by rejecting it as not-supported-feature error we need to fix the
second problem by other way.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center