<h3 dir="auto">Environment</h3>
<p dir="auto">SQLAlchemy: 1.3.5 or 1.3.11<br>
PostgreSQL: 9.6<br>
Python: 3.7.4</p>
<h3 dir="auto">Problem</h3>
<p dir="auto">When delete parent model which has a relationship to children via secondary table, and a relationship to secondary table directly, duplicated delete queries on same instance have been emitted and report SAWarning.</p>
<p dir="auto">IMO, Unexpectedly <code class="notranslate">db.session.deleted</code> keep deleted instance after delete record in secondary table automatically.</p>
<p dir="auto">Following logs are outputted when delete parent record. (Some comments are my opinion)</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# Delete automatically by secondary relationship
# https://docs.sqlalchemy.org/en/13/orm/cascades.html#delete
2019-11-19 14:30:03,653 INFO sqlalchemy.engine.base.Engine DELETE FROM user_assignments WHERE user_assignments.user_id = %(user_id)s AND user_assignments.account_id = %(account_id)s
2019-11-19 14:30:03,654 INFO sqlalchemy.engine.base.Engine ({'user_id': 8, 'account_id': 6}, {'user_id': 8, 'account_id': 51})

# Delete automatically by explicitly cascade
# Unexpectedly db.session.deleted keep deleted instance?
2019-11-19 14:30:03,655 INFO sqlalchemy.engine.base.Engine DELETE FROM user_assignments WHERE user_assignments.user_id = %(user_id)s AND user_assignments.account_id = %(account_id)s
2019-11-19 14:30:03,656 INFO sqlalchemy.engine.base.Engine ({'user_id': 8, 'account_id': 6}, {'user_id': 8, 'account_id': 51}, {'user_id': 8, 'account_id': 987})

# Report SAWarning due to part of user_assignments are already deleted.
/home/centos/.pyenv/versions/3.7.4/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py:1367: SAWarning: DELETE statement on table 'user_assignments' expected to delete 3 row(s); 1 were matched.  Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning."><pre class="notranslate"><code class="notranslate"># Delete automatically by secondary relationship
# https://docs.sqlalchemy.org/en/13/orm/cascades.html#delete
2019-11-19 14:30:03,653 INFO sqlalchemy.engine.base.Engine DELETE FROM user_assignments WHERE user_assignments.user_id = %(user_id)s AND user_assignments.account_id = %(account_id)s
2019-11-19 14:30:03,654 INFO sqlalchemy.engine.base.Engine ({'user_id': 8, 'account_id': 6}, {'user_id': 8, 'account_id': 51})

# Delete automatically by explicitly cascade
# Unexpectedly db.session.deleted keep deleted instance?
2019-11-19 14:30:03,655 INFO sqlalchemy.engine.base.Engine DELETE FROM user_assignments WHERE user_assignments.user_id = %(user_id)s AND user_assignments.account_id = %(account_id)s
2019-11-19 14:30:03,656 INFO sqlalchemy.engine.base.Engine ({'user_id': 8, 'account_id': 6}, {'user_id': 8, 'account_id': 51}, {'user_id': 8, 'account_id': 987})

# Report SAWarning due to part of user_assignments are already deleted.
/home/centos/.pyenv/versions/3.7.4/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py:1367: SAWarning: DELETE statement on table 'user_assignments' expected to delete 3 row(s); 1 were matched.  Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.
</code></pre></div>
<h3 dir="auto">Reproduce</h3>
<p dir="auto">We have two models <code class="notranslate">Account</code> and <code class="notranslate">User</code>.<br>
These models have many-to-many relation via <code class="notranslate">UserAssignment</code> model which has data for email confirmation status.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class Account(db.Model):
  users = db.relationship('User', secondary='user_assignments', back_populates='accounts', lazy='dynamic',
                           secondaryjoin='''and_(User.id == UserAssignment.account_id,
                                                 UserAssignment.confirmed_at.isnot(None))''')

  assignments = db.relationship('UserAssignment', cascade='all, delete-orphan', lazy='dynamic')"><pre class="notranslate"><code class="notranslate">class Account(db.Model):
  users = db.relationship('User', secondary='user_assignments', back_populates='accounts', lazy='dynamic',
                           secondaryjoin='''and_(User.id == UserAssignment.account_id,
                                                 UserAssignment.confirmed_at.isnot(None))''')

  assignments = db.relationship('UserAssignment', cascade='all, delete-orphan', lazy='dynamic')
