Support ORDER BY ... NULLS FIRST/LAST in the SQL API #8464
Description
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: stringSQL 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