Description
Migrated issue, originally created by paj (@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!
Attachments: bug-schema-minimal.py | alias-oid.patch
Metadata
Metadata
Assignees
Type
Projects
Relationships
Development
Participants
Activity
sqlalchemy-bot commentedon Feb 3, 2007
paj (@paj) wrote:
minimal test case
sqlalchemy-bot commentedon Feb 3, 2007
Changes by paj (@paj):
sqlalchemy-bot commentedon Feb 3, 2007
paj (@paj) wrote:
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.
sqlalchemy-bot commentedon Feb 6, 2007
paj (@paj) wrote:
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.
sqlalchemy-bot commentedon Feb 6, 2007
Anonymous wrote:
(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.
sqlalchemy-bot commentedon Feb 13, 2007
paj (@paj) wrote:
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 :-)
sqlalchemy-bot commentedon Feb 13, 2007
paj (@paj) wrote:
Proposed fix
sqlalchemy-bot commentedon Feb 13, 2007
Changes by paj (@paj):
sqlalchemy-bot commentedon Feb 14, 2007
Anonymous wrote:
(original author: ram) committed in rev 2315, thanks
sqlalchemy-bot commentedon Feb 14, 2007
Changes by Anonymous:
sqlalchemy-bot commentedon Apr 27, 2007
Michael Bayer (@zzzeek) wrote:
Milestone next micropoint release deleted
sqlalchemy-bot commentedon Apr 27, 2007
Changes by Michael Bayer (@zzzeek):
2 remaining items