Bug: Default gateway catalog leaks into catalog-unsupported secondary gateways

[mday-io]

Describe the bug

When a multi-gateway SQLMesh project uses a default gateway that has a default catalog set
(e.g., Trino with catalog: example_catalog), that catalog is silently prepended to model
names targeting secondary gateways that do not support catalogs (e.g., ClickHouse).

At evaluation time, the secondary gateway's engine adapter rejects the catalog:

UnsupportedCatalogOperationError:
  clickhouse does not support catalogs and a catalog was provided: example_catalog

The failure is deterministic. No ClickHouse model can be evaluated in a multi-gateway setup
where the default gateway has a default catalog set.

To Reproduce

1. Configure a multi-gateway SQLMesh project with a catalog-supporting default gateway and
   a ClickHouse secondary gateway
2. Create a model targeting the ClickHouse gateway:

   MODEL (
       name my_schema.my_model,
       kind VIEW,
       gateway 'clickhouse_gw',
       dialect 'clickhouse',
   );
   SELECT 1 AS id

3. Run sqlmesh plan. The model is loaded and its name is normalized to
   "example_catalog"."my_schema"."my_model" (three-part name with leaked catalog)
4. Attempt to evaluate or apply. Fails with UnsupportedCatalogOperationError

Minimal reproduction with Python:

from sqlglot import parse
from sqlmesh.core.model import load_sql_based_model

expressions = parse("""
    MODEL (name my_schema.my_model, kind FULL, gateway clickhouse_gw, dialect clickhouse);
    SELECT 1 AS id
""", read="clickhouse")

model = load_sql_based_model(expressions, default_catalog="example_catalog", dialect="clickhouse")

print(model.fqn)      # "example_catalog"."my_schema"."my_model"  <-- catalog leaked
print(model.catalog)   # example_catalog

Expected behavior

The model name should remain a two-part name ("my_schema"."my_model") when targeting a
gateway whose engine does not support catalogs. The default gateway's catalog should only apply
to models that target catalog-supporting gateways.

All the information needed to prevent the leak is available at model-loading time:

• Each adapter exposes catalog_support (returns CatalogSupport.UNSUPPORTED for ClickHouse)
• The per-gateway catalog dict is already built by the scheduler
• The model loader already has per-gateway override logic

The override logic simply doesn't account for gateways that are absent from the dict.

Root cause (identified)

The per-gateway catalog dict (default_catalog_per_gateway) is built by the scheduler in
core/config/scheduler.py:

def get_default_catalog_per_gateway(self, context):
    default_catalogs_per_gateway = {}
    for gateway, adapter in context.engine_adapters.items():
        if catalog := adapter.default_catalog:
            default_catalogs_per_gateway[gateway] = catalog
    return default_catalogs_per_gateway

Catalog-unsupported adapters return None for default_catalog (because the base adapter
short-circuits: if self.catalog_support.is_unsupported: return None). They are never added
to the dict.

The model loader in core/model/definition.py then checks the dict:

if (
    default_catalog_per_gateway
    and gateway_name
    and (catalog := default_catalog_per_gateway.get(gateway_name)) is not None
):
    loader_kwargs["default_catalog"] = catalog

When a gateway is not in the dict, this condition is False, and the global
default_catalog (from the default gateway) remains in loader_kwargs unchanged. It gets
prepended to the model name during normalization.

The loader cannot distinguish "this gateway has no catalog" from "this gateway wasn't checked"
because both cases result in the gateway being absent from the dict.

Impact

• ClickHouse (and potentially other UNSUPPORTED engines) cannot be used as a secondary
  gateway in any project where the default gateway has a default catalog set
• The plan step succeeds without warning. The error only surfaces at evaluation/apply time,
  after the user has already committed to the change
• Workarounds exist but are disruptive: removing the catalog from the default gateway
  connection, swapping which gateway is the default, or fully qualifying all model names

Suggested fix

Two possible fix locations:

Option A: In the scheduler (core/config/scheduler.py)

Explicitly register catalog-unsupported gateways with empty string in the per-gateway dict:

def get_default_catalog_per_gateway(self, context):
    default_catalogs_per_gateway = {}
    for gateway, adapter in context.engine_adapters.items():
        if catalog := adapter.default_catalog:
            default_catalogs_per_gateway[gateway] = catalog
        elif adapter.catalog_support.is_unsupported:
            default_catalogs_per_gateway[gateway] = ""
    return default_catalogs_per_gateway

This works because the model loader checks is not None. Empty string passes the check and
overrides default_catalog to "", which does not get prepended to the model name. The
empty string value represents "this gateway was checked and has no catalog" as distinct from
None which means "this gateway was not found in the dict."

Option B: In the model loader (core/model/definition.py)

Set loader_kwargs["default_catalog"] = None when a gateway is known but absent from the dict:

if default_catalog_per_gateway and gateway_name:
    catalog = default_catalog_per_gateway.get(gateway_name)
    if catalog is not None:
        loader_kwargs["default_catalog"] = catalog
    else:
        loader_kwargs["default_catalog"] = None

Test verification

Test	Input	Model FQN	Status
Bug reproduction	default_catalog="example_catalog"	"example_catalog"."my_schema"."my_model"	Catalog leaked
Fix verification	default_catalog=""	"my_schema"."my_model"	Correct
Integration	Scheduler with Option A applied	{'default_gw': 'example_catalog', 'clickhouse_gw': ''}	Correct

Regression testing with Option A applied against SQLMesh test suite:

• test_multi_virtual_layer: passed (multi-gateway integration)
• test_multiple_gateways: passed (multi-gateway context)
• 353 core tests passed; 14 failures + 53 errors match the results on unmodified main

Environment

• SQLMesh version: 0.230.1