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.

        Attachments

          Activity

            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: