<p dir="auto"><strong>Migrated issue, originally created by anti-social (<a href="https://github.com/anti-social">@anti-social</a>)</strong></p>
<p dir="auto">After the <a href="https://bitbucket.org/zzzeek/sqlalchemy/commits/ceeb033054f09db3eccbde3fad1941ec42919a54" rel="nofollow">commit</a> next test case begun to fail:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class FilterUniqueJoinTest(fixtures.MappedTest, AssertsCompiledSQL):
    run_setup_mappers = 'once'

    @classmethod
    def define_tables(cls, metadata):
        Table('users', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('name', String(50)),
              Column('date_created', DateTime, default=datetime.datetime.now))

        Table('orders', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('user_id', Integer, ForeignKey('users.id')))

    @classmethod
    def setup_classes(cls):
        users = cls.tables.users
        orders = cls.tables.orders

        class User(cls.Comparable):
            pass

        class Order(cls.Comparable):
            pass

        mapper(User, users, properties={
            'orders': relationship(Order, order_by=users.c.id.desc())
        })

        mapper(Order, orders)

    @classmethod
    def insert_data(cls):
        User = cls.classes.User
        Order = cls.classes.Order

        sess = create_session()
        o1 = Order()
        o2 = Order()
        o3 = Order()

        u1 = User(name='Mike')
        u1.orders = [o1, o2]
        u2 = User(name='John')
        u2.orders = [o3]

        sess.add(u1)
        sess.add(u2)
        sess.flush()
        sess.close()

    def test_filter_unique_column(self):
        User = self.classes.User

        sess = create_session()

        users = (
            sess.query(
                User.date_created.label('dt'),
                User,
            )
            .options(joinedload(User.orders))
            .all()
        )
        assert len(users) == 2

    def test_filter_unique_func_column(self):
        User = self.classes.User

        sess = create_session()

        users = (
            sess.query(
                func.date(User.date_created, 'start of month').label('month'),
                User,
            )
            .options(joinedload(User.orders))
            .all()
        )
        assert len(users) == 2
"><pre class="notranslate"><code class="notranslate">class FilterUniqueJoinTest(fixtures.MappedTest, AssertsCompiledSQL):
    run_setup_mappers = 'once'

    @classmethod
    def define_tables(cls, metadata):
        Table('users', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('name', String(50)),
              Column('date_created', DateTime, default=datetime.datetime.now))

        Table('orders', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('user_id', Integer, ForeignKey('users.id')))

    @classmethod
    def setup_classes(cls):
        users = cls.tables.users
        orders = cls.tables.orders

        class User(cls.Comparable):
            pass

        class Order(cls.Comparable):
            pass

        mapper(User, users, properties={
            'orders': relationship(Order, order_by=users.c.id.desc())
        })

        mapper(Order, orders)

    @classmethod
    def insert_data(cls):
        User = cls.classes.User
        Order = cls.classes.Order

        sess = create_session()
        o1 = Order()
        o2 = Order()
        o3 = Order()

        u1 = User(name='Mike')
        u1.orders = [o1, o2]
        u2 = User(name='John')
        u2.orders = [o3]

        sess.add(u1)
        sess.add(u2)
        sess.flush()
        sess.close()

    def test_filter_unique_column(self):
        User = self.classes.User

        sess = create_session()

        users = (
            sess.query(
                User.date_created.label('dt'),
                User,
            )
            .options(joinedload(User.orders))
            .all()
        )
        assert len(users) == 2

    def test_filter_unique_func_column(self):
        User = self.classes.User

        sess = create_session()

        users = (
            sess.query(
                func.date(User.date_created, 'start of month').label('month'),
                User,
            )
            .options(joinedload(User.orders))
            .all()
        )
        assert len(users) == 2

</code></pre></div>
<p dir="auto">Sqlalchemy 1.0:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_column PASSED
test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_func_column PASSED"><pre class="notranslate"><code class="notranslate">test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_column PASSED
test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_func_column PASSED
</code></pre></div>
<p dir="auto">Sqlalchemy 1.1:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_column PASSED
test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_func_column FAILED
"><pre class="notranslate"><code class="notranslate">test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_column PASSED
test/orm/test_joins.py::FilterUniqueJoinTest::test_filter_unique_func_column FAILED

</code></pre></div>
<p dir="auto">Is it expected behaviour?</p>
<p dir="auto"><strong>Migrated issue, originally created by adridg (<a href="https://github.com/adridg">@adridg</a>)</strong></p>
<p dir="auto">With SQL Server, you can create columns of type VARCHAR(MAX). This is the modern rendition of NTEXT or TEXT, and SQL Alchemy translates String() into VARCHAR(MAX). With SQL Server 2012 or later, this happens automatically, and it's described in <a href="http://docs.sqlalchemy.org/en/latest/changelog/changelog_10.html#change-f520106ec3455eaa056110c048aa4862" rel="nofollow">http://docs.sqlalchemy.org/en/latest/changelog/changelog_10.html#change-f520106ec3455eaa056110c048aa4862</a> .</p>
<p dir="auto">However, on reflection (through the inspector) you can end up with a VARCHAR (sql type-)object that has a length of "max" (i.e. a string). Printing that object causes an exception. As a very simple and contrived example:</p>
<p dir="auto">from sqlalchemy import VARCHAR<br>
print VARCHAR()<br>
print VARCHAR(80)<br>
print VARCHAR("max")  # Fails in _render_type_string</p>
<p dir="auto">Attached find a simple test program that creates a table with a String() column, then reflects it to discover the VARCHAR(MAX) column.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3504/t.py">t.py</a></p>