Re: [Sqlalchemy-tickets] [sqlalchemy] #973: MSSQL Failing deletes with subquery + schema

[sqlalchemy <mi...@zz...>]

#973: MSSQL Failing deletes with subquery + schema
--------------------------------+-------------------------------------------
    Reporter:  paj              |       Owner:  ram              
        Type:  defect           |      Status:  new              
    Priority:  high             |   Milestone:  0.4.xx           
   Component:  mssql            |    Severity:  major - 1-3 hours
  Resolution:                   |    Keywords:                   
Status_field:  awaiting triage  |  
--------------------------------+-------------------------------------------
Comment (by zzzeek):

 whats the desired SQL here?  I can see that it has to do with schema-
 qualified table names needing to be alised.  But, only in subqueries ?

 anyway, the way that MS-SQL is creating the "alias" for tables is a little
 weak - within visit_select(), clause adaption should be used to return an
 entirely new select() statement with the proper aliasing built in.  The
 general technique is described below:

 {{{
 from sqlalchemy import *

 m = MetaData()
 t = Table('t1', m, Column('somedata', Integer), schema="someschema")

 s = select([t.c.somedata]).where(t.c.somedata==5)
 d = t.delete().where(t.c.somedata.in_(s))

 from sqlalchemy.databases import mssql

 # this prints the "wrong" SQL
 print d.compile(dialect=mssql.dialect())

 print "--------------------"

 # if visit_select did this, would be better
 from sqlalchemy.sql.util import find_tables, ClauseAdapter
 tables = find_tables(s)

 for t in find_tables(s):
     s = ClauseAdapter(t.alias()).traverse(s, clone=True)

 d = t.delete().where(t.c.somedata.in_(s))
 print d.compile(dialect=mssql.dialect())

 }}}

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/973#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python