Issue 1:
Migrated issue, originally created by Erich Seifert (@eseifert)

At the moment, with SQLAlchemy 0.8.4, introspecting a materialized view in a PostgreSQL 9.3.2 database fails with:

File "/tmp/test/meterializedview.py", line 16, in __init__
self.MaterializedView = Table('mytable', MetaData(), autoload=True, autoload_with=self.db)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line 332, in __new__
table._init(name, metadata, *args, **kw)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line 396, in _init
self._autoload(metadata, autoload_with, include_columns)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py", line 413, in _autoload
self, include_columns, exclude_columns
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1595, in run_callable
return conn.run_callable(callable_, *args, **kwargs)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1118, in run_callable
return callable_(self, *args, **kwargs)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 262, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 414, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 254, in get_columns
**kw)
File "<string>", line 1, in <lambda>
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 49, in cache
ret = fn(self, con, *args, **kw)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1678, in get_columns
info_cache=kw.get('info_cache'))
File "<string>", line 1, in <lambda>
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 49, in cache
ret = fn(self, con, *args, **kw)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1593, in get_table_oid
raise exc.NoSuchTableError(table_name)

`#!sql
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE (%s)
AND c.relname = :table_name AND c.relkind in ('r','v')

However, a new relkind value m was introduced for materialized views, which isn't handled at the moment.

To (partly) solve the problem for me, i just added m to the where clause:

AND c.relname = :table_name AND c.relkind in ('r','v','m')

There are several other cases where relkind is used (get_table_names, get_view_names, get_indexes, etc.) where it isn't clear how to deal with materialized view because they are a hybrid of tables and views and can have their own indexes and table spaces.

As far as I can see, the same issue exists in SQLAlchemy 0.9 (browser:lib/sqlalchemy/dialects/postgresql/base.py, rev 164bff0).

Issue 2:
Migrated issue, originally created by Christophe Biocca (@christophebiocca)

I attached a reproduction of the bug. The important bit:

# This doesn't filter both side of the union, and the generated sql looks really wrong.
session.query(B).join(A).union(session.query(B).join(A)).filter(A.id == '2').count()

Simple workaround was to duplicate the filter call to each query before the union. To be honest, I'm not even sure the filter call is valid (but at least it should throw an error, not let everything go through).

Attachments: union_and_join_filter.py