Re: [Sqlalchemy-tickets] [sqlalchemy] #2746: Add a flag to explicitly enable correlation even when the subquery has `asfrom=True`

[sqlalchemy <mi...@zz...>]

#2746: Add a flag to explicitly enable correlation even when the subquery has
`asfrom=True`
------------------------------+----------------------------------
      Reporter:  sayap        |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  highest      |  Milestone:  0.8.xx
     Component:  sql          |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  medium => highest
 * status_field:  in queue => needs tests
 * milestone:  0.9.0 => 0.8.xx


Comment:

 See also #2668 which we'll close as a dupe here, we need to consider
 propagating the correlation list recursively:

 {{{
 #!python
 --- a/lib/sqlalchemy/sql/compiler.py
 +++ b/lib/sqlalchemy/sql/compiler.py
 @@ -1241,6 +1241,8 @@ class SQLCompiler(engine.Compiled):
          froms = select._get_display_froms(existingfroms, asfrom=asfrom)

          correlate_froms = set(sql._from_objects(*froms))
 +        if existingfroms:
 +            correlate_froms = correlate_froms.union(existingfroms)


          self.stack.append({'from': correlate_froms,
 }}}

 here's the test case for that:

 {{{
 #!python
 from sqlalchemy.sql import table, column, select, exists

 p = table('parent', column('id'))
 c = table('child', column('id'), column('parent_id'), column('pos'))

 s = c.select().where(c.c.parent_id == p.c.id).order_by(c.c.pos).limit(1)
 s = s.correlate(p, froms=True)
 s = exists().select_from(s).where(s.c.id == 1)
 s = select([p]).where(s)
 print s

 }}}

 we want:

 {{{
 SELECT parent.id
 FROM parent
 WHERE EXISTS (SELECT *
 FROM (SELECT child.id AS id, child.parent_id AS parent_id, child.pos AS
 pos
 FROM child
 WHERE child.parent_id = parent.id ORDER BY child.pos
  LIMIT :param_1)
 WHERE id = :id_1)
 }}}

 and not:

 {{{
 SELECT parent.id
 FROM parent
 WHERE EXISTS (SELECT *
 FROM (SELECT child.id AS id, child.parent_id AS parent_id, child.pos AS
 pos
 FROM child, parent
 WHERE child.parent_id = parent.id ORDER BY child.pos
  LIMIT :param_1)
 WHERE id = :id_1)
 }}}


 and not

 {{{
 SELECT parent.id
 FROM parent
 WHERE EXISTS (SELECT *
 FROM (SELECT child.id AS id, child.parent_id AS parent_id, child.pos AS
 pos
 FROM child, parent
 WHERE child.parent_id = parent.id ORDER BY child.pos
  LIMIT :param_1)
 WHERE id = :id_1)
 }}}

 I'm a little concerned about propagating the FROMs all the way in like
 that but no tests fail, and I'm getting the impression that when you have
 "SELECT ( SELECT  ( SELECT ) ) ", those selects are typically going to be
 nested in the FROM clause and this new "froms" flag is a nice way of
 ensuring it doesn't happen by default.

 updated patch is attached and i think we have to make this 0.8 also since
 people from 0.7 are getting hit by it.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2746#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python