possible bug when using schema_translate_map and remapping "None"

[Bertrand Mathieu]

Hi,

I am using sqlalchemy 1.1.5 with python 3.5 and postgresql (9.4).
I have encountered what I think is a bug, it occurs when:

* no schema is set in metadata or tables (schema = None)
* using schema_translate_map={None: 'other_schema_name'}.

It happens when joining an aliased table. Basically, when the statement is rendered the alias ends up prefixed with "other_schema_name" in the condition expression.

See below for code to reproduce it.

I don't know if this is an unintended setup; I use it in particular to run each test session in its own, dedicated schema on a real database. Otherwise I have no reason to set a schema name in my code. For now as a workaround for the tests session I patch metadata.schema and tables schema with a constant string, and then remap this constant name to the temporary schema.

Here is the output of the script below:

ProgrammingError: (psycopg2.ProgrammingError) invalid reference to FROM-clause entry for table "users_groups_1"
LINE 2: ...ers_groups_1 ON translated_schema_name.users.id = translated...
                                                             ^
HINT:  There is an entry for table "users_groups_1", but it cannot be referenced from this part of the query.
 [SQL: 'SELECT translated_schema_name.users.id AS users_id, translated_schema_name.users.name AS users_name, translated_schema_name.users.fullname AS users_fullname, translated_schema_name.users.password AS users_password \nFROM translated_schema_name.users JOIN translated_schema_name.users_groups AS users_groups_1 ON translated_schema_name.users.id = translated_schema_name.users_groups_1.user_id JOIN translated_schema_name.groups ON translated_schema_name.groups.id = translated_schema_name.users_groups_1.group_id JOIN translated_schema_name.users_groups AS users_groups_2 ON translated_schema_name.users.id = translated_schema_name.users_groups_2.user_id JOIN translated_schema_name.groups AS groups_1 ON translated_schema_name.groups_1.id = translated_schema_name.users_groups_2.group_id \nWHERE translated_schema_name.groups.name = %(name_1)s AND translated_schema_name.groups_1.name = %(name_2)s'] [parameters: {'name_2': 'other', 'name_1': 'some'}]


What is incorrectly generated:
JOIN translated_schema_name.users_groups AS users_groups_2  ON translated_schema_name.users.id = translated_schema_name.users_groups_2.user_id

The right expression should be: "users_groups_2.user_id"


Here is the code - just adjust the connection string to your local setup.

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

engine = sa.create_engine('postgres://user:password@localhost:5432/testdb', echo=True)
metadata = sa.MetaData()
Base = declarative_base(metadata=metadata)
SCHEMA_NAME = 'translated_schema_name'


class Group(Base):
    __tablename__ = 'groups'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)

user_groups = sa.Table(
    'users_groups', metadata,
    sa.Column('user_id', sa.Integer, sa.ForeignKey('users.id'), primary_key=True),
    sa.Column('group_id', sa.Integer, sa.ForeignKey('groups.id'), primary_key=True),
)


class User(Base):
    __tablename__ = 'users'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    fullname = sa.Column(sa.String)
    password = sa.Column(sa.String)
    groups = sa.orm.relationship(Group, secondary=user_groups)

GroupAlias = sa.orm.aliased(Group)
conn = engine.connect().execution_options(schema_translate_map={None: SCHEMA_NAME})
with conn.begin() as tr:
    conn.execute(sa.schema.CreateSchema(SCHEMA_NAME))
    metadata.create_all(bind=conn)
    session = sa.orm.Session(bind=conn)
    query = session.query(User) \
                   .join(Group, User.groups) \
                   .join(GroupAlias, User.groups) \
                   .filter(Group.name == 'some', GroupAlias.name == 'other')
    query.all()  # Boom
    tr.rollback()

[mike bayer]

this is a bug
https://bitbucket.org/zzzeek/sqlalchemy/issues/3924/schema_translate_map-is-running-on-aliases
is added.

> .filter(Group.name =='some',GroupAlias.name =='other')
> query.all() # Boom
> tr.rollback()
>
> |
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.