ORM does not select column from nested union

44 views
Skip to first unread message

Mikhail Knyazev

unread,
Nov 29, 2019, 1:53:07 PM11/29/19
to sqlalchemy
Hi, Mike.
I run into strange behavior, see example below. In short, ORM does not select column from nested union when a label is assigned to the column. This results in mixed up attributes of a mapped object.

from datetime import datetime

from sqlalchemy import Column, DateTime, Integer, create_engine, select, union
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime)
    modified_at = Column(DateTime)


engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

dt1, dt2 = datetime(2011, 1, 1), datetime(2012, 2, 2)
dt3, dt4 = datetime(2013, 3, 3), datetime(2014, 4, 4)

article1 = Article(created_at=dt1, modified_at=dt2)
article2 = Article(created_at=dt3, modified_at=dt4)
session.add_all((article1, article2))
session.commit()
session.expunge_all()

query = (
    session.query(Article)
    .select_entity_from(
        union(
            select((Article, Article.modified_at.label('order_by'))),
            select((Article, Article.created_at.label('order_by'))),
        )
    )
    .order_by(Article.id)
)

article1, article2 = query.all()

print('article1')
print('\tcreated_at', article1.created_at, '\t\texpected', dt1.isoformat())
print('\tmodified_at', article1.modified_at, '\texpected', dt2.isoformat())
print('article2')
print('\tcreated_at', article2.created_at, '\t\texpected', dt3.isoformat())
print('\tmodified_at', article2.modified_at, '\texpected', dt4.isoformat())

# article1
#         created_at 2011-01-01 00:00:00          expected 2011-01-01T00:00:00
#         modified_at 2011-01-01 00:00:00         expected 2012-02-02T00:00:00
# article2
#         created_at 2013-03-03 00:00:00          expected 2013-03-03T00:00:00
#         modified_at 2013-03-03 00:00:00         expected 2014-04-04T00:00:00

Rendered SQL query:

SELECT anon_1.id AS anon_1_id, anon_1.order_by AS anon_1_order_by, anon_1.modified_at AS anon_1_modified_at
FROM (SELECT article.id          AS id,
             article.created_at  AS created_at, -- this field is not selected by outer SELECT
             article.modified_at AS modified_at,
             article.created_at  AS order_by
      FROM article
      UNION
      SELECT article.id          AS id,
             article.created_at  AS created_at, -- this field is not selected by outer SELECT
             article.modified_at AS modified_at,
             article.modified_at AS order_by
      FROM article) AS anon_1
ORDER BY anon_1.id




Mike Bayer

unread,
Nov 29, 2019, 6:20:37 PM11/29/19
to noreply-spamdigest via sqlalchemy


On Fri, Nov 29, 2019, at 1:53 PM, Mikhail Knyazev wrote:
Hi, Mike.
I run into strange behavior, see example below. In short, ORM does not select column from nested union when a label is assigned to the column. This results in mixed up attributes of a mapped object.

hi

the "modified_at" / "created_at" columns are being repeated in each SELECT in a mixed way and I would guess this is confusing the ORM, which is likely using that last column to populate "modified_at" in the entity.      the query as given doesn't seem to be using this "order_by" column and it's also selecting dupes so I believe we have to resolve for a modified  XY problem here [1]  with the modification that "user doesn't know how to do Y" should read "SQLAlchemy can't really do Y without some trickery if at all"   ....  What is the *actual* thing you need to do ?


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

Mike Bayer

unread,
Nov 29, 2019, 6:28:07 PM11/29/19
to noreply-spamdigest via sqlalchemy
quick and dirty I would just manufacture that column so that it has no Python-side correspondence:

from sqlalchemy import column

select_entity_from(
   select([Article, column('modified_at').label('order_by')]),
   select([Article, column('created_at').label('order_by')]),
).order_by("order_by")  # im assuming this is what you are actually doing

also this *might* be different if you tried github master / 1.4 where there have been changes to how dupe columns are handled

Mikhail Knyazev

unread,
Dec 2, 2019, 6:03:27 AM12/2/19
to sqlalchemy
Mike, thanks a lot for your help! Sorry for over-distilled example, I'm adding some context to (maybe) make the issue more searchable. Your guess is on point: I'm trying to select two querysets from the same table. One of them should be sorted by `modified_at` column while the other should use `created_at` column. Then the resulting query is augmented with row_number like `SELECT ..., row_number() OVER (ORDER BY order_by) FROM <union>` and used in `select_entity_from`.

Using constructed columns like you suggested did the trick.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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 sqlal...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages