Skip to content

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

Description

@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

Activity

  1. added
    enhancementLEGACY. Use the Feature issue type instead
    api:sqlIssues related to SQL API
    on Jul 15, 2024
  2. singhals commented on Oct 8, 2024

    @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

  3. jwilson232 commented on Nov 18, 2024

    @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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

api:sqlIssues related to SQL API

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions