Sadly no. The first example (order by COUNTRY DESC) returns NULL at last, not like Oracle's "NULLS FIRST is the default for descending order".
On Fri, Nov 13, 2015 at 11:40 AM, Julian Hyde <[email protected]> wrote: > I don’t know whether we ever spelled out what Calcite’s behavior is. I > don’t think the SQL standards specifies, so let’s look to Oracle. The > Oracle specification is as follows[1]: > > > NULLS LAST is the default for ascending order, and NULLS FIRST is the > default for descending order. > > Is Calcite consistent with that? > > Julian > > > > > > On Nov 12, 2015, at 7:30 PM, Li Yang <[email protected]> wrote: > > > > Encountered a seemingly inconsistent behavior about NULL in order by. > Could > > anyone shed some light? (calcite 1.4 release) > > > > When a column contains NULL value and order by it desc, the NULL is > > positioned at the end of result. > > > > E.g. "select cal_dt, country, ... from xxx order by COUNTRY DESC" > returns > > > > 2013-08-11 US .... > > 2013-08-18 FR .... > > 2013-03-31 null .... > > 2013-11-10 null .... > > > > However if order by one more column, the NULL will come to the top this > > time. > > > > E.g. "select cal_dt, country, ... from xxx order by COUNTRY DESC, CAL_DT > > ASC" returns > > > > 2013-03-31 null .... > > 2013-11-10 null .... > > 2013-08-11 US .... > > 2013-08-18 FR .... > > > > I understand NULL's order comparing to other values is vague in SQL, > either > > putting it first or last is fine. However do expect the order is > > consistent. From a user point of view, if it seems NULL is the smallest, > > then it should always be so. > > > > Thanks > >
