[sqlalchemy] SQLAlchemy 1.0.12 use inner JOIN instead of LEFT OUTER JOIN

[Alex Dev]

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 -*-
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.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'])
    )

class Plant(Base):
    __table__ = _plant_table

class PlantDimensionsseries(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'))

class PlantDimensionsseriesDataComputed(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
    print session.query(Plant).\
        outerjoin(Plant.plant_dimensionsseries).\
        options(contains_eager(Plant.plant_dimensionsseries)).\
        options(joinedload(Plant.plant_dimensionsseries, 
PlantDimensionsseries.data_computed))

    # query 2:
    # SQLAlchemy 1.0.12: Correct SQL generated
    print session.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 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.