Re: [SQL] Re: "order by" weirdness

>> consider these three statements:
>>
>> create table t (id integer, date datetime ) ;
>> select id from t group by id ;
>> select id from t group by id order by max(date) ;
>>
>> Is it correct behavior that the second select returns
>> one row whereas the first select returns zero rows?

No, it is not. (Although it took a few rounds of discussion in the
mailing lists to get everyone to agree on that... if you check the
archives you will find this issue has come up repeatedly.)

I have in fact just fixed this in current sources. So, in 6.6 and
later, both statements will return zero rows if t is empty.

Note that with aggregates and no GROUP BY, you will get a row:

select count(id) from t ;
count
-----
0
(1 row)

select max(id) from t ;
max
---

(1 row)

which is correct behavior and will not change.

regards, tom lane