https://issues.apache.org/jira/browse/CALCITE-969
On Sat, Nov 14, 2015 at 5:12 AM, Julian Hyde <[email protected]> wrote: > Can you log a JIRA case please? > > For extra credit, the fix would allow the position of nulls to be > configurable. In CalciteConnectionProperty, add > > enum NullCollation { LOW, HIGH, FIRST, LAST } > > and > > DEFAULT_NULL_COLLATION, default HIGH (last for ASC, first for DESC). > > The minimum fix is to support HIGH without looking at a config property. > > Julian > > > > On Nov 12, 2015, at 9:19 PM, Li Yang <[email protected]> wrote: > > > > 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 > >> > >> > >
