Re: [HACKERS] alter_table.sql

Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> In the alter table regression test, alter_table.sql, it says:
> -- 20 values, sorted
> SELECT unique1 FROM ten_k WHERE unique1 < 20;
> Why sorted? Shouldn't it be
> SELECT unique1 FROM ten_k WHERE unique1 < 20 ORDER BY unique1;
> if we really expect the output to be sorted?

The regression test author evidently expected the optimizer to choose an
indexscan, which will produce the values in sorted order as a byproduct.
I agree this code is bogus in a theoretical sense, but I don't think
it's worth worrying about until we alter the optimizer so far that it
doesn't choose an indexscan for this query.  (Indeed, that might be a
sign of an optimizer bug --- so I'd look into why the behavior changed
before changing the regress test.)

Since our regress tests are checked on the basis of exact equality of
output, in theory every single regress test SELECT that doesn't specify
"ORDER BY" is broken, because in theory the system could choose to put
out the tuples in some other order than what's in the regress test
reference outputs.  But in practice, the implementation-dependent
ordering you get is reproducible across platforms, so the tests
accomplish what they're supposed to.  Every so often we have to throw in
an ORDER BY when we find that one of the test cases isn't so
reproducible.
        regards, tom lane