Re: [sqlalchemy] Potential bug with .exists() queries and subqueryloads

2017-07-18 Thread vineet 

>
> 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

2017-07-18 Thread Mike Bayer 
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
>