MSSQL Failing deletes with subquery + schema

[sqlalchemy-bot]

Issue created by paj (paj)

---

SA currently generates a query like this:

DELETE FROM tsr.vulnres WHERE tsr.vulnres.tool != ? AND tsr.vulnres.targetid IN (SELECT tsr.target.id \nFROM tsr.target AS target_1, tsr.testing AS testing_1 \nWHERE tsr.target.testingid = tsr.testing.id AND tsr.testing.tsrid = ?)

In the subquery, the tables are aliased, but the columns don't use the aliased name.

A likely fix for this is in MSSQLCompiler.visit_column, to check if it's in a subquery inside a delete/update. However, is_subquery doesn't work at the moment, so that will need to be fixed first.

For SQL 2005 a workaround is to disable all the table aliasing.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

Removing milestone: 0.5.0 (automated comment)

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

also is 0.5 OK for this since I dont want to destabilize 0.4 ?

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

to get is_subquery() working inside of delete/update/insert, this should work:

Index: lib/sqlalchemy/sql/compiler.py
===================================================================
--- lib/sqlalchemy/sql/compiler.py  (revision 5145)
+++ lib/sqlalchemy/sql/compiler.py  (working copy)
@@ -475,7 +475,7 @@
         stack_entry = {'select':select}
         prev_entry = self.stack and self.stack[-1](-1) or None

-        if asfrom or (prev_entry and 'select' in prev_entry):
+        if asfrom or (prev_entry and 'from' in prev_entry):
             stack_entry['is_subquery']('is_subquery') = True
             stack_entry['iswrapper']('iswrapper') = iswrapper
             if not iswrapper and prev_entry and 'iswrapper' in prev_entry:

then MSSQL visit_column() would have to use the label name for the column when is_subquery is detected (and maybe also self.isdelete=True ?). To get the label name look at the logic within compiler.label_select_column().

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

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](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())

[sqlalchemy-bot]

Michael Trier (empty) wrote:

---

This issue was related to MSSQL aliasing the schemas. Not entirely sure why aliasing is required in this situation but looks like without it there are lots of issues that crop up. Fixed in 260c201f656ce3afe35f9ae069cdf46593d4dffb.

[sqlalchemy-bot]

paj (paj) wrote:

---

The desired SQL is this:

DELETE FROM tsr.vulnres WHERE tsr.vulnres.tool != ? AND tsr.vulnres.targetid IN (SELECT tsr.target.id \nFROM tsr.target AS target_1, tsr.testing AS testing_1 \nWHERE target_1.testingid = testing_1.id AND testing_1.tsrid = ?)

There are a few other options that would work, but that seems the closest to what's generated already.

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): removed "0.5.0" milestone

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): changed milestone from "0.4.xx" to "0.5.xx"

[sqlalchemy-bot]

Changes by Michael Trier (empty): set state to "resolved"; changed milestone from "0.5.xx" to "0.5.0"