<p dir="auto"><strong>Migrated issue, originally created by Marek Baczyński (<a href="https://github.com/imbaczek">@imbaczek</a>)</strong></p>
<p dir="auto">DB: SQL Server 2008R2</p>
<p dir="auto">SQLAlchemy==0.9.3</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import Enum, Text, Sequence, BigInteger, DateTime, Boolean
from sqlalchemy import UniqueConstraint, CheckConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

convention = {
    &quot;ix&quot;: 'ix_%(column_0_label)s',
    &quot;uq&quot;: &quot;uq_%(table_name)s_%(column_0_name)s&quot;,
    &quot;ck&quot;: &quot;ck_%(table_name)s_%(constraint_name)s&quot;,
    &quot;fk&quot;: &quot;fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s&quot;,
    &quot;pk&quot;: &quot;pk_%(table_name)s&quot;
}

metadata = MetaData(naming_convention=convention)
Base = declarative_base(metadata=metadata)


class Table(Base):
    __tablename__ = 'table'
    id = Column(Integer, primary_key=True)
    col1 = Column(Enum('one', 'two', name='col1'), nullable=True)
    col2 = Column(Boolean(name='col2'), nullable=True)
"><pre class="notranslate"><code class="notranslate">from sqlalchemy import Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import Enum, Text, Sequence, BigInteger, DateTime, Boolean
from sqlalchemy import UniqueConstraint, CheckConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)
Base = declarative_base(metadata=metadata)


class Table(Base):
    __tablename__ = 'table'
    id = Column(Integer, primary_key=True)
    col1 = Column(Enum('one', 'two', name='col1'), nullable=True)
    col2 = Column(Boolean(name='col2'), nullable=True)

