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

If NULLS FIRST/LAST not specified, sort NULL values HIGH

    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

      We have not specified what Calcite does if you do not specify NULLS FIRST or NULLS LAST. The policy seems to have been to always sort nulls first, but was not consistent (see CALCITE-969).

      Now Calcite will treat nulls as HIGH by default. That means NULLS LAST is assumed for ASC, NULLS FIRST is assumed for DESC. This is the same behavior as Oracle.

      If a project does not like this behavior, they can change the new parameter "defaultNullCollation", which allows alternative policies. Its value is defined by

      enum NullCollation { LOW, HIGH, FIRST, LAST }

      The "defaultNullCollation" parameter only applies to user SQL. If you create a RelFieldCollation via RelBuilder or directly, and do not specify a NullDirection value, you will get HIGH semantics. We used to use NullDirection.UNSPECIFIED quite widely in RelNode land, and now it is very rarely used, except with Direction.CLUSTERED.

        Activity

        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/19a67e1d .
        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:
            julianhyde Julian Hyde
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development