<p dir="auto"><strong>Migrated issue, originally created by Erich Seifert (<a href="https://github.com/eseifert">@eseifert</a>)</strong></p>
<p dir="auto">At the moment, with SQLAlchemy 0.8.4, introspecting a materialized view in a PostgreSQL 9.3.2 database fails with:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="  File &quot;/tmp/test/meterializedview.py&quot;, line 16, in __init__
    self.MaterializedView = Table('mytable', MetaData(), autoload=True, autoload_with=self.db)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py&quot;, line 332, in __new__
    table._init(name, metadata, *args, **kw)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py&quot;, line 396, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/schema.py&quot;, line 413, in _autoload
    self, include_columns, exclude_columns
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1595, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1118, in run_callable
    return callable_(self, *args, **kwargs)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py&quot;, line 262, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 414, in reflecttable
    for col_d in self.get_columns(table_name, schema, **tblkw):
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 254, in get_columns
    **kw)
  File &quot;&lt;string&gt;&quot;, line 1, in &lt;lambda&gt;
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 49, in cache
    ret = fn(self, con, *args, **kw)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py&quot;, line 1678, in get_columns
    info_cache=kw.get('info_cache'))
  File &quot;&lt;string&gt;&quot;, line 1, in &lt;lambda&gt;
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py&quot;, line 49, in cache
    ret = fn(self, con, *args, **kw)
  File &quot;/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py&quot;, 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')"><pre class="notranslate"><code class="notranslate">  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 "&lt;string&gt;", line 1, in &lt;lambda&gt;
  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 "&lt;string&gt;", line 1, in &lt;lambda&gt;
  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')
</code></pre></div>
<p dir="auto">However, a new <code class="notranslate">relkind</code> value <code class="notranslate">m</code> was introduced for materialized views, which isn't handled at the moment.</p>
<p dir="auto">To (partly) solve the problem for me, i just added <code class="notranslate">m</code> to the where clause:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="AND c.relname = :table_name AND c.relkind in ('r','v','m')"><pre class="notranslate"><code class="notranslate">AND c.relname = :table_name AND c.relkind in ('r','v','m')
</code></pre></div>
<p dir="auto">There are several other cases where <code class="notranslate">relkind</code> is used (<code class="notranslate">get_table_names</code>, <code class="notranslate">get_view_names</code>, <code class="notranslate">get_indexes</code>, 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.</p>
<p dir="auto">As far as I can see, the same issue exists in SQLAlchemy 0.9 (browser:lib/sqlalchemy/dialects/postgresql/base.py, rev <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/164bff07496c345c3c57a8b26439aa6a0fbce3b8/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/164bff07496c345c3c57a8b26439aa6a0fbce3b8"><tt>164bff0</tt></a>).</p>
<p dir="auto"><strong>Migrated issue, originally created by Christophe Biocca (<a href="https://github.com/christophebiocca">@christophebiocca</a>)</strong></p>
<p dir="auto">I attached a reproduction of the bug. The important bit:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# 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()"><pre class="notranslate"><code class="notranslate"># 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()
</code></pre></div>
<p dir="auto">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).</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3224/union_and_join_filter.py">union_and_join_filter.py</a></p>