Re: [HACKERS] alter_table.sql

OK - got it! It is because vacuum analyze <tablename> doesn't work for me,
therefore the select doesn't use indices, so uses a sequential rather than
index scan => my rows are returned out of order.

Thanks for the pointer.

Cheers,

Patrick

On Tue, Mar 07, 2000 at 11:19:08AM -0500, Tom Lane wrote:
> Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)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