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
>>
>>
