SQL Server VARCHAR(MAX) on reflection can't compile or print

[adridg]

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 http://docs.sqlalchemy.org/en/latest/changelog/changelog_10.html#change-f520106ec3455eaa056110c048aa4862 .

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:

from sqlalchemy import VARCHAR print VARCHAR() print VARCHAR(80) print VARCHAR("max") # Fails in _render_type_string

Attached find a simple test program that creates a table with a String() column, then reflects it to discover the VARCHAR(MAX) column.

[adridg]

Darn code formatting.

Anyway, filed as a minor bug because it's not something that happens every day -- you're not printing or compiling reflected types, usually. It goes without saying that you'll need a SQL Server instance to test this and will have to fix up the engine connect-string in the example program.

The resulting backtrace looks like this:

  File "t.py", line 59, in <module>
    print c["name"], c["type"]
  File "sqlalchemy\sql\type_api.py", line 481, in __str__
    return unicode(self.compile()).\
  File "sqlalchemy\sql\type_api.py", line 468, in compile
    return dialect.type_compiler.process(self)
  File "sqlalchemy\sql\compiler.py", line 261, in process
    return type_._compiler_dispatch(self, **kw)
  File "sqlalchemy\sql\visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "sqlalchemy\sql\compiler.py", line 2531, in visit_VARCHAR
    return self._render_string_type(type_, "VARCHAR")
  File "sqlalchemy\sql\compiler.py", line 2519, in _render_string_type
    text += "(%d)" % type_.length
TypeError: %d format: a number is required, not str

[adridg]

Can't really tell if it's the reflector that's at fault -- for not understanding "max" -- or the fact that this code path is going straight to the generic compiler's visit_VARCHAR() -- while the MSSQL dialect's compiler has an adapted visit_VARCHAR().

[Mike Bayer]

• Fixed issue where the SQL Server dialect would reflect a string- or other variable-length column type with unbounded length by assigning the token "max" to the length attribute of the string. While using the "max" token explicitly is supported by the SQL Server dialect, it isn't part of the normal contract of the base string types, and instead the length should just be left as None. The dialect now assigns the length to None on reflection of the type so that the type behaves normally in other contexts. fixes #3504

→ <<cset dd6110eed335>>

[Mike Bayer]

thanks for reporting. normally, we don't consider dialect-specific types that can't stringify against the generic dialect to be a bug, e.g. to make this work you'd ideally be using str(type.compile(dialect=myengine.dialect)), which is definitely necessary if your types happen to be instances of VARBINARY, IMAGE, or NTEXT that are SQL-server only. However, in this case the "length" parameter of the base string types is expected to be an integer or None, where None represents "unlengthed", so having "max" there is not really appropriate.

I think part of what happens here is that there was a difference between None and "max", which we've changed a bit as a result of #3039, but I'm not sure if we took this into account within reflection in any case. I can see that maybe if one has "deprecate_large_types" set to False and they do a reflection of TEXT(), they'd expect a difference between "None" and "max", I'm not sure. I don't think it's going to cause an issue but if it does, we'll be back here.

[Mike Bayer]

Issue #3759 was marked as a duplicate of this issue.