On 04/25/2016 05:16 PM, Alex Dev wrote:
Hello,
I have a broken query when migrating from SQLAlchemy 0.9.4 to 1.0.12. It
seems to be linked to a behavioral change in the ORM
(http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#right-inner-join-nesting-now-the-default-for-joinedload-with-innerjoin-true)
Here is simplified version of the code:
|
# -*- coding: utf-8 -*-
fromsqlalchemy import*
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm import*
Base=declarative_base()
_plant_table =Table('plant',Base.metadata,
Column('id',Integer,primary_key=True)
)
_plant_dimensionsseries_table =Table('plant_dimensionsseries',Base.metadata,
Column('plant_id',Integer,primary_key=True),
Column('dimensionsseriestype_id',Integer,primary_key=True),
ForeignKeyConstraint(['plant_id'],['plant.id'])
)
_view_plant_dimensionsseries_table
=Table('view_plant_dimensionsseries',Base.metadata,
Column('plant_id',Integer,primary_key=True),
Column('dimensionsseriestype_id',Integer,primary_key=True),
ForeignKeyConstraint(
['plant_id','dimensionsseriestype_id'],
['plant_dimensionsseries.plant_id','plant_dimensionsseries.dimensionsseriestype_id'])
)
classPlant(Base):
__table__ =_plant_table
classPlantDimensionsseries(Base):
__table__ =_plant_dimensionsseries_table
_plant_id =__table__.c.plant_id
_dimensionsseriestype_id =__table__.c.dimensionsseriestype_id
plant =relationship('Plant',
innerjoin=True,
backref=backref('plant_dimensionsseries'))
classPlantDimensionsseriesDataComputed(Base):
__table__ =_view_plant_dimensionsseries_table
_plant_id =__table__.c.plant_id
_dimensionsseriestype_id =__table__.c.dimensionsseriestype_id
# One-to-one relationship
dimensionsseries =relationship('PlantDimensionsseries',
innerjoin=True,
backref=backref('data_computed',
innerjoin=True))
if__name__ =='__main__':
engine
=create_engine('postgresql://nurseryacme_employee@localhost:5432/nurseryacme')
Session=sessionmaker(bind=engine)
session =Session()
# query 1:
# SQLAlchemy 0.9.4: Correct SQL generated
# SQLAlchemy 1.0.12: Wrong SQL generated, a inner JOIN is used instead
of a LEFT OUTER JOIN between plant_dimensionsseries and
view_plant_dimensionsseries
printsession.query(Plant).\
outerjoin(Plant.plant_dimensionsseries).\
options(contains_eager(Plant.plant_dimensionsseries)).\
options(joinedload(Plant.plant_dimensionsseries,PlantDimensionsseries.data_computed))
well this usage above is wrong. You can't have contains_eager() and
joinedload() along the same paths at the same time like that. Also,
chaining joinedload() from contains_eager() is not a very typical thing
to do, it works, but joinedload() does not coordinate with
contains_eager() in any way, and it has no idea that you are using
outerjoin() to the left of it.
The two correct ways to do this are:
print session.query(Plant).\
join(Plant.plant_dimensionsseries).\
options(contains_eager(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed,
))
and
print session.query(Plant).\
outerjoin(Plant.plant_dimensionsseries).\
options(contains_eager(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed,
innerjoin=False))
or of course:
print session.query(Plant).\
outerjoin(Plant.plant_dimensionsseries).\
options(joinedload(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed))
where in the last one, the joinedload() to the right coordinates with
the joinedload to the left to become a LEFT OUTER JOIN.
# query 2:
# SQLAlchemy 1.0.12: Correct SQL generated
printsession.query(Plant).\
outerjoin(Plant.plant_dimensionsseries).\
options(contains_eager(Plant.plant_dimensionsseries)).\
options(joinedload(Plant.plant_dimensionsseries,PlantDimensionsseries.data_computed,innerjoin=False))
|
Result with SQLAlchemy 0.9.4:
|
# query 1
SELECT ...
FROM plant LEFT OUTER JOIN plant_dimensionsseries ON plant.id
=plant_dimensionsseries.plant_id *LEFT OUTER
JOIN*view_plant_dimensionsseries AS view_plant_dimensionsseries_1 ON
plant_dimensionsseries.plant_id =view_plant_dimensionsseries_1.plant_id
AND plant_dimensionsseries.dimensionsseriestype_id
=view_plant_dimensionsseries_1.dimensionsseriestype_id
# query 2
SELECT ...
FROM plant LEFT OUTER JOIN plant_dimensionsseries ON plant.id
=plant_dimensionsseries.plant_id *LEFT OUTER
JOIN*view_plant_dimensionsseries AS view_plant_dimensionsseries_1 ON
plant_dimensionsseries.plant_id =view_plant_dimensionsseries_1.plant_id
AND plant_dimensionsseries.dimensionsseriestype_id
=view_plant_dimensionsseries_1.dimensionsseriestype_id
|
Result with SQLAlchemy 1.0.12:
|
# query 1
SELECT ...
FROM plant LEFT OUTER JOIN plant_dimensionsseries ON plant.id
=plant_dimensionsseries.plant_id *JOIN*view_plant_dimensionsseries AS
view_plant_dimensionsseries_1 ON plant_dimensionsseries.plant_id
=view_plant_dimensionsseries_1.plant_id AND
plant_dimensionsseries.dimensionsseriestype_id
=view_plant_dimensionsseries_1.dimensionsseriestype_id
# query 2
SELECT ...
FROM plant LEFT OUTER JOIN plant_dimensionsseries ON plant.id
=plant_dimensionsseries.plant_id *LEFT OUTER
JOIN*view_plant_dimensionsseries AS view_plant_dimensionsseries_1 ON
plant_dimensionsseries.plant_id =view_plant_dimensionsseries_1.plant_id
AND plant_dimensionsseries.dimensionsseriestype_id
=view_plant_dimensionsseries_1.dimensionsseriestype_id
|
In query 1 under SQLAlchemy 1.0.12, the query discard many rows due the
JOIN chained to the LEFT OUTER JOIN and this precisely what SQLAlchemy
wanted to avoid if I refer to the description of "Right-nested inner
joins available in joined eager loads"
(http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_09.html#feature-2976).
I can fix the query 1 by adding the |innerjoin=False| as in query 2 but
I am wondering if this is a bug or the expected behavior following the
behavioral change in SQLAlchemy 1.0.x
(http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#right-inner-join-nesting-now-the-default-for-joinedload-with-innerjoin-true).
Is this a bug?
--
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
