On Mon, Oct 2, 2017 at 10:12 AM, Michael Williamson <[email protected]> wrote: > On Mon, 2 Oct 2017 10:00:43 -0400 > Mike Bayer <[email protected]> wrote: > >> On Mon, Oct 2, 2017 at 7:18 AM, Michael Williamson >> <[email protected]> wrote: >> > I'm trying to select a value using a correlated subquery that uses >> > table inheritance, but the generated query seems to leak the table >> > from the subquery into the outer query. For instance, suppose >> > employee is a table, engineer inherits from employee, and each >> > employee has a foreign key to a country. I want to get the number >> > of engineers per country, so I write: >> >> >> you found a bug, and it's in a method that has caused lots of bugs so >> this is not too surprising. >> https://bitbucket.org/zzzeek/sqlalchemy/issues/4103/_adjust_for_single_inheritance-screwing-up >> is added. > > > Thanks for the quick reply, Mike. For now, we're querying against the > base class and adding in the condition on the discriminator manually. > > When using Query, is there a way to make sure that only tables > explicitly listed using `select_from` or `join` are selected from? This > would help us prevent cases where we accidentally perform a cross join, > whether as a result of bugs or an incorrectly written query.
this issue has been discussed as SQLAlchemy's whole means of producing a FROM clause leads to this kind of thing being possible. I recall some months ago I helped someone come up with a recipe for catching these at the event level, with the goal being to produce some kind of recipe or flag for SQLAlchemy in general that could catch these (looks) OK so I've found the thread at https://groups.google.com/d/topic/sqlalchemy/z8cQJn5enZM/discussion , and one thing I get into in there with much verbosity is that it's really hard to change SQLAlchemy to not make use of the way FROM behavior works, and to require explicit FROM in all cases, however, it *is* pretty easy to detect a SELECT structure that has the issue and then emit a warning or raise an error, although I would love some more feedback on this so that I can look into making it a feature of SQLAlchemy itself. I've packaged up the recipe at: https://gist.github.com/zzzeek/c514e2c874fca54df80fc55b680e51c5 it's a good thing you brought this up because I think this might be a really promising new feature. It considers all the FROM elements as nodes in a graph and then ensures they are all reachable, traversing all JOIN / WHERE criteria that refer to two different FROM elements as an edge. The tricky part is making sure the traversal doesn't include "inner" queries that aren't part of the same context. Which is also exactly the bug being fixed above. > > Thanks > > Michael > >> > >> > engineer_count = Query([func.count(Engineer.id)]) \ >> > .select_from(Engineer) \ >> > .filter(Engineer.country_id == Country.id) \ >> > .correlate(Country) \ >> > .subquery() \ >> > .as_scalar() >> > >> > print(session.query(Country.id, engineer_count).all()) >> > >> > But this generates the query (note that employee is in the outer >> > FROM clauses): >> > >> > SELECT country.id AS country_id, (SELECT count(employee.id) AS >> > count_1 FROM employee >> > WHERE employee.country_id = country.id AND employee.type IN (?)) AS >> > anon_1 FROM country, employee >> > WHERE employee.type IN (?) >> > >> > whereas I'd expect: >> > >> > SELECT country.id AS country_id, (SELECT count(employee.id) AS >> > count_1 FROM employee >> > WHERE employee.country_id = country.id AND employee.type IN (?)) AS >> > anon_1 FROM country >> > >> > Am I doing something wrong? Full example below. >> > >> > Thanks >> > >> > Michael >> > >> > from sqlalchemy import Column, create_engine, ForeignKey, func, >> > Integer, String >> > from sqlalchemy.ext.declarative import declarative_base >> > from sqlalchemy.orm import Query, relationship, Session >> > >> > >> > Base = declarative_base() >> > >> > >> > class Country(Base): >> > __tablename__ = "country" >> > >> > id = Column(Integer, primary_key=True) >> > name = Column(String, nullable=False) >> > >> > >> > class Employee(Base): >> > __tablename__ = "employee" >> > >> > id = Column(Integer, primary_key=True) >> > discriminator = Column("type", String(50), nullable=False) >> > country_id = Column(Integer, ForeignKey("country.id"), >> > nullable=False) country = relationship(Country) >> > >> > __mapper_args__ = {"polymorphic_on": discriminator} >> > >> > >> > class Engineer(Employee): >> > __mapper_args__ = {"polymorphic_identity": "engineer"} >> > >> > >> > engine = create_engine("sqlite://", echo=True) >> > >> > Base.metadata.create_all(engine) >> > >> > >> > session = Session(engine) >> > >> > session.add(Country(name="United Kingdom")) >> > >> > >> > engineer_count = Query([func.count(Engineer.id)]) \ >> > .select_from(Engineer) \ >> > .filter(Engineer.country_id == Country.id) \ >> > .correlate(Country) \ >> > .subquery() \ >> > .as_scalar() >> > >> > print(session.query(Country.id, engineer_count).all()) >> > >> > >> > engineer_count = Query([func.count(Employee.id)]) \ >> > .select_from(Employee) \ >> > .filter(Employee.discriminator == >> > Engineer.__mapper_args__["polymorphic_identity"]) \ >> > .filter(Employee.country_id == Country.id) \ >> > .correlate(Country) \ >> > .subquery() \ >> > .as_scalar() >> > >> > print(session.query(Country.id, engineer_count).all()) >> > >> > -- >> > SQLAlchemy - >> > The Python SQL Toolkit and Object Relational Mapper >> > >> > http://www.sqlalchemy.org/ >> > >> > 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 [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. >> > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ 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 [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.
