Issue 1:
Describe the bug

Weirdly making an unnecessary INSERT INTO in a query request when running the code below.

The second line from last always get executed successfully.

The last line never gets executed successfully, and will be all fine when commented out.

*Expected behavior

Just query without adding INSERT INTO.

To Reproduce

Please try to provide a Minimal, Complete, and Verifiable example.

See also Reporting Bugs on the website, and some example issues.

Please do not use Flask-SQLAlchemy or any other third-party extensions or dependencies in test cases. The test case must illustrate the problem without using any third party SQLAlchemy extensions. Otherwise, please report the bug to those projects first.

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

Error

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 <module>
x = User('1', '1')
File "<string>", 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)

Versions.

OS: macOS Mojave 10.14.6 (18G95)

Python: 3.9.1

SQLAlchemy: 1.3.22

Database: SQLite 3.24.0

DBAPI: sqlite3

Additional context

Have a nice day!

Issue 2:
Describe the bug

According to the docs, mapped_column() derives nullability from the Mapped annotation if nullable argument is not specified.

In this case, Column._user_defined_nullable remains NULL_UNSPECIFIED.

It appears that Column.copy() ignores the actual nullable property of the original column, but only uses _user_defined_nullable:

sqlalchemy/lib/sqlalchemy/sql/schema.py

Lines 2243 to 2246
in
f55ae66

if self._user_defined_nullable is not NULL_UNSPECIFIED:

column_kwargs["nullable"] = self._user_defined_nullable

The new column's constructor sets nullable as if it wasn't specified (usually True).

To Reproduce

from sqlalchemy.orm import Mapped, mapped_column, registry

mapper_registry = registry()

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

column = User.name.expression
print(repr(column)) # Column('name', String(), table=<user>, nullable=False)
print(column.nullable) # False

column_copy = column._copy()
print(repr(column_copy)) # Column('name', String(), table=None)
print(column_copy.nullable) # True

Error

No errors produced

Versions

Python: 3.10.2

SQLAlchemy: 2.0.0b1 (f55ae66)

Additional context

This impacts alembic migrations autogen, causing nullable=True being set on mapped columns that are not Optional.