MSSQL can't use CTEs

[chrismcv]

Describe the bug
Using mssql when my cube sql contains a CTE, I can't perform aggregations.

The following is valid syntax:

WITH cte_employees AS (
   select employee from employee_table
)
SELECT count(*) FROM cte_employees

Cube generates, which isn't valid in mssql:

SELECT COUNT(employee_id) FROM ( 
   WITH cte_employees AS (
      select employee from employee_table
   )
 )

To Reproduce
Steps to reproduce the behavior:

1. Add a model using the following

sql:  WITH cte_employees AS (
   select employee from employee_table
)
select * from cte_employees

Expected behavior
It should generate valid SQL for the driver.

Screenshots
If applicable, add screenshots to help explain your problem.

Minimally reproducible Cube Schema
In case your bug report is data modelling related please put your minimally reproducible Cube Schema here.
You can use selects without tables in order to achieve that as follows.

cube(
  'cube_practitioners',

  {
    shown: true,

    sql: `
       with 

        select_columns as (
            select
        
                practitionerID as practitioner_pk,

            from tblPractitioner

        )
        select * from select_columns
        `,

    dimensions: {
      // join keys
      practitioner_pk: {
        sql: `${CUBE}.practitioner_pk`,
        type: `string`,
        primaryKey: true,
        shown: false
      }
    },

    measures: {
      count_practitioners: {
        type: `count`
      }
    }
  }
);

Version:
0.35.20

[igorlukanin]

Hi @chrismcv 👋

Sorry, I was n't able to reproduce this. I used exactly the data model code that you've provided and tested it against a Fabric data warehouse that, I believe, uses the same SQL syntax like MS SQL Server.

Here's what I've got:

Could you please double-check your data model and maybe provide more details? It would be great to see the full output of the /v1/sql API endpoint of your REST API. Also, if you can reproduce this in Cube Cloud (on a free tier), it would be tremendously helpful.