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
