Cube.js: MSSQL order by not supported in subquery

[fgamr]

When I'm trying to use a subquery in MSSQL I'm getting the following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I'm trying to reproduce the same behaviour of the following query:

select COUNT(*) as VALUE, sg.NAME from (
   select MAX(ts) as TS from wo_status_log
   group by ID_WORK_ORDER) w join wo_status_log wsl
on w.TS=wsl.TS and w.ID_WORK_ORDER=wsl.ID_WORK_ORDER
join status s on s.ID=wsl.ID_STATUS_TO
join status_group sg on sg.ID=s.ID_STATUS_GROUP
join work_order wo on w.ID_WORK_ORDER=wo.ID
group by sg.NAME

This is the cube that I created:

cube(`WoStatusLog`, {
  sql: `SELECT * FROM dbo.wo_status_log`,
  joins: {
    StatusFrom: {
      relationship: `hasOne`,
      sql: `${WoStatusLog}.id_status_from = ${StatusFrom}.id`
    },
    StatusTo: {
      relationship: `hasOne`,
      sql: `${WoStatusLog}.id_status_to = ${StatusTo}.id`
    },
    WorkOrder: {
      relationship: `hasOne`,
      sql: `${WoStatusLog}.id_work_order = ${WorkOrder}.id`
    }
  },
  measures: {
    count: {
      type: `count`,
      drillMembers: [id]
    },
    dateMax: {
      sql: `${CUBE}."TS"`,
      type: `max`
    }
  },
  dimensions: {
    id: {
      sql: `${CUBE}."ID"`,
      type: `number`,
      primaryKey: true
    },
    fake: {
      sql: `${CUBE}."FAKE"`,
      type: `string`
    },
    ts: {
      sql: `${CUBE}."TS"`,
      type: `time`
    },
    id_work_order: {
      sql: `${CUBE}."ID_WORK_ORDER"`,
      type: `number`
    },
    id_status_from: {
      sql: `${CUBE}."ID_STATUS_FROM"`,
      type: `number`
    },
    id_status_to: {
      sql: `${CUBE}."ID_STATUS_TO"`,
      type: `number`
    },
    maxDates: {
      sql: `${CUBE.dateMax}`,
      type: `time`,
      subQuery: true
    }
  }
});

And this is the query that I'm trying to run:

{
  "measures": [
    "WoStatusLog.count"
  ],
  "timeDimensions": [],
  "dimensions": [
    "WoStatusLog.maxDates"
  ],
  "filters": []
}

Expected behaviour
The query should run successfully.

Version:
"@cubejs-backend/mssql-driver": "0.19.17"
"@cubejs-backend/server": "0.19.19"

Thanks!

[paveltiunov]

@fgamr Hey Fausto! Thanks for posting this! Could you please share generated SQL by Cube.js?

[fgamr]

@paveltiunov Sure! Here it is:

SELECT
  TOP 10000 "wo_status_log__max_dates" "wo_status_log__max_dates",
  count("wo_status_log"."ID") "wo_status_log__count"
FROM
  dbo.wo_status_log AS "wo_status_log"
  LEFT JOIN (
    SELECT
      "wo_status_log_max_dates_subquery___wo_status_log"."ID" "wo_status_log__id",
      max(
        "wo_status_log_max_dates_subquery___wo_status_log"."TS"
      ) "wo_status_log__max_dates"
    FROM
      dbo.wo_status_log AS "wo_status_log_max_dates_subquery___wo_status_log"
    GROUP BY
      "wo_status_log_max_dates_subquery___wo_status_log"."ID"
    ORDER BY
      2 DESC
  ) AS "wo_status_log_max_dates_subquery" ON "wo_status_log_max_dates_subquery"."wo_status_log__id" = "wo_status_log"."ID"
GROUP BY
  "wo_status_log__max_dates"
ORDER BY
  2 DESC

If you have any other question, just ask!

[paveltiunov]

@fgamr Gotcha. Yep. We need to pass empty ordering while building sub queries.

[ivanaszuber]

Hope it's ok to jump in and give a +1 for this issue. I'm afraid we're quite blocked by this as we're not able to have subqueries in mssql. I see there is a PR open for the fix, curious @paveltiunov if there is also a timeline when this might be released so that we can plan our features accordingly 🙏 ?