Re: [Sqlalchemy-tickets] [sqlalchemy] #2668: auto_correlate fails if all froms are existing in a superquery

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

#2668: auto_correlate fails if all froms are existing in a superquery
-----------------------------------+-------------------------------
      Reporter:  lukecyca          |      Owner:  zzzeek
          Type:  defect            |     Status:  reopened
      Priority:  medium            |  Milestone:  0.8.0final
     Component:  orm               |   Severity:  major - 1-3 hours
    Resolution:                    |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by avoidscorn):

 * status:  closed => reopened
 * cc: sqlalchemy@… (added)
 * resolution:  fixed =>


Comment:

 This change causes problems for me, as I rely on "invalid" SELECT-used-
 as-a-FROM behavior. PostgreSQL at least supports it. My use case: given a
 parent table and child table, find the parent whose max-child satisfies
 some condition.

 Example:
 {{{#!python
 import sqlalchemy as sa

 metadata = sa.MetaData()
 parent = sa.Table("parent", metadata,
     sa.Column("id", sa.Integer, primary_key=True))
 child = sa.Table("child", metadata,
     sa.Column("id", sa.Integer, primary_key=True),
     sa.Column("parent_id", sa.Integer, sa.ForeignKey("parent.id")),
     sa.Column("pos", sa.Integer))

 # Find the max-child for a given parent.
 child_q = sa.select([child])
 child_q = child_q.where(child.c.parent_id == parent.c.id)
 child_q = child_q.order_by(child.c.pos)
 child_q = child_q.limit(1)
 child_q = child_q.correlate(parent)
 child_q = child_q.alias("max_child")

 max_child_q = sa.select([1], from_obj=child_q)
 # Arbitrary max-child condition.
 max_child_q = max_child_q.where(child_q.c.id == 1)

 q = sa.select([parent])
 q = q.where(sa.exists(max_child_q))
 print q
 }}}

 Produces this SQL:
 {{{#!sql
 SELECT parent.id
 FROM parent
 WHERE EXISTS (SELECT 1
 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) AS max_child
 WHERE max_child.id = :id_1)
 }}}

 Note the unwanted "parent" FROM clause in the innermost SELECT. It does
 not show up in 0.7.x.

 I realize there are more efficient ways to do max-child queries, but this
 method is convenient when the code that generates the max-child clause is
 independent from the code that creates the overall query.

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