<p dir="auto"><strong>Describe the bug</strong></p>

<p dir="auto">Weirdly making an unnecessary <code class="notranslate">INSERT INTO</code> in a query request when running the code below.<br>
The second line from last always get executed successfully.<br>
The last line never gets executed successfully, and will be all fine when commented out.<br>
*<em>Expected behavior</em></p>

<p dir="auto">Just query  without adding <code class="notranslate">INSERT INTO</code>.<br>
<strong>To Reproduce</strong><br>
Please try to provide a <a href="http://stackoverflow.com/help/mcve" rel="nofollow">Minimal, Complete, and Verifiable</a> example.<br>
See also <a href="https://www.sqlalchemy.org/participate.html#bugs" rel="nofollow">Reporting Bugs</a> on the website, and some <a href="https://github.com/sqlalchemy/sqlalchemy/issues?q=label%3A%22great+mcve%22">example issues</a>.</p>
<p dir="auto"><strong>Please do not use Flask-SQLAlchemy or any other third-party extensions or dependencies in test cases</strong>.  The test case must illustrate the problem <strong>without</strong> using any third party SQLAlchemy extensions.  Otherwise, please report the bug to those projects first.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from pathlib import Path

from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.engine.base import Engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relation

Base = declarative_base()
_engine: Engine = create_engine(
    'sqlite:///' + str(Path(__file__).parent.joinpath('db.sqlite3')),
    echo=True)
db_session = Session(_engine)


class User(Base):
    __tablename__ = 'users'
    _id = Column('id', Integer, primary_key=True)
    _username = Column('username', String, unique=True, nullable=False)
    _user_type_id = Column('user_type_id',
                           Integer,
                           ForeignKey('user_type.id'),
                           nullable=False)
    _user_type = relation('UserType',
                          order_by='UserType._name',
                          backref='_users')

    def __init__(self, username: str, user_type: str):
        self._username = username
        tmp_query = db_session.query(UserType).filter_by(
            _name=user_type).first()  # sqlalchemy makes unnecessary `INSERT INTO` here when executing the last line
        if tmp_query:
            self._user_type = tmp_query
        else:
            self._user_type = UserType(user_type)


class UserType(Base):
    __tablename__ = 'user_type'
    _id = Column('id', Integer, primary_key=True)
    _name = Column('name', String, unique=True, nullable=False)

    def __init__(self, name: str):
        self._name = str(name)


Base.metadata.create_all(_engine)

if not db_session.query(User).filter_by(_username='1').first():
    db_session.add(User('1', '1'))
    db_session.commit()

x = User('1', '1')  # this line can always be executed successfully
x = User('1', '1')  # would be all fine if comment this line"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">pathlib</span> <span class="pl-k">import</span> <span class="pl-v">Path</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">ForeignKey</span>, <span class="pl-v">Integer</span>, <span class="pl-v">String</span>, <span class="pl-s1">create_engine</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">engine</span>.<span class="pl-s1">base</span> <span class="pl-k">import</span> <span class="pl-v">Engine</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</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-v">Session</span>, <span class="pl-s1">relation</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()
<span class="pl-s1">_engine</span>: <span class="pl-v">Engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(
    <span class="pl-s">'sqlite:///'</span> <span class="pl-c1">+</span> <span class="pl-en">str</span>(<span class="pl-v">Path</span>(<span class="pl-s1">__file__</span>).<span class="pl-s1">parent</span>.<span class="pl-en">joinpath</span>(<span class="pl-s">'db.sqlite3'</span>)),
    <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
<span class="pl-s1">db_session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>(<span class="pl-s1">_engine</span>)


<span class="pl-k">class</span> <span class="pl-v">User</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'users'</span>
    <span class="pl-s1">_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-s">'id'</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">_username</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-s">'username'</span>, <span class="pl-v">String</span>, <span class="pl-s1">unique</span><span class="pl-c1">=</span><span class="pl-c1">True</span>, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
    <span class="pl-s1">_user_type_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-s">'user_type_id'</span>,
                           <span class="pl-v">Integer</span>,
                           <span class="pl-v">ForeignKey</span>(<span class="pl-s">'user_type.id'</span>),
                           <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
    <span class="pl-s1">_user_type</span> <span class="pl-c1">=</span> <span class="pl-en">relation</span>(<span class="pl-s">'UserType'</span>,
                          <span class="pl-s1">order_by</span><span class="pl-c1">=</span><span class="pl-s">'UserType._name'</span>,
                          <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-s">'_users'</span>)

    <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>, <span class="pl-s1">username</span>: <span class="pl-s1">str</span>, <span class="pl-s1">user_type</span>: <span class="pl-s1">str</span>):
        <span class="pl-s1">self</span>.<span class="pl-s1">_username</span> <span class="pl-c1">=</span> <span class="pl-s1">username</span>
        <span class="pl-s1">tmp_query</span> <span class="pl-c1">=</span> <span class="pl-s1">db_session</span>.<span class="pl-en">query</span>(<span class="pl-v">UserType</span>).<span class="pl-en">filter_by</span>(
            <span class="pl-s1">_name</span><span class="pl-c1">=</span><span class="pl-s1">user_type</span>).<span class="pl-en">first</span>()  <span class="pl-c"># sqlalchemy makes unnecessary `INSERT INTO` here when executing the last line</span>
        <span class="pl-k">if</span> <span class="pl-s1">tmp_query</span>:
            <span class="pl-s1">self</span>.<span class="pl-s1">_user_type</span> <span class="pl-c1">=</span> <span class="pl-s1">tmp_query</span>
        <span class="pl-k">else</span>:
            <span class="pl-s1">self</span>.<span class="pl-s1">_user_type</span> <span class="pl-c1">=</span> <span class="pl-v">UserType</span>(<span class="pl-s1">user_type</span>)


<span class="pl-k">class</span> <span class="pl-v">UserType</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'user_type'</span>
    <span class="pl-s1">_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-s">'id'</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">_name</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-s">'name'</span>, <span class="pl-v">String</span>, <span class="pl-s1">unique</span><span class="pl-c1">=</span><span class="pl-c1">True</span>, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)

    <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>, <span class="pl-s1">name</span>: <span class="pl-s1">str</span>):
        <span class="pl-s1">self</span>.<span class="pl-s1">_name</span> <span class="pl-c1">=</span> <span class="pl-en">str</span>(<span class="pl-s1">name</span>)


