Issue 1:
Describe the bug

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.

(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.)

Expected behavior

For the labels to be the same as the keys in the result set.

To Reproduce

If we have two queries joined on each other, like this:

query1 = <query>.alias('a')
query2 = <query>.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)
)

We then get SQL produced like:

SELECT a.foo as a_foo, b.foo as b_foo
FROM (<query>) a
JOIN (<query>) b ON a.join_field = b.join_field

So far so good. The column names are disambiguated in SQL according to the specific method.

However, when I try to get the results, as follows, that disambiguation disappears:

result = session.execute(joined_query)
for row in result:
print(row.keys())

I get a result that looks like: RMKeyView(['foo', 'foo']) which means that I can't reference the two columns separately. I would think I could do something like row['a_foo'] or row.a_foo and row['b_foo'] or row.b_foo, but these throw an error.

(Note that this is true even using the default label disambiguation style, which would produce something like a.foo, b.foo AS foo_1 in the SQL, but still return both keys as foo in the result set.)

Versions.

OS: macOS 11.1

Python: 3.9.0

SQLAlchemy: 1.4.0b2

Database: PostgreSQL

DBAPI: psycopg2==2.8.6

Thank you for any and all help!

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.

If this is not a bug, any recommendations about how to resolve this problem would be appreciated!

Issue 2:
Migrated issue, originally created by Leonardo Rossi (@hachreak)

update: this specifically hits the _stored_in_collection assertion because the check for if event_key._listen_fn not in self.listeners fails for an event key with wrapping.

I'm using Invenio 2.0 and try to replace old version of SQLAlchemy 0.8.7 with the last 0.9.7.

The utility to automaticaly create the db works (inveniomanage database recreate --yes-i-know).

But when I start tests with: python setup.py test

It return me a error:

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

In /home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/views.py (row 264)

# 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()

In /home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/modules/messages/models.py (row 202)

# 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)

Someone can help me? :)

(Sorry, this is my first issues and I don't know how to set "kind", etc... T_T)

Installed:

-e git+https://github.com/mitsuhiko/flask-sqlalchemy@c7eccba63314f3ea77e2c6217d3d3c8b0d2552fd#egg=Flask_SQLAlchemy-2.0

MySQL-python==1.2.5

SQLAlchemy==0.9.7

SQLAlchemy-Utils==0.23.5