On 11/20/2015 12:20 PM, Martin Pengelly-Phillips wrote:
> Hi there,
>
> Using SQLAlchemy 1.0.9
>
> I am dealing with some legacy code and came across the following issue.
> It appears that in a polymorphic relationship SQLAlchemy is not able to
> correctly determine what to load for a relationship when using a
> subselect (due to limit on query) and child class has a different field
> name for the primary key.
>
> Here is a reproducible test case to show the issue better:
>
> |
> fromuuid importuuid1 asuuid
>
> importsqlalchemy
> importsqlalchemy.orm
> fromsqlalchemy import(
> Column,Unicode,ForeignKey,CHAR,Boolean,UniqueConstraint
> )
> fromsqlalchemy.orm importrelationship,backref,synonym
> fromsqlalchemy.ext.declarative importdeclarative_base,declared_attr
>
>
> Base=declarative_base()
>
>
> classContext(Base):
> '''Represent a context.'''
>
> __tablename__ ='context'
>
> context_type =Column(Unicode(32),nullable=False)
>
> __mapper_args__ ={
> 'polymorphic_on':context_type,
> 'polymorphic_identity':'context'
> }
>
> id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
>
>
> classTask(Context):
> '''Represent a task.'''
>
> __tablename__ ='task'
>
> __mapper_args__ ={
> 'polymorphic_identity':'task'
> }
>
> # Change this and references to it to 'id' to fix issue.
> task_id =Column(
> CHAR(36),
> ForeignKey('context.id'),
> primary_key=True
> )
>
> scopes =relationship('Scope',secondary='task_scope')
>
>
> classScope(Base):
> '''Represent a Scope.'''
>
> __tablename__ ='scope'
>
> id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
>
>
> classTaskScope(Base):
> '''Represent a relation between a scope and a task.'''
>
> __tablename__ ='task_scope'
>
> id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
>
> scope_id =Column(CHAR(36),ForeignKey(Scope.id),nullable=False)
>
> task_id =Column(CHAR(36),ForeignKey(Task.task_id),nullable=False)
>
>
> defmain():
> '''Execute test.'''
> engine =sqlalchemy.create_engine('sqlite://')
> Base.metadata.create_all(engine)
> Session=sqlalchemy.orm.sessionmaker(bind=engine)
>
> session =Session()
>
> task =Task()
> scope =Scope()
>
> session.add(task)
> session.add(scope)
> session.commit()
>
> link =TaskScope(scope_id=scope.id,task_id=task.task_id)
> session.add(link)
> session.commit()
>
> query =session.query(Task).options(
> sqlalchemy.orm.load_only('context_type'),
> sqlalchemy.orm.joinedload('scopes').load_only()
> ).limit(10)
> printquery
>
> results =query.all()
> printresults
>
>
> if__name__ =='__main__':
> main()
> |
>
> Running the above gives:
>
> |
> sqlalchemy.exc.OperationalError:(sqlite3.OperationalError)nosuch
> column:task.task_id [SQL:u'SELECT anon_1.context_context_type AS
> anon_1_context_context_type, anon_1.context_id AS anon_1_context_id,
> anon_2.scope_1_id AS scope_1_id \nFROM (SELECT context.context_type AS
> context_context_type, context.id AS context_id \nFROM context JOIN task
> ON context.id = task.task_id\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER
> JOIN (SELECT task_scope_1.id AS task_scope_1_id, task_scope_1.scope_id
> AS task_scope_1_scope_id, task_scope_1.task_id AS task_scope_1_task_id,
> scope_1.id AS scope_1_id \nFROM task_scope AS task_scope_1 JOIN scope AS
> scope_1 ON scope_1.id = task_scope_1.scope_id) AS anon_2 ON task.task_id
> = anon_2.task_scope_1_task_id'][parameters:(10,0)]
> |
>
>
> If you change 'task_id' to 'id' (and references to it) then the query
> will now work correctly.
this is a bug and requires five separate conditions to all be in place
at the same time in order to occur. This is issue
https://bitbucket.org/zzzeek/sqlalchemy/issues/3592/logic-to-ensure-parent-cols-present-in
fixed in 60c36ca8418cec18073 and 7998f15b1687a0bd0b3 for rel_1_0. For
workaround, don't exclude the attribute that's needed for the
joinedload(), eg:
query = session.query(Task).options(
sqlalchemy.orm.load_only('context_type', 'task_id'),
sqlalchemy.orm.joinedload('scopes').load_only()
).limit(10)
>
> As I mentioned this is legacy code so there are many references to
> task_id throughout the code base unfortunately. I have found a
> workaround by using a synonym, but wanted to report the issue to see if
> the failure is expected and there is a requirement for the primary key
> field names to match or not.
>
> cheers,
>
>
> Martin
>
> --
> 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
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
