Careful with making the database adhere to people’s “expectations". MySQL used to guarantee
that the output of GROUP BY is sorted and they lived to regret it.
> On Aug 1, 2015, at 8:58 AM, Jacques Nadeau <jacques@apache.org> wrote:
>
> I think Aman and Vicki mentioned that this is a situation where most
> databases diverge from the spec since people have a certain expectation.
>
> On Fri, Jul 31, 2015 at 9:56 PM, Julian Hyde <jhyde@apache.org> wrote:
>
>>
>>> Jinfeng, the subquery's ORDER-BY can be dropped in some cases but not
>> all..
>>> for instance in the following query:
>>> SELECT a1 FROM (SELECT a1 FROM t1 WHERE .... ORDER BY a1) LIMIT 10;
>>> The OB should not be dropped. There are other cases, this is one
>> example.
>>
>> FWIW, My understanding of the SQL standard is that that ORDER BY can be
>> dropped. You can’t rely on the order of output from a sub-query. If you
>> want the desired effect you have to combine ORDER BY and LIMIT into the
>> same clause.
>>
>> Julian
>>
>>
|