Environment

1. Python version: 3.11+ (reproduced on 3.11, 3.12, 3.14)

2. OS/architecture: Reproducible on all platforms — this is a compile-time (SQL generation) bug, not platform-specific.

3. Component versions:

   snowflake-sqlalchemy==1.9.0
   sqlalchemy==2.0.49
   

4. What did you do?

Importing snowflake.sqlalchemy anywhere in the process silently breaks SQLAlchemy's with_loader_criteria for all dialects (PostgreSQL, MySQL, SQLite, etc.), not just Snowflake. The issue occurs when with_loader_criteria targets an entity inside a sealed .subquery() that contains an ORM .join().

Minimal reproduction (no database connection needed):

import sqlalchemy as sa
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, with_loader_criteria

class Base(DeclarativeBase):
    pass

class Parent(Base):
    __tablename__ = "parents"
    id = Column(Integer, primary_key=True)
    tenant_id = Column(String, nullable=False)

class Child(Base):
    __tablename__ = "children"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("parents.id"))
    tenant_id = Column(String, nullable=False)

class GrandChild(Base):
    __tablename__ = "grandchildren"
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey("children.id"))
    is_active = Column(sa.Boolean, nullable=False, server_default="true")

# --- This single import breaks with_loader_criteria for ALL dialects ---
import snowflake.sqlalchemy  # noqa: F401

# --- Build a query with an ORM join inside a sealed subquery ---
child_subq = (
    sa.select(Child.parent_id, sa.func.count().label("cnt"))
    .join(GrandChild, GrandChild.child_id == Child.id)
    .group_by(Child.parent_id)
    .subquery("child_counts")
)

stmt = (
    sa.select(Parent, child_subq.c.cnt)
    .outerjoin(child_subq, Parent.id == child_subq.c.parent_id)
)

stmt_with_criteria = stmt.options(
    with_loader_criteria(
        GrandChild, GrandChild.is_active == True, include_aliases=True
    )
)

# --- Compile against PostgreSQL (not Snowflake!) ---
from sqlalchemy.dialects import postgresql

compiled = str(stmt_with_criteria.compile(
    dialect=postgresql.dialect(),
    compile_kwargs={"literal_binds": True},
))

has_filter = "is_active" in compiled
print(f"filter injected: {has_filter}")
# Prints: "filter injected: False"
# Expected: "filter injected: True"

Toggle test: Remove or comment out import snowflake.sqlalchemy and the filter is correctly injected.

5. What did you expect to see?

with_loader_criteria should inject is_active into the compiled SQL regardless of whether snowflake-sqlalchemy is imported. The Snowflake-specific BCR-1057 handling should only affect queries compiled against the Snowflake dialect.

What actually happens: The is_active filter is silently dropped from the compiled SQL. No error is raised — the criteria simply aren't applied. This affects any application that has snowflake-sqlalchemy installed and imported, even when querying PostgreSQL, MySQL, or SQLite.

Root cause

src/snowflake/sqlalchemy/base.py registers two global CompileState plugins:

@CompileState.plugin_for("default", "select")        # replaces ALL core select compilation
class SnowflakeSelectState(SelectState): ...

@sql.base.CompileState.plugin_for("orm", "select")   # replaces ALL ORM select compilation
class SnowflakeORMSelectCompileState(context.ORMSelectCompileState): ...

CompileState.plugin_for is a global singleton — there is no per-dialect dispatch. Once registered, every ORM select() in the process compiles through the Snowflake overrides regardless of target dialect.

The overridden _join_left_to_right constructs _Snowflake_ORMJoin instead of the standard _ORMJoin. _Snowflake_ORMJoin.__init__ (in util.py) is a stale copy of _ORMJoin.__init__ that calls _Snowflake_Selectable_Join.__init__ directly instead of going through the standard _ORMJoin.__init__ → Join.__init__ chain, breaking _extra_criteria propagation.

6. Debug logs

No relevant log output — the bug is in SQL compilation (silent incorrect behavior), not in connection handling or query execution. There is no error, warning, or log message; the filter is simply absent from the generated SQL.

Suggested fix

1. The overridden methods in SnowflakeSelectState and SnowflakeORMSelectCompileState should detect the target dialect and delegate to super() for non-Snowflake compilations.

2. _Snowflake_ORMJoin and _Snowflake_Selectable_Join should delegate to super().__init__() instead of reimplementing the parent logic. The BCR-1057 Lateral-without-onclause case can be handled by catching NoForeignKeysError from super().

Validation

Deleting the overridden methods restores correct behavior:

from snowflake.sqlalchemy.base import (
    SnowflakeORMSelectCompileState,
    SnowflakeSelectState,
)

for attr in ("_setup_joins", "_join_determine_implicit_left_side"):
    if attr in vars(SnowflakeSelectState):
        delattr(SnowflakeSelectState, attr)

for attr in ("_join_determine_implicit_left_side", "_join_left_to_right"):
    if attr in vars(SnowflakeORMSelectCompileState):
        delattr(SnowflakeORMSelectCompileState, attr)

After this patch, with_loader_criteria correctly injects filters on all dialects while the Snowflake dialect still registers and can be used for connections.