<p dir="auto"><strong>Describe the bug</strong><br>
I have a query which worked in sqlalchemy version 1.3.9, and after upgrading to 1.4.4 It started throwing exception of "Missing property _is_subquery". the query purpose is to count how many shared indexes are between 2 tables.</p>
<p dir="auto"><strong>Expected behavior</strong><br>
return a number.</p>
<p dir="auto"><strong>Code to Reproduce</strong><br>
attached a code I pieced from my unit testing:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="table_a = Table(...)
table_b = select(
    [col for col in table_a.c if col.name != 'index'] + [(func.row_number().over()).label('index')]
)
index_a = table_a.c['index']
index_b = table_b.c['index']
count = select([func.count()])\
              .where(index_a.in_(select([index_b])))\
              .execute().scalar()"><pre class="notranslate"><span class="pl-s1">table_a</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(...)
<span class="pl-s1">table_b</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(
    [<span class="pl-s1">col</span> <span class="pl-k">for</span> <span class="pl-s1">col</span> <span class="pl-c1">in</span> <span class="pl-s1">table_a</span>.<span class="pl-s1">c</span> <span class="pl-k">if</span> <span class="pl-s1">col</span>.<span class="pl-s1">name</span> <span class="pl-c1">!=</span> <span class="pl-s">'index'</span>] <span class="pl-c1">+</span> [(<span class="pl-s1">func</span>.<span class="pl-en">row_number</span>().<span class="pl-en">over</span>()).<span class="pl-en">label</span>(<span class="pl-s">'index'</span>)]
)
<span class="pl-s1">index_a</span> <span class="pl-c1">=</span> <span class="pl-s1">table_a</span>.<span class="pl-s1">c</span>[<span class="pl-s">'index'</span>]
<span class="pl-s1">index_b</span> <span class="pl-c1">=</span> <span class="pl-s1">table_b</span>.<span class="pl-s1">c</span>[<span class="pl-s">'index'</span>]
<span class="pl-s1">count</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>([<span class="pl-s1">func</span>.<span class="pl-en">count</span>()])\
              .<span class="pl-en">where</span>(<span class="pl-s1">index_a</span>.<span class="pl-en">in_</span>(<span class="pl-en">select</span>([<span class="pl-s1">index_b</span>])))\
              .<span class="pl-en">execute</span>().<span class="pl-en">scalar</span>()</pre></div>
