[CALCITE-970] If NULLS FIRST/LAST not specified, sort NULL values HIGH

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.6.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 16/Nov/15 19:00
• Updated: 21/Jan/16 22:20
• Resolved: 17/Nov/15 19:33

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.