<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">_engine</span>)

<span class="pl-k">if</span> <span class="pl-c1">not</span> <span class="pl-s1">db_session</span>.<span class="pl-en">query</span>(<span class="pl-v">User</span>).<span class="pl-en">filter_by</span>(<span class="pl-s1">_username</span><span class="pl-c1">=</span><span class="pl-s">'1'</span>).<span class="pl-en">first</span>():
    <span class="pl-s1">db_session</span>.<span class="pl-en">add</span>(<span class="pl-v">User</span>(<span class="pl-s">'1'</span>, <span class="pl-s">'1'</span>))
    <span class="pl-s1">db_session</span>.<span class="pl-en">commit</span>()

<span class="pl-s1">x</span> <span class="pl-c1">=</span> <span class="pl-v">User</span>(<span class="pl-s">'1'</span>, <span class="pl-s">'1'</span>)  <span class="pl-c"># this line can always be executed successfully</span>
<span class="pl-s1">x</span> <span class="pl-c1">=</span> <span class="pl-v">User</span>(<span class="pl-s">'1'</span>, <span class="pl-s">'1'</span>)  <span class="pl-c"># would be all fine if comment this line</span></pre></div>
<p dir="auto"><strong>Error</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(&quot;users&quot;)
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(&quot;user_type&quot;)
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.username AS users_username, users.user_type_id AS users_user_type_id 
FROM users 
WHERE users.username = ?
 LIMIT ? OFFSET ?
INFO sqlalchemy.engine.base.Engine ('1', 1, 0)
INFO sqlalchemy.engine.base.Engine SELECT user_type.id AS user_type_id, user_type.name AS user_type_name 
FROM user_type 
WHERE user_type.name = ?
 LIMIT ? OFFSET ?
