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() -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.