<p dir="auto">Hello,</p>
<p dir="auto">When writing some after_flush code we noticed a strange behavior, where the last linked child object is duplicated in the parent object's association proxy. This association proxy is a many to many.</p>
<p dir="auto">Here's a little script to reproduce</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

engine = sa.create_engine('sqlite:///:memory:')
Base = declarative_base()
Base.metadata.bind = engine
Session = sa.orm.sessionmaker(autocommit=False, autoflush=False, bind=engine)
session = sa.orm.scoped_session(Session)


class Teacher(Base):
    __tablename__ = 'teacher'
    id = sa.Column(sa.Integer, primary_key=True)


class Class(Base):
    __tablename__ = 'class'

    teacher = sa.orm.relationship('Teacher', backref='classes')
    teacher_id = sa.Column(sa.Integer, sa.ForeignKey('teacher.id'), primary_key=True)

    student = sa.orm.relationship('Student', backref='classes')
    student_id = sa.Column(sa.Integer, sa.ForeignKey('student.id'), primary_key=True)


class Student(Base):
    __tablename__ = 'student'

    id = sa.Column(sa.Integer, primary_key=True)

    teachers = association_proxy('classes', 'teacher')



@sa.event.listens_for(Session, 'after_flush')
def report_teachers(session, flush_context):
    for target in set(session.new).union(session.dirty):
        if isinstance(target, Student):
            print(target.teachers)
            print([t.id for t in target.teachers])

Base.metadata.create_all()

student = Student()

french_class = Class(teacher=Teacher(), student=student)
session.add(french_class)
session.commit()

dutch_class = Class(teacher=Teacher(), student=student)
session.add(dutch_class)
session.commit()"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">as</span> <span class="pl-s1">sa</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">ext</span>.<span class="pl-s1">associationproxy</span> <span class="pl-k">import</span> <span class="pl-s1">association_proxy</span>

<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-en">create_engine</span>(<span class="pl-s">'sqlite:///:memory:'</span>)
<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()
<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-s1">bind</span> <span class="pl-c1">=</span> <span class="pl-s1">engine</span>
<span class="pl-v">Session</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">sessionmaker</span>(<span class="pl-s1">autocommit</span><span class="pl-c1">=</span><span class="pl-c1">False</span>, <span class="pl-s1">autoflush</span><span class="pl-c1">=</span><span class="pl-c1">False</span>, <span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)
<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">scoped_session</span>(<span class="pl-v">Session</span>)


<span class="pl-k">class</span> <span class="pl-v">Teacher</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'teacher'</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</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-k">class</span> <span class="pl-v">Class</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'class'</span>

    <span class="pl-s1">teacher</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">relationship</span>(<span class="pl-s">'Teacher'</span>, <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-s">'classes'</span>)
    <span class="pl-s1">teacher_id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>, <span class="pl-s1">sa</span>.<span class="pl-v">ForeignKey</span>(<span class="pl-s">'teacher.id'</span>), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

    <span class="pl-s1">student</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">relationship</span>(<span class="pl-s">'Student'</span>, <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-s">'classes'</span>)
    <span class="pl-s1">student_id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>, <span class="pl-s1">sa</span>.<span class="pl-v">ForeignKey</span>(<span class="pl-s">'student.id'</span>), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-k">class</span> <span class="pl-v">Student</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'student'</span>

    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</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">teachers</span> <span class="pl-c1">=</span> <span class="pl-en">association_proxy</span>(<span class="pl-s">'classes'</span>, <span class="pl-s">'teacher'</span>)



<span class="pl-en">@<span class="pl-s1">sa</span>.<span class="pl-s1">event</span>.<span class="pl-en">listens_for</span>(<span class="pl-v">Session</span>, <span class="pl-s">'after_flush'</span>)</span>
<span class="pl-k">def</span> <span class="pl-en">report_teachers</span>(<span class="pl-s1">session</span>, <span class="pl-s1">flush_context</span>):
    <span class="pl-k">for</span> <span class="pl-s1">target</span> <span class="pl-c1">in</span> <span class="pl-en">set</span>(<span class="pl-s1">session</span>.<span class="pl-s1">new</span>).<span class="pl-en">union</span>(<span class="pl-s1">session</span>.<span class="pl-s1">dirty</span>):
        <span class="pl-k">if</span> <span class="pl-en">isinstance</span>(<span class="pl-s1">target</span>, <span class="pl-v">Student</span>):
            <span class="pl-en">print</span>(<span class="pl-s1">target</span>.<span class="pl-s1">teachers</span>)
            <span class="pl-en">print</span>([<span class="pl-s1">t</span>.<span class="pl-s1">id</span> <span class="pl-k">for</span> <span class="pl-s1">t</span> <span class="pl-c1">in</span> <span class="pl-s1">target</span>.<span class="pl-s1">teachers</span>])

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

<span class="pl-s1">student</span> <span class="pl-c1">=</span> <span class="pl-v">Student</span>()

<span class="pl-s1">french_class</span> <span class="pl-c1">=</span> <span class="pl-v">Class</span>(<span class="pl-s1">teacher</span><span class="pl-c1">=</span><span class="pl-v">Teacher</span>(), <span class="pl-s1">student</span><span class="pl-c1">=</span><span class="pl-s1">student</span>)
<span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">french_class</span>)
<span class="pl-s1">session</span>.<span class="pl-en">commit</span>()

<span class="pl-s1">dutch_class</span> <span class="pl-c1">=</span> <span class="pl-v">Class</span>(<span class="pl-s1">teacher</span><span class="pl-c1">=</span><span class="pl-v">Teacher</span>(), <span class="pl-s1">student</span><span class="pl-c1">=</span><span class="pl-s1">student</span>)
<span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">dutch_class</span>)
<span class="pl-s1">session</span>.<span class="pl-en">commit</span>()</pre></div>
<p dir="auto">When running this script, the result is</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="❱ python test.py
[&lt;__main__.Teacher object at 0x7f6df50bb6d8&gt;]
[1]
[&lt;__main__.Teacher object at 0x7f6df5048cf8&gt;, &lt;__main__.Teacher object at 0x7f6df5048da0&gt;, &lt;__main__.Teacher object at 0x7f6df5088978&gt;]
[1, 2, 2]"><pre class="notranslate"><code class="notranslate">❱ python test.py
[&lt;__main__.Teacher object at 0x7f6df50bb6d8&gt;]
[1]
[&lt;__main__.Teacher object at 0x7f6df5048cf8&gt;, &lt;__main__.Teacher object at 0x7f6df5048da0&gt;, &lt;__main__.Teacher object at 0x7f6df5088978&gt;]
[1, 2, 2]
</code></pre></div>
<p dir="auto">As you can see, after the first commit, the list only has the teacher for the french class, while after the second commit, the list has one teacher for the french class, and a duplicated teacher for the dutch class (same database id, different internal python id).</p>
<p dir="auto">Doing a <code class="notranslate">session.refresh(target)</code> within the after flush is a current workaround.</p>
<p dir="auto">Versions this was reproduced on:</p>
<ul dir="auto">
<li>1.2.0b3</li>
<li>current master branch (<a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/3836a90dcf9b202db2eb8f013b8eca07245067be/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/3836a90dcf9b202db2eb8f013b8eca07245067be"><tt>3836a90</tt></a>)</li>
</ul>
<p dir="auto">Python version is 3.6.7</p>
<p dir="auto">To allow Mr. Bayer's fun on Link's awakening, I will investigate further and see if I can propose a fix at a later date ;)</p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">In code like this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Index('ix_foo',
      sometable.c.somecol,
      postgres_where=sometable.c.type=='somevalue')"><pre class="notranslate"><code class="notranslate">Index('ix_foo',
      sometable.c.somecol,
      postgres_where=sometable.c.type=='somevalue')
</code></pre></div>
<p dir="auto">I expect the index creation DDL to be:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CREATE UNIQUE INDEX ix_foo ON sometable (somecol) WHERE sometable.type = 'somevalue'"><pre class="notranslate"><code class="notranslate">CREATE UNIQUE INDEX ix_foo ON sometable (somecol) WHERE sometable.type = 'somevalue'
</code></pre></div>
<p dir="auto">but it is:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CREATE UNIQUE INDEX ix_foo ON sometable (somecol) WHERE sometable.type = somevalue"><pre class="notranslate"><code class="notranslate">CREATE UNIQUE INDEX ix_foo ON sometable (somecol) WHERE sometable.type = somevalue
</code></pre></div>
<p dir="auto">So the string literal is not quoted, as it is when I specify WHERE conditions in queries. This violates the principle of least astonishment.</p>
<p dir="auto">As a workaround, nested quoting (<code class="notranslate">"'base'"</code>) works.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1640/0001-test_create_partial_index_quoting.diff">0001-test_create_partial_index_quoting.diff</a></p>