[Sqlalchemy-tickets] Issue #4019: Unable to access outside tables within subqueries in FROM (zzzeek/sqlalchemy)

[Volker Diels-G. <iss...@bi...>]

New issue 4019: Unable to access outside tables within subqueries in FROM
https://bitbucket.org/zzzeek/sqlalchemy/issues/4019/unable-to-access-outside-tables-within

Volker Diels-Grabsch:

Wrong SQL code is produced when a sub query appears within a FROM clause and tries to access an outside table.

Usually a sub query is able to access outside tables. However, when a sub query appears within a FROM clause, it is not able to access outside tables. Instead an unwanted additional instance of the table is added to the sub query.

For example, consider two tables `a` and `b` with a sub query `e`. When sticking these together to `query`, the outer table `a` should be used in the sub query `e`:

```
#!python

from sqlalchemy import *

a = table('a', column('w'), column('x'))
b = table('b', column('y'), column('z'))

e = select([b.c.y]).where(a.c.x == b.c.z).alias('e')

query = select([
    a.c.w,
    e.c.y.label('f'),
])

print(str(query))
```

This produces the following wrong SQL code with an unwanted inner select from `a`:


```
#!sql

SELECT a.w, e.y AS f
FROM a, (SELECT b.y AS y
FROM b, a  -- ERROR
WHERE a.x = b.z) AS e
```

Expected SQL code:

```
#!sql

SELECT a.w, e.y AS f
FROM a, (SELECT b.y AS y
FROM b  -- OK
WHERE a.x = b.z) AS e
```

For comparison, when putting the sub select into the SELECT clause rather than the FROM clause, everything works fine:

```
#!python

from sqlalchemy import *

a = table('a', column('w'), column('x'))
b = table('b', column('y'), column('z'))

e = select([b.c.y]).where(a.c.x == b.c.z).alias('e')

query = select([
    a.c.w,
    e.as_scalar().label('f'),  # Difference to first example
])

print(str(query))
```

This produces correct SQL code without the unwanted inner select from `a`:

```
#!sql

SELECT a.w, (SELECT b.y 
FROM b  -- OK 
WHERE a.x = b.z) AS v 
FROM a
```

Of course, in our more complex production code, the sub select cannot be easily moved from the FROM to the SELECT clause. The example above is just the minimal code I came up with that reproduces the issue.

For the sake of completeness, this code is intended to run on PostgreSQL >= 9.2. However, the dialect doesn't change anything in this bug report. In all examples, `str(query)` produces the same SQL code as `str(query.compile(dialect=postgresql.dialect()))`.