Tesseract engine generating Postgresql dialect queries for MSSQL DB

[UsmanYasin]

Describe the bug
While experimenting with Tesseract engine with MSSQL database, I have noted that the queries being generated do not follow T-SQL dialect but are generating PostgreSQL-ish dialect queries

To Reproduce
Steps to reproduce the behavior:

1. Start cube.dev with a test data model
2. Select a column to query and check the generated SQL. The generated SQL is following T-SQL dialect and runs fine.
3. Enable Tesseract engine by setting the env variable true and restart cube.dev
4. Select a column to query and check the generated SQL. The generated SQL is following postgresql dialect and fails to run on mssql.

Expected behavior
Tesseract should generate SQL based on the dialect of the underlying DBMS just as cube.dev generates queries based on the DBMS dialect when Tesseract is not being used.

Screenshots
Without Tesseract:

With Tesseract:

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(`TestModel`, {
    sql: `
    select 'Europe' as Continent, 'Germany' as  Country
    UNION ALL
    select 'Europe' as Continent, 'France' as  Country
    UNION ALL
    select 'Europe' as Continent, 'Norway' as  Country
    UNION ALL
    select 'Asia' as Continent, 'China' as  Country
    UNION ALL
    select 'Asia' as Continent, 'Singapore' as  Country
    `,
    measures: {
      count: {
        type: `count`,
      },
    },
    dimensions: {
      continent: {
        sql: `Continent`,
        type: `string`,
      },
      country: {
        sql: `Country`,
        type: `string`,
      },
    },
  });

Version:
1.3.14

Additional context
N/A

[UsmanYasin]

Hi team 👋

I did some initial investigation into this, and based on my current understanding of the codebase, the issue seems to stem from the sqlTemplates in MssqlQuery.ts

It looks like MssqlQuery.ts does not override some of the key templates — such as templates.statements.select, templates.params.param, and a few others. As a result, the query generation falls back to the defaults from BaseQuery.js, which aren’t fully compatible with T-SQL.

Overriding these templates locally has resulted in T-SQL-compatible queries on my end. Could you please confirm if I’m on the right track here or if I’m missing something?

Happy to put together a PR if this direction makes sense. Thanks!

[KSDaemon]

Hi @UsmanYasin you're absolutely on the right way! Currently Tesseract doesn't fully support all dialects that are supported by current engine. And while there are some cases when special flow needs to be introduced in the tesseract code base, most of the things are resolved with the templates. Have a look at Postgres/BigQuery/Athena dialect templates (as they are more supported by tesseract). Looking forward to your PR with support for MsSQL!

[KSDaemon]

You can try to run drivers tests with tesseract turned on to see how big is the work area :)

cd packages/cubejs-testing-drivers
DRIVERS_TESTS_CUBEJS_TESSERACT_SQL_PLANNER=true yarn run mssql-full --mode=local

Hoping you have docker/podman installed/running.

Ideally - all the tests should pass and results in the same snapshots. But in practice this doesn't happen. So you can disable some failing tests. Have a look at bigquery.json fixtures config as an example.

[KSDaemon]

Feel free to ping us if you need any help/advice.

[UsmanYasin]

Thanks @KSDaemon for the detailed response, I have been testing the changes locally to fully support mssql, clickhouse, mysql and some other databases with tesseract by modifying the sqltemplates. With your confirmation of the approach, I'll test them locally by running the driver tests and will send PRs incrementally.