query.get() fails on tables with a schema

[paj]

MSSQL supports schemas (as in schema.table) and SA supports this. However, a simple query.get() fails with a KeyError inside MSSQLCompiler.visit_column. A minimal test case is attached.

I have found a workaround, which is to manually specify an order_by for the mapper. Replacing the mapper creation in the test case with: m=mapper(T, t, order_by=t.columns'id') Makes the test work.

The test case also works if you don't have a schema on the table. It seems the main difference having a schema makes is that the table is aliased in the query. This triggers code in MSSQLCompiler.visit_column to alias the column as well. That works ok for the columns on the table, but the default_order_by column is a bit different; it is an oid column. FromClause.corresponding_column returns an object that is not part of the table, and this causes a KeyError in MSSQLCompiler.

Right now I'm not sure exactly which bit of this is at fault, but I will have a look at how some other databases with schemas handle this for some inspiration!

[paj]

minimal test case

[paj]

Hmmm, MSSQL is the only DB that considers schemas explicitly. The others just use the functionality in ansisql. I notice that simply removing visit_table, visit_alias and visit_column from MSSQLCompiler makes my simple test case work.

Why does MSSQL do the aliasing? If it's just a hangover from old days when ansisql didn't support schemas, I guess we can remove it.

[paj]

Rick Morrison explains: the aliasing is required by SQL server when performing mixed-schema queries on SQL Server 2000. The table reflection queries rely on this, so it can't be removed just yet.

[Former user]

(original author: ram) One possible workaround would be to set order_by to None if not set otherwise. This would break queries doing cross-schema selects that also depend on the default ordering - but that's a pretty small corner case.

[paj]

Ok, I think I have found the proper solution to this!

And it's frustratingly simple! Just a single line, and kind of obvious when you see it :-)

[paj]

Proposed fix

[Former user]

(original author: ram) committed in rev 2315, thanks

[Mike Bayer]

Milestone next micropoint release deleted

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)