</code></pre></div>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="select * from user_assignments where user_id = 8;
 user_id | account_id |         confirmed_at          
---------+------------+-------------------------------
       8 |          6 | 2017-05-19 16:21:38.938036+00 
       8 |        987 |                               
       8 |         51 | 2019-09-27 05:38:53.467833+00 "><pre class="notranslate"><code class="notranslate">select * from user_assignments where user_id = 8;
 user_id | account_id |         confirmed_at          
---------+------------+-------------------------------
       8 |          6 | 2017-05-19 16:21:38.938036+00 
       8 |        987 |                               
       8 |         51 | 2019-09-27 05:38:53.467833+00 
</code></pre></div>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="user = User.query.get(8)
db.session.delete(user)
db.session.deleted
# IdentitySet([&lt;User 8&gt;, &lt;UserAssignment 8, 6&gt;, &lt;UserAssignment 8, 987&gt;, &lt;UserAssignment 8, 51&gt;])

db.session.flush()"><pre class="notranslate"><code class="notranslate">user = User.query.get(8)
db.session.delete(user)
db.session.deleted
# IdentitySet([&lt;User 8&gt;, &lt;UserAssignment 8, 6&gt;, &lt;UserAssignment 8, 987&gt;, &lt;UserAssignment 8, 51&gt;])

db.session.flush()
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">SQLite with default=func.now() Column definition fails on insert</p>
<p dir="auto">Testcase:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import sqlalchemy as sqa
import datetime


