Issue 1:
Migrated issue, originally created by Eliot (@saltycrane)

Using mssql, I don't see the NOLOCK hint in the generated SQL of my select query when I specify a schema in my Table. (I do see the hint if I don't specify a schema.) Here is a test case:

# -*- 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)')

Here is the console output when I run it with sqlalchemy master branch (commit 525cc6f). The first tests passes but the second test fails.

$ 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 <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x7f09c573b590>
------------------------------------------------------------- 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 ======================================================

Is this a bug or is my usage incorrect?

Real life usage

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.

I am using the ORM and Flask-SQLAlchemy:

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()

SQLAlchemy==1.0.4, Flask-SQLAlchemy==2.0, pyodbc==3.0.5

My laptop: Ubuntu 14.10

Database: SQL Server ?2008 I think?

Issue 2:
Migrated issue, originally created by Andrey Zholos (@zholos)

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.

A subquery uses an alias that was not introduced (it could be introduced in an mssql "from" clause):

UPDATE [schema].sometable SET val=
(SELECT [#other].newval FROM [#other] WHERE sometable_1.sym = [#other].sym)

An alias is introduced but the "where" clause doesn't use it:

UPDATE [schema].sometable SET val=[#other].newval
FROM [schema].sometable AS sometable_1, [#other]
WHERE [schema].sometable.sym = [#other].sym

Both of these statements fail. Complete program attached.

Attachments: update.py | update_self.py