Repository navigation
Open
Labels
ormsqluse casenot really a feature or a bug; can be support for new DB features or user use cases not anticipatednot really a feature or a bug; can be support for new DB features or user use cases not anticipated
Milestone
Description
Describe the bug
What I want to do is to associate an ORM entity USER with columns of FromClause produced by subquery() as described in tutorial.
But when I use associated <AliasedClass> instance in group_by() of new stmt it 'unpacks' all columns that are selected in a subquery instead of unpacking just User's ones.
So in example below I expect NOT to have anon_1.date in GROUP BY row
Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected
SQLAlchemy Version in Use
1.4.46
DBAPI (i.e. the database driver)
Database Vendor and Major Version
Python Version
3.9
Operating system
To Reproduce
from sqlalchemy import Integer, String, ForeignKey, Column, DateTime
from sqlalchemy import create_engine
from sqlalchemy import func
from sqlalchemy import select
from sqlalchemy.orm import declarative_base, Session, aliased
Base = declarative_base()
class User(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
name = Column(String)
surname = Column(String)
class Appointments(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
user = Column(Integer, ForeignKey("a.id"))
date = Column(DateTime(timezone=True))
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
subq = (
select(User, Appointments.date)
.join(Appointments)
.subquery()
)
user = aliased(User, subq)
stmt = (
select(
func.extract("year", subq.c.date).label("year"),
func.count(user.id),
user
)
.group_by("year", user)
)
print(stmt)Error
SELECT EXTRACT(year FROM anon_1.date) AS year, count(anon_1.id) AS count_1, anon_1.id, anon_1.name, anon_1.surname
FROM (SELECT a.id AS id, a.name AS name, a.surname AS surname, b.date AS date
FROM a JOIN b ON a.id = b."user") AS anon_1
GROUP BY year, anon_1.id, anon_1.name, anon_1.surname, anon_1.date
Additional context
No response
Metadata
Metadata
Assignees
Labels
ormsqluse casenot really a feature or a bug; can be support for new DB features or user use cases not anticipatednot really a feature or a bug; can be support for new DB features or user use cases not anticipated
Activity
[-]AliasedClass linked to a subquery when passed to order_by() unpacks all selectables of subquery (unexpeced)[/-][+]AliasedClass linked to a subquery when passed to group_by() unpacks all selectables of subquery (unexpeced)[/+][-]AliasedClass linked to a subquery when passed to group_by() unpacks all selectables of subquery (unexpeced)[/-][+]AliasedClass linked to a subquery when passed to group_by() unpacks all selectables of subquery (unexpected)[/+]CaselIT commentedon Apr 21, 2023
Hi,
I think the query generated is correct.
What SQL do you want to generate?
frozzway commentedon Apr 21, 2023
I expect NOT to have anon_1.date in GROUP BY row
CaselIT commentedon Apr 21, 2023
well you are adding user to the groupby, that includes
date. You should add the single columns you need of userfrozzway commentedon Apr 21, 2023
User includes only id, name and surname.
Date is part of Appointments table.
CaselIT commentedon Apr 21, 2023
at that point user is the subquery you created.
That includes
datefrozzway commentedon Apr 21, 2023
Well, I included user in select(), but "date" is not part of SELECT row.
I included user in group_by(), and "date" is part of GROUP BY row.
Why?
zzzeek commentedon Apr 22, 2023
the columns expand out according to the User entity only when doing an ORM query that is being asked to return User objects, such as the top level of the SELECT:
this is because the ORM takes over the columns clause of the select() when this is asked for, which makes use of mappings and all that.
group_by() does something much more basic, as it's generally not desirable to put whole FROM clauses in group_by in any case in SQL because it means the database to uniquify the whole row, so this is not a really first class use case. So when this is done, it just expands out the whole .c collection of the FromClause object, so it's going straight to the subq object that's inside. There's no hook there which can pull in the ORM to interpret the aliased object in an ORM context. It would require a significant architectural change to how the Select object represents the contents of group_by() so that this expansion could be deferred until the compilation of the object, and then new hooks added to orm/context.py that would look inside of the group_by() and check for this unusual usage.
but you're right, it is inconsistent
frozzway commentedon Apr 22, 2023
My User table has lots of columns and I still would like to use ORM-entity just to group them all together and avoid specifying each on every one in
group_by()So I come up with workaround
user_cols = subq.c._all_columns[:-1]... .group_by("year", *user_cols)Is it fine?
zzzeek commentedon Apr 22, 2023
it's too bad subq.c is not allowing an integer range since it allows integer indexes, that should be fixed too
do it like this for now, dont use any underscored things
zzzeek commentedon Apr 22, 2023
group_by appears to be a unique spot where we have a simple hardcoding to expanding out to "c" at argument time, and to accommodate where the ORM currently plugs itself in without adding tons of new methods that are used nowhere but here, group_by() can be amended to use this technique
which is a little hacky as it's going through the Select(), but that's currently where the hook is for Core to lead into ORM and get lists of columns.
[-]AliasedClass linked to a subquery when passed to group_by() unpacks all selectables of subquery (unexpected)[/-][+]reroute group_by coercion to expand fromclause using ORM-enabled plugin points[/+]zzzeek commentedon Apr 22, 2023
this is a change in behavior but I doubt people are relying on this
CaselIT commentedon Apr 22, 2023
That feels a lot like an hack..
I'm not sure how common is that use case. Can we detected if the from clause is an orm thing or not?
zzzeek commentedon Apr 22, 2023
I will try to abstract it into another API call that is similar to
sqlalchemy.sql.base._entity_namespace, which I will likely callsqlalchemy.sql.base._entity_expansion. There are necessarily some gateways in Core to extract ORM-related views of things, so this will build into that system.zzzeek commentedon Apr 22, 2023
that is instead of group_by() assuming the columns from a "select", I'll try to reorganize both on this concept of "expansion", at least to the degree that the select().selected_columns thing works
zzzeek commentedon Apr 22, 2023
for the column slice thing, super huge omission from #8285. needs an extra isinstance
sqla-tester commentedon Apr 22, 2023
Mike Bayer referenced this issue:
support slice access for .c https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4580
support slice access for .c