Support `ORDER BY ... NULLS FIRST/LAST` in the SQL API

[igorlukanin]

Data model Consider the following data model:

cubes:
  - name: employees
    sql: >
      SELECT 1 AS id, 'Ali' AS first_name, 'Los Gatos' AS city UNION ALL
      SELECT 2 AS id, 'Bob' AS first_name, 'San Diego' AS city UNION ALL
      SELECT 3 AS id, 'Eve' AS first_name, 'San Diego' AS city UNION ALL
      SELECT 4 AS id, 'Foe' AS first_name, NULL AS city
 
    measures:
      - name: count
        type: count
 
    dimensions:
      - name: city
        sql: city
        type: string

SQL queries I would like the SQL API to support ORDER BY ... NULLS FIRST/LAST, similarly to how Postgres supports it: https://www.postgresql.org/docs/current/queries-order.html

Currently, the SQL API just ignores NULLS FIRST/LAST:

# CUBESQL_SQL_PUSH_DOWN=false
=> SELECT city, count FROM employees ORDER BY city;
   city    | count 
-----------+-------
 Los Gatos |     1
 San Diego |     2
           |     1
(3 rows)

=> SELECT city, count FROM employees ORDER BY city NULLS FIRST;
   city    | count 
-----------+-------
 Los Gatos |     1
 San Diego |     2
           |     1
(3 rows)

=> SELECT city, count FROM employees ORDER BY city NULLS LAST;
   city    | count 
-----------+-------
 Los Gatos |     1
 San Diego |     2
           |     1
(3 rows)

# CUBESQL_SQL_PUSH_DOWN=true
=> SELECT city, MEASURE(count) FROM employees GROUP BY city ORDER BY city;
   city    | measure(employees.count) 
-----------+--------------------------
 Los Gatos |                        1
 San Diego |                        2
           |                        1
(3 rows)

=> SELECT city, MEASURE(count) FROM employees GROUP BY city ORDER BY city NULLS FIRST;
   city    | measure(employees.count) 
-----------+--------------------------
 Los Gatos |                        1
 San Diego |                        2
           |                        1
(3 rows)

=> SELECT city, MEASURE(count) FROM employees GROUP BY city ORDER BY city NULLS LAST;
   city    | measure(employees.count) 
-----------+--------------------------
 Los Gatos |                        1
 San Diego |                        2
           |                        1
(3 rows)

I would expect the results of the second query to have the row with NULL in city at the first position.

Version: 0.35.58

Additional context Inspired by a conversation in Slack: https://cube-js.slack.com/archives/C04NYBJP7RQ/p1720814502553919

[singhals]

Would I be able to control this at the data model level? I would like to specify that a dimension should order by nulls last for example. we use the rest api heavily from our frontend

[jwilson232]

Is there currently a workaround for this? The steps outlined in the custom sorting guide don’t seem to fully meet our needs, as our use case involves multiple dimensions that end users can dynamically choose to sort by.