[Bug] PostgreSQL data source receives MySQL-style SQL (backticks, CONVERT_TZ, DATE_FORMAT) in multi-datasource setup

[not-yes]

Describe the bug

In a multi-datasource configuration, when a cube uses data_source: 'postgres' (or similar) and the driverFactory correctly returns a PostgresDriver instance, the generated SQL still uses MySQL dialect instead of PostgreSQL. As a result, queries fail with syntax errors when executed against PostgreSQL.

Observed MySQL syntax in generated SQL (sent to Postgres):

• Identifier quoting: Backticks (`) instead of double quotes (")
• Functions: CONVERT_TZ(), convert_tz(), DATE_FORMAT()
• Session: @@session.time_zone
• Types/casts: TIMESTAMP(), CAST(... AS DATETIME), UNIX_TIMESTAMP()

Expected: The schema-compiler / query generator should use the target data source's dialect (here: PostgreSQL) when building SQL, so that:

• Identifiers are quoted with " instead of `
• CONVERT_TZ → (... AT TIME ZONE 'tz') AT TIME ZONE 'tz'
• DATE_FORMAT → TO_CHAR(..., 'format')
• @@session.time_zone → 'UTC' or equivalent
• TIMESTAMP() / CAST(... AS DATETIME) / UNIX_TIMESTAMP() → PostgreSQL equivalents

To Reproduce

Environment:

• @cubejs-backend/server-core: 1.6.1 (also checked 1.6.4; issue persists)
• @cubejs-backend/postgres-driver: 1.6.1
• @cubejs-backend/mysql-driver: 1.6.1

Configuration (cube.js):

• driverFactory returns:
  • MySQLDriver for dataSource === 'default' (or when not postgres)
  • PostgresDriver for dataSource === 'postgres' (or postgresDataSource)
• Cubes with data_source: 'postgres' exist and use sql_table, measures, dimensions, and time dimensions.

Steps:

1. Define a cube with data_source: 'postgres' and a time dimension (e.g. granularities: ['day', 'month']).
2. Send a /cubejs-api/v1/load (or equivalent) query that hits that Postgres cube.
3. Inspect the SQL passed to PostgresDriver.query() (e.g. via logging or a patch).

Result: The SQL contains MySQL syntax (backticks, CONVERT_TZ, DATE_FORMAT, @@session.time_zone, TIMESTAMP(), CAST(... AS DATETIME), UNIX_TIMESTAMP()), causing PostgreSQL to reject it.

Workaround

We apply a monkey-patch on PostgresDriver.prototype.query and executeQuery to transform the SQL before execution:

1. Replace backtick ` with double quote "
2. Replace CONVERT_TZ / convert_tz / @@session.time_zone with PostgreSQL AT TIME ZONE expressions
3. Replace DATE_FORMAT with TO_CHAR
4. Replace TIMESTAMP(), CAST(... AS DATETIME), UNIX_TIMESTAMP() with PostgreSQL equivalents

This works but is fragile and must be reapplied when upgrading; we'd prefer an upstream fix.

Related

• Can't find dialect for 'datasource1' data source: undefined #6890 ("Can't find dialect for 'datasource1' data source: undefined"): different symptom (dialect not resolved at all). Our case: dialect/driver is Postgres, but generated SQL uses MySQL dialect.
• Docs say Cube uses dbType / connection to choose identifier quoting and dialect; in our multi-datasource + Postgres setup, the compiler appears to use a default (MySQL) dialect for SQL generation.

Root cause hypothesis

The schema-compiler (or the layer that generates SQL for a query) may:

• Use a single, default dialect (e.g. MySQL) when compiling, regardless of the cube's data_source, or
• Resolve data_source for the driver but not pass the correct dialect into the SQL generator, so identifier quoting and function translation still follow the default.

We have not confirmed the exact call path; the above is inferred from "PostgresDriver receives MySQL-style SQL."

Additional context

• We use both driverFactory and data_source on cubes; the Postgres driver and connection work; the failure is purely from SQL syntax.
• The same cube definition works when the default (and only) data source is Postgres; the problem appears in multi-datasource when the default is MySQL and a non-default data source is Postgres.

---

References:

• Multiple data sources
• Postgres data source

[igorlukanin]

Hi @not-yes 👋

It would be great if you can show the exact driver_factory implementation that you use. I've tested this myself and it works as it should.

My wild guess would be that you're returning instances of MySQLDriver and PostgresDriver classes in your JavaScript code—and this makes Cube unable to obtain the db_type for them. The workarounds are either to return a dictionary of properties with dbType explicitly set (see example in the docs) or to set CUBEJS_DB_TYPE (and the decorated one for the other data source) as an environment variable.