Skip to content

reroute group_by coercion to expand fromclause using ORM-enabled plugin points #9690

Open

Description

@frozzway

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

https://docs.sqlalchemy.org/en/14/tutorial/data_select.html#orm-entity-subqueries-ctes

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

Activity

  1. changed the title [-]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)[/+] on Apr 21, 2023
  2. changed the title [-]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)[/+] on Apr 21, 2023
  3. CaselIT commented on Apr 21, 2023

    @CaselIT
    Member

    Hi,

    I think the query generated is correct.
    What SQL do you want to generate?

  4. added
    awaiting infowaiting for the submitter to give more information
    and removed
    requires triageNew issue that requires categorization
    on Apr 21, 2023
  5. frozzway commented on Apr 21, 2023

    @frozzway
    Author

    Hi,

    I think the query generated is correct. What SQL do you want to generate?

    I expect NOT to have anon_1.date in GROUP BY row

  6. CaselIT commented on Apr 21, 2023

    @CaselIT
    Member

    well you are adding user to the groupby, that includes date. You should add the single columns you need of user

  7. frozzway commented on Apr 21, 2023

    @frozzway
    Author

    well you are adding user to the groupby, that includes date. You should add the single columns you need of user

    User includes only id, name and surname.
    Date is part of Appointments table.

  8. CaselIT commented on Apr 21, 2023

    @CaselIT
    Member

    at that point user is the subquery you created.

    subq = (
        select(User, Appointments.date)
        .join(Appointments)
        .subquery()
    )

    That includes date

  9. frozzway commented on Apr 21, 2023

    @frozzway
    Author

    at that point user is the subquery you created.

    subq = (
        select(User, Appointments.date)
        .join(Appointments)
        .subquery()
    )

    That includes date

    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?

  10. zzzeek commented on Apr 22, 2023

    @zzzeek
    Member

    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:

    user = aliased(User, subq)
    
    print(select(user))
    SELECT 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

    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

  11. frozzway commented on Apr 22, 2023

    @frozzway
    Author

    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?

  12. zzzeek commented on Apr 22, 2023

    @zzzeek
    Member

    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

       .group_by("year", *select(user).selected_columns)
    
  13. zzzeek commented on Apr 22, 2023

    @zzzeek
    Member

    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

    diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py
    index 3b187e49d4..cf64a30ae8 100644
    --- a/lib/sqlalchemy/sql/coercions.py
    +++ b/lib/sqlalchemy/sql/coercions.py
    @@ -987,6 +987,7 @@ class OrderByImpl(ByOfImpl, RoleImpl):
     class GroupByImpl(ByOfImpl, RoleImpl):
         __slots__ = ()
     
    +    @util.preload_module("sqlalchemy.sql.selectable")
         def _implicit_coercions(
             self,
             element: Any,
    @@ -995,7 +996,10 @@ class GroupByImpl(ByOfImpl, RoleImpl):
             **kw: Any,
         ) -> Any:
             if is_from_clause(resolved):
    -            return elements.ClauseList(*resolved.c)
    +            selectable = util.preloaded.sql_selectable
    +            return elements.ClauseList(
    +                *selectable.Select(element).selected_columns
    +            )
             else:
                 return resolved
     
    

    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.

  14. added
    use casenot really a feature or a bug; can be support for new DB features or user use cases not anticipated
    and removed
    awaiting infowaiting for the submitter to give more information
    on Apr 22, 2023
  15. added this to the 2.0.x milestone on Apr 22, 2023
  16. changed the title [-]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[/+] on Apr 22, 2023
  17. zzzeek commented on Apr 22, 2023

    @zzzeek
    Member

    this is a change in behavior but I doubt people are relying on this

  18. CaselIT commented on Apr 22, 2023

    @CaselIT
    Member

    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?

  19. zzzeek commented on Apr 22, 2023

    @zzzeek
    Member

    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?

    I will try to abstract it into another API call that is similar to sqlalchemy.sql.base._entity_namespace, which I will likely call sqlalchemy.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.

  20. zzzeek commented on Apr 22, 2023

    @zzzeek
    Member

    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

  21. zzzeek commented on Apr 22, 2023

    @zzzeek
    Member

    for the column slice thing, super huge omission from #8285. needs an extra isinstance

  22. sqla-tester commented on Apr 22, 2023

    @sqla-tester
    Collaborator
  23. added a commit that references this issue on Apr 22, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    ormsqluse casenot really a feature or a bug; can be support for new DB features or user use cases not anticipated

    Type

    No type

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions