Issue 1:
Migrated issue, originally created by Marek Baczyński (@imbaczek)

DB: SQL Server 2008R2

SQLAlchemy==0.9.3

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)

metadata.create_all() results in this sql:

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

Issue 2:
Describe the bug

I get selectin join sql produced even when adding noload to options on the select_from subquery

Expected behavior

adding a noload strategy to the Select should override the default load strategy and additional selectin statements shouldn't be produced

To Reproduce

Run the code below - look at the SQL output

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 #6253

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__ = "child"
__table_args__ = {'schema': 'ex'}
__sa_dataclass_metadata_key__ = "sa"
child_guid: str = field(metadata={"sa": Column(VARCHAR(36), primary_key=True)}, default=None)
example_guid: str = field(metadata={"sa": Column(VARCHAR(36), ForeignKey('ex.example.example_guid'))}, default=None)
child_field: str = field(metadata={"sa": Column(VARCHAR)}, default=None)

@mapper_registry.mapped
@dataclass
class ExampleEntity:
__tablename__ = "example"
__table_args__ = {'schema': 'ex'}
__sa_dataclass_metadata_key__ = "sa"
example_guid: str = field(metadata={"sa": Column(VARCHAR(36), primary_key=True)}, default=None)
example_field: str = field(metadata={"sa": Column(VARCHAR)}, default=None)
children: List[ExampleChildEntity] = field(default_factory=list, metadata={"sa": relationship(
"ExampleChildEntity", cascade="all, delete-orphan", 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("%field%"))
stmt = stmt.options(noload(ExampleEntity.children))

c_stmt = select(ExampleEntity)
c_stmt = c_stmt.where(ExampleEntity.example_field.ilike("%field%"))
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)

DDL

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)
);

Error

The additional SELECT IN with the Child:

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

Versions.

OS: MacOS Catalina 10.15.7

Python: 3.8.6

SQLAlchemy: 1.4.6

Database: postgres 12

DBAPI: psycopg2

Additional context

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:

with Session() as session:
with session.begin():
stmt = select(ExampleEntity)
stmt = stmt.where(ExampleEntity.example_field.ilike("%field%"))
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)

but even if I put the stmt = stmt.options(noload(ExampleEntity.children)) after the count select execution I still get the child loaded into my object in the second query

Have a nice day!