Cube SQL FROM clause generation error with table name schema

[simonedbarber]

Describe the bug
Cube SQL generation generates invalid SQL when using sql_table with a schema-qualified table name (e.g., schema.table). When the cube is dynamically generated from database introspection, the scaffolding automatically includes the database schema prefix in the sql_table parameter. However, when the database connection is already scoped to that schema (or the schema doesn't need to be explicitly referenced), the generated SQL fails to execute because the schema prefix in sql_table is not valid for the target database context.

The core issue is that there is no way to control whether the schema prefix should be included in the sql_table value during dynamic cube model generation. The scaffolding code in BaseSchemaFormatter.ts always includes the schema prefix when present in the database metadata.

This ocurrs with both the the legacy and tesseact planners.

To Reproduce
Steps to reproduce the behavior:

1. Connect Cube to a database that has tables in a non-default schema (e.g., PostgreSQL with a dvd_rental schema containing a staff table)
2. The generated cube will have sql_table: dvd_rental.staff (schema-qualified)
3. Query the cube using the REST API or Playground
4. Observe that the generated SQL includes FROM dvd_rental.staff AS "staff"
5. Query the Cube SQL endpoint with generated Cube SQL - the FROM dvd_rental.staff AS "staff"fails. FROM staff AS "staff" or using the cube name FROM Staff AS "staff" works

Expected behavior
SQL Generation should generate the Cube SQL to reference the Cube name not the underlying table name/schema+table name

The generated SQL should be valid and executable against the configured database connection without requiring manual modification of the schema prefix.

Screenshots
N/A

Minimally reproducible Cube Schema

# This cube is generated by Cube's scaffolding when introspecting 
# a table "staff" in schema "dvd_rental"
cubes:
  - name: Staff
    sql_table: dvd_rental.staff  # Schema prefix automatically added by scaffolding
    
    dimensions:
      - name:  staff_id
        sql: staff_id
        type: number
        primary_key: true
      
      - name: store_id
        sql: store_id
        type: number
      
      - name: address_id
        sql: address_id
        type: number

    measures:
      - name: count
        type: count
      
      - name: address_id_max
        sql: address_id
        type: max

Generated SQL (fails):

SELECT
  "staff". store_id "staff__store_id", 
  count("staff".address_id) "staff__count", 
  max("staff".address_id) "staff__address_id_max"
FROM
  dvd_rental.staff AS "staff"  
GROUP BY 1 
ORDER BY 2 DESC, 3 DESC, 1 ASC 
LIMIT 1000

Working SQL (after manually removing schema prefix):

SELECT
  "staff".store_id "staff__store_id", 
  count("staff".address_id) "staff__count", 
  max("staff".address_id) "staff__address_id_max"
FROM
  staff AS "staff"  
GROUP BY 1 
ORDER BY 2 DESC, 3 DESC, 1 ASC 
LIMIT 1000

Version:
Affects multiple versions as was running latest. Currently v1.6.1

Additional context
LLM Generated analysis:
The issue originates in the scaffolding code at [packages/cubejs-schema-compiler/src/scaffolding/formatters/BaseSchemaFormatter.ts](https://github.com/cube-js/cube/blob/master/packages/cubejs-schema-compiler/src/scaffolding/formatters/BaseSchemaFormatter. ts#L129-L155):

let table = `${
  tableSchema.schema?. length ? `${this.escapeName(tableSchema.schema)}.` : ''
}${this.escapeName(tableSchema.table)}`;

This code always prepends the schema name when it exists in the database metadata, with no option to exclude it.

I believe it should reference the cube name.

[igorlukanin]

Hi @simonedbarber 👋

Query the Cube SQL endpoint with generated Cube SQL - the FROM dvd_rental.staff AS "staff"fails. FROM staff AS "staff" or using the cube name FROM Staff AS "staff" works

This line looks quite confusing to me and probably explains what goes wrong for you.

The generated SQL is not intended to be run against Cube's own SQL API endpoint. It's generated for the underlying data source. Your original tables are not available directly via Cube's SQL API, only your cubes and views, hence the error.

I hope this helps.