Re: INSERT INTO .. SELECT nextval() ORDER BY - returns unexpectedly ordered values

Thanks, that makes sense!

On Thu, Sep 3, 2015 at 2:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Sjon Hortensius <sjon(at)hortensius(dot)net> writes:
> > INSERT INTO test2 SELECT name, nextval('tmp'), id FROM test ORDER BY id
> ASC;
>
> > I have worked around this by clustering the old table on the new id
> before
> > SELECTing but this behavior doesn't seem to be documented, is this a bug?
>
> No. You're assuming that the nextval() happens after the row ordering,
> but this is not necessarily so --- indeed, a strict reading of the SQL
> standard would imply that it should *never* be so, because the standard
> execution model is that ORDER BY happens after computing the SELECT list.
> (Without that, locutions like "ORDER BY 1" would make no sense.) It might
> accidentally work if the ORDER BY were done via an indexscan rather than
> an explicit sort step, but that's not possible in your test case.
>
> Try something like this to force the evaluation order:
>
> INSERT INTO test2
> SELECT name, nextval('tmp'), id FROM
> (SELECT name, id FROM test ORDER BY id ASC) ss;
>
> regards, tom lane
>