MSSQL - Missing NOLOCK hint when using a Table with a schema

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 525cc6fe0247a76201c173e535d8309333461afc). 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?

Real life usage

Comments (8)