Re: "order by" and "order by asc" returning different results on date field

Rick Gentry <rick(dot)gentry(at)zenus-biometrics(dot)com> writes:
> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
> 20140911 (Red Hat 4.8.3-9), 64-bit

Hm. You might try updating to current (9.6.9), though this doesn't
sound much like any recent bug fix.

> The result set looks like some form of merge sort was being performed, but
> the system decided to skip the last few merge iterations.
>
> You're correct, the statement I've sent does not reproduce the issue. The
> actual query I'm using is much longer, but I've managed to reproduce the
> issue with "select * from tbl_recognition_logging where action='identify'
> order by created". 'action' is a text field and I see that pgAdmin 4
> highlights it like a reserved word.

Can we see EXPLAIN ANALYZE output for both the working and non-working
cases?

If the system is relying on an indexscan to produce the sort ordering,
it's possible that this traces to some kind of index corruption. But
that's purely a guess.

On the whole, I think we're going to need a self-contained test case
to get far with this.

regards, tom lane