db = sqa.create_engine(&quot;sqlite://&quot;)
metadata = sqa.BoundMetaData(db)

#metadata.engine.echo = True # debug output

events_table = sqa.Table('events', metadata,
  sqa.Column('id', sqa.Integer, primary_key=True),
  sqa.Column('name', sqa.String(255)),
  sqa.Column('datetime', sqa.DateTime, default=sqa.func.now()),
)
events_table.create()

i = events_table.insert()
name = &quot;just-a-name&quot;
i.execute(name = name)"><pre class="notranslate"><code class="notranslate">import sqlalchemy as sqa
import datetime


db = sqa.create_engine("sqlite://")
metadata = sqa.BoundMetaData(db)

#metadata.engine.echo = True # debug output

events_table = sqa.Table('events', metadata,
  sqa.Column('id', sqa.Integer, primary_key=True),
  sqa.Column('name', sqa.String(255)),
  sqa.Column('datetime', sqa.DateTime, default=sqa.func.now()),
)
events_table.create()

i = events_table.insert()
name = "just-a-name"
i.execute(name = name)
</code></pre></div>
<p dir="auto">The error message:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;D:\testcase02.py&quot;, line 19, in ?
    i.execute(name = name)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py&quot;, line 1207, in execute
    return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py&quot;, line 1097, in execute
    return e.execute_compiled(self, *multiparams, **params)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 780, in execute_compiled
    return connection.execute_compiled(compiled, *multiparams, **params)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 567, in execute_compiled
    context.pre_exec()
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py&quot;, line 200, in pre_exec
    self._process_defaults()
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py&quot;, line 302, in _process_defaults
    newid = drunner.get_column_default(c)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1235, in get_column_default
    return column.default.accept_visitor(self)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\schema.py&quot;, line 840, in accept_visitor
    return visitor.visit_column_default(self)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1276, in visit_column_default
    return self.exec_default_sql(default)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1264, in exec_default_sql
    return self.connection.execute_compiled(c).scalar()
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 568, in execute_compiled
    self._execute_raw(context)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 581, in _execute_raw
    self._execute(context)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 599, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (OperationalError) no such function: now u'SELECT now()' [workaround
`
#!diff
--- sqlalchemy/databases/sqlite_old.py	Sat Jul 21 03:18:32 2007
+++ sqlalchemy/databases/sqlite.py	Thu Aug 02 15:10:01 2007
@@ -325,6 +325,12 @@
                 self.typemap.setdefault(&quot;CAST&quot;, cast.type)
             self.strings[cast](]"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "D:\testcase02.py", line 19, in ?
    i.execute(name = name)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1207, in execute
    return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1097, in execute
    return e.execute_compiled(self, *multiparams, **params)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 780, in execute_compiled
    return connection.execute_compiled(compiled, *multiparams, **params)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 567, in execute_compiled
    context.pre_exec()
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 200, in pre_exec
    self._process_defaults()
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 302, in _process_defaults
    newid = drunner.get_column_default(c)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 1235, in get_column_default
    return column.default.accept_visitor(self)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\schema.py", line 840, in accept_visitor
    return visitor.visit_column_default(self)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 1276, in visit_column_default
    return self.exec_default_sql(default)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 1264, in exec_default_sql
    return self.connection.execute_compiled(c).scalar()
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 568, in execute_compiled
    self._execute_raw(context)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 581, in _execute_raw
    self._execute(context)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 599, in _execute
    raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (OperationalError) no such function: now u'SELECT now()' [workaround
`
#!diff
--- sqlalchemy/databases/sqlite_old.py	Sat Jul 21 03:18:32 2007
+++ sqlalchemy/databases/sqlite.py	Thu Aug 02 15:10:01 2007
@@ -325,6 +325,12 @@
                 self.typemap.setdefault("CAST", cast.type)
             self.strings[cast](]
</code></pre></div>
<p dir="auto">Possible) = self.strings<a href="cast.clause">cast.clause</a></p>
<ul dir="auto">
<li>def visit_function( self , func ):</li>
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   if func.name.lower() == 'now':"><pre class="notranslate"><code class="notranslate">   if func.name.lower() == 'now':
</code></pre></div>
</li>
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="       self.strings[func](func) = 'DATETIME(&quot;NOW&quot;)'"><pre class="notranslate"><code class="notranslate">       self.strings[func](func) = 'DATETIME("NOW")'
</code></pre></div>
</li>
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   else:"><pre class="notranslate"><code class="notranslate">   else:
</code></pre></div>
</li>
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="       ansisql.ANSICompiler.visit_function( self , func )"><pre class="notranslate"><code class="notranslate">       ansisql.ANSICompiler.visit_function( self , func )
</code></pre></div>
</li>
<li>def limit_clause(self, select):<br>
text = ""<br>
if select.limit is not None:</li>
</ul>
<p dir="auto">But it also fails</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;D:\testcase02.py&quot;, line 19, in ?
    i.execute(name = name)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py&quot;, line 1207, in execute
    return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py&quot;, line 1097, in execute
    return e.execute_compiled(self, *multiparams, **params)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 780, in execute_compiled
    return connection.execute_compiled(compiled, *multiparams, **params)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py&quot;, line 567, in execute_compiled
    context.pre_exec()
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py&quot;, line 201, in pre_exec
    self.parameters = self._encode_param_keys(self.dialect.convert_compiled_params(self.compiled_parameters))
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py&quot;, line 120, in convert_compiled_params
    parameters = parameters.get_raw_list()
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py&quot;, line 864, in get_raw_list
    return [for key in self.positional](self.get_processed(key))
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py&quot;, line 845, in get_processed
    return bind.typeprocess(value, self.dialect)
  File &quot;C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py&quot;, line 1883, in typeprocess
    return self.type.dialect_impl(dialect).convert_bind_param(value, dialect)
  File &quot;C:\Tools\Python24\lib\site-packages\sqlalchemy\databases\sqlite.py&quot;, line 38, in convert_bind_param
    return value.strftime(self.__format__)
AttributeError: 'unicode' object has no attribute 'strftime'"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "D:\testcase02.py", line 19, in ?
    i.execute(name = name)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1207, in execute
    return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1097, in execute
    return e.execute_compiled(self, *multiparams, **params)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 780, in execute_compiled
    return connection.execute_compiled(compiled, *multiparams, **params)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 567, in execute_compiled
    context.pre_exec()
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 201, in pre_exec
    self.parameters = self._encode_param_keys(self.dialect.convert_compiled_params(self.compiled_parameters))
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 120, in convert_compiled_params
    parameters = parameters.get_raw_list()
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 864, in get_raw_list
    return [for key in self.positional](self.get_processed(key))
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 845, in get_processed
    return bind.typeprocess(value, self.dialect)
  File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1883, in typeprocess
    return self.type.dialect_impl(dialect).convert_bind_param(value, dialect)
  File "C:\Tools\Python24\lib\site-packages\sqlalchemy\databases\sqlite.py", line 38, in convert_bind_param
    return value.strftime(self.__format__)
AttributeError: 'unicode' object has no attribute 'strftime'
</code></pre></div>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/708/testcase02.py">testcase02.py</a></p>