<p dir="auto">I've tried adding to the subquery <code class="notranslate">.subquery()</code> or <code class="notranslate">.alias('something')</code> but it didn't help</p>
<p dir="auto"><strong>Error</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="../tests.py:59: in run
    count = select([func.count()]).where(
&lt;string&gt;:2: in execute
    ???
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py:390: in warned
    return fn(*args, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:932: in execute
    return e._execute_clauseelement(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3064: in _execute_clauseelement
    return connection._execute_clauseelement(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1381: in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:535: in _compile_w_cache
    compiled_sql = self._compiler(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:568: in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:766: in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:455: in __init__
    self.string = self.process(self.statement, **compile_kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:490: in process
    return obj._compiler_dispatch(self, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py:81: in _compiler_dispatch
    return meth(self, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:2964: in visit_select
    compile_state = select_stmt._compile_state_factory(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:492: in create_for_statement
    return klass.create_for_statement(statement, compiler, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:490: in create_for_statement
    return cls(statement, compiler, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py:4065: in __init__
    self.froms = self._get_froms(statement)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = &lt;sqlalchemy.sql.selectable.SelectState object at 0x7fb64a4c2680&gt;
statement = &lt;sqlalchemy.sql.selectable.Select object at 0x7fb62a881400&gt;

    def _get_froms(self, statement):
        seen = set()
        froms = []
    
        for item in itertools.chain(
            itertools.chain.from_iterable(
                [element._from_objects for element in statement._raw_columns]
            ),
            itertools.chain.from_iterable(
                [
                    element._from_objects
                    for element in statement._where_criteria
                ]
            ),
            self.from_clauses,
        ):
&gt;           if item._is_subquery and item.element is statement:
E           AttributeError: 'Select' object has no attribute '_is_subquery'"><pre class="notranslate"><code class="notranslate">../tests.py:59: in run
    count = select([func.count()]).where(
&lt;string&gt;:2: in execute
    ???
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py:390: in warned
    return fn(*args, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:932: in execute
    return e._execute_clauseelement(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3064: in _execute_clauseelement
    return connection._execute_clauseelement(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1381: in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:535: in _compile_w_cache
    compiled_sql = self._compiler(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:568: in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:766: in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:455: in __init__
    self.string = self.process(self.statement, **compile_kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:490: in process
    return obj._compiler_dispatch(self, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py:81: in _compiler_dispatch
    return meth(self, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:2964: in visit_select
    compile_state = select_stmt._compile_state_factory(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:492: in create_for_statement
    return klass.create_for_statement(statement, compiler, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:490: in create_for_statement
    return cls(statement, compiler, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py:4065: in __init__
    self.froms = self._get_froms(statement)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = &lt;sqlalchemy.sql.selectable.SelectState object at 0x7fb64a4c2680&gt;
statement = &lt;sqlalchemy.sql.selectable.Select object at 0x7fb62a881400&gt;

    def _get_froms(self, statement):
        seen = set()
        froms = []
    
        for item in itertools.chain(
            itertools.chain.from_iterable(
                [element._from_objects for element in statement._raw_columns]
            ),
            itertools.chain.from_iterable(
                [
                    element._from_objects
                    for element in statement._where_criteria
                ]
            ),
            self.from_clauses,
        ):
&gt;           if item._is_subquery and item.element is statement:
E           AttributeError: 'Select' object has no attribute '_is_subquery'
</code></pre></div>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: Mac BigSur</li>
<li>Python: 3.8.6</li>
<li>SQLAlchemy: 1.4.4</li>
<li>Database: PrestoDB</li>
<li>DBAPI:</li>
</ul>
<p dir="auto">Thanks</p>
<p dir="auto">I use a <code class="notranslate">.in_(select([...]))</code> construction: something like <code class="notranslate">select(scan.xxx).where(scan.id.in_(select(port.scan.id.where(...))))</code> (the exact code is <a href="https://github.com/cea-sec/ivre/blob/master/ivre/db/sql/__init__.py#L1955">here</a>).</p>
<p dir="auto">this code used to work (1.3.23) and no longer works (1.4.4). I would like to understand whether my code was wrong in the first place or if the API has changed, and how I should code that.</p>
<p dir="auto">Using <code class="notranslate">git bisect</code>, <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/a9b62055bfa61c11e9fe0b2984437e2c3e32bf0e/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/a9b62055bfa61c11e9fe0b2984437e2c3e32bf0e"><tt>a9b6205</tt></a> seems to be the "guilty" commit.</p>
<p dir="auto">With the current SQLAchemy code, I get the following stacktrace:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;&lt;string&gt;&quot;, line 1, in &lt;module&gt;
  File &quot;/VENV/lib/python3.9/site-packages/ivre/db/sql/__init__.py&quot;, line 1497, in count
    return self.db.execute(
  File &quot;&lt;string&gt;&quot;, line 2, in execute
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py&quot;, line 390, in warned
    return fn(*args, **kwargs)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 3009, in execute
    return connection.execute(statement, *multiparams, **params)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py&quot;, line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py&quot;, line 1381, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py&quot;, line 533, in _compile_w_cache
    compiled_sql = self._compiler(
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py&quot;, line 566, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py&quot;, line 766, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py&quot;, line 455, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py&quot;, line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py&quot;, line 81, in _compiler_dispatch
    return meth(self, **kw)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py&quot;, line 2964, in visit_select
    compile_state = select_stmt._compile_state_factory(
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/base.py&quot;, line 492, in create_for_statement
    return klass.create_for_statement(statement, compiler, **kw)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/orm/context.py&quot;, line 603, in create_for_statement
    SelectState.__init__(self, self.statement, compiler, **kw)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py&quot;, line 4065, in __init__
    self.froms = self._get_froms(statement)
  File &quot;/VENV/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py&quot;, line 4145, in _get_froms
    if item._is_subquery and item.element is statement:
AttributeError: 'Select' object has no attribute '_is_subquery'"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "&lt;string&gt;", line 1, in &lt;module&gt;
  File "/VENV/lib/python3.9/site-packages/ivre/db/sql/__init__.py", line 1497, in count
    return self.db.execute(
  File "&lt;string&gt;", line 2, in execute
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 390, in warned
    return fn(*args, **kwargs)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3009, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 533, in _compile_w_cache
    compiled_sql = self._compiler(
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 566, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 766, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 2964, in visit_select
    compile_state = select_stmt._compile_state_factory(
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 492, in create_for_statement
    return klass.create_for_statement(statement, compiler, **kw)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 603, in create_for_statement
    SelectState.__init__(self, self.statement, compiler, **kw)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 4065, in __init__
    self.froms = self._get_froms(statement)
  File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 4145, in _get_froms
    if item._is_subquery and item.element is statement:
AttributeError: 'Select' object has no attribute '_is_subquery'
</code></pre></div>
<p dir="auto">Thanks in advance!</p>