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