INFO sqlalchemy.engine.base.Engine ('1', 1, 0)
INFO sqlalchemy.engine.base.Engine INSERT INTO users (username, user_type_id) VALUES (?, ?)
INFO sqlalchemy.engine.base.Engine ('1', 1)
INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1276, in _execute_context
    self.dialect.do_execute(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py&quot;, line 609, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: users.username

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;/backend/models.py&quot;, line 53, in &lt;module&gt;
    x = User('1', '1')
  File &quot;&lt;string&gt;&quot;, line 4, in __init__
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/state.py&quot;, line 433, in _initialize_instance
    manager.dispatch.init_failure(self, args, kwargs)
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py&quot;, line 68, in __exit__
    compat.raise_(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py&quot;, line 182, in raise_
    raise exception
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/state.py&quot;, line 430, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File &quot;/backend/models.py&quot;, line 29, in __init__
    tmp_query = db_session.query(UserType).filter_by(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py&quot;, line 3429, in first
    ret = list(self[0:1])
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py&quot;, line 3203, in __getitem__
    return list(res)
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py&quot;, line 3534, in __iter__
    self.session._autoflush()
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py&quot;, line 1633, in _autoflush
    util.raise_(e, with_traceback=sys.exc_info()[2])
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py&quot;, line 182, in raise_
    raise exception
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py&quot;, line 1622, in _autoflush
    self.flush()
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py&quot;, line 2540, in flush
    self._flush(objects)
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py&quot;, line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py&quot;, line 68, in __exit__
    compat.raise_(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py&quot;, line 182, in raise_
    raise exception
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py&quot;, line 2642, in _flush
    flush_context.execute()
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py&quot;, line 422, in execute
    rec.execute(self)
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py&quot;, line 586, in execute
    persistence.save_obj(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py&quot;, line 239, in save_obj
    _emit_insert_statements(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py&quot;, line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1011, in execute
    return meth(self, multiparams, params)
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py&quot;, line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1316, in _execute_context
    self._handle_dbapi_exception(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1510, in _handle_dbapi_exception
    util.raise_(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py&quot;, line 182, in raise_
    raise exception
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1276, in _execute_context
    self.dialect.do_execute(
  File &quot;/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py&quot;, line 609, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) UNIQUE constraint failed: users.username
[SQL: INSERT INTO users (username, user_type_id) VALUES (?, ?)]
[parameters: ('1', 1)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)"><pre class="notranslate"><code class="notranslate">INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("users")
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("user_type")
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.username AS users_username, users.user_type_id AS users_user_type_id 
FROM users 
WHERE users.username = ?
 LIMIT ? OFFSET ?
INFO sqlalchemy.engine.base.Engine ('1', 1, 0)
INFO sqlalchemy.engine.base.Engine SELECT user_type.id AS user_type_id, user_type.name AS user_type_name 
FROM user_type 
WHERE user_type.name = ?
 LIMIT ? OFFSET ?
INFO sqlalchemy.engine.base.Engine ('1', 1, 0)
INFO sqlalchemy.engine.base.Engine INSERT INTO users (username, user_type_id) VALUES (?, ?)
INFO sqlalchemy.engine.base.Engine ('1', 1)
INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: users.username

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/backend/models.py", line 53, in &lt;module&gt;
    x = User('1', '1')
  File "&lt;string&gt;", line 4, in __init__
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 433, in _initialize_instance
    manager.dispatch.init_failure(self, args, kwargs)
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 430, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "/backend/models.py", line 29, in __init__
    tmp_query = db_session.query(UserType).filter_by(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3429, in first
    ret = list(self[0:1])
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3203, in __getitem__
    return list(res)
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3534, in __iter__
    self.session._autoflush()
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1633, in _autoflush
    util.raise_(e, with_traceback=sys.exc_info()[2])
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1622, in _autoflush
    self.flush()
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) UNIQUE constraint failed: users.username
[SQL: INSERT INTO users (username, user_type_id) VALUES (?, ?)]
[parameters: ('1', 1)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
</code></pre></div>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: macOS Mojave 10.14.6 (18G95)</li>
<li>Python: 3.9.1</li>
<li>SQLAlchemy: 1.3.22</li>
<li>Database: SQLite 3.24.0</li>
<li>DBAPI: sqlite3</li>
</ul>
<p dir="auto"><strong>Additional context</strong></p>

<p dir="auto"><strong>Have a nice day!</strong></p>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">According to the <a href="https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html#mapped-column-derives-the-datatype-and-nullability-from-the-mapped-annotation" rel="nofollow">docs</a>, <code class="notranslate">mapped_column()</code> derives nullability from the <code class="notranslate">Mapped</code> annotation if <code class="notranslate">nullable</code> argument is not specified.</p>
<p dir="auto">In this case, <code class="notranslate">Column._user_defined_nullable</code> remains <code class="notranslate">NULL_UNSPECIFIED</code>.</p>
<p dir="auto">It appears that <code class="notranslate">Column.copy()</code> ignores the actual <code class="notranslate">nullable</code> property of the original column, but only uses <code class="notranslate">_user_defined_nullable</code>:<br>
</p><div class="Box Box--condensed my-2">
  <div class="Box-header f6">
    <p class="mb-0 text-bold">
      <a href="https://github.com/sqlalchemy/sqlalchemy/blob/f55ae66f70d6b28fbccd75ac9089f11e951c4c39/lib/sqlalchemy/sql/schema.py#L2243-L2246">sqlalchemy/lib/sqlalchemy/sql/schema.py</a>
    </p>
    <p class="mb-0 color-fg-muted">
        Lines 2243 to 2246
      in
      <a data-pjax="true" class="commit-tease-sha" href="/sqlalchemy/sqlalchemy/commit/f55ae66f70d6b28fbccd75ac9089f11e951c4c39">f55ae66</a>
    </p>
  </div>
  <div itemprop="text" class="Box-body p-0 blob-wrapper blob-wrapper-embedded data">
    <table class="highlight tab-size mb-0 js-file-line-container" data-tab-size="8" data-paste-markdown-skip="">

        <tbody><tr class="border-0">
          <td id="L2243" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="2243"></td>
          <td id="LC2243" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line">  </td>
        </tr>

        <tr class="border-0">
          <td id="L2244" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="2244"></td>
          <td id="LC2244" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line"> <span class="pl-k">if</span> <span class="pl-s1">self</span>.<span class="pl-s1">_user_defined_nullable</span> <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-v">NULL_UNSPECIFIED</span>: </td>
        </tr>

        <tr class="border-0">
          <td id="L2245" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="2245"></td>
          <td id="LC2245" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line">     <span class="pl-s1">column_kwargs</span>[<span class="pl-s">"nullable"</span>] <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-s1">_user_defined_nullable</span> </td>
        </tr>

        <tr class="border-0">
          <td id="L2246" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="2246"></td>
          <td id="LC2246" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line">  </td>
        </tr>
    </tbody></table>
  </div>
</div>
<br>
The new column's constructor sets <code class="notranslate">nullable</code> as if it wasn't specified (usually <code class="notranslate">True</code>).<p></p>
<h3 dir="auto">To Reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy.orm import Mapped, mapped_column, registry


mapper_registry = registry()


@mapper_registry.mapped
class User:
    __tablename__ = &quot;user&quot;
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


column = User.name.expression
print(repr(column))  # Column('name', String(), table=&lt;user&gt;, nullable=False)
print(column.nullable)  # False

column_copy = column._copy()
print(repr(column_copy))  # Column('name', String(), table=None)
print(column_copy.nullable)  # True"><pre class="notranslate"><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-v">Mapped</span>, <span class="pl-s1">mapped_column</span>, <span class="pl-s1">registry</span>


<span class="pl-s1">mapper_registry</span> <span class="pl-c1">=</span> <span class="pl-en">registry</span>()


<span class="pl-en">@<span class="pl-s1">mapper_registry</span>.<span class="pl-s1">mapped</span></span>
<span class="pl-k">class</span> <span class="pl-v">User</span>:
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"user"</span>
    <span class="pl-s1">id</span>: <span class="pl-v">Mapped</span>[<span class="pl-s1">int</span>] <span class="pl-c1">=</span> <span class="pl-en">mapped_column</span>(<span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span>: <span class="pl-v">Mapped</span>[<span class="pl-s1">str</span>]


<span class="pl-s1">column</span> <span class="pl-c1">=</span> <span class="pl-v">User</span>.<span class="pl-s1">name</span>.<span class="pl-s1">expression</span>
<span class="pl-en">print</span>(<span class="pl-en">repr</span>(<span class="pl-s1">column</span>))  <span class="pl-c"># Column('name', String(), table=&lt;user&gt;, nullable=False)</span>
<span class="pl-en">print</span>(<span class="pl-s1">column</span>.<span class="pl-s1">nullable</span>)  <span class="pl-c"># False</span>

<span class="pl-s1">column_copy</span> <span class="pl-c1">=</span> <span class="pl-s1">column</span>.<span class="pl-en">_copy</span>()
<span class="pl-en">print</span>(<span class="pl-en">repr</span>(<span class="pl-s1">column_copy</span>))  <span class="pl-c"># Column('name', String(), table=None)</span>
<span class="pl-en">print</span>(<span class="pl-s1">column_copy</span>.<span class="pl-s1">nullable</span>)  <span class="pl-c"># True</span></pre></div>
<h3 dir="auto">Error</h3>
<p dir="auto">No errors produced</p>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>Python: 3.10.2</li>
<li>SQLAlchemy: 2.0.0b1 (<a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/f55ae66f70d6b28fbccd75ac9089f11e951c4c39/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/f55ae66f70d6b28fbccd75ac9089f11e951c4c39"><tt>f55ae66</tt></a>)</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">This impacts alembic migrations autogen, causing <code class="notranslate">nullable=True</code> being set on mapped columns that are not <code class="notranslate">Optional</code>.</p>