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 <[email protected]> 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 > >
