<p dir="auto"><strong>Migrated issue, originally created by Eliot (<a href="https://github.com/saltycrane">@saltycrane</a>)</strong></p>
<p dir="auto">Using mssql, I don't see the NOLOCK hint in the generated SQL of my <code class="notranslate">select</code> query when I specify a <code class="notranslate">schema</code> in my <code class="notranslate">Table</code>. (I do see the hint if I don't specify a schema.) Here is a test case:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# -*- encoding: utf-8
from sqlalchemy import *
from sqlalchemy.databases import mssql
from sqlalchemy.testing import fixtures, AssertsCompiledSQL


class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mssql.dialect()

    def test_select_without_schema_with_nolock(self):
        metadata = MetaData()
        t = Table(
            'sometable', metadata,
            Column('somecolumn', Integer),
        )
        self.assert_compile(select([t]).with_hint(t, &quot;WITH (NOLOCK)&quot;),
                            'SELECT sometable.somecolumn '
                            'FROM sometable WITH (NOLOCK)')

    def test_select_with_schema_with_nolock(self):
        metadata = MetaData()
        t = Table(
            'sometable', metadata,
            Column('somecolumn', Integer),
            schema='dlr',
        )
        self.assert_compile(select([t]).with_hint(t, &quot;WITH (NOLOCK)&quot;),
                            'SELECT sometable_1.somecolumn '
                            'FROM dlr.sometable AS sometable_1 WITH (NOLOCK)')"><pre class="notranslate"><code class="notranslate"># -*- encoding: utf-8
from sqlalchemy import *
from sqlalchemy.databases import mssql
from sqlalchemy.testing import fixtures, AssertsCompiledSQL


class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mssql.dialect()

    def test_select_without_schema_with_nolock(self):
        metadata = MetaData()
        t = Table(
            'sometable', metadata,
            Column('somecolumn', Integer),
        )
        self.assert_compile(select([t]).with_hint(t, "WITH (NOLOCK)"),
                            'SELECT sometable.somecolumn '
                            'FROM sometable WITH (NOLOCK)')

    def test_select_with_schema_with_nolock(self):
        metadata = MetaData()
        t = Table(
            'sometable', metadata,
            Column('somecolumn', Integer),
            schema='dlr',
        )
        self.assert_compile(select([t]).with_hint(t, "WITH (NOLOCK)"),
                            'SELECT sometable_1.somecolumn '
                            'FROM dlr.sometable AS sometable_1 WITH (NOLOCK)')