</code></pre></div>
<p dir="auto">metadata.create_all() results in this sql:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE [table] (
        id INTEGER NOT NULL IDENTITY(1,1),
        col1 VARCHAR(3) NULL,
        col2 BIT NULL,
        CONSTRAINT pk_table PRIMARY KEY (id),
 -- expected ck_table_col1
        CONSTRAINT ck_table_ck_table_col1 CHECK (col1 IN ('one', 'two')),
 -- expected ck_table_col2
        CONSTRAINT ck_table_ck_table_col2 CHECK (col2 IN (0, 1))
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT"><pre class="notranslate"><code class="notranslate">INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE [table] (
        id INTEGER NOT NULL IDENTITY(1,1),
        col1 VARCHAR(3) NULL,
        col2 BIT NULL,
        CONSTRAINT pk_table PRIMARY KEY (id),
 -- expected ck_table_col1
        CONSTRAINT ck_table_ck_table_col1 CHECK (col1 IN ('one', 'two')),
 -- expected ck_table_col2
        CONSTRAINT ck_table_ck_table_col2 CHECK (col2 IN (0, 1))
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT
</code></pre></div>
<p dir="auto"><strong>Describe the bug</strong><br>
I get selectin join sql produced even when adding noload to options on the select_from subquery</p>
<p dir="auto"><strong>Expected behavior</strong><br>
adding a noload strategy to the Select should override the default load strategy and additional selectin statements shouldn't be produced</p>
<p dir="auto"><strong>To Reproduce</strong><br>
Run the code below - look at the SQL output</p>
<p dir="auto">I attempted to add the noload option to the outer c_stmt select but I get the "Query has only expression-based entities" error like in <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="856887403" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/6253" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/6253/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/6253">#6253</a></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from dataclasses import dataclass
from dataclasses import field
from typing import List

from sqlalchemy import Column, VARCHAR, create_engine, ForeignKey
from sqlalchemy import select
from sqlalchemy.orm import registry, sessionmaker, relationship, noload
from sqlalchemy.sql.expression import func

mapper_registry = registry()
engine = create_engine('DB_URL', echo=True, future=True)
Session = sessionmaker(engine, expire_on_commit=False)


@mapper_registry.mapped
@dataclass
class ExampleChildEntity:
    __tablename__ = &quot;child&quot;
    __table_args__ = {'schema': 'ex'}
    __sa_dataclass_metadata_key__ = &quot;sa&quot;
    child_guid: str = field(metadata={&quot;sa&quot;: Column(VARCHAR(36), primary_key=True)}, default=None)
    example_guid: str = field(metadata={&quot;sa&quot;: Column(VARCHAR(36), ForeignKey('ex.example.example_guid'))}, default=None)
    child_field: str = field(metadata={&quot;sa&quot;: Column(VARCHAR)}, default=None)


@mapper_registry.mapped
@dataclass
class ExampleEntity:
    __tablename__ = &quot;example&quot;
    __table_args__ = {'schema': 'ex'}
    __sa_dataclass_metadata_key__ = &quot;sa&quot;
    example_guid: str = field(metadata={&quot;sa&quot;: Column(VARCHAR(36), primary_key=True)}, default=None)
    example_field: str = field(metadata={&quot;sa&quot;: Column(VARCHAR)}, default=None)
    children: List[ExampleChildEntity] = field(default_factory=list, metadata={&quot;sa&quot;: relationship(
        &quot;ExampleChildEntity&quot;, cascade=&quot;all, delete-orphan&quot;, lazy='selectin')})


example = ExampleEntity(
    example_guid='EX-1',
    example_field='example field',
    children=[ExampleChildEntity(
        child_guid='CH-1',
        example_guid='EX-1',
        child_field='child field'
    )]
)

with Session() as session:
    with session.begin():
        session.add(example)

with Session() as session:
    with session.begin():
        stmt = select(ExampleEntity)
        stmt = stmt.where(ExampleEntity.example_field.ilike(&quot;%field%&quot;))
        stmt = stmt.options(noload(ExampleEntity.children))

        c_stmt = select(ExampleEntity)
        c_stmt = c_stmt.where(ExampleEntity.example_field.ilike(&quot;%field%&quot;))
        c_stmt = c_stmt.options(noload(ExampleEntity.children))

        count_stmt = select(func.count()).select_from(c_stmt)
        count = session.execute(count_stmt)

        result = session.execute(stmt)
        examples = result.scalars().all()
        print(examples)

with Session() as session:
    with session.begin():
        e = session.get(ExampleEntity, example.example_guid)
        session.delete(e)"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">dataclasses</span> <span class="pl-k">import</span> <span class="pl-s1">dataclass</span>
<span class="pl-k">from</span> <span class="pl-s1">dataclasses</span> <span class="pl-k">import</span> <span class="pl-s1">field</span>
<span class="pl-k">from</span> <span class="pl-s1">typing</span> <span class="pl-k">import</span> <span class="pl-v">List</span>

<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">VARCHAR</span>, <span class="pl-s1">create_engine</span>, <span class="pl-v">ForeignKey</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">select</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">registry</span>, <span class="pl-s1">sessionmaker</span>, <span class="pl-s1">relationship</span>, <span class="pl-s1">noload</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">sql</span>.<span class="pl-s1">expression</span> <span class="pl-k">import</span> <span class="pl-s1">func</span>

<span class="pl-s1">mapper_registry</span> <span class="pl-c1">=</span> <span class="pl-en">registry</span>()
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'DB_URL'</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>, <span class="pl-s1">future</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
<span class="pl-v">Session</span> <span class="pl-c1">=</span> <span class="pl-en">sessionmaker</span>(<span class="pl-s1">engine</span>, <span class="pl-s1">expire_on_commit</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)


<span class="pl-en">@<span class="pl-s1">mapper_registry</span>.<span class="pl-s1">mapped</span></span>
<span class="pl-en">@<span class="pl-s1">dataclass</span></span>
<span class="pl-k">class</span> <span class="pl-v">ExampleChildEntity</span>:
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"child"</span>
    <span class="pl-s1">__table_args__</span> <span class="pl-c1">=</span> {<span class="pl-s">'schema'</span>: <span class="pl-s">'ex'</span>}
    <span class="pl-s1">__sa_dataclass_metadata_key__</span> <span class="pl-c1">=</span> <span class="pl-s">"sa"</span>
    <span class="pl-s1">child_guid</span>: <span class="pl-s1">str</span> <span class="pl-c1">=</span> <span class="pl-en">field</span>(<span class="pl-s1">metadata</span><span class="pl-c1">=</span>{<span class="pl-s">"sa"</span>: <span class="pl-v">Column</span>(<span class="pl-v">VARCHAR</span>(<span class="pl-c1">36</span>), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)}, <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-c1">None</span>)
    <span class="pl-s1">example_guid</span>: <span class="pl-s1">str</span> <span class="pl-c1">=</span> <span class="pl-en">field</span>(<span class="pl-s1">metadata</span><span class="pl-c1">=</span>{<span class="pl-s">"sa"</span>: <span class="pl-v">Column</span>(<span class="pl-v">VARCHAR</span>(<span class="pl-c1">36</span>), <span class="pl-v">ForeignKey</span>(<span class="pl-s">'ex.example.example_guid'</span>))}, <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-c1">None</span>)
    <span class="pl-s1">child_field</span>: <span class="pl-s1">str</span> <span class="pl-c1">=</span> <span class="pl-en">field</span>(<span class="pl-s1">metadata</span><span class="pl-c1">=</span>{<span class="pl-s">"sa"</span>: <span class="pl-v">Column</span>(<span class="pl-v">VARCHAR</span>)}, <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-c1">None</span>)


<span class="pl-en">@<span class="pl-s1">mapper_registry</span>.<span class="pl-s1">mapped</span></span>
<span class="pl-en">@<span class="pl-s1">dataclass</span></span>
<span class="pl-k">class</span> <span class="pl-v">ExampleEntity</span>:
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"example"</span>
    <span class="pl-s1">__table_args__</span> <span class="pl-c1">=</span> {<span class="pl-s">'schema'</span>: <span class="pl-s">'ex'</span>}
    <span class="pl-s1">__sa_dataclass_metadata_key__</span> <span class="pl-c1">=</span> <span class="pl-s">"sa"</span>
    <span class="pl-s1">example_guid</span>: <span class="pl-s1">str</span> <span class="pl-c1">=</span> <span class="pl-en">field</span>(<span class="pl-s1">metadata</span><span class="pl-c1">=</span>{<span class="pl-s">"sa"</span>: <span class="pl-v">Column</span>(<span class="pl-v">VARCHAR</span>(<span class="pl-c1">36</span>), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)}, <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-c1">None</span>)
    <span class="pl-s1">example_field</span>: <span class="pl-s1">str</span> <span class="pl-c1">=</span> <span class="pl-en">field</span>(<span class="pl-s1">metadata</span><span class="pl-c1">=</span>{<span class="pl-s">"sa"</span>: <span class="pl-v">Column</span>(<span class="pl-v">VARCHAR</span>)}, <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-c1">None</span>)
    <span class="pl-s1">children</span>: <span class="pl-v">List</span>[<span class="pl-v">ExampleChildEntity</span>] <span class="pl-c1">=</span> <span class="pl-en">field</span>(<span class="pl-s1">default_factory</span><span class="pl-c1">=</span><span class="pl-s1">list</span>, <span class="pl-s1">metadata</span><span class="pl-c1">=</span>{<span class="pl-s">"sa"</span>: <span class="pl-en">relationship</span>(
        <span class="pl-s">"ExampleChildEntity"</span>, <span class="pl-s1">cascade</span><span class="pl-c1">=</span><span class="pl-s">"all, delete-orphan"</span>, <span class="pl-s1">lazy</span><span class="pl-c1">=</span><span class="pl-s">'selectin'</span>)})


<span class="pl-s1">example</span> <span class="pl-c1">=</span> <span class="pl-v">ExampleEntity</span>(
    <span class="pl-s1">example_guid</span><span class="pl-c1">=</span><span class="pl-s">'EX-1'</span>,
    <span class="pl-s1">example_field</span><span class="pl-c1">=</span><span class="pl-s">'example field'</span>,
    <span class="pl-s1">children</span><span class="pl-c1">=</span>[<span class="pl-v">ExampleChildEntity</span>(
        <span class="pl-s1">child_guid</span><span class="pl-c1">=</span><span class="pl-s">'CH-1'</span>,
        <span class="pl-s1">example_guid</span><span class="pl-c1">=</span><span class="pl-s">'EX-1'</span>,
        <span class="pl-s1">child_field</span><span class="pl-c1">=</span><span class="pl-s">'child field'</span>
    )]
)

<span class="pl-k">with</span> <span class="pl-v">Session</span>() <span class="pl-k">as</span> <span class="pl-s1">session</span>:
    <span class="pl-k">with</span> <span class="pl-s1">session</span>.<span class="pl-en">begin</span>():
        <span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">example</span>)

<span class="pl-k">with</span> <span class="pl-v">Session</span>() <span class="pl-k">as</span> <span class="pl-s1">session</span>:
    <span class="pl-k">with</span> <span class="pl-s1">session</span>.<span class="pl-en">begin</span>():
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-v">ExampleEntity</span>)
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">stmt</span>.<span class="pl-en">where</span>(<span class="pl-v">ExampleEntity</span>.<span class="pl-s1">example_field</span>.<span class="pl-en">ilike</span>(<span class="pl-s">"%field%"</span>))
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">stmt</span>.<span class="pl-en">options</span>(<span class="pl-en">noload</span>(<span class="pl-v">ExampleEntity</span>.<span class="pl-s1">children</span>))

        <span class="pl-s1">c_stmt</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-v">ExampleEntity</span>)
        <span class="pl-s1">c_stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">c_stmt</span>.<span class="pl-en">where</span>(<span class="pl-v">ExampleEntity</span>.<span class="pl-s1">example_field</span>.<span class="pl-en">ilike</span>(<span class="pl-s">"%field%"</span>))
        <span class="pl-s1">c_stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">c_stmt</span>.<span class="pl-en">options</span>(<span class="pl-en">noload</span>(<span class="pl-v">ExampleEntity</span>.<span class="pl-s1">children</span>))

        <span class="pl-s1">count_stmt</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-s1">func</span>.<span class="pl-en">count</span>()).<span class="pl-en">select_from</span>(<span class="pl-s1">c_stmt</span>)
        <span class="pl-s1">count</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-s1">count_stmt</span>)

        <span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-s1">stmt</span>)
        <span class="pl-s1">examples</span> <span class="pl-c1">=</span> <span class="pl-s1">result</span>.<span class="pl-en">scalars</span>().<span class="pl-en">all</span>()
        <span class="pl-en">print</span>(<span class="pl-s1">examples</span>)

<span class="pl-k">with</span> <span class="pl-v">Session</span>() <span class="pl-k">as</span> <span class="pl-s1">session</span>:
    <span class="pl-k">with</span> <span class="pl-s1">session</span>.<span class="pl-en">begin</span>():
        <span class="pl-s1">e</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">get</span>(<span class="pl-v">ExampleEntity</span>, <span class="pl-s1">example</span>.<span class="pl-s1">example_guid</span>)
        <span class="pl-s1">session</span>.<span class="pl-en">delete</span>(<span class="pl-s1">e</span>)</pre></div>
<p dir="auto">DDL</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CREATE TABLE ex.example
(
    example_guid  VARCHAR(36) NOT NULL,
    example_field VARCHAR,
    CONSTRAINT example_pk PRIMARY KEY (example_guid)
);

CREATE TABLE ex.child
(
    child_guid  VARCHAR(36) NOT NULL,
    example_guid  VARCHAR(36) NOT NULL,
    child_field VARCHAR,
    CONSTRAINT child_pk PRIMARY KEY (child_guid),
    CONSTRAINT example_fk FOREIGN KEY (example_guid) REFERENCES example (example_guid)
);"><pre class="notranslate"><code class="notranslate">CREATE TABLE ex.example
(
    example_guid  VARCHAR(36) NOT NULL,
    example_field VARCHAR,
    CONSTRAINT example_pk PRIMARY KEY (example_guid)
);

CREATE TABLE ex.child
(
    child_guid  VARCHAR(36) NOT NULL,
    example_guid  VARCHAR(36) NOT NULL,
    child_field VARCHAR,
    CONSTRAINT child_pk PRIMARY KEY (child_guid),
    CONSTRAINT example_fk FOREIGN KEY (example_guid) REFERENCES example (example_guid)
);
</code></pre></div>
<p dir="auto"><strong>Error</strong><br>
The additional SELECT IN with the Child:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="INFO sqlalchemy.engine.Engine select version()
2021-04-27 15:20:49,397 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-04-27 15:20:49,400 INFO sqlalchemy.engine.Engine select current_schema()
2021-04-27 15:20:49,401 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-04-27 15:20:49,403 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2021-04-27 15:20:49,404 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-04-27 15:20:49,407 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-04-27 15:20:49,409 INFO sqlalchemy.engine.Engine INSERT INTO ex.example (example_guid, example_field) VALUES (%(example_guid)s, %(example_field)s)
2021-04-27 15:20:49,409 INFO sqlalchemy.engine.Engine [generated in 0.00029s] {'example_guid': 'EX-1', 'example_field': 'example field'}
2021-04-27 15:20:49,416 INFO sqlalchemy.engine.Engine INSERT INTO ex.child (child_guid, example_guid, child_field) VALUES (%(child_guid)s, %(example_guid)s, %(child_field)s)
2021-04-27 15:20:49,416 INFO sqlalchemy.engine.Engine [generated in 0.00033s] {'child_guid': 'CH-1', 'example_guid': 'EX-1', 'child_field': 'child field'}
2021-04-27 15:20:49,420 INFO sqlalchemy.engine.Engine COMMIT
2021-04-27 15:20:49,427 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-04-27 15:20:49,431 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 
FROM (SELECT ex.example.example_guid AS example_guid, ex.example.example_field AS example_field 
FROM ex.example 
WHERE ex.example.example_field ILIKE %(example_field_1)s) AS anon_1
2021-04-27 15:20:49,431 INFO sqlalchemy.engine.Engine [generated in 0.00033s] {'example_field_1': '%field%'}
2021-04-27 15:20:49,436 INFO sqlalchemy.engine.Engine SELECT ex.example.example_guid, ex.example.example_field 
FROM ex.example 
WHERE ex.example.example_field ILIKE %(example_field_1)s
2021-04-27 15:20:49,437 INFO sqlalchemy.engine.Engine [generated in 0.00033s] {'example_field_1': '%field%'}
[ExampleEntity(example_guid='EX-1', example_field='example field', children=[])]
2021-04-27 15:20:57,557 INFO sqlalchemy.engine.Engine COMMIT
2021-04-27 15:20:57,561 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-04-27 15:20:57,563 INFO sqlalchemy.engine.Engine SELECT ex.example.example_guid AS ex_example_example_guid, ex.example.example_field AS ex_example_example_field 
FROM ex.example 
WHERE ex.example.example_guid = %(pk_1)s
2021-04-27 15:20:57,563 INFO sqlalchemy.engine.Engine [generated in 0.00046s] {'pk_1': 'EX-1'}
2021-04-27 15:20:57,575 INFO sqlalchemy.engine.Engine SELECT ex.child.example_guid AS ex_child_example_guid, ex.child.child_guid AS ex_child_child_guid, ex.child.child_field AS ex_child_child_field 
FROM ex.child 
WHERE ex.child.example_guid IN (%(primary_keys_1)s)
2021-04-27 15:20:57,576 INFO sqlalchemy.engine.Engine [generated in 0.00098s] {'primary_keys_1': 'EX-1'}
2021-04-27 15:20:57,582 INFO sqlalchemy.engine.Engine DELETE FROM ex.child WHERE ex.child.child_guid = %(child_guid)s
2021-04-27 15:20:57,582 INFO sqlalchemy.engine.Engine [generated in 0.00041s] {'child_guid': 'CH-1'}
2021-04-27 15:20:57,585 INFO sqlalchemy.engine.Engine DELETE FROM ex.example WHERE ex.example.example_guid = %(example_guid)s
2021-04-27 15:20:57,585 INFO sqlalchemy.engine.Engine [generated in 0.00038s] {'example_guid': 'EX-1'}
2021-04-27 15:20:57,588 INFO sqlalchemy.engine.Engine COMMIT"><pre class="notranslate"><code class="notranslate">INFO sqlalchemy.engine.Engine select version()
2021-04-27 15:20:49,397 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-04-27 15:20:49,400 INFO sqlalchemy.engine.Engine select current_schema()
2021-04-27 15:20:49,401 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-04-27 15:20:49,403 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2021-04-27 15:20:49,404 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-04-27 15:20:49,407 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-04-27 15:20:49,409 INFO sqlalchemy.engine.Engine INSERT INTO ex.example (example_guid, example_field) VALUES (%(example_guid)s, %(example_field)s)
2021-04-27 15:20:49,409 INFO sqlalchemy.engine.Engine [generated in 0.00029s] {'example_guid': 'EX-1', 'example_field': 'example field'}
2021-04-27 15:20:49,416 INFO sqlalchemy.engine.Engine INSERT INTO ex.child (child_guid, example_guid, child_field) VALUES (%(child_guid)s, %(example_guid)s, %(child_field)s)
2021-04-27 15:20:49,416 INFO sqlalchemy.engine.Engine [generated in 0.00033s] {'child_guid': 'CH-1', 'example_guid': 'EX-1', 'child_field': 'child field'}
2021-04-27 15:20:49,420 INFO sqlalchemy.engine.Engine COMMIT
2021-04-27 15:20:49,427 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-04-27 15:20:49,431 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 
FROM (SELECT ex.example.example_guid AS example_guid, ex.example.example_field AS example_field 
FROM ex.example 
WHERE ex.example.example_field ILIKE %(example_field_1)s) AS anon_1
2021-04-27 15:20:49,431 INFO sqlalchemy.engine.Engine [generated in 0.00033s] {'example_field_1': '%field%'}
2021-04-27 15:20:49,436 INFO sqlalchemy.engine.Engine SELECT ex.example.example_guid, ex.example.example_field 
FROM ex.example 
WHERE ex.example.example_field ILIKE %(example_field_1)s
2021-04-27 15:20:49,437 INFO sqlalchemy.engine.Engine [generated in 0.00033s] {'example_field_1': '%field%'}
[ExampleEntity(example_guid='EX-1', example_field='example field', children=[])]
2021-04-27 15:20:57,557 INFO sqlalchemy.engine.Engine COMMIT
2021-04-27 15:20:57,561 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-04-27 15:20:57,563 INFO sqlalchemy.engine.Engine SELECT ex.example.example_guid AS ex_example_example_guid, ex.example.example_field AS ex_example_example_field 
FROM ex.example 
WHERE ex.example.example_guid = %(pk_1)s
2021-04-27 15:20:57,563 INFO sqlalchemy.engine.Engine [generated in 0.00046s] {'pk_1': 'EX-1'}
2021-04-27 15:20:57,575 INFO sqlalchemy.engine.Engine SELECT ex.child.example_guid AS ex_child_example_guid, ex.child.child_guid AS ex_child_child_guid, ex.child.child_field AS ex_child_child_field 
FROM ex.child 
WHERE ex.child.example_guid IN (%(primary_keys_1)s)
2021-04-27 15:20:57,576 INFO sqlalchemy.engine.Engine [generated in 0.00098s] {'primary_keys_1': 'EX-1'}
2021-04-27 15:20:57,582 INFO sqlalchemy.engine.Engine DELETE FROM ex.child WHERE ex.child.child_guid = %(child_guid)s
2021-04-27 15:20:57,582 INFO sqlalchemy.engine.Engine [generated in 0.00041s] {'child_guid': 'CH-1'}
2021-04-27 15:20:57,585 INFO sqlalchemy.engine.Engine DELETE FROM ex.example WHERE ex.example.example_guid = %(example_guid)s
2021-04-27 15:20:57,585 INFO sqlalchemy.engine.Engine [generated in 0.00038s] {'example_guid': 'EX-1'}
2021-04-27 15:20:57,588 INFO sqlalchemy.engine.Engine COMMIT
</code></pre></div>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: MacOS Catalina 10.15.7</li>
<li>Python: 3.8.6</li>
<li>SQLAlchemy: 1.4.6</li>
<li>Database: postgres 12</li>
<li>DBAPI: psycopg2</li>
</ul>
<p dir="auto"><strong>Additional context</strong><br>
Originally I wanted to have one select statement that I could use for both the count and the actual query but it seemed to be precompiled or cached or the like after I executed the count select.  Is this part of this bug or expected behavior?  I'd like to not have to duplicate my select for this so something like this is desirable:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="with Session() as session:
    with session.begin():
        stmt = select(ExampleEntity)
        stmt = stmt.where(ExampleEntity.example_field.ilike(&quot;%field%&quot;))
        stmt = stmt.options(noload(ExampleEntity.children))

        count_stmt = select(func.count()).select_from(stmt)
        count = session.execute(count_stmt)

        result = session.execute(stmt)
        examples = result.scalars().all()
        print(examples)"><pre class="notranslate"><span class="pl-k">with</span> <span class="pl-v">Session</span>() <span class="pl-k">as</span> <span class="pl-s1">session</span>:
    <span class="pl-k">with</span> <span class="pl-s1">session</span>.<span class="pl-en">begin</span>():
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-v">ExampleEntity</span>)
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">stmt</span>.<span class="pl-en">where</span>(<span class="pl-v">ExampleEntity</span>.<span class="pl-s1">example_field</span>.<span class="pl-en">ilike</span>(<span class="pl-s">"%field%"</span>))
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">stmt</span>.<span class="pl-en">options</span>(<span class="pl-en">noload</span>(<span class="pl-v">ExampleEntity</span>.<span class="pl-s1">children</span>))

        <span class="pl-s1">count_stmt</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-s1">func</span>.<span class="pl-en">count</span>()).<span class="pl-en">select_from</span>(<span class="pl-s1">stmt</span>)
        <span class="pl-s1">count</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-s1">count_stmt</span>)

        <span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-s1">stmt</span>)
        <span class="pl-s1">examples</span> <span class="pl-c1">=</span> <span class="pl-s1">result</span>.<span class="pl-en">scalars</span>().<span class="pl-en">all</span>()
        <span class="pl-en">print</span>(<span class="pl-s1">examples</span>)</pre></div>
<p dir="auto">but even if I put the <code class="notranslate">stmt = stmt.options(noload(ExampleEntity.children))</code> after the count select execution I still get the child loaded into my object in the second query</p>
<p dir="auto"><strong>Have a nice day!</strong></p>