Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-969

Composite EnumerableSort with DESC wrongly sorts NULL values low

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.6.0
    • Component/s: None
    • Labels:
      None

      Description

      NULL first or NULL last is not clearly defined by sql spec, but should be consistent in calcite.

      I spot order by (A desc) returns NULL last, but order by (A desc, B) returns NULL first.

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

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

        Activity

        Hide
        liyang.gmt8@gmail.com liyang added a comment -

        Copy solution discussion from email.

        Julian Hyde <jhyde@apache.org>
        Nov 14

        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.

        Show
        liyang.gmt8@gmail.com liyang added a comment - Copy solution discussion from email. Julian Hyde <jhyde@apache.org> Nov 14 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.
        Hide
        julianhyde Julian Hyde added a comment -

        liyang, Can you post the EXPLAIN of each of the queries?

        Show
        julianhyde Julian Hyde added a comment - liyang , Can you post the EXPLAIN of each of the queries?
        Hide
        julianhyde Julian Hyde added a comment -

        Changing the subject of this issue to focus on just the bug in EnumerableSort. Only occurs when sort is composite and sort item has DESC.

        I have logged CALCITE-970 to deal with the other matters.

        Show
        julianhyde Julian Hyde added a comment - Changing the subject of this issue to focus on just the bug in EnumerableSort. Only occurs when sort is composite and sort item has DESC. I have logged CALCITE-970 to deal with the other matters.
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/80bbb5bf .
        Hide
        liyang.gmt8@gmail.com liyang added a comment -

        Sorry I overlooked "liyang, Can you post the EXPLAIN of each of the queries?"..

        Many thanks to Julian Hyde for the quick fix. Looking forward to 1.6 release.

        Show
        liyang.gmt8@gmail.com liyang added a comment - Sorry I overlooked "liyang, Can you post the EXPLAIN of each of the queries?".. Many thanks to Julian Hyde for the quick fix. Looking forward to 1.6 release.
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.6.0 (2016-01-22).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.6.0 (2016-01-22).

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            liyang.gmt8@gmail.com liyang
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development