</code></pre></div>
<p dir="auto">Here is the console output when I run it with sqlalchemy master branch (commit <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/525cc6fe0247a76201c173e535d8309333461afc/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/525cc6fe0247a76201c173e535d8309333461afc"><tt>525cc6f</tt></a>). The first tests passes but the second test fails.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="$ py.test test/test_mssql_schema_nolock.py
============================================================= test session starts ==============================================================
platform linux2 -- Python 2.7.8 -- py-1.4.27 -- pytest-2.7.1 -- /home/eliot/src/bb/sqlalchemy/venv/bin/python
rootdir: /home/eliot/src/bb/sqlalchemy, inifile: setup.cfg
collected 2 items 

test/test_mssql_schema_nolock.py::CompileTest::test_select_with_schema_with_nolock FAILED
test/test_mssql_schema_nolock.py::CompileTest::test_select_without_schema_with_nolock PASSED

=================================================================== FAILURES ===================================================================
_______________________________________________ CompileTest.test_select_with_schema_with_nolock ________________________________________________
Traceback (most recent call last):
  File &quot;/home/eliot/src/bb/sqlalchemy/test/test_mssql_schema_nolock.py&quot;, line 28, in test_select_with_schema_with_nolock
    'SELECT sometable_1.somecolumn '
  File &quot;/home/eliot/src/bb/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py&quot;, line 314, in assert_compile
    eq_(cc, result, &quot;%r != %r on dialect %r&quot; % (cc, result, dialect))
  File &quot;/home/eliot/src/bb/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py&quot;, line 211, in eq_
    assert a == b, msg or &quot;%r != %r&quot; % (a, b)
AssertionError: u'SELECT sometable_1.somecolumn FROM dlr.sometable AS sometable_1' != 'SELECT sometable_1.somecolumn FROM dlr.sometable AS sometable_1 WITH (NOLOCK)' on dialect &lt;sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x7f09c573b590&gt;
------------------------------------------------------------- Captured stdout call -------------------------------------------------------------

SQL String:
SELECT sometable_1.somecolumn 
FROM dlr.sometable AS sometable_1{}
=========================================================== short test summary info ============================================================
FAIL test/test_mssql_schema_nolock.py::CompileTest::()::test_select_with_schema_with_nolock
====================================================== 1 failed, 1 passed in 0.07 seconds ======================================================"><pre class="notranslate"><code class="notranslate">$ py.test test/test_mssql_schema_nolock.py
============================================================= test session starts ==============================================================
platform linux2 -- Python 2.7.8 -- py-1.4.27 -- pytest-2.7.1 -- /home/eliot/src/bb/sqlalchemy/venv/bin/python
rootdir: /home/eliot/src/bb/sqlalchemy, inifile: setup.cfg
collected 2 items 

test/test_mssql_schema_nolock.py::CompileTest::test_select_with_schema_with_nolock FAILED
test/test_mssql_schema_nolock.py::CompileTest::test_select_without_schema_with_nolock PASSED

=================================================================== FAILURES ===================================================================
_______________________________________________ CompileTest.test_select_with_schema_with_nolock ________________________________________________
Traceback (most recent call last):
  File "/home/eliot/src/bb/sqlalchemy/test/test_mssql_schema_nolock.py", line 28, in test_select_with_schema_with_nolock
    'SELECT sometable_1.somecolumn '
  File "/home/eliot/src/bb/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 314, in assert_compile
    eq_(cc, result, "%r != %r on dialect %r" % (cc, result, dialect))
  File "/home/eliot/src/bb/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 211, in eq_
    assert a == b, msg or "%r != %r" % (a, b)
AssertionError: u'SELECT sometable_1.somecolumn FROM dlr.sometable AS sometable_1' != 'SELECT sometable_1.somecolumn FROM dlr.sometable AS sometable_1 WITH (NOLOCK)' on dialect &lt;sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x7f09c573b590&gt;
------------------------------------------------------------- Captured stdout call -------------------------------------------------------------

SQL String:
SELECT sometable_1.somecolumn 
FROM dlr.sometable AS sometable_1{}
=========================================================== short test summary info ============================================================
FAIL test/test_mssql_schema_nolock.py::CompileTest::()::test_select_with_schema_with_nolock
====================================================== 1 failed, 1 passed in 0.07 seconds ======================================================
</code></pre></div>
<p dir="auto">Is this a bug or is my usage incorrect?</p>
<h3 dir="auto">Real life usage</h3>
<p dir="auto">I don't think the following information is needed, but here are notes about my real life usage. I can provide further details if it is required.</p>
<ul dir="auto">
<li>
<p dir="auto">I am using the ORM and Flask-SQLAlchemy:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content=" class User(db.Model):
     __tablename__ = 'User'
     __table_args__ = (
         {'schema': 'dlr'},
     )

     user_id = db.Column(
         'UserId', db.BigInteger, nullable=False, primary_key=True,
         autoincrement=False)

 db.session.query(User).with_hint(User, 'WITH (NOLOCK)').order_by(User.user_id).all()"><pre class="notranslate"><code class="notranslate"> class User(db.Model):
     __tablename__ = 'User'
     __table_args__ = (
         {'schema': 'dlr'},
     )

     user_id = db.Column(
         'UserId', db.BigInteger, nullable=False, primary_key=True,
         autoincrement=False)

 db.session.query(User).with_hint(User, 'WITH (NOLOCK)').order_by(User.user_id).all()
</code></pre></div>
</li>
<li>
<p dir="auto">SQLAlchemy==1.0.4, Flask-SQLAlchemy==2.0, pyodbc==3.0.5</p>
</li>
<li>
<p dir="auto">My laptop: Ubuntu 14.10</p>
</li>
<li>
<p dir="auto">Database: SQL Server ?2008 I think?</p>
</li>
</ul>
<p dir="auto"><strong>Migrated issue, originally created by Andrey Zholos (<a href="https://github.com/zholos">@zholos</a>)</strong></p>
<p dir="auto">When updating a table with a specified schema name, some parts of the generated statement refer to the table with an alias and some parts don't.</p>
<p dir="auto">A subquery uses an alias that was not introduced (it could be introduced in an mssql "from" clause):</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="UPDATE [schema].sometable SET val=
(SELECT [#other].newval FROM [#other] WHERE sometable_1.sym = [#other].sym)"><pre class="notranslate"><code class="notranslate">UPDATE [schema].sometable SET val=
(SELECT [#other].newval FROM [#other] WHERE sometable_1.sym = [#other].sym)
</code></pre></div>
<p dir="auto">An alias is introduced but the "where" clause doesn't use it:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="UPDATE [schema].sometable SET val=[#other].newval
FROM [schema].sometable AS sometable_1, [#other]
WHERE [schema].sometable.sym = [#other].sym"><pre class="notranslate"><code class="notranslate">UPDATE [schema].sometable SET val=[#other].newval
FROM [schema].sometable AS sometable_1, [#other]
WHERE [schema].sometable.sym = [#other].sym
</code></pre></div>
<p dir="auto">Both of these statements fail. Complete program attached.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3424/update.py">update.py</a> | <a href="../wiki/imported_issue_attachments/3424/update_self.py">update_self.py</a></p>