Re: NEXT VALUE FOR sequence

Ashutosh Bapat wrote:
> On Tue, Feb 20, 2018 at 8:39 PM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> > Tom Lane wrote:
> > > Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> > > > The SQL standard has the expression "NEXT VALUE FOR asequence" to do
> > > > what we traditionally do with "nextval('asequence')".
> > > > This is an attempt to implement this on top of the recently introduced
> > > > NextValueExpr node.
> > >
> > > This has been proposed repeatedly, and rejected repeatedly, because in
> > > fact the standard's semantics for NEXT VALUE FOR are *not* like nextval().
> > > See SQL:2011 4.22.2 "Operations involving sequence generators":
> > >
> > > If there are multiple instances of <next value expression>s specifying
> > > the same sequence generator within a single SQL-statement, all those
> > > instances return the same value for a given row processed by that
> > > SQL-statement.
> > >
> > > This is not terribly exact --- what is a "processed row" in a join query,
> > > for instance? But it's certainly not supposed to act like independent
> > > executions of nextval() or NextValueExpr would. Pending somebody doing
> > > the legwork to produce something that at least arguably conforms to the
> > > spec's semantics, we've left the syntax unimplemented.
> >
> > Would it be reasonable to say that any two NextValueExpr in the same
> > target list are "in one row"?
>
> I think, "processed row" thing gets pretty complicated. Consider
> simple case. What happens when NextValueExpr appears in one of the
> conditions and that row gets eliminated, do we consider that as a
> processed row and increment the NextValueExpr or do not increment it?

I think that is an unrelated question.

In my opinion both would be ok. SQL does not decree in which order
conditions are evaluated, and it is ok to evaluate an expression even if
it is never used, right?

All that has to be guaranteed is that using "NEXT VALUE FOR asequence"
several times "in one row" (whatever exactly that is) will return the
same value each time.

The exact rules seem to be:

a) If NVE is directly contained in a <query specification> QS, then the
General Rules of Subclause 9.21, “Generation of the next value of a
sequence generator”, are applied once per row in the result of QS
with SEQ as SEQUENCE. The result of each evaluation of NVE for a given
row is the RESULT returned by the General Rules of Subclause 9.21,
“Generation of the next value of a sequence generator”.

I understand:
For each NEXT VALUE FOR that appears in a select list (not a subselect),
return the same value per row.

b) If NVE is directly contained in a <contextually typed table value constructor>
TVC, then the General Rules of Subclause 9.21, “Generation of the
next value of a sequence generator”, are applied once per
<contextually typed row value expression> contained in TVC.
The result of each evaluation of NVE for a given <row value expression>
is the RESULT returned by the General Rules of Subclause 9.21,
“Generation of the next value of a sequence generator”.

I understand:
In a VALUES clause, each invocation of NEXT VALUE FOR should return the same value.

c) If NVE is directly contained in an <update source>, then the General Rules
of Subclause 9.21, “Generation of the next value of a sequence generator”,
are applied once per row to be updated by the <update statement: searched>
or <update statement: positioned>. The result of each evaluation of NVE for a
given row is the RESULT returned by the General Rules of Subclause 9.21,
“Generation of the next value of a sequence generator”.

I understand:
In an UPDATE statement, all invocations of NEXT VALUE FOR in the SET clause
should return the same value per row.

Yours,
Laurenz Albe