Re: [sqlalchemy] Potential bug with .exists() queries and subqueryloads
> > however, I can't think of any reason public use of .subquery() or > .exists() would ever *want* eagerloaded options to take place since by > definition .subquery() and .exists() are never used to load objects. > So in 1.2 I'll propose to make this automatic > Yep, I agree with this! Thanks, I'll use .enable_eagerloads(False) to fix it for now. On Tuesday, July 18, 2017 at 11:56:08 AM UTC-7, Mike Bayer wrote: > > On Tue, Jul 18, 2017 at 2:13 PM,> wrote: > > Hello, I'm running into some difference in behavior for .exists() in > > SQLAlchemy 1.0.17 and 1.1.11, related to subqueryloads. > > > > > > class A(db.Model): > > __tablename__ = 'a' > > > > id = db.Column(db.Integer, primary_key=True) > > b_id = db.Column(db.Integer, db.ForeignKey('b.id'), nullable=False) > > b = db.relationship('B', lazy='subquery') > > > > > > class B(db.Model): > > __tablename__ = 'b' > > > > id = db.Column(db.Integer, primary_key=True) > > > > > > In SQLAlchemy 1.0.17, I can do all of the following: > > > > A.query.exists() # Note that A.b has lazy='subquery' > > A.query.options(subqueryload(A.b)).exists() > > > > # We usually use this in the context of: > > db.session.query(A.query.exists()).scalar() > > There is a bug here in add_column() / subqueryload added as > > https://bitbucket.org/zzzeek/sqlalchemy/issues/4033/dont-crash-for-add_columns-when. > > > > however if you go into your 1.0 version and instead do lazy="joined" > you'll see your EXISTS query rendering out the LEFT OUTER JOIN > entirely unnecessarily. > > Current API when you're turning the query into a subquery or exists is > to call enable_eagerloads(False): > > print s.query(A).enable_eagerloads(False).exists() > > > the method is documented as such: > > > https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=enable_eagerloads#sqlalchemy.orm.query.Query.enable_eagerloads > > > however, I can't think of any reason public use of .subquery() or > .exists() would ever *want* eagerloaded options to take place since by > definition .subquery() and .exists() are never used to load objects. > So in 1.2 I'll propose to make this automatic, that's > > https://bitbucket.org/zzzeek/sqlalchemy/issues/4032/enable_eagerloads-false-automatically-for. > > > > > > > > > > > > In SQLALchemy 1.1.11, we run into the following error: > > A.query.exists() # Note that A.b has lazy='subquery' > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in exists(self) > >3046 # .with_only_columns() after we have a core select() so > that > >3047 # we get just "SELECT 1" without any entities. > > -> 3048 return sql.exists(self.add_columns('1').with_labels(). > >3049 statement.with_only_columns([1])) > >3050 > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > statement(self) > > 465 """ > > 466 > > --> 467 stmt = self._compile_context(labels=self._with_labels).\ > > 468 statement > > 469 if self._params: > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > > _compile_context(self, labels) > >3363 > >3364 for entity in self._entities: > > -> 3365 entity.setup_context(self, context) > >3366 > >3367 for rec in context.create_eager_joins: > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > > setup_context(self, query, context) > >3725 with_polymorphic=self._with_polymorphic, > >3726 only_load_props=query._only_load_props, > > -> 3727 > > polymorphic_discriminator=self._polymorphic_discriminator) > >3728 > >3729 def __str__(self): > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in > > _setup_entity_query(context, mapper, query_entity, path, adapter, > > column_collection, with_polymorphic, only_load_props, > > polymorphic_discriminator, **kw) > > 257 column_collection=column_collection, > > 258 memoized_populators=quick_populators, > > --> 259 **kw > > 260 ) > > 261 > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/interfaces.pyc in > setup(self, > > context, entity, path, adapter, **kwargs) > > 515 else: > > 516 strat = self.strategy > > --> 517 strat.setup_query(context, entity, path, loader, > adapter, > > **kwargs) > > 518 > > 519 def create_row_processor( > > > > .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in > > setup_query(self, context, entity, path, loadopt, adapter, > > column_collection, parentmapper, **kwargs) > > 786 orig_query, leftmost_mapper, > > 787 leftmost_attr, leftmost_relationship, > > --> 788 entity.entity_zero > > 789 ) > > 790
Re: [sqlalchemy] Potential bug with .exists() queries and subqueryloads
On Tue, Jul 18, 2017 at 2:13 PM,wrote: > Hello, I'm running into some difference in behavior for .exists() in > SQLAlchemy 1.0.17 and 1.1.11, related to subqueryloads. > > > class A(db.Model): > __tablename__ = 'a' > > id = db.Column(db.Integer, primary_key=True) > b_id = db.Column(db.Integer, db.ForeignKey('b.id'), nullable=False) > b = db.relationship('B', lazy='subquery') > > > class B(db.Model): > __tablename__ = 'b' > > id = db.Column(db.Integer, primary_key=True) > > > In SQLAlchemy 1.0.17, I can do all of the following: > > A.query.exists() # Note that A.b has lazy='subquery' > A.query.options(subqueryload(A.b)).exists() > > # We usually use this in the context of: > db.session.query(A.query.exists()).scalar() There is a bug here in add_column() / subqueryload added as https://bitbucket.org/zzzeek/sqlalchemy/issues/4033/dont-crash-for-add_columns-when. however if you go into your 1.0 version and instead do lazy="joined" you'll see your EXISTS query rendering out the LEFT OUTER JOIN entirely unnecessarily. Current API when you're turning the query into a subquery or exists is to call enable_eagerloads(False): print s.query(A).enable_eagerloads(False).exists() the method is documented as such: https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=enable_eagerloads#sqlalchemy.orm.query.Query.enable_eagerloads however, I can't think of any reason public use of .subquery() or .exists() would ever *want* eagerloaded options to take place since by definition .subquery() and .exists() are never used to load objects. So in 1.2 I'll propose to make this automatic, that's https://bitbucket.org/zzzeek/sqlalchemy/issues/4032/enable_eagerloads-false-automatically-for. > > In SQLALchemy 1.1.11, we run into the following error: > A.query.exists() # Note that A.b has lazy='subquery' > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in exists(self) >3046 # .with_only_columns() after we have a core select() so that >3047 # we get just "SELECT 1" without any entities. > -> 3048 return sql.exists(self.add_columns('1').with_labels(). >3049 statement.with_only_columns([1])) >3050 > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in statement(self) > 465 """ > 466 > --> 467 stmt = self._compile_context(labels=self._with_labels).\ > 468 statement > 469 if self._params: > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > _compile_context(self, labels) >3363 >3364 for entity in self._entities: > -> 3365 entity.setup_context(self, context) >3366 >3367 for rec in context.create_eager_joins: > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > setup_context(self, query, context) >3725 with_polymorphic=self._with_polymorphic, >3726 only_load_props=query._only_load_props, > -> 3727 > polymorphic_discriminator=self._polymorphic_discriminator) >3728 >3729 def __str__(self): > > .../lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in > _setup_entity_query(context, mapper, query_entity, path, adapter, > column_collection, with_polymorphic, only_load_props, > polymorphic_discriminator, **kw) > 257 column_collection=column_collection, > 258 memoized_populators=quick_populators, > --> 259 **kw > 260 ) > 261 > > .../lib/python2.7/site-packages/sqlalchemy/orm/interfaces.pyc in setup(self, > context, entity, path, adapter, **kwargs) > 515 else: > 516 strat = self.strategy > --> 517 strat.setup_query(context, entity, path, loader, adapter, > **kwargs) > 518 > 519 def create_row_processor( > > .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in > setup_query(self, context, entity, path, loadopt, adapter, > column_collection, parentmapper, **kwargs) > 786 orig_query, leftmost_mapper, > 787 leftmost_attr, leftmost_relationship, > --> 788 entity.entity_zero > 789 ) > 790 > > .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in > _generate_from_original_query(self, orig_query, leftmost_mapper, > leftmost_attr, leftmost_relationship, orig_entity) > 859 ent['entity'] for ent in > orig_query.column_descriptions > 860 ])), > --> 861 False > 862 ) > 863 > > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in > _set_select_from(self, obj, set_base_alias) > 191 > 192 for from_obj in obj: > --> 193 info = inspect(from_obj) > 194 if hasattr(info, 'mapper') and \ > 195 (info.is_mapper or info.is_aliased_class): > > .../lib/python2.7/site-packages/sqlalchemy/inspection.pyc in >
[sqlalchemy] Potential bug with .exists() queries and subqueryloads
Hello, I'm running into some difference in behavior for .exists() in SQLAlchemy 1.0.17 and 1.1.11, related to subqueryloads. class A(db.Model): __tablename__ = 'a' id = db.Column(db.Integer, primary_key=True) b_id = db.Column(db.Integer, db.ForeignKey('b.id'), nullable=False) b = db.relationship('B', lazy='subquery') class B(db.Model): __tablename__ = 'b' id = db.Column(db.Integer, primary_key=True) In SQLAlchemy 1.0.17, I can do all of the following: A.query.exists() # Note that A.b has lazy='subquery' A.query.options(subqueryload(A.b)).exists() # We usually use this in the context of: db.session.query(A.query.exists()).scalar() In SQLALchemy 1.1.11, we run into the following error: A.query.exists() # Note that A.b has lazy='subquery' .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in exists(self) 3046 # .with_only_columns() after we have a core select() so that 3047 # we get just "SELECT 1" without any entities. -> 3048 return sql.exists(self.add_columns('1').with_labels(). 3049 statement.with_only_columns([1])) 3050 .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in statement(self) 465 """ 466 --> 467 stmt = self._compile_context(labels=self._with_labels).\ 468 statement 469 if self._params: .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _compile_context(self, labels) 3363 3364 for entity in self._entities: -> 3365 entity.setup_context(self, context) 3366 3367 for rec in context.create_eager_joins: .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in setup_context(self, query, context) 3725 with_polymorphic=self._with_polymorphic, 3726 only_load_props=query._only_load_props, -> 3727 polymorphic_discriminator=self._polymorphic_discriminator) 3728 3729 def __str__(self): .../lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in _setup_entity_query(context, mapper, query_entity, path, adapter, column_collection, with_polymorphic, only_load_props, polymorphic_discriminator, **kw) 257 column_collection=column_collection, 258 memoized_populators=quick_populators, --> 259 **kw 260 ) 261 .../lib/python2.7/site-packages/sqlalchemy/orm/interfaces.pyc in setup(self, context, entity, path, adapter, **kwargs) 515 else: 516 strat = self.strategy --> 517 strat.setup_query(context, entity, path, loader, adapter, **kwargs) 518 519 def create_row_processor( .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in setup_query(self, context, entity, path, loadopt, adapter, column_collection, parentmapper, **kwargs) 786 orig_query, leftmost_mapper, 787 leftmost_attr, leftmost_relationship, --> 788 entity.entity_zero 789 ) 790 .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in _generate_from_original_query(self, orig_query, leftmost_mapper, leftmost_attr, leftmost_relationship, orig_entity) 859 ent['entity'] for ent in orig_query.column_descriptions 860 ])), --> 861 False 862 ) 863 .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _set_select_from(self, obj, set_base_alias) 191 192 for from_obj in obj: --> 193 info = inspect(from_obj) 194 if hasattr(info, 'mapper') and \ 195 (info.is_mapper or info.is_aliased_class): .../lib/python2.7/site-packages/sqlalchemy/inspection.pyc in inspect(subject, raiseerr) 73 "No inspection system is " 74 "available for object of type %s" % ---> 75 type_) 76 return ret 77 NoInspectionAvailable: No inspection system is available for object of type In particular, a subqueried relationship (either through lazy='subquery' or subqueryload(A.b)) causes .exists() to fail. We obviously don't call subqueryload(A.b) directly with exists queries, but we have models using lazy='subquery' that we use .exists() queries for. My current workaround is A.query.options(lazyload('*')).exists(). Does this seem like a bug introduced in 1.1, or were there any intentional changes regarding use of .exists() queries? Thanks! -- 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 sqlalchemy+unsubscr...@googlegroups.com. To