<p dir="auto"><strong>Describe the bug</strong><br>
Using SQLAlchemy 1.4.0b2, I'm joining one version of subquery on itself, so I get duplicate column names. The SQL produced properly disambiguates these column names, however the keys returned in the result set are not disambiguated.</p>
<p dir="auto">(I'm new to SQLAlchemy, so this may not be a bug, but I would think it would be as I can't distinguish between the two different fields in the results.)</p>
<p dir="auto"><strong>Expected behavior</strong><br>
For the labels to be the same as the keys in the result set.</p>
<p dir="auto"><strong>To Reproduce</strong><br>
If we have two queries joined on each other, like this:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="query1 = &lt;query&gt;.alias('a')
query2 = &lt;query&gt;.alias('b')

joined_query = (
        select(query1, query2)
        .join(query1, query1.c.join_field == query2.c.join_field)
        .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
    )"><pre class="notranslate"><span class="pl-s1">query1</span> <span class="pl-c1">=</span> <span class="pl-c1">&lt;</span><span class="pl-s1">query</span><span class="pl-c1">&gt;</span>.<span class="pl-en">alias</span>(<span class="pl-s">'a'</span>)
<span class="pl-s1">query2</span> <span class="pl-c1">=</span> <span class="pl-c1">&lt;</span><span class="pl-s1">query</span><span class="pl-c1">&gt;</span>.<span class="pl-en">alias</span>(<span class="pl-s">'b'</span>)

<span class="pl-s1">joined_query</span> <span class="pl-c1">=</span> (
        <span class="pl-en">select</span>(<span class="pl-s1">query1</span>, <span class="pl-s1">query2</span>)
        .<span class="pl-en">join</span>(<span class="pl-s1">query1</span>, <span class="pl-s1">query1</span>.<span class="pl-s1">c</span>.<span class="pl-s1">join_field</span> <span class="pl-c1">==</span> <span class="pl-s1">query2</span>.<span class="pl-s1">c</span>.<span class="pl-s1">join_field</span>)
        .<span class="pl-en">set_label_style</span>(<span class="pl-v">LABEL_STYLE_TABLENAME_PLUS_COL</span>)
    )</pre></div>
<p dir="auto">We then get SQL produced like:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT a.foo as a_foo, b.foo as b_foo
FROM (&lt;query&gt;) a
JOIN (&lt;query&gt;) b ON a.join_field = b.join_field"><pre class="notranslate"><span class="pl-k">SELECT</span> <span class="pl-c1">a</span>.<span class="pl-c1">foo</span> <span class="pl-k">as</span> a_foo, <span class="pl-c1">b</span>.<span class="pl-c1">foo</span> <span class="pl-k">as</span> b_foo
<span class="pl-k">FROM</span> (<span class="pl-k">&lt;</span>query<span class="pl-k">&gt;</span>) a
<span class="pl-k">JOIN</span> (<span class="pl-k">&lt;</span>query<span class="pl-k">&gt;</span>) b <span class="pl-k">ON</span> <span class="pl-c1">a</span>.<span class="pl-c1">join_field</span> <span class="pl-k">=</span> <span class="pl-c1">b</span>.<span class="pl-c1">join_field</span></pre></div>
<p dir="auto">So far so good. The column names are disambiguated in SQL according to the specific method.</p>
<p dir="auto">However, when I try to get the results, as follows, that disambiguation disappears:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="result = session.execute(joined_query)
for row in result:
    print(row.keys())"><pre class="notranslate"><span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-s1">joined_query</span>)
<span class="pl-k">for</span> <span class="pl-s1">row</span> <span class="pl-c1">in</span> <span class="pl-s1">result</span>:
    <span class="pl-en">print</span>(<span class="pl-s1">row</span>.<span class="pl-en">keys</span>())</pre></div>
<p dir="auto">I get a result that looks like: <code class="notranslate">RMKeyView(['foo', 'foo'])</code> which means that I can't reference the two columns separately. I would think I could do something like <code class="notranslate">row['a_foo']</code> or <code class="notranslate">row.a_foo</code> and <code class="notranslate">row['b_foo']</code> or <code class="notranslate">row.b_foo</code>, but these throw an error.</p>
<p dir="auto">(Note that this is true even using the default label disambiguation style, which would produce something like <code class="notranslate">a.foo, b.foo AS foo_1</code> in the SQL, but still return both keys as <code class="notranslate">foo</code> in the result set.)</p>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: macOS 11.1</li>
<li>Python: 3.9.0</li>
<li>SQLAlchemy: 1.4.0b2</li>
<li>Database: PostgreSQL</li>
<li>DBAPI: psycopg2==2.8.6</li>
</ul>
<p dir="auto"><strong>Thank you for any and all help!</strong><br>
I have done extensive Googling about this and can't find a resolution, which makes me think this may be a bug introduced in the new version.</p>
<p dir="auto">If this is <em>not</em> a bug, any recommendations about how to resolve this problem would be appreciated!</p>
<p dir="auto"><strong>Migrated issue, originally created by Leonardo Rossi (<a href="https://github.com/hachreak">@hachreak</a>)</strong></p>
<p dir="auto">update: this specifically hits the _stored_in_collection assertion because the check for <code class="notranslate">if event_key._listen_fn not in self.listeners</code> fails for an event key with wrapping.</p>
<p dir="auto">I'm using Invenio 2.0 and try to replace old version of SQLAlchemy 0.8.7 with the last 0.9.7.<br>
The utility to automaticaly create the db works (inveniomanage database recreate --yes-i-know).</p>
<p dir="auto">But when I start tests with: python setup.py test</p>
<p dir="auto">It return me a error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="test_fisrt_blueprint (invenio.testsuite.test_ext_template.TemplateLoaderCase) ... --------------------------------------------------------------------------------
ERROR in wrappers [/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/logging/wrappers.py:310]:

--------------------------------------------------------------------------------
Traceback (most recent call last):
  File &quot;/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/legacy/__init__.py&quot;, line 124, in __call__
    response = self.app.full_dispatch_request()
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask/app.py&quot;, line 1470, in full_dispatch_request
    self.try_trigger_before_first_request_functions()
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask/app.py&quot;, line 1497, in try_trigger_before_first_request_functions
    func()
  File &quot;/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/views.py&quot;, line 264, in invoke_email_alert_register
    email_alert_register()
  File &quot;/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/models.py&quot;, line 202, in email_alert_register
    event.listen(MsgMESSAGE, 'after_insert', email_alert)
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/api.py&quot;, line 63, in listen
    _event_key(target, identifier, fn).listen(*args, **kw)
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py&quot;, line 187, in listen
    self.dispatch_target.dispatch._listen(self, *args, **kw)
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/orm/events.py&quot;, line 547, in _listen
    event_key.base_listen(**kw)
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py&quot;, line 226, in base_listen
    for_modify(target.dispatch).append(self, propagate)
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py&quot;, line 328, in append
    event_key.append_to_list(self, self.listeners)
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py&quot;, line 237, in append_to_list
    _stored_in_collection(self, owner)
  File &quot;/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py&quot;, line 74, in _stored_in_collection
    assert dispatch_reg[owner_ref] == listen_ref
AssertionError"><pre class="notranslate"><code class="notranslate">test_fisrt_blueprint (invenio.testsuite.test_ext_template.TemplateLoaderCase) ... --------------------------------------------------------------------------------
ERROR in wrappers [/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/logging/wrappers.py:310]:

--------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/legacy/__init__.py", line 124, in __call__
    response = self.app.full_dispatch_request()
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask/app.py", line 1470, in full_dispatch_request
    self.try_trigger_before_first_request_functions()
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask/app.py", line 1497, in try_trigger_before_first_request_functions
    func()
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/views.py", line 264, in invoke_email_alert_register
    email_alert_register()
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/models.py", line 202, in email_alert_register
    event.listen(MsgMESSAGE, 'after_insert', email_alert)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/api.py", line 63, in listen
    _event_key(target, identifier, fn).listen(*args, **kw)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 187, in listen
    self.dispatch_target.dispatch._listen(self, *args, **kw)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/orm/events.py", line 547, in _listen
    event_key.base_listen(**kw)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 226, in base_listen
    for_modify(target.dispatch).append(self, propagate)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 328, in append
    event_key.append_to_list(self, self.listeners)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 237, in append_to_list
    _stored_in_collection(self, owner)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/event/registry.py", line 74, in _stored_in_collection
    assert dispatch_reg[owner_ref] == listen_ref
AssertionError
</code></pre></div>
<p dir="auto">In /home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/views.py (row 264)</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# Registration of email_alert invoked from blueprint
# in order to use before_app_first_request.
# Reading config CFG_WEBMESSAGE_EMAIL_ALERT
# required app context.
@blueprint.before_app_first_request
def invoke_email_alert_register():
    email_alert_register()"><pre class="notranslate"><code class="notranslate"># Registration of email_alert invoked from blueprint
# in order to use before_app_first_request.
# Reading config CFG_WEBMESSAGE_EMAIL_ALERT
# required app context.
@blueprint.before_app_first_request
def invoke_email_alert_register():
    email_alert_register()
</code></pre></div>
<p dir="auto">In /home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/models.py (row 202)</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# Registration of email_alert invoked from blueprint
# in order to use before_app_first_request.
# Reading config CFG_WEBMESSAGE_EMAIL_ALERT
# required app context.
def email_alert_register():
    if cfg['CFG_WEBMESSAGE_EMAIL_ALERT']:
        from sqlalchemy import event
        # Register after insert callback.
        event.listen(MsgMESSAGE, 'after_insert', email_alert)"><pre class="notranslate"><code class="notranslate"># Registration of email_alert invoked from blueprint
# in order to use before_app_first_request.
# Reading config CFG_WEBMESSAGE_EMAIL_ALERT
# required app context.
def email_alert_register():
    if cfg['CFG_WEBMESSAGE_EMAIL_ALERT']:
        from sqlalchemy import event
        # Register after insert callback.
        event.listen(MsgMESSAGE, 'after_insert', email_alert)
</code></pre></div>
<p dir="auto">Someone can help me? :)<br>
(Sorry, this is my first issues and I don't know how to set "kind", etc... T_T)</p>
<p dir="auto">Installed:</p>
<ul dir="auto">
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="-e git+https://github.com/mitsuhiko/flask-sqlalchemy@c7eccba63314f3ea77e2c6217d3d3c8b0d2552fd#egg=Flask_SQLAlchemy-2.0"><pre class="notranslate"><code class="notranslate">-e git+https://github.com/mitsuhiko/flask-sqlalchemy@c7eccba63314f3ea77e2c6217d3d3c8b0d2552fd#egg=Flask_SQLAlchemy-2.0
</code></pre></div>
</li>
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="MySQL-python==1.2.5"><pre class="notranslate"><code class="notranslate">MySQL-python==1.2.5
</code></pre></div>
</li>
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SQLAlchemy==0.9.7"><pre class="notranslate"><code class="notranslate">SQLAlchemy==0.9.7
</code></pre></div>
</li>
<li>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SQLAlchemy-Utils==0.23.5"><pre class="notranslate"><code class="notranslate">SQLAlchemy-Utils==0.23.5
</code></pre></div>
</li>